Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 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
share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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