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!
CodePudding user response:
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)
)
CodePudding user response:
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!