PostgreSQL latitude longitude query

Question

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

Answer 1

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.

Answer 2

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;