Instantly share code, notes, and snippets.

Embed
What would you like to do?
Split up OSM ways in PostgreSQL
--PostgreSQL
CREATE TABLE osm_network_graph AS
SELECT ROW_NUMBER() OVER (ORDER BY lt.way_id) AS split_way_id,
lt.way_id AS old_way_id,
lt.left_node AS start_node,
rt.right_node AS end_node,
rt.right_sequence AS sequence_id
FROM
(SELECT way_id,
node_id AS left_node,
sequence_id AS left_sequence
FROM way_nodes) AS lt
JOIN
(SELECT way_id,
node_id AS right_node,
sequence_id - 1 AS
right_sequence
FROM way_nodes WHERE sequence_id != 0) AS rt
ON lt.left_sequence = rt.right_sequence
WHERE lt.way_id = rt.way_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment