Copilot commented on code in PR #3160: URL: https://github.com/apache/sedona/pull/3160#discussion_r3644333569
########## docs/blog/posts/osm-pbf-reader.md: ########## @@ -0,0 +1,219 @@ +--- +date: + created: 2026-07-24 +links: + - Geofabrik downloads (regional .osm.pbf): https://download.geofabrik.de/ + - OpenStreetMap: https://www.openstreetmap.org/ + - Spatial DataFrame / SQL app: https://sedona.apache.org/latest/tutorial/sql/ +authors: + - jia +title: "OpenStreetMap, Meet Sedona: Raw .osm.pbf to Spatial SQL" +--- + +# OpenStreetMap, Meet Sedona: Raw .osm.pbf to Spatial SQL + +OpenStreetMap is the world's map — every road, café, and coastline, edited by millions of people. It ships as `.osm.pbf`: a dense, compressed Protocol-Buffers blob of nodes, ways, and relations. Getting that into a cluster usually means a preprocessing detour through `osmium` or a staging database. + + + +<!-- more --> + +SedonaSpark reads `.osm.pbf` natively. Point the `osmpbf` format at a file and you get a Sedona DataFrame of raw OSM elements — no conversion step. And because Sedona is a distributed engine, that one-line read fans out across a cluster: a planet-scale file is just more partitions. From there it's ordinary Spatial SQL: assemble geometries, filter by tag, measure, join. Every snippet below runs against the small **Monaco** extract that ships in Sedona's test resources, so you can reproduce it verbatim — and scale it up unchanged. + +## One line to raw OSM + +```python +from sedona.spark import SedonaContext + +sedona = SedonaContext.create(SedonaContext.builder().master("local[*]").getOrCreate()) + +# a regional extract from Geofabrik, or Sedona's bundled Monaco sample +osm = sedona.read.format("osmpbf").load( + "spark/common/src/test/resources/osmpbf/monaco-latest.osm.pbf" +) +osm.createOrReplaceTempView("osm") +osm.printSchema() +``` + +The reader gives you the raw OSM element model, one row per element: + +``` + |-- id: long + |-- kind: string (node | way | relation) + |-- location: struct<longitude, latitude> -- nodes only + |-- tags: map<string, string> + |-- refs: array<long> -- ordered member node/way ids (ways & relations) + |-- ref_roles: array<string> + |-- ref_types: array<string> + |-- changeset / timestamp / uid / user / version / visible -- edit metadata +``` + +Monaco is small but complete — three element kinds, one table: + +```python +sedona.sql("SELECT kind, COUNT(*) AS n FROM osm GROUP BY kind ORDER BY n DESC").show() +``` + +``` ++--------+-----+ +|kind |n | ++--------+-----+ +|node |39587| +|way | 5777| +|relation| 309| ++--------+-----+ +``` + +## Nodes are points + +A **node** is the only element with a coordinate, so points are immediate. Here are Monaco's amenities: + +```python +sedona.sql(""" + SELECT id, tags['amenity'] AS amenity, tags['name'] AS name, + ST_ReducePrecision(ST_Point(location.longitude, location.latitude), 5) AS geom + FROM osm + WHERE kind = 'node' AND tags['amenity'] IS NOT NULL + ORDER BY id LIMIT 5 +""").show(truncate=False) +``` + +``` ++--------+-------+------------------------------+------------------------+ +|id |amenity|name |geom | ++--------+-------+------------------------------+------------------------+ +|25191432|parking|Parking du Chemin des Pêcheurs|POINT (7.42711 43.73128)| +|25230434|fuel |Esso |POINT (7.41209 43.72851)| +|25239190|parking|null |POINT (7.42919 43.74531)| +|25239191|parking|Parking du centre commercial |POINT (7.41792 43.7309) | +|25249199|parking|Parking de la Gare |POINT (7.41908 43.7389) | ++--------+-------+------------------------------+------------------------+ +``` + +## Ways are just node IDs — assemble the lines + +Here's the part that trips people up. A **way** (a road, a river, a building outline) has **no coordinates of its own** — only `refs`, an *ordered* list of the node IDs it passes through. To get a geometry you resolve those IDs back to node coordinates, in order, and connect them. + + + +That's a `posexplode` to keep the ordering, a join to the nodes, and a rebuild in sequence: + +```python +roads = sedona.sql(""" + WITH nodes AS ( + SELECT id, location.longitude AS lon, location.latitude AS lat + FROM osm WHERE kind = 'node' + ), + way_pts AS ( -- one row per (way, vertex), ordered + SELECT w.id AS way_id, w.tags AS tags, w.pos AS seq, n.lon, n.lat + FROM (SELECT id, tags, posexplode(refs) AS (pos, ref) + FROM osm WHERE kind = 'way') w + JOIN nodes n ON n.id = w.ref + ) + SELECT way_id, + any_value(tags)['highway'] AS highway, + ST_GeomFromText(concat('LINESTRING(', + array_join( + transform(array_sort(collect_list(struct(seq, lon, lat))), + x -> concat(x.lon, ' ', x.lat)), + ', '), + ')')) AS geom + FROM way_pts + GROUP BY way_id + HAVING count(*) >= 2 AND any_value(tags)['highway'] IS NOT NULL +""") Review Comment: The road-assembly query uses `any_value(tags)['highway']`, which is a non-deterministic aggregate and also appears nowhere else in the docs. Since `tags` is constant per `way_id`, you can avoid `any_value` entirely by extracting `highway` before the join and grouping by `(way_id, highway)`. This also lets you build the LineString without string/WKT concatenation by using `ST_MakeLine(ARRAY[Geometry])`, which is more robust. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
