Opened 9 years ago

Closed 7 years ago

#3448 closed defect (fixed)

ST_Distance yields inconsistent results when used in a statement involving multiple Point and Multipolygon Geography pairs

Reported by: ewcz Owned by: pramsey
Priority: blocker Milestone: PostGIS 2.4.0
Component: postgis Version: 2.2.x
Keywords: ST_Distance, _ST_DistanceTree Cc:

Description

The function ST_Distance returns inconsistent results when calculating the mutual geographical distance of several points with respect to given (multi)polygon.

Using the tables as in the attached minimal example, following SQL statement works as expected:

sample=# SELECT N.node_id, P.iid, ST_Distance(P.outline::geography, N.location::geography)
FROM N, P WHERE (N.node_id IN (2) AND P.iid=1);
 node_id | iid |   st_distance    
---------+-----+------------------
       2 |   1 | 1730626.41376862
(1 row)

While a similar statement taking into account all three points gives wrong distance for the second point, i.e.,

sample=# SELECT N.node_id, P.iid, ST_Distance(P.outline::geography, N.location::geography)
FROM N, P WHERE (N.node_id IN (1,2,3) AND P.iid=1);
 node_id | iid |   st_distance    
---------+-----+------------------
       1 |   1 | 1951140.06556506
       2 |   1 |                0
       3 |   1 | 1727662.98350914
(3 rows)

However, using _ST_DistanceUnCached in the latter statement recovers the expected result:

sample=# SELECT N.node_id, P.iid, _ST_DistanceUnCached(P.outline::geography, N.location::geography)
FROM N, P WHERE (N.node_id IN (1,2,3) AND P.iid=1);
 node_id | iid | _st_distanceuncached 
---------+-----+----------------------
       1 |   1 |     1951140.06556506
       2 |   1 |     1730626.41376862
       3 |   1 |     1727662.98350914
(3 rows)

Statements above were tested on:

PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
(Red Hat 4.8.5-4), 64-bit

POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012"
GDAL="GDAL 1.11.2, released 2015/02/10" LIBXML="2.9.1"
LIBJSON="0.11" RASTER

Attachments (1)

sample.sql (274.2 KB ) - added by ewcz 9 years ago.
sample SQL data

Download all attachments as: .zip

Change History (12)

by ewcz, 9 years ago

Attachment: sample.sql added

sample SQL data

comment:1 by pramsey, 9 years ago

Milestone: PostGIS 2.2.2PostGIS 2.2.3

comment:2 by pramsey, 9 years ago

Confirmed on OSX and against trunk,

select 
  _st_distancetree(n.location, p.outline),
  _st_distanceuncached(n.location, p.outline), 
  n.node_id, p.iid 
from n, p;

 _st_distancetree | _st_distanceuncached | node_id | iid 
------------------+----------------------+---------+-----
 1951140.06557644 |     1951140.06557644 |       1 |   1
                0 |      1730626.4137779 |       2 |   1
  1727662.9835187 |      1727662.9835187 |       3 |   1
(3 rows)

comment:3 by pramsey, 9 years ago

The point-in-polygon test is short-circuiting the calculation early with an erroneous result, hence the zero distance return. Why? Dunno, probably a robustness failure in the spherical geometry core algorithms, gah.

comment:4 by dbaston, 9 years ago

Interestingly, the issue seems to disappear if we extract the polygon from p, even though p is a single-member multipolygon:

 select 
  _st_distancetree(n.location, ST_GeometryN(p.outline, 1)),
  _st_distanceuncached(n.location, p.outline), 
  n.node_id, p.iid 
from n, p;
 _st_distancetree | _st_distanceuncached | node_id | iid 
------------------+----------------------+---------+-----
 1951140.06556506 |     1951140.06556506 |       1 |   1
 1730626.41376862 |     1730626.41376862 |       2 |   1
 1727662.98350914 |     1727662.98350914 |       3 |   1

comment:5 by pramsey, 9 years ago

Oooh, that's a nice clue...

comment:6 by robe, 8 years ago

Milestone: PostGIS 2.2.3PostGIS 2.4.0

A clue you will not do anything with for a while.

comment:7 by dbaston, 8 years ago

This seems important enough to fix back to 2.2, since it causes PostGIS to report erroneous results.

comment:8 by robe, 8 years ago

Milestone: PostGIS 2.4.0PostGIS 2.2.4

Dan,

I'm planning to release 2.2.3 sometime next week. I feel we should wait for 2.2.4 for this, since I want to be able to say that 2.2.3 and 2.3.0 are on parallel tracks.

comment:9 by robe, 8 years ago

Milestone: PostGIS 2.2.4PostGIS 2.3.2

Sadly we still have not fixed this. My 2.3.1dev shows the same issue.

Pushing this to 2.3.2. If pramsey or dbaston have the energy to fix this by Monday or Tuesday fill free to push back to 2.2.4 and let me know, otherwise going to have to wait till next release.

comment:10 by robe, 8 years ago

Milestone: PostGIS 2.3.2PostGIS 2.4.0

comment:11 by dbaston, 7 years ago

Priority: mediumblocker
Resolution: fixed
Status: newclosed
Note: See TracTickets for help on using tickets.