Question 113714 in PostgreSQL Question posted in
The official documentation can be found here.

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!

Tags:

2

Answers


  1. 0

    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)
         )
    Login or Signup to reply.
  2. 0

    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!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search