21

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

21

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;
```

Can you explain the math in here? What 69.1 and 57.3 represent - jamesfzhang 2013-07-15 18:00

I'd imagine they are constants used to convert latitude and longitude into miles - Jeffrey Biles 2013-09-24 16:05

To use KM instead of miles, use constants 111.12 & 92.215 to replace 69.1 & 57. - Le Droid 2014-06-09 23:19

Why is the approach to do this with Postgresql and not with Java for example in a functio - Fernando Pie 2017-06-15 05:01

@FernandoPie When there are many rows and the distance is part of the

`WHERE`

clause, it's faster to let the database filter the results than transfering all the rows to Java first - Jodiug 2018-10-10 14:17
33

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.

Got a vote for being cleaner than the other answer based on

`earthdistance`

igorsantos07 2015-11-22 05:24
thank you, great solution - Bagdat 2016-06-18 19:50

what is the unit of distance - William 2018-08-30 09:35

"Gives the distance in statute miles between two points on the Earth's surface. - Steve Tauber 2018-08-31 08:55

Do you have to run "create extension" queries each time - TheRealChx101 2018-10-29 21:26

@TheRealChx101 i've updated the instructions to make it clear that those are for setup and you only need to run them onc - Steve Tauber 2018-10-31 13:17

8

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
```

Thanks, this answer is the best way to do it. You just need to run

`CREATE EXTENSION cube; CREATE EXTENSION earthdistance;`

to install earthdistance - sudo 2015-07-27 23:51
5

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.