Effective PostGIS ORDER BY distance of points

Question

I am confused about results given to me by PostGIS using <-> operator.

I have expected it will just order the result by distance, but result of some_point <-> another_point and result of ST_Distance(some_point, another_point) gives different result for ordering:

SELECT
    ST_Distance(ST_Transform(center_user.point, 26986), 
                ST_Transform(other_users.point, 26986)) AS meters,
    center_user.point <-> other_users.point AS distance 
FROM users_location AS other_users
JOIN users_location AS center_user ON center_user.user_id = :centerUserId
ORDER BY center_user.point <-> other_users.point ASC

produces in my DB something like

ST_Distance, <->
----------------
0,0
206.11336884337268,0.0019665329491254787
208.96246894617778,0.0019766891510790722
224.92733571488515,0.0026949211491237995
251.25590975255494,0.0032947762670634587
253.14204017878214,0.00330651870401501
274.53945946816725,0.0035784966354604113
285.917211185707,0.0036773903790610313
433.7996839913383,0.0038184893269424917
522.5377178205588,0.00533181687795157
661.6008575368243,0.006886766161997201
836.6488883011285,0.007021395872612183
531.6273177109663,0.007060573631086105
521.2550016825262,0.00743217412941762
763.6712950324617,0.007838289030776643
805.1587064493592,0.008299877192465086
703.5233840918338,0.00897977705770720
...

as you can see meters are NOT always in the same sequential order as distance (see lines 836.6488883011285,0.007021395872612183, 805.1587064493592,0.008299877192465086).

Why is this so? What I have missed about <-> operator?

What version of PostGIS? – Evan Carroll Sep 13 '17 at 15:27 — Evan Carroll, Sep 13 '17 at 15:27

score 8 · Accepted Answer · answered Sep 13 '17 at 15:15

8

Your points seems to be in lat-longs, so the <-> operator returns a distance in degree. One degree of latitude does not have the same length, in meters, as one degree of longitude. When you use the st_distance function, you first projected the points so the system is able to compute the distance (though the projection introduce some distortions that can be quiet important).

To get more accurate results, you can look at the ST_Distance_Sphere function that computes distances in meters using points in lat-long.

answered Sep 13 '17 at 15:15

JGH

41,794
3
43
89

You hit the point, thank you. My coordinates are really persisted as lat, long in WGS84 format (SRID 4326). – Jaroslav Týc Sep 15 '17 at 08:47
And I now understand what <-> is giving to me. I will now look for a way how to index (or just speed up) order by distance in meters, namely result of ST_DistanceSphere, because my current "solution" produces quite misleading results for higher distances and because ordering by ST_DistanceSphere itself takes 10 times longer now. – Jaroslav Týc Sep 15 '17 at 09:01
@JaroslavTýc you can also consider st_distance using geographies (so cast your geometry to geography- or just store your data as geography) – JGH Sep 15 '17 at 10:43
@JaroslavTýc have a look at this post that makes uses of st_dwithin (for example to filter nearby points, and then to order just these points) – JGH Sep 15 '17 at 12:03

Effective PostGIS ORDER BY distance of points

1 Answers1