ST_Intersects returns true for non-intersecting geography

Question

Here's my geography data:

select 'SRID=4326;MULTIPOLYGON (((105.67151103718783 26.882512088629863, 115.99865947468783 31.74650295647645, 116.37219463093783 43.48451247459262, 147.02954326375033 62.362161196046245, -177.2690102030465 66.81825017074695, -168.77863849894493 66.26641372873476, -169.78045429484337 60.98223428128946, 165.38220439656283 44.47269646435982, 172.99572978718783 23.382219202927942, 168.86487041218783 -11.63112025581975, 140.82776103718783 -11.372743681342062, 130.19299541218783 -9.644484126039586, 124.04065166218783 -12.662180464457489, 103.91369853718783 -12.23305845779793, 90.37854228718783 6.9914492544889555, 88.79651103718783 23.945718564044213, 105.67151103718783 26.882512088629863)))'::geography
union all
select 'SRID=4326;POINT (0 80)'::geography

Which looks like this in DBeaver:

Note that the polyon is distorted on the picture due to the antimeridian, but it's basically located in the Asia-Indonesian region.

As you can see the point and the polygon don't intersect neither geometrically nor geographically.

But the following query returns true anyway:

select st_intersects(
'SRID=4326;MULTIPOLYGON (((105.67151103718783 26.882512088629863, 115.99865947468783 31.74650295647645, 116.37219463093783 43.48451247459262, 147.02954326375033 62.362161196046245, -177.2690102030465 66.81825017074695, -168.77863849894493 66.26641372873476, -169.78045429484337 60.98223428128946, 165.38220439656283 44.47269646435982, 172.99572978718783 23.382219202927942, 168.86487041218783 -11.63112025581975, 140.82776103718783 -11.372743681342062, 130.19299541218783 -9.644484126039586, 124.04065166218783 -12.662180464457489, 103.91369853718783 -12.23305845779793, 90.37854228718783 6.9914492544889555, 88.79651103718783 23.945718564044213, 105.67151103718783 26.882512088629863)))'::geography,
'SRID=4326;POINT (0 80)'::geography
)

Why is this happening?

Ran it in PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit with postgis 2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1..... And for me it returns as false — Bert-Jan Stroop, Jun 19 '23 at 13:54
What happens if you fix the distortion due to the antimeridian? If one piece of software gets screwed up by it, why wouldn't another? — jjanes, Jun 19 '23 at 15:30

Pepe N O · Answer 1 · 2023-06-19T15:51:02.577

That's because your multipolygon is invalid, check

select
st_isvalid('SRID=4326;MULTIPOLYGON (((105.67151103718783 26.882512088629863, 115.99865947468783 31.74650295647645, 116.37219463093783 43.48451247459262, 
147.02954326375033 62.362161196046245, -177.2690102030465 66.81825017074695, -168.77863849894493 66.26641372873476, -169.78045429484337 60.98223428128946, 
165.38220439656283 44.47269646435982, 172.99572978718783 23.382219202927942, 168.86487041218783 -11.63112025581975, 140.82776103718783 -11.372743681342062, 
130.19299541218783 -9.644484126039586, 124.04065166218783 -12.662180464457489, 103.91369853718783 -12.23305845779793, 90.37854228718783 6.9914492544889555, 
88.79651103718783 23.945718564044213, 105.67151103718783 26.882512088629863)))'::geometry);

though there is no st_isvalid for geography, we can test for geometry. It returns in the output log

Self-intersection at or near point 121.48832421332854 46.634833731220766

But if you make your polygon valid it returns false

select 
st_intersects(
st_makevalid('POLYGON ((105.67151103718783 26.882512088629863, 115.99865947468783 31.74650295647645, 116.37219463093783 43.48451247459262, 147.02954326375033 62.362161196046245
, -177.2690102030465 66.81825017074695, -168.77863849894493 66.26641372873476, -169.78045429484337 60.98223428128946, 165.38220439656283 44.47269646435982
, 172.99572978718783 23.382219202927942, 168.86487041218783 -11.63112025581975, 140.82776103718783 -11.372743681342062, 130.19299541218783 -9.644484126039586, 
124.04065166218783 -12.662180464457489, 103.91369853718783 -12.23305845779793, 90.37854228718783 6.9914492544889555, 88.79651103718783 23.945718564044213, 105.67151103718783 26.882512088629863))'::geometry),
'SRID=4326;POINT (0 80)'::geography);

Not all PostGIS functions are available for geometry and geography but can be used alternatively for testing purposes (not for measuring) Working sample

ST_Intersects returns true for non-intersecting geography

1 Answers1