javaspringpostgresqlgeolocationproximity

Sort by proximity after getting distance using pythagoras - postgresql

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!

Solution

  • 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)
         )