Find all nodes, ways, and relations in a bounding box (AWS Athena + OSM ORC). A work in progress.

bbox.sql

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