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:[email protected]] On Behalf Of Regina Obe Sent: torsdag 13. juli 2017 22.51 To: 'PostGIS Users Discussion' <[email protected]> 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:[email protected]] On Behalf Of Jakob Miksch Sent: Thursday, July 13, 2017 4:27 AM To: [email protected]<mailto:[email protected]> 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
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
