0

I found a psql code which looks like this:

select * from (
SELECT *,( 3959 * acos( cos( radians(6.414478) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( lat ) ) ) ) AS distance
FROM station_location
) al
where distance < 5
ORDER BY distance
LIMIT 20;

find the nearest location by latitude and longitude in postgresql

The problem is I completely don't get it. Before I used to use mysql and the syntax was completely different:

SELECT latitude, longitude, SQRT(
POW(69.1 * (latitude - [startlat]), 2) +
POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < 25 ORDER BY distance;

Find nearest latitude/longitude with an SQL query

How to convert this mysql instruction into psql, so the user's longitude and latitude will be startlng and startlat?

macpan
  • 93
  • 7

1 Answers1

1

Not commenting on the calculations themselves, this "port" to PostgreSQL should work:

select * from (select SQRT(
POW(69.1 * (10 - 8), 2) +
POW(69.1 * (100 - 10) * COS(10 / 57.3), 2)) AS distance
from table) d where distance < 25 ORDER BY distance;
Bjarni Ragnarsson
  • 1,731
  • 1
  • 6
  • 8