Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres geolocation points Distance

Tags:

sql

postgresql

I'm trying to make a query to determinate the distances between geolocation points in a Postgres database this is my query

SELECT latitude, longitude, SQRT(
POW(69.1 * (latitude - 31.8679), 2) +
POW(69.1 * (-116.6567 - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM locations 
HAVING distance < 25 
ORDER BY distance

but it give me this error

ERROR: column "distance" does not exist LINE 5: HAVING distance < 25

if i remove the part "HAVING distance < 25" the query runs OK

like image 381
Alejandro Rangel Avatar asked Nov 18 '13 06:11

Alejandro Rangel


1 Answers

ok i solve it checking other questions

SELECT t.* FROM (
SELECT latitude, longitude,SQRT(POW(69.1 * (latitude - 31.8679), 2) +
POW(69.1 * (-116.6567 - longitude) * COS(latitude / 57.3), 2)) AS distance 
FROM Locations) t
WHERE distance < 1
ORDER BY distance 
like image 121
Alejandro Rangel Avatar answered Oct 10 '22 07:10

Alejandro Rangel