[postgis-users] <-> operator does not return true distance

[Jakob Miksch]

Hello Regina,

Thanks for your helpful response. Indeed there was a problem with the update of PostGIS.
We fixed it and now everything works like it should.

Kind regards,
Jakob

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Regina Obe
Sent: torsdag 13. juli 2017 22.51
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] <-> operator does not return true distance

Jakob,

Did you upgrade your install from an older version say 9.4.

I think if you did a pg_upgrade, and didn't do a ALTER EXTENSION postgis... after upgrade, this would explain your issue since the operators would not get upgraded.


On my PostGIS 2.3.2, 9.6 behavior is as expected.  Unfortunately I don't have 2.2.2 lying around at moment.

Also there was an ordering bug I think in 2.2 early version that may have caused this.  If that is your issue, then output of <-> would be real even though ordering is wrong.

Try this query:

SELECT id,
       ST_Distance(my_point.geom,
                   my_polygons.geom) AS real_distance,
       ST_Distance(my_point.geom,
                   ST_Centroid(my_polygons.geom)) AS centroid_distance,  my_point.geom <-> my_polygons.geom

FROM (
    SELECT 'blue' AS id, ST_Polygon(ST_GeomFromText('LINESTRING(0 3, 2 3,  2 4, 0 4 ,0 3)'),4326) AS geom
    UNION
    SELECT 'green' AS id,  ST_Polygon(ST_GeomFromText('LINESTRING(2 0, 8 0 , 8 2, 2 2 ,2 0)'),4326) AS geom

) AS my_polygons,

(
    SELECT ST_SetSRID(ST_Point(1, 1),4326) AS geom
) AS my_point

ORDER BY my_point.geom <-> my_polygons.geom

-- output should be --

  id   | real_distance | centroid_distance | ?column?
-------+---------------+-------------------+----------
green |             1 |                 4 |        1
blue  |             2 |               2.5 |        2
(2 rows)


Hope that helps,
Regina
http://postgis.us
http://www.paragoncorporation.com

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jakob Miksch
Sent: Thursday, July 13, 2017 4:27 AM
To: postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
Subject: [postgis-users] <-> operator does not return true distance

Hi,

(I asked a similar question here https://gis.stackexchange.com/questions/247034/postgis-operator-does-not-return-true-distance )

I used the <-> operator with PostGIS 2.2.2 and PostgreSQL 9.5.7 .

According to the docs (http://postgis.net/docs/manual-2.2/geometry_distance_knn.html) the <-> operator should order by "true KNN distance" and *not* by "centroid distance".

However it does not work for me. See the minimal example below:
( illustration:  https://i.stack.imgur.com/QZA6D.png )

SELECT id,
       ST_Distance(my_point.geom,
                   my_polygons.geom) AS real_distance,
       ST_Distance(my_point.geom,
                   ST_Centroid(my_polygons.geom)) AS centroid_distance

FROM (
    SELECT 'blue' AS id, ST_Polygon(ST_GeomFromText('LINESTRING(0 3, 2 3,  2 4, 0 4 ,0 3)'),4326) AS geom
    UNION
    SELECT 'green' AS id,  ST_Polygon(ST_GeomFromText('LINESTRING(2 0, 8 0 , 8 2, 2 2 ,2 0)'),4326) AS geom

) AS my_polygons,

(
    SELECT ST_SetSRID(ST_Point(1, 1),4326) AS geom
) AS my_point

ORDER BY my_point.geom <-> my_polygons.geom


--- obtained Result:


|       | real_distance | centroid_distance |
---------------------------------------------
| blue  |       2       |        2.5        |
| green |       1       |        4          |

(I expected the rows to be in the opposite order)

Apparently the <-> operator orders by the centroid_distance and not by the real_distance, even though the docs say: "[...] for PostgreSQL 9.5+, does true KNN distance search giving true distance between geometries [...]"

I tried the same example on 2.3.2 and PostgreSQL 9.5.7 and I get the expected result (which orders by "real_distance").

So, for me it seems that ordering by "real_distance" with the <-> operator does not work with PostGIS 2.2.2 - but this is *not* written in the docs.

Can you reproduce this behaviour? Or did I understand/make something wrong here?

Thanks and best regards,
Jakob
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170801/3b4278a0/attachment.html>