Opened 6 months ago

Closed 3 months ago

#4604 closed defect (invalid)

ST_Intersects discrepancy between 2.5.3 and 3.0.0

Reported by: paf31 Owned by: pramsey
Priority: medium Milestone: PostGIS 3.0.2
Component: postgis Version: 3.0.x
Keywords: Cc:

Description

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!

Change History (6)

comment:1 Changed 6 months ago by 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)')
  );
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;

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

Last edited 6 months ago by robe (previous) (diff)

comment:2 Changed 4 months ago by 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.

comment:3 Changed 4 months ago by 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)

comment:4 Changed 3 months ago by komzpa

Milestone: PostGIS 3.0.1PostGIS next

comment:5 Changed 3 months ago by komzpa

Milestone: PostGIS nextPostGIS 3.0.2

Milestone renamed

comment:6 Changed 3 months ago by pramsey

Resolution: invalid
Status: newclosed

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

Note: See TracTickets for help on using tickets.