Examples: Geospatial Single-Table Predicates - Teradata Vantage NewSQL Engine - 16.20

Teradata Vantage™ Geospatial Data Types

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1181-162K

The following examples show the use of geospatial single-table predicates that enable the optimizer to use a geospatial index for data access.

CREATE TABLE StatT2
 (a INT,b CHAR(20000),sp ST_Geometry)
 INDEX(sp);

INSERT StatT2(1, 'Teradata01','Point(10 10 10)');


The number of rows inserted into the table, and the nature of the Geospatial Data itself will play a significant role in whether the Optimizer chooses to use the index.

SELECT a,b(format 'x(10)'), sp
FROM StatT2 
WHERE StatT2.sp.ST_WITHIN(CAST(
'POLYGON((0 0 0, 0 50 50, 50 50 50, 50 0 50, 0 0 0))' 
AS ST_Geometry)) = 1;

SELECT a,b (format 'x(10)'), sp
FROM StatT2
WHERE StatT2.sp.ST_WITHIN(NEW ST_GEOMETRY(
 'POLYGON((0 0 0, 0 50 50, 50 50 50, 50 0 50, 0 0 0))')) = 1;

SELECT a,b (format 'x(10)'), sp
FROM StatT2
WHERE StatT2.sp.ST_WITHIN(NEW ST_GEOMETRY(
 'POLYGON((0 0, 0 20, 20 20, 20 0, 0 0))').ST_INTERSECTION(
 'POLYGON((0 0, 0 15, 15 15, 15 0, 0 0))')) = 1;

The following shows an example of EXPLAIN output showing the optimizer utilizing a geospatial index.

Explanation
-----------------------------------------------------------------------
1) First, we lock JOSHUA.StatT2 for read on a reserved RowHash to
prevent global deadlock.
2) Next, we lock JOSHUA.StatT2 for read.
3) We do an all-AMPs SUM step to aggregate from JOSHUA.StatT2  by way 
 of Spatial index # 4  "Retrieving from CDT indexed column
JOSHUA.StatT2.sp via CDT key expression NEW ST_GEOMETRY (
'POLYGON((0 0, 0 20, 20 20, 20 0, 0 0))'(VARCHAR(64000), CHARACTER
SET LATIN, NOT CASESPECIFIC)).ST_INTERSECTION (CAST(('POLYGON((9
9, 9 18, 18 18, 9 9))'(VARCHAR(64000), CHARACTER SET LATIN, NOT
CASESPECIFIC)) AS SYSUDTLIB.ST_Geometry)).ST_MBR ()"with a
residual condition of ("(JOSHUA.StatT2.sp .ST_WITHIN (NEW
ST_GEOMETRY ('POLYGON((0 0, 0 20, 20 20, 20 0, 0
0))'(VARCHAR(64000), CHARACTER SET LATIN, NOT
CASESPECIFIC)).ST_INTERSECTION (CAST(('POLYGON((9 9, 9 18, 18 18,
9 9))'(VARCHAR(64000), CHARACTER SET LATIN, NOT CASESPECIFIC)) AS
SYSUDTLIB.ST_Geometry))))= 1"), and the grouping identifier in
field 1027. Aggregate Intermediate Results are computed globally,
then placed in Spool 3. The size of Spool 3 is estimated with no
confidence to be 3 rows (63 bytes). The estimated time for this
step is 0.07 seconds.
4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
an all-rows scan into Spool 1 (group_amps), which is built locally
on the AMPs. Then we do a SORT to order Spool 1 by the sort key
in spool field1. The size of Spool 1 is estimated with no
confidence to be 3 rows (96 bytes). The estimated time for this
step is 0.04 seconds.
5) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.11 seconds.