select motorway exit nodes SQL

select_exits.md

I use this series of queries to get all nodes that should represent motorway_junction nodes:

-- create temp table to store all motorway nodes
create table motorway_nodes as (select (st_dump(st_points(linestring))).geom geom, id from ways where tags->'highway' = 'motorway');
-- create temp table to store all motorway_link start nodes
create table motorway_link_start_nodes as (select st_pointn(linestring, 1) geom, id from ways where tags->'highway' = 'motorway_link');
-- create temp table with the intersection between these two
create table offramp_starts as (select l.id lid, m.id mid, l.geom from motorway_nodes m, motorway_link_start_nodes l where st_equals(m.geom, l.geom));
-- select the OSM nodes at these locations
create table exit_nodes as (select n.* from nodes n, offramp_starts s where st_equals(n.geom, s.geom));

Not sure if there is a more optimal way...?

CTE version,

with 
	motorway_nodes as (select (st_dump(st_points(linestring))).geom geom, id from ways where tags->'highway' = 'motorway'),
	motorway_link_start_nodes as (select st_pointn(linestring, 1) geom, id from ways where tags->'highway' = 'motorway_link'),
	offramp_starts as (select l.id lid, m.id mid, l.geom from motorway_nodes m, motorway_link_start_nodes l where st_equals(m.geom, l.geom))
select n.* into exit_nodes from nodes n, offramp_starts s where st_equals(n.geom, s.geom);

Here's a variant that selects the first motorway_link connected to the junction nodes. There should have destination and / or destination:* on them:

with 
	motorway_nodes as (select (st_dump(st_points(linestring))).geom geom, id from ways where tags->'highway' = 'motorway'),
	motorway_link_start_nodes as (select st_pointn(linestring, 1) geom, id from ways where tags->'highway' = 'motorway_link'),
	offramp_starts as (select l.id lid, m.id mid, l.geom from motorway_nodes m, motorway_link_start_nodes l where st_equals(m.geom, l.geom))
select w.* into exit_links from ways w, offramp_starts s where st_equals(st_pointn(w.linestring, 1), s.geom) and w.tags->'highway'='motorway_link';