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
Here's another example using the point operator:
Initial setup (only need to run once):
create extension cube;
create extension earthdistance;
And then the query:
select (point(-0.1277,51.5073) <@> point(-74.006,40.7144)) as distance;
distance
------------------
3461.10547602474
(1 row)
Note that points are created with LONGITUDE FIRST. Per the documentation:
Points are taken as (longitude, latitude) and not vice versa because longitude is closer to the intuitive idea of x-axis and latitude to y-axis.
Which is terrible design... but that's the way it is.
Your output will be in miles.
Gives the distance in statute miles between two points on the Earth's surface.
earthdistance
cube and earthdistance installed and verified by pg_available_extensions. Restarted PostgreSQL. [42883] ERROR: operator does not exist: point <@> point. Same as stackoverflow.com/questions/2665786/… Maybe an issue with Postgres on Windows?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;
WHERE clause, it's faster to let the database filter the results than transfering all the rows to Java first.A more accurate version of @strkol's answer, using the Haversine formula
CREATE OR REPLACE FUNCTION distance(
lat1 double precision,
lon1 double precision,
lat2 double precision,
lon2 double precision)
RETURNS double precision AS
$BODY$
DECLARE
R integer = 6371e3; -- Meters
rad double precision = 0.01745329252;
φ1 double precision = lat1 * rad;
φ2 double precision = lat2 * rad;
Δφ double precision = (lat2-lat1) * rad;
Δλ double precision = (lon2-lon1) * rad;
a double precision = sin(Δφ/2) * sin(Δφ/2) + cos(φ1) * cos(φ2) * sin(Δλ/2) * sin(Δλ/2);
c double precision = 2 * atan2(sqrt(a), sqrt(1-a));
BEGIN
RETURN R * c;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Input is in degrees (e.g. 52.34273489, 6.23847) and output is in meters.
Δ and λ is a good idea.Assuming you've installed the earthdistance module correctly, this will give you the distance in miles between two cities. This method uses the simpler point-based earth distances. Note that the arguments to point() are first longitude, then latitude.
create table lat_lon (
city varchar(50) primary key,
lat float8 not null,
lon float8 not null
);
insert into lat_lon values
('London, GB', 51.67234320, 0.14787970),
('New York, NY', 40.91524130, -73.7002720);
select
(
(select point(lon,lat) from lat_lon where city = 'London, GB') <@>
(select point(lon,lat) from lat_lon where city = 'New York, NY')
) as distance_miles
distance_miles
--
3447.58672105301
CREATE EXTENSION cube; CREATE EXTENSION earthdistance; to install earthdistance.An alternate Haversine formula, returning miles. (source)
CREATE OR REPLACE FUNCTION public.geodistance(
latitude1 double precision,
longitude1 double precision,
latitude2 double precision,
longitude2 double precision)
RETURNS double precision AS
$BODY$
SELECT asin(
sqrt(
sin(radians($3-$1)/2)^2 +
sin(radians($4-$2)/2)^2 *
cos(radians($1)) *
cos(radians($3))
)
) * 7926.3352 AS distance;
$BODY$
LANGUAGE sql IMMUTABLE
COST 100;
