1.1 - 8.10 - GeometryOverlay Example: Intersection - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

Input

  • SourceTable: source_gatetype, which contains the geometrical coordinates of US and international gates in three airport terminals (A, B, and C)
  • ReferenceTable: ref_terminal, which contains the terminal coordinates in WKT syntax
SourceTable: source_gatetype
id boundary_coordinates boundary_name boundary_attributes
1 POLYGON ((10 10, 10 20, 20 20, 20 10, 10 10)) US Gates US
2 POLYGON ((50 50, 50 150, 150 150, 150 50,50 50)) International Gates International
ReferenceTable: ref_terminal
id boundary_coordinates boundary_name boundary_attributes
1 POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0)) Terminal A A
2 POLYGON ((100 0, 200 0, 200 100, 100 100, 100 0)) Terminal B B
3 POLYGON ((0 100, 100 100, 100 200, 0 200, 0 100)) Terminal C C

SQL Call

SELECT * FROM GeometryOverlay (
  ON source_gatetype AS SourceTable PARTITION BY ANY
  ON ref_terminal AS ReferenceTable DIMENSION
  USING
  SourceLocationColumn ('boundary_coordinates')
  ReferenceLocationColumn ('boundary_coordinates')
  ReferenceNameColumns ('boundary_name')
  BoundaryOperator ('intersection')
  OutputAll ('false')
  Accumulate ('boundary_attributes')
) AS dt ;

Output

The output shows that all domestic gates are within Terminal A. International gates are spread over all three terminals.

 overlay_boundary                                      ref_boundary_name overlay_flag boundary_attributes 
 ----------------------------------------------------- ----------------- ------------ ------------------- 
 POLYGON ((10 10, 10 20, 20 20, 20 10, 10 10))         terminal a                   1 domestic           
 POLYGON ((50 50, 50 100, 100 100, 100 50, 50 50))     terminal a                   1 international      
 POLYGON ((150 100, 150 50, 100 50, 100 100, 150 100)) terminal b                   1 international      
 POLYGON ((50 100, 50 150, 100 150, 100 100, 50 100))  terminal c                   1 international

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.