postgis - 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.