insert into street_segments (way_id, node_from, node_to, geom)
--This subselect is responsible for getting the geometry
select g.way_id, node_from, node_to, st_makeline(geom order by g.sequence_id) from
way_nodes_with_intersection as g,
nodes,
-- and this one gets a from and to node id and sequence number where
-- from and to are intersections and no nodes between from and to are
-- intersections
(select f.way_id, f.node_id as node_from, t.node_id as node_to, f.sequence_id as f_seq, t.sequence_id as t_seq from
way_nodes_with_intersection as f, way_nodes_with_intersection as t
where
t.way_id = f.way_id and
f.intersection = true and
t.intersection = true and
f.sequence_id < t.sequence_id and
not exists (select between_wn.node_id as x
from way_nodes_with_intersection as between_wn
where
between_wn.way_id = f.way_id and
between_wn.intersection = true and
between_wn.sequence_id > f.sequence_id and
between_wn.sequence_id < t.sequence_id)) as rest_q
where
nodes.id = g.node_id and
g.way_id = rest_q.way_id and
g.sequence_id >= f_seq and
g.sequence_id <= t_seq group by g.way_id, node_from, node_to;