Input
- source: source_passenger, which groups parsed location file formats and contains four passengers with known x and y coordinates
- reference: reference_terminal, which has the geographical locations of two airport terminals, A and B, specified as polygon coordinates, using WKT syntax
| 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 |
| 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 source partition BY ANY
ON reference_terminal AS reference 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;
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 ((200 0, 400 0, 400 200, 200 200, 200 0)) | Terminal B | 1 | 2 | John |
| POINT (300 10) | POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0)) | Terminal A | 0 | 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 |