PointInPolygon Example: OutputAll ('true') | Teradata Vantage - PointInPolygon Example: OutputAll ('true') - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
9.02
9.01
2.0
1.3
Published
February 2022
Language
English (United States)
Last Update
2022-02-10
dita:mapPath
rnn1580259159235.ditamap
dita:ditavalPath
ybt1582220416951.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

Input

  • SourceTable: source_passenger, which groups parsed location file formats and contains four passengers with known x and y coordinates
  • ReferenceTable: reference_terminal, which has the geographical locations of two airport terminals, A and B, specified as polygon coordinates, using WKT syntax
SourceTable: source_passenger
customer_id source_location_point customer_name
3 POINT (300 20) Maria
2 POINT (300 10) John
1 POINT (30 10) Jeff
4 POINT (400 20) Macy
ReferenceTable: reference_terminal
terminal_id reference_location_polygon terminal_name
1 POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0)) Terminal A
2 POLYGON ((200 0, 400 0, 400 200, 200 200, 200 0)) Terminal B

SQL Call

SELECT * FROM PointInPolygon (
  ON source_passenger AS SourceTable PARTITION BY ANY
  ON reference_terminal AS ReferenceTable DIMENSION
  USING
  SourceLocationColumn ('source_location_point')
  ReferenceLocationColumn ('reference_location_polygon')
  ReferenceNameColumns ('terminal_name')
  outputall ('true')
  Accumulate ('customer_id', 'customer_name')
) AS dt ORDER BY source_location_point,customer_id,customer_name;

Output

The output table shows all passengers, whether or not they are in a terminal.

 source_location_point ref_reference_location_polygon                    ref_terminal_name pip_flag customer_id customer_name 
 --------------------- ------------------------------------------------- ----------------- -------- ----------- ------------- 
 point (30 10)         polygon ((200 0, 400 0, 400 200, 200 200, 200 0)) terminal b               0           1 jeff         
 point (30 10)         polygon ((0 0, 100 0, 100 100, 0 100, 0 0))       terminal a               1           1 jeff         
 point (300 10)        polygon ((0 0, 100 0, 100 100, 0 100, 0 0))       terminal a               0           2 john         
 point (300 10)        polygon ((200 0, 400 0, 400 200, 200 200, 200 0)) terminal b               1           2 john         
 point (300 20)        polygon ((200 0, 400 0, 400 200, 200 200, 200 0)) terminal b               1           3 maria        
 point (300 20)        polygon ((0 0, 100 0, 100 100, 0 100, 0 0))       terminal a               0           3 maria        
 point (400 20)        polygon ((200 0, 400 0, 400 200, 200 200, 200 0)) terminal b               0           4 macy         
 point (400 20)        polygon ((0 0, 100 0, 100 100, 0 100, 0 0))       terminal a               0           4 macy

Download a zip file of all examples and a SQL script file that creates their input tables.