PostgreSQL latitude longitude query
postgis, postgresql
Solution
This module is optional and is not installed in the default PostgreSQL instalatlion. You must install it from the contrib directory.
You can use the following function to calculate the approximate distance between coordinates (in miles):
CREATE OR REPLACE FUNCTION distance(lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT) RETURNS FLOAT AS $$
DECLARE
x float = 69.1 * (lat2 - lat1);
y float = 69.1 * (lon2 - lon1) * cos(lat1 / 57.3);
BEGIN
RETURN sqrt(x * x + y * y);
END
$$ LANGUAGE plpgsql;
Problem
i have `latitude` and `longitude` columns in `location` table in PostgreSQL database, and I am trying to execute distance query with a PostgreSQL function. I read this chapter of the manual: https://www.postgresql.org/docs/current/static/earthdistance.html but I think I'm missing something there. How should I do that? Are there more examples available