6

I'm doing a search to find links close to a point.
The problem is if i limit to 20 result i found the closer link at 6mts
but if i limit to 10 result that link doesnt appear and the closest link is at 17mts

Shouldn't the top 10 closest link to my point appear in both querys?

I'm using PostgreSQL 9.3.2, POSTGIS=2.1.1

query result image

    SELECT 
        S.Seg_ID, 
        S.Link_ID, 
        S.Course, 
        ST_Distance(ST_GeomFromText('POINT(-66.879670 10.497950)',2309), S.geom  ) as distance
    FROM vzla_seg S
    ORDER BY S.geom <-> ST_GeomFromText('POINT(-66.879670 10.497950)',2309)
    LIMIT 20
1

2 Answers 2

6

<-> will only deterministically return a distance ordered result with point-on-point tests. For any other geometries, you need to use <-> to quickly get a candidate set, and then sort that set explicitly on distance.

WITH candidates AS (
SELECT 
    S.Seg_ID, 
    S.Link_ID, 
    S.Course, 
    ST_Distance(ST_GeomFromText('POINT(-66.879670 10.497950)',2309), S.geom  ) as distance
FROM vzla_seg S
ORDER BY S.geom <-> ST_GeomFromText('POINT(-66.879670 10.497950)',2309)
LIMIT 100
)
SELECT * FROM candidates ORDER BY distance LIMIT 10;
3

Removed old solution as another answer covers it

Edit following comment, line to point

You have to guess either the arbitrary number ( the 100) of the <-> candidates that should ensure you have all the nearest, or you have to guess the amount by to expand the point into a box. With the expanded box version it fails if yo don't have a large enough expanded point, you just don't end up with any candidates, so can then increase the bbox size. The <-> version just needs the right data dependant figure to begin with

Okay so <-> compares the centre of the bounding box of the line to the point you are testing. So the error is locate there. E.g. a \ line and a / line & a L line will have the same box, but the centroid will be different, a better example would be a backward r.

Oh and the best explanation for a polygon's centroid I heard is if you had to placed a cardboard cut of the polygon balanced on a pin, it'd have to be on the centroid. But even then centroid distance to point is not the same as the distance between a point and a line. If the line is 100KM long, yet the point is near one end?

Since it's Lines you are dealing with you might have to stop using the <-> to choose your candidates.

If you imagine your point is in the middle of a "star" bust of massively long lines raying out from the centre, almost touching the point a bit like a *

These rays would be the candidates you want to find.

Also there are 10, 20, 100 short horizontal lines around the point, but these are further away from the point than one end of the rays.

You will never see the massively long rays/lines in your candidate selection (<->)! Both their bounding box centres, and their centroids are further away from the point than the short horizontal lines.

If your data is relatively uniform in length, you might get away with the <-> operator, only you know your data! You might need 10, 20 or 2000 candidates.

Else you will have to expand the point into a reasonable size bbox, and then use all geom that their bbox intersects expanded points' bbox.

Further reading, plus with illustrations:

http://postgis.net/workshops/postgis-intro/knn.html

3
  • Thanks for both answers. The thing is the Limit 100 look arbitrary to me. What if the result is on the place 105? Mar 28, 2014 at 17:22
  • 1
    My geom is a line. should i use the centroid to make the first aproach point to point instead point to line? im curious, how can i do the "maths" to see if there is a round issue? Mar 28, 2014 at 17:25
  • Okay so <-> compares the centre of the bounding box of the line to the point you are testing. So the error is locate there. E.g. a '\' line and a '/' line & a 'L' line will have the same box, but the centroid will be different, a better example would be a backward 'r'. Oh and the best explanation for a polygon's centroid I heard is if you had to placed a cardboard cut of the polygon balanced on a pin, it'd have to be on the centroid. But even then centroid distance to point is not the same as the distance between a point and a line. If the line is 100KM long, yet the point is near one end? Mar 28, 2014 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.