<-> operator does not return true distance

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

<-> operator does not return true distance

Jakob Miksch

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: <-> operator does not return true distance

Brian M Hamlin
 Hi Jakob -
 
  I am interested in this topic, but have not looked into it for a while..
I wrote a blog post long ago which may explain the situation, as it was
at that time.. [0]
 
  http://blog.light42.com/wordpress/?s=knn
 
 
  best regards
    --Brian

 
--
(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

--
Brian M Hamlin
OSGeo California Chapter
blog.light42.com

 

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: <-> operator does not return true distance

Regina Obe-2
In reply to this post by Jakob Miksch

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:[hidden email]] On Behalf Of Jakob Miksch
Sent: Thursday, July 13, 2017 4:27 AM
To: [hidden email]
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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