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

[Regina Obe]

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
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-ret
urn-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/20170713/ff96797c/attachment.html>