Point inside a Polygon (Teradata Geospatial SQL)

Question

I have tried search on the web, but unable to find an answer. I could use Teradata SQL to draw a square polygon: New ST_GEOMETRY('Polygon((150 -35, 150 -34, 149 -34, 149 -35))') As Region1 and create a point: New ST_GEOMETRY('Point(149.5 -34.5)') As Location1

Is there a way to check if the point "Location1" is inside the polygon "Region1"?

Also if Region2, which is inside Region1, is to be exclude New ST_GEOMETRY('Polygon((149.75 -34.25, 149.75 -34.75, 149.25 -34.75, 149.25 -34.25))') As Region2 What is the method (Teradata SQL) to create a polygon/shape "Region3", which is "Region1" exclude "Region2"?

Thanks

[relevant[(https://stackoverflow.com/questions/39853481/is-point-inside-polygon) — user5226582, Oct 10 '16 at 11:16
Teradata supports Open Geospatial Consortium Simple Features specification. Consider using `ST_CONTAINS()` predicate and `ST_DIFFERENCE()` method. — Dima Chubarov, Oct 11 '16 at 07:50

score 0 · Answer 1 · answered Mar 18 '21 at 13:44

Summary

As Dmitri Chubarov mentions, you can use ST_CONTAINS. See the code below for a working example.

For the polygon to work the polygon should be "closed" with the first and last point of the polygon being identical.

Working Example Tested with Teradata 16

The code below creates:

A table with points
A table with polygons
A select statement with a combination off all points and polygons, and an indicator of the point is in the polygon

SQL

---------- 1. Create points ----------

-- DROP TABLE SAMPLE_POINTS;
CREATE VOLATILE TABLE SAMPLE_POINTS (SKEY INTEGER, POINT ST_GEOMETRY) ON COMMIT PRESERVE ROWS;
INSERT INTO SAMPLE_POINTS VALUES (1, Cast('Point(149.5 -34.5)' AS ST_GEOMETRY));
INSERT INTO SAMPLE_POINTS VALUES (2, Cast('Point(1 1)' AS ST_GEOMETRY));

---------- 2. Create polygons ----------

-- DROP TABLE SAMPLE_POLYGONS;
CREATE VOLATILE TABLE SAMPLE_POLYGONS (SKEY INTEGER, POLYGON ST_GEOMETRY) ON COMMIT PRESERVE ROWS;
INSERT INTO SAMPLE_POLYGONS VALUES (1, Cast('Polygon((150 -35, 150 -34, 149 -34, 149 -35))' AS ST_GEOMETRY));
INSERT INTO SAMPLE_POLYGONS VALUES (2, Cast('Polygon((150 -35, 150 -34, 149 -34, 149 -35, 150 -35))' AS ST_GEOMETRY));
INSERT INTO SAMPLE_POLYGONS VALUES (3, Cast('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))' AS ST_GEOMETRY));
INSERT INTO SAMPLE_POLYGONS VALUES (4, Cast('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))' AS ST_GEOMETRY));

---------- 3. Point in polygon indicator ----------

SELECT
  POI.SKEY                             AS POINT_KEY
  , Cast(POI.POINT AS VARCHAR(255))    AS POINT
  , POL.SKEY                           AS POLYGON_KEY
  , Cast(POL.POLYGON AS VARCHAR(255))  AS POLYGON
  , POL.POLYGON.ST_Contains(POI.POINT) AS PIP_IND
FROM SAMPLE_POLYGONS AS POL
     , SAMPLE_POINTS AS POI
;

Output

POINT_KEY	POINT	POLYGON_KEY	POLYGON	PIP_IND
1	POINT (149.5 -34.5)	4	POLYGON ((0 0,0 1,1 1,1 0,0 0))	0
1	POINT (149.5 -34.5)	2	POLYGON ((150 -35,150 -34,149 -34,149 -35,150 -35))	1
1	POINT (149.5 -34.5)	3	POLYGON ((0 0,0 2,2 2,2 0,0 0))	0
1	POINT (149.5 -34.5)	1	POLYGON ((150 -35,150 -34,149 -34,149 -35))	0
2	POINT (1 1)	4	POLYGON ((0 0,0 1,1 1,1 0,0 0))	0
2	POINT (1 1)	2	POLYGON ((150 -35,150 -34,149 -34,149 -35,150 -35))	0
2	POINT (1 1)	3	POLYGON ((0 0,0 2,2 2,2 0,0 0))	1
2	POINT (1 1)	1	POLYGON ((150 -35,150 -34,149 -34,149 -35))	0

Please note:

Closed polygon: The initial polygon 1 was not working because the polygon needs to be closed by repeating the first point. Polygon 2 is the corrected version.
Edge case: Point 2 is on the edge of polygon 4 and therefore is not marked as being in the polygon.

Source:

Point inside a Polygon (Teradata Geospatial SQL)

1 Answers1