PostGIS ST_Relate reports polygons intersect but Google Maps says otherwise

Question

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:

1. User creates and exports a KML document from Google Maps.
2. 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)
3. My service will check if added polygons overlap/intersect with old polygons or with themselves.
4. 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:

Problem

KML File

• Google Maps view:
• KML content:

<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>

DB

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.

Remarks

• 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/
• Since ST_Relate uses 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.

Question

For extreme cases like above, what should I do so that I can check for overlaps by using ST_Relate?

Version

• PostgreSQL 14.7
• PostGIS 3.2.0 c3e3cc0

Answer 1

You should be carefull about the projection you use. The original data that you have is not projected (4326) but the function takes geometry in, so it will interpret by default your data degree as if you were in a cartesian plan and leaves you with incoherent results.

You should first project your data in a suited projection before using it for things like intersection and such. If your data is around the north pole, you can use a polar projection like 102016 for example (you can also check an old answer of mine about seeing your results in google earth, wich is more suited for "extrem" data like that)