How <-> operator work?

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

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

Tags:

Postgis