jiayuasu commented on code in PR #3160: URL: https://github.com/apache/sedona/pull/3160#discussion_r3645964244
########## 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: Fixed in 2b156ed3c23. The highway tag is now extracted before the join with the filter pushed ahead of it, grouping is on (way_id, highway), and the LineString is built with ST_MakeLine over the sorted point array. Verified against the Monaco extract: identical results (3334 ways, 163.9 km, same per-class breakdown). -- 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]
