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?

Vince
  • 20,017
  • 15
  • 45
  • 64
bcsta
  • 191
  • 5
  • I would guess one is in metres and one is in degrees – Ian Turton Feb 12 '24 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 Feb 12 '24 at 15:31
  • because the two values are not comparable - remember the earth is curved – Ian Turton Feb 12 '24 at 15:45
  • 2
    While the question is different, this answer applies here. – JGH Feb 12 '24 at 15:48
  • @JGH I converted the order to geography type and that actually worked. The second row (1065m) went to the top as the closest. Problem now is the query is so much slower. Is there a way I can optimise this? – bcsta Feb 12 '24 at 15:51
  • 2
    Have you created the spatial index on geographies (as shown in the comments of the linked answer)? – JGH Feb 12 '24 at 15:52
  • @JGH thats right I only had geometry index, creating index on geographies worked. Thanks! – bcsta Feb 12 '24 at 15:55

0 Answers0