How to get distance between one point and all other points in a table?

Question

I have a table in Postgres with lat and long columns. Can I use the ST_Distance_Sphere or ST_Distance_Spheroid methods to get the distance between one point and all other points in the table? Do I first have to convert my existing lat/long to the Point datatype; if so is there any method to do it?

Answer 1

Yes, you can, and the PostGIS documentation page for ST_Distance_Sphere and ST_Distance_Sphereoid both contain good examples. Here's one with lon/lat values plugged in to the ST_Point function:

SELECT 
     st_distance_sphere(
           st_point(-69.23, 44.61), st_point(-75.42, 43.55)
     )

The result in this example is 508166.687378974 meters.

To make the calculation from a given point to other in a "lon_lat" table you would just do the following:

SELECT 
    st_distance_sphere(
        st_point(-69.23, 44.61), st_point(lon_lat.lon, lon_lat.lat)
    )
FROM
    lon_lat

Answer 2

In the line with katahdin answer, to calculate the distance to ALL other locations in your table, and assuming you have some kind of id in your rows, you can use something like this:

SELECT 
    g1.id,
    g2.id,
    st_distance_sphere(
        st_point(g1.lon,g1.lat),st_point(g2.lon,g2.lat)
    )
FROM
    lon_lat as g1,
    lon_lat as g2
WHERE 
    g1.id != g2.id