Hi all,
i’ve downloaded the german.highway.osm.bz file into a postgres database. i want to extract all motorway junction nodes of a certain highway with a sql query in the order they are connected, so that each pair of geocoordinate build a section between one junction and the next. I need that to calculate distances of highway sections. can anybody tell me which query to use to get the desired result?
i’ve tried the following query but i don’t know if it’s right. also i have no idea which function to use for tranforming the geometry datatypes into geocoordinates.
SELECT * FROM nodes n,ways w,way_nodes wn,relations r, relation_members rm
WHERE r.id = rm.relation_id AND rm.member_id = w.id AND n.id = wn.node_id AND w.id = wn.way_id
AND w.tags::hstore → ‘highway’ = ‘motorway’ AND w.tags::hstore → ‘ref’ = ‘A 1’
ORDER BY rm.sequence_id, wn.way_id, wn.sequence_id;
whereby ‘A 1’ is one possible german motorway. the query should print something out like
highway sequence longitude/latitude
A1 0 8.3214/50.342234
A1 1 8.2665/50.023244
Any help would be appreciated.
regards
mario