Is this postpass query to return a relation the simplest way to do it?

Help and support
,
1

I’m after the postpass equivalent of this OP routine:

https://overpass-turbo.eu/s/2x8z

{{data:overpass,server=https://overpass.atownsend.org.uk/api/}}
[bbox:{{bbox}}];
rel[name~"West London Orbital"];
out geom;

Note: as well as linear ways it includes nodes which are explicitly tagged as part of the relation.

After a lot of wrangling with an AI LLM it produced this:

https://overpass-turbo.eu/s/2x8C

WITH named_relations AS (
  SELECT
    osm_id AS relation_id,
    tags,
    geom
  FROM postpass_line
  WHERE osm_type = 'R'
    AND geom && {{bbox}}
    AND tags->>'name' ~ 'West London Orbital'
),
member_nodes AS (
  SELECT DISTINCT
    nr.relation_id,
    (member->>'ref')::bigint AS node_id
  FROM named_relations nr
  JOIN planet_osm_rels r
    ON r.id = nr.relation_id
  CROSS JOIN LATERAL jsonb_array_elements(r.members) AS member
  WHERE member->>'type' = 'N'
)
SELECT
  nr.relation_id AS osm_id,
  'R' AS osm_type,
  nr.tags,
  nr.geom
FROM named_relations nr

UNION ALL

SELECT
  p.osm_id,
  'N' AS osm_type,
  p.tags,
  p.geom
FROM member_nodes mn
JOIN postpass_point p
  ON p.osm_id = mn.node_id
WHERE p.geom && {{bbox}}

It works as expected, but is this really the efficient way to obtain this data? The query looks very long winded.

2

I’ve only used PostPass for basic things, and I haven’t been able to work relations or most Boolean operators. However, that search looks absurd.

Is the GeoFabrik server at a new address? It doesn’t seem to be working just at the moment.

3

For the data itself, there’s a different set of tables for raw OSM objects postpass-ops/SCHEMA.md at main · woodpeck/postpass-ops · GitHub

{{data:sql,server=https://postpass.geofabrik.de/api/,geojson=false}}
SELECT
    id, members, tags
    FROM 
    	planet_osm_rels
    WHERE 
      tags ->> 'name' ~ 'West London Orbital'

You would indeed need to retrieve and collect them yourself, at least as shown from JOIN planet_osm_rels r by the AI. Whether its query is the most efficient is another question. Haven’t done it myself.