Sort by proximity after getting distance using pythagoras - postgresql

Question

I'm using postgresql to calculate if different points are inside a specific range (calculated using pythagoras).

The thing is that I would like to get the results ordered by the proximity instead of randomly.

This is my query:

select * from point l where ((abs(l.lat*111139 - myPointLat*111139)^2) + (abs(l.lng*111139 - (myPointLng*111139))^2)) <= metres^2;

And I would like to sort it using this part of the query:

((abs(l.lat111139 - myPointLat111139)^2) + (abs(l.lng111139 - (myPointLng111139))^2))

How could I achieve this?

Thank you very much!

Wouldn't adding a simple order by work? `select * from point l where ((abs(l.lat*111139 - myPointLat*111139)^2) + (abs(l.lng*111139 - (myPointLng*111139))^2)) <= metres^2 order by ((abs(l.lat111139 - myPointLat111139)^2) + (abs(l.lng111139 - (myPointLng111139))^2))` — user3738870, Nov 17 '22 at 14:59
@user3738870 I'd like to avoid calculating again the same distance :S — Varox, Nov 17 '22 at 15:01
Unless you live right on the equator, this equation will return trash, since the ground length (in meters) of a degree of longitude varies by latitude (down to a few cm only near the poles...). Either use the real haversine formula, or, maybe simpler, use PostGIS which will handle the distance measurement for you. — JGH, Nov 17 '22 at 15:08
@JGH You are right, just found that formula for PostgreSQL! Im going to try it, thanks. Do you know if it is better for performance to just use the formula instead of postgis? — Varox, Nov 17 '22 at 15:29
PostGIS has spatial indexes which are very efficient for distance ordering and selectivity (you don't need to compute every distances to keep only the near ones. PostGIS would first apply a "square" of 2m and then would compute the exact distance just for points inside this square). If you have more tasks to do using your coordinates, PostGIS is a clear winner. If it is the only one, well, it's up to you... — JGH, Nov 17 '22 at 15:34

user3738870 · Accepted Answer · 2022-11-17T15:20:11.913

If you would like to avoid repeating the expression, you can add a computed column in a subquery and then do the filtering on the subquery.

select Column1, Column2, Column3 from 
(
  select *,
       (
          (abs(l.lat*111139 - myPointLat*111139)^2) + 
          (abs(l.lng*111139 - (myPointLng*111139))^2)
       ) as proximity
  from point l
)
where proximity <= metres^2
order by proximity

Column1, Column2, Column3 are the columns from the original point table that you're interested in.

Performance-wise, it's better to just repeat the expression though, since then Postgres can surely do the filtering first, which means it has to work with a potentially much smaller dataset, which will be faster:

select *
from point l
where (
          (abs(l.lat*111139 - myPointLat*111139)^2) + 
          (abs(l.lng*111139 - (myPointLng*111139))^2)
      ) <= metres^2
order by (
          (abs(l.lat*111139 - myPointLat*111139)^2) + 
          (abs(l.lng*111139 - (myPointLng*111139))^2)
         )

Hi, thank you for your quick anwser! Do you know if the computed column will work on PostgreSQL 15? DBeaver tells me that the column proximity does not exist :( — Varox, Nov 17 '22 at 15:15
Yes, sorry I didn't check the `where` clause. I've updated my answer. — user3738870, Nov 17 '22 at 15:20

score 1 · Answer 2 · answered Nov 17 '22 at 15:24

1

Checkout this other question How to re-use result for SELECT, WHERE and ORDER BY clauses? because I think it would help you a lot!

answered Nov 17 '22 at 15:24

Manuel González Costa

154
6

Sort by proximity after getting distance using pythagoras - postgresql

2 Answers2

Linked