Skip to content

Instantly share code, notes, and snippets.

@mojodna
Created February 1, 2017 23:13
Show Gist options
  • Save mojodna/72af642f9a7d6dfb9b5fe61bb3fd7dfe to your computer and use it in GitHub Desktop.
Save mojodna/72af642f9a7d6dfb9b5fe61bb3fd7dfe to your computer and use it in GitHub Desktop.
Find all nodes, ways, and relations in a bounding box (AWS Athena + OSM ORC). A work in progress.
with params as (
select
27.61649608612061 as minlon,
53.85379229563698 as minlat,
27.671985626220707 as maxlon,
53.886459293813054 as maxlat
), direct_nodes as (
select n.id, n.version, n.changeset, n.timestamp, n.lat, n.lon
from
planet n,
params p
where type = 'node'
and lon between minlon and maxlon
and lat between minlat and maxlat
), all_request_ways as (
select
distinct w.id, w.version, w.changeset, w.timestamp, w.nds
FROM planet w
CROSS JOIN UNNEST(nds) WITH ORDINALITY AS t (nd, idx)
JOIN direct_nodes n ON n.id = nd.ref
where w.type = 'way'
), all_request_nodes as (
select n.id, n.version, n.changeset, n.timestamp, n.lat, n.lon
from
all_request_ways w
CROSS JOIN UNNEST(nds) AS t (nd)
JOIN planet n ON n.id = nd.ref
where n.type = 'node'
union
select n.id, n.version, n.changeset, n.timestamp, n.lat, n.lon
from direct_nodes n
), relations_from_ways_and_nodes as (
select distinct id, version, changeset, timestamp, tags, members
from
(
select r.id, r.version, r.changeset, r.timestamp, r.tags, r.members
FROM planet r
CROSS JOIN UNNEST(members) WITH ORDINALITY AS t (member, idx)
JOIN all_request_nodes n ON n.id = member.ref
where r.type = 'relation'
and member.type = 'node'
union all
select r2.id, r2.version, r2.changeset, r2.timestamp, r2.tags, r2.members
FROM planet r2
CROSS JOIN UNNEST(members) WITH ORDINALITY AS t (member, idx)
JOIN all_request_ways w ON w.id = member.ref
where r2.type = 'relation'
and member.type = 'way'
) wn
), all_request_relations as (
select r.id, r.version, r.changeset, r.timestamp, r.tags
from relations_from_ways_and_nodes r
union
select r.id, r.version, r.changeset, r.timestamp, r.tags
FROM relations_from_ways_and_nodes r2
CROSS JOIN UNNEST(members) WITH ORDINALITY AS t (member, idx)
JOIN planet r ON r.id = member.ref
where r.type = 'relation'
)
select *
from all_request_relations
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment