I'm creating a Geography database that does not allow polygons to overlap/intersect (having positive common area that is). The user interaction flow is:
- User creates and exports a KML document from Google Maps.
- Upload KML file to my service to import polygons. Only simple polygons are allowed. (polygons with no holes and does not intersect with itself, disallow multipolygon type)
- My service will check if added polygons overlap/intersect with old polygons or with themselves.
- Report back error or success.
I have found a case where my implementation fails, but I could not figure it out. I will walk you through:
<Placemark> <name>Polygon 16</name> <styleUrl>#poly-000000-2000-77-nodesc</styleUrl> <Polygon> <outerBoundaryIs> <LinearRing> <tessellate>1</tessellate> <coordinates> -17.475056,49.757219,0 39.828604,61.984216,0 38.381786,75.767261,0 -18.318666,74.923636,0 -17.475056,49.757219,0 </coordinates> </LinearRing> </outerBoundaryIs> </Polygon> </Placemark> <Placemark> <name>Polygon 14</name> <styleUrl>#poly-000000-2000-77-nodesc</styleUrl> <Polygon> <outerBoundaryIs> <LinearRing> <tessellate>1</tessellate> <coordinates> 43.5279101,74.5130924,0 45.0606593,61.3642652,0 83.4214287,50.2074728,0 107.0306304,48.9310218,0 155.4440589,55.7337492,0 162.4223541,60.6716876,0 167.3779,63.2983803,0 -179.5771561,64.3661727,0 -179.2404288,66.2561204,0 171.1667344,68.6413074,0 43.5279101,74.5130924,0 </coordinates> </LinearRing> </outerBoundaryIs> </Polygon> </Placemark>
Let's say I have a table
tmp_poly that is created by this query:
CREATE TABLE tmp_poly ( id SERIAL PRIMARY KEY, polygon GEOGRAPHY(POLYGON) );
The above KML file translates to the below insertion query:
INSERT INTO tmp_poly (polygon) VALUES ( 'SRID=4326;POLYGON ((-17.475056 49.757219, 39.828604 61.984216, 38.381786 75.767261, -18.318666 74.923636, -17.475056 49.757219))' ),( 'SRID=4326;POLYGON ((43.527910 74.513092, 45.060659 61.364265, 83.421429 50.207473, 107.030630 48.931022, 155.444059 55.733749, 162.422354 60.671688, 167.377900 63.298380, -179.577156 64.366173, -179.240429 66.256120, 171.166734 68.641307, 43.527910 74.513092))' );
I refer'd from this answer to check for overlapped polygons: https://gis.stackexchange.com/a/270802. Check as follow:
SELECT p1.id, p2.id, ST_Relate(p1.poly::geometry, p2.poly::geometry, '2********') FROM tmp_poly AS p1 JOIN tmp_poly AS p2 ON p1.id != p2.id;
In which returns:
id | id | st_relate ----+----+----------- 1 | 2 | t 2 | 1 | t
And I was expecting
ST_Relate to returns FALSE since visually on Google Maps they do not intersect.
- I'm suspecting the part where polygons wrap around the earth (lat long was positive then later on goes negative) would mess up my overlap checking logic, but I'm not sure what to do next. Above image is Google Maps viewer, below is https://clydedacruz.github.io/openstreetmap-wkt-playground/
ST_Relateuses Geometry types, which is a Cartesian coordinates system (no wrap-around), maybe the problem is at the Geography to Geometry casting step? If this was the case then I don't know what to do next.
For extreme cases like above, what should I do so that I can check for overlaps by using ST_Relate?
- PostgreSQL 14.7
- PostGIS 3.2.0 c3e3cc0