ST_Intersects discrepancy between 2.5.3 and 3.0.0

[paf31]

The following query returns different results between versions 2.5.3 (returns true) and 3.0.0 (returns false) running on Postgres 12.1:

SELECT 
  ST_Intersects(
    ST_GeographyFromText('SRID=4326;POINT(-11.0 0.00005)'), 
    ST_GeographyFromText('SRID=4326;LINESTRING(-11.0 0.0,-11.0 0.0001)')
  );

The result on 3.0.0 is very sensitive to the numbers used. Changing 11.0 to 10.0, for example, changes the result to true.

We discussed this on IRC and concluded it's probably a bug, so I'm posting here. Thanks!

[robe]

I confirm that I also get false on PostgreSQL 12.1 + PostGIS 3.0.0 on windows but PostgreSQL 11 + PostGIS 3.0.0 returns true. I did an ST_Distance and _ST_DistanceTree on the two and got the same answer — did we flip to using _ST_DistanceTree for 12?

SELECT postgis_full_version() || ' ' ||  version();
SELECT 
  ST_Intersects(
    ST_GeographyFromText('SRID=4326;POINT(-11.0 0.00005)'), 
    ST_GeographyFromText('SRID=4326;LINESTRING(-11.0 0.0,-11.0 0.0001)')
  );

Outputs

POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="Rel. 5.2.0, September 15th, 2018" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)" PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit

false

0	3.9548627765821976e-10

However

On windows PostgreSQL 11, 3.0.0 — I get the same answer of false.

SELECT postgis_full_version() || ' ' ||  version();
SELECT 
  ST_Intersects(
    ST_GeographyFromText('SRID=4326;POINT(-11.0 0.00005)'), 
    ST_GeographyFromText('SRID=4326;LINESTRING(-11.0 0.0,-11.0 0.0001)')
  );

SELECT 
  ST_Distance(
    ST_GeographyFromText('SRID=4326;POINT(-11.0 0.00005)'), 
    ST_GeographyFromText('SRID=4326;LINESTRING(-11.0 0.0,-11.0 0.0001)')
  ) AS dist,
  _ST_DistanceTree(
    ST_GeographyFromText('SRID=4326;POINT(-11.0 0.00005)'), 
    ST_GeographyFromText('SRID=4326;LINESTRING(-11.0 0.0,-11.0 0.0001)')
  ) AS tree_dist;

However testing on windows PostgreSQL 11, 3.0.0 - I get the old answer of true. outputs

POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="110" GEOS="3.8.0-CAPI-1.13.1 " PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.4.3, released 2019/10/28" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)" RASTER PostgreSQL 11.6, compiled by Visual C++ build 1914, 64-bit

true

0	3.9548627765822e-10

2.5.3 is also true.

However testing on windows PostgreSQL 11, 2.5.3 - I get the old answer of true. outputs

POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="110" GEOS="3.7.2-CAPI-1.11.0 3.7.2" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.4, released 2018/03/19" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" RASTER PostgreSQL 11.6, compiled by Visual C++ build 1914, 64-bit

true

0	3.9548627765822e-10

I thought that ST_Distance and _ST_DistanceTree are supposed to agree with each other - in all cases they do not agree (2.5 or 3.0) regardless of PostgreSQL version

[sergius]

I think the following info is related.

Official documentation ( ​https://postgis.net/docs/ST_Intersects.html ) example shows:

Geography Examples

SELECT ST_Intersects(
'SRID=4326;LINESTRING(-43.23456 72.4567,-43.23456 72.4568)'::geography,
'SRID=4326;POINT(-43.23456 72.4567772)'::geography
);
 st_intersects
---------------
t

But at my environment the same code provides: false

"PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="Rel. 5.2.0, September 15th, 2018" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)""

PostgreSQL 11 with POSTGIS 3.0.0 executes this query correctly.

[komzpa]

3.1alpha1 says false:

21:05:10 [gis] > SELECT ST_Intersects(
[more] ( >  'SRID=4326;LINESTRING(-43.23456 72.4567,-43.23456 72.4568)'::geography,
[more] ( >  'SRID=4326;POINT(-43.23456 72.4567772)'::geography
[more] ( >  );
┌───────────────┐
│ st_intersects │
├───────────────┤
│ f             │
└───────────────┘
(1 row)

Time: 1,333 ms
21:05:13 [gis] > select postgis_full_version();
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                                                            postgis_full_version                                                                                                                             │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ POSTGIS="3.1.0alpha1 rf7a3648" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " SFCGAL="1.3.7" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.4.2, released 2019/06/28" LIBXML="2.9.4" LIBJSON="0.13.1" LIBPROTOBUF="1.3.1" WAGYU="0.4.3 (Internal)" TOPOLOGY RASTER │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)

Time: 1226,835 ms (00:01,227)

[komzpa]

Milestone renamed

[pramsey]

This is actually a result of your Proj version bumping, and PostGIS being able to use PROJ_GEODESIC functions.

​https://github.com/postgis/postgis/blob/master/postgis/geography_measurement.c#L43

The functions trim away tiny variances, and I'm not losing any sleep over nanometer changes in behaviour.

​https://github.com/postgis/postgis/blob/master/postgis/geography_measurement.c#L257