Geospatial Join Predicate Syntax | Teradata Vantage - 17.10 - Geospatial Join Predicate Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Geospatial Data Types

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1181-171K
Language
English (United States)

Join Predicate

For join predicates except distance methods ST_Distance and ST_3DDistance (where GeoColExpression contains a column from Table2):

SELECT a, b FROM {
  Table1, Table2 WHERE Table1.GeoCol.SupportedGeoMethod
   ( { Table2.GeoCol | GeoColExpression | Table1.GeoCol } ) = 1 |

  Table2 JOIN Table1 ON Table1.GeoCol.SupportedGeoMethod(Table2.GeoCol)=1
}
The expressions must be set to evaluate to 1 (true) for these to qualify as join predicates.

Join Distance Predicate

For distance methods ST_Distance and ST_3DDistance:

SELECT a, b FROM {
  Table1, Table2 WHERE
    Table1.GeoCol.SupportedGeoDistanceMethod
      ( { Table2.GeoCol | GeoColExpression | Table1.GeoCol ) { < | <= } DistanceLiteral |

  Table2 JOIN Table1 ON Table1.GeoCol.SupportedGeoDistanceMethod
    (Table2.GeoCol) { < | <= } DistanceLiteral
}

Syntax Elements

Table1
Table2
Table containing geospatial data columns.
GeoCol
An ST_Geometry column.
At least one side of the join, the owner expression or the argument, must be an ST_Geometry column that has a geospatial index.
GeoColExpression
An expression that evaluates to an ST_Geometry value and contains one reference to a column in one of the tables being joined.
SupportedGeoMethod
Any of the geospatial methods, excluding ST_Distance and ST_3DDistance, listed in Geospatial Predicates and the Optimizer.
SupportedGeoDistanceMethod
Either ST_Distance or ST_3DDistance.
DistanceLiteral
A floating point value representing a distance.