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)
Change History (12)
by , 9 years ago
Attachment: | sample.sql added |
---|
comment:1 by , 9 years ago
Milestone: | PostGIS 2.2.2 → PostGIS 2.2.3 |
---|
comment:2 by , 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 , 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 , 8 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:6 by , 8 years ago
Milestone: | PostGIS 2.2.3 → PostGIS 2.4.0 |
---|
A clue you will not do anything with for a while.
comment:7 by , 8 years ago
This seems important enough to fix back to 2.2, since it causes PostGIS to report erroneous results.
comment:8 by , 8 years ago
Milestone: | PostGIS 2.4.0 → PostGIS 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 , 8 years ago
Milestone: | PostGIS 2.2.4 → PostGIS 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 , 8 years ago
Milestone: | PostGIS 2.3.2 → PostGIS 2.4.0 |
---|
comment:11 by , 7 years ago
Priority: | medium → blocker |
---|---|
Resolution: | → fixed |
Status: | new → closed |
sample SQL data