GeometryOverlay Example: Union - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.10
1.1
Published
October 2019
Language
English (United States)
Last Update
2019-12-31
dita:mapPath
ima1540829771750.ditamap
dita:ditavalPath
jsj1481748799576.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

This example computes the union of area for both the gates and the terminals.

Input

Input tables are from GeometryOverlay Example: Intersection:

  • 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

SQL Call

SELECT * FROM GeometryOverlay (
  ON source_gatetype AS SourceTable PARTITION BY id
  ON ref_terminal AS ReferenceTable PARTITION BY id
  USING
  SourceLocationColumn ('boundary_coordinates')
  ReferenceLocationColumn ('boundary_coordinates')
  ReferenceNameColumns ('boundary_name')
  BoundaryOperator ('union')
  OutputAll ('false')
  Accumulate ('boundary_name','id')
) AS dt ;

Output

Because all US gates are within Terminal A, the union of the area specified by the domestic gates and the area of Terminal A is just Terminal A, as shown in the first row of the output. The second row shows the union of the coordinates of the international gates and all three terminals.

 overlay_boundary                                                                  ref_boundary_name overlay_flag boundary_name        id 
 --------------------------------------------------------------------------------- ----------------- ------------ -------------------- -- 
 POLYGON ((0 0, 0 100, 100 100, 100 0, 0 0))                                       terminal a                   1 domestic gates        1
 POLYGON ((50 50, 50 150, 150 150, 150 100, 200 100, 200 0, 100 0, 100 50, 50 50)) terminal b                   1 international gates   2

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