3

I have a query where I want to get the closes row by coordinate distance like so:

SELECT A.id,
    st_distance(coords, ST_SetSRID(ST_MakePoint(-9.15054,54.115341), 4326), true) AS dst,
    A.coords <-> ST_SetSRID(ST_MakePoint(-9.15054, 54.115341), 4326) as the_order_dst
FROM location AS A WHERE coords IS NOT NULL
ORDER BY (
    A.coords <-> ST_SetSRID(ST_MakePoint(-9.15054, 54.115341), 4326)
) ASC
LIMIT 5;

I am printing the first 5 closest locations by the <-> distance operator. I am also printing the distance result when I use `ST_Distance'. I am getting this result set:

   id    |      dst      |    the_order_dst
---------+---------------+----------------------
 2965018 |  1365.1318663 | 0.015069556098305344
 2966778 | 1065.11660817 |  0.01618465758056096
 3315904 | 2067.91034003 |    0.031301170281637
 3315895 | 3827.52969307 |  0.03468120904755041
 3315914 | 3151.49795391 |    0.042830532345513

Why is the first value of dst larger than the second value? (same with 4th and 5th results) I thought ST_Distance and <-> produce the same result.

I manually checked on google maps for comparison. And G-maps' distance value is closer to ST_Distance than to <->.

I want to use <-> for query speed reasons, but it seems that the results are not accurate, at least with my current query. Is there a way to make the results identical?

Improve this question
7
  • I would guess one is in metres and one is in degrees
    – Ian Turton
    Commented Feb 12 at 15:29
  • but shouldn't the sort order in both cases produce the same ordered list? why is the one in meters not sorted correctly?
    – bcsta
    Commented Feb 12 at 15:31
  • because the two values are not comparable - remember the earth is curved
    – Ian Turton
    Commented Feb 12 at 15:45
  • 2
    While the question is different, this answer applies here.
    – JGH
    Commented Feb 12 at 15:48
  • 2
    Have you created the spatial index on geographies (as shown in the comments of the linked answer)?
    – JGH
    Commented Feb 12 at 15:52

0

Reset to default

Your Answer

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