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?

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

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.