Missing ref values in ST_ReadOSM function

[RaczeQ]

I've started working on native OSM file reader and expand SQL-fu from the comment in #90.

Unfortunately, there seems to be some kind of bug, since some ways and relations have empty refs column.

Here is the example of the object that I can't parse with duckdb:
https://www.openstreetmap.org/relation/15950028

PBF file: https://download.geofabrik.de/europe/albania-latest.osm.pbf

import duckdb

connection = duckdb.connect()
connection.install_extension('spatial')
connection.load_extension('spatial')

elements = connection.sql("SELECT * FROM ST_READOSM('albania-latest.osm.pbf');")

# Finding relation with given id
elements.filter('id = 15950028')
# >> refs = [226793337, 1178795731, 1178795730, 1178795729]  # this part is fine

# Trying to get nodes of the way
elements.filter('id = 226793337')
# >> refs = NULL  # HERE is the problem

I can't find the refs of the way that is the part of a relation.
I tried to get the geometry with osmnx and it seems to be loading it correctly, and openstreetmap returns full members hirerachy from relation down to nodes.

import osmnx as ox
ox.geocode_to_gdf(query=["R15950028"], by_osmid=True)

[RaczeQ]

I tried to join all relations with ways and it turns out that more way members from some relations have this problem. Here is the full SQL query that I have used:

WITH elements AS (
    SELECT * FROM ST_ReadOSM('albania-latest.osm.pbf')
),
nodes AS (
    SELECT *, ST_Point(lon, lat) geometry
    FROM elements
    WHERE kind = 'node'
        AND lon IS NOT NULL
        AND lat IS NOT NULL
),
ways AS (
    SELECT *
    FROM elements
    WHERE kind = 'way'
    AND len(refs) > 0
),
relations AS (
    SELECT *
    FROM elements
    WHERE kind = 'relation'
    AND len(refs) > 0
    AND list_contains(map_keys(tags), 'type')
    AND list_has_any(map_extract(tags, 'type'), ['boundary', 'multipolygon'])
),
ways_with_geometries AS (
    SELECT id, ST_MakeLine(list(geometry)) geometry
    FROM (
        SELECT w.*, n.geometry
        FROM (
            SELECT
                id,
                UNNEST(refs) as ref,
                UNNEST(range(length(refs))) as idx,
            FROM ways
        ) w
        JOIN nodes n
        ON n.id = w.ref 
        ORDER BY w.id, w.idx
    )
    GROUP BY id
),
relations_unnested AS (
    SELECT
        *
    FROM (
        SELECT
            id,
            tags,
            UNNEST(refs) as ref,
            UNNEST(ref_types) as ref_type,
            UNNEST(ref_roles) as ref_role,
            UNNEST(range(length(refs))) as idx,
        FROM relations
    )
    WHERE
        ref_type = 'way'
),
relations_with_ways AS (
    SELECT
        r.id, r.ref_type, r.ref_role, r.ref, w.geometry
    FROM relations_unnested r
    JOIN ways_with_geometries w
    ON w.id = r.ref 
    ORDER BY r.id, r.idx
)
-- SELECT COUNT(DISTINCT id) FROM relations -- 1309 rows
SELECT COUNT(DISTINCT id) FROM relations_with_ways -- 1031 rows (278 relations can't be parsed)

[Maxxen]

Hello! Thanks for reporting this issue! It seems like we are too greedy when parsing the PBF and assume that fields are in order, which doesn't seem to be the case here. It should be pretty straight forward to change this and I'm working on a fix.