Search code examples
sqlpostgresqlgeospatialpostgisopenstreetmap

SQL Network Length Calculation Lon/Lat

I currently have an Azure postgresql database containing openstreetmap data and I was wondering if there's a SQL query that can get the total distance of a way by using the lat/longs of the nodes the way uses.

I would like the SQL query to return way_id and distance.

My current approach is using C# to download all the ways and all the nodes into dictionaries (with their id's being the key). I then loop through all the ways, grouping all the nodes that belong to that way and then use their lat/longs (value divided by 10000000) to calculate the distance. This part works as excepted but rather it be done on the server.

The SQL I have attempted is below but I'm stuck on calculating the total distance per way based on the lat/longs.

Update: Postgis extension is installed.

SELECT current_ways.id as wId, node_id, (CAST(latitude as float)) / 10000000 as lat, (CAST(longitude as float)) / 10000000 as lon FROM public.current_ways
JOIN current_way_nodes as cwn ON current_ways.id = cwn.way_id
JOIN current_nodes as cn ON cwn.node_id = cn.id

*output*
wId node_id latitude    longitude
2   1312575 51.4761127  -3.1888786
2   1312574 51.4759647  -3.1874216
2   1312573 51.4759207  -3.1870016
2   1213756 51.4758761  -3.1865223
3   ....

*desired_output*
way_id  length
2   x.xxx
3   ...

**Tables**
current_nodes
    id
    latitude
    longitude

current_ways
    id

current_way_nodes
    way_id
    node_id
    sequence_id
Solution

  • It would be much simpler should you also had the geometry in your table, i.e. the actual point instead of just the coordinates, or, even better, the actual lines.

    That being said, here is a query to get what you are looking for:

    SELECT w.way_id,
    ST_Length( -- compute the length
      ST_MAKELINE( --of a new line
        ST_SetSRID( --made of an aggregation of NEW points
          ST_MAKEPOINT((CAST(longitude as float)) / 10000000,(CAST(latitude as float)) / 10000000), --created using the long/lat from your text fields
        4326)  -- specify the projection 
       ORDER BY w.sequence_id -- order the points using the given sequence
       )::geography --cast to geography so the output length will be in meters and not in degrees
    ) as length_m
FROM current_way_nodes w
    JOIN current_nodes n ON w.node_id = n.node_id
GROUP BY w.way_id;