ST_Intersects function is returning the wrong results for a simple rectangle that crosses any two quarter longitude lines

[hangstrap]

the ST_Intersects function is returning the wrong results for a simple rectangle that crosses any two quarter longitude lines (e.g 0, 90, 180, -90).

Running the following query returns the points outside the polygon as well as the expected points inside

select postgis.st_astext( point_) from 
(
	with foo ( point_  ) as ( values  
		( postgis.ST_GeogFromText( 'POINT (-6 0)')), 
		( postgis.ST_GeogFromText( 'POINT (-4 0)')),
		( postgis.ST_GeogFromText( 'POINT (94 0)')),
		( postgis.ST_GeogFromText( 'POINT (96 0)'))
	)
	select * from foo
) bar
where postgis.ST_Intersects( point_, postgis.ST_GeogFromText('POLYGON ((  -5 -10,  -5 10,  95.0 10, 95.0 -10 , -5 -10))'));

The select statement should return the points (-4 0) and (94 0), but returns all four points.

This select statement, that does not cross two quarter longitude lines, works correctly

--works only closses 0 line
select postgis.st_astext( point_) from 
(
	with foo ( point_  ) as ( values  
		( postgis.ST_GeogFromText( 'POINT (-51 0)')), 
		( postgis.ST_GeogFromText( 'POINT (-49 0)')),
		( postgis.ST_GeogFromText( 'POINT (49 0)')),
		( postgis.ST_GeogFromText( 'POINT (51 0)'))
	)
	select * from foo
) bar
where postgis.ST_Intersects( point_, postgis.ST_GeogFromText('POLYGON ((  -50 -10,  -50 10,  50 10, 50 -10 , -50 -10))'));

Tested on PostgreSQL 12.18 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

With Postgis

POSTGIS="3.1.11 ca03d62" [EXTENSION] PGSQL="120" GEOS="3.12.1-CAPI-1.18.1" PROJ="9.2.1" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)"

and

POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="120" GEOS="3.12.1-CAPI-1.18.1" PROJ="9.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=​https://cdn.proj.org USER_WRITABLE_DIRECTORY=/var/lib/pgsql/.local/share/proj DATABASE_PATH=/usr/proj92/share/proj/proj.db" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)" (core procs from "3.1.11 ca03d62" need upgrade)

The queries do work as expected on postgres 10, postgis 2.4.8

[pramsey]

Seems true.

SELECT 
  ST_AsText(g) AS geog,
  ST_Intersects(ST_GeogFromText('POLYGON ((  -5 -10,  -5 10,  95.0 10, 95.0 -10 , -5 -10))'), v.g) 
FROM
  (VALUES
    ( ST_GeogFromText( 'POINT (-6 0)')), 
    ( ST_GeogFromText( 'POINT (-4 0)')),
    ( ST_GeogFromText( 'POINT (94 0)')),
    ( ST_GeogFromText( 'POINT (96 0)'))
    ) v(g);

[pramsey]

Curiouser and curiouser, you can get wrong answers one at a time, on both sides of the line…

SELECT 
  ST_AsText(g) AS geog,
  ST_Intersects(ST_GeogFromText('POLYGON ((  -5 -10,  -5 10,  95.0 10, 95.0 -10 , -5 -10))'), v.g) 
FROM
  (VALUES
    ( ST_GeogFromText( 'POINT (-4 0)'))
    ) v(g);

    geog     | st_intersects 
-------------+---------------
 POINT(-4 0) | f

[pramsey]

Peeling back the onion further, the caching code can be taken out of consideration too:

SELECT 
  _ST_DWithinUnCached(
  'POLYGON ((  -5 -10,  -5 10,  95.0 10, 95.0 -10 , -5 -10))'::geography, 
  'POINT (-6 0)'::geography,
  0.0,
  true
  );

 _st_dwithinuncached 
---------------------
 t

[pramsey]

This is an effect of ring orientation?

-- CCW (bounds small area, pt is outside)
SELECT _ST_DistanceUnCached(
  'POLYGON ((  -5 10,  -5 -10,  95.0 -10, 95.0 10 , -5 10))'::geography, 
  'POINT (-6 0)'::geography
  );

-- CW (bounds everything except small area, pt is inside)
SELECT _ST_DistanceUnCached(
  'POLYGON ((  -5 -10,  -5 10,  95.0 10, 95.0 -10 , -5 -10))'::geography, 
  'POINT (-6 0)'::geography
  );

But I cannot remember handling ring orientation.

[pramsey]

And in general we do not handle ring orientation, but, when multiple corner cases drive us to extremes… we fall back on ring orientation.

​https://github.com/postgis/postgis/blob/8c30a25acb166de09a7d647e1e02dc6d71eae9ea/liblwgeom/lwgeodetic.c#L1429-L1441

So the "answer" is: even though we don't, in general respect ring orientation in geography, if you fail to respect it there are corner cases where you'll get the wrong answer, like this case.