Invalid DuckDB query of Overture Maps with Geometry types

Question

I am trying to follow the example provided here https://dfhx9f55j8eg5.cloudfront.net/how-to/pr/96/examples/QGIS/#13/47.6/-122.33/0/45. The query is

SELECT 
    id, 
    names.primary AS primary_name,
    ST_AsText(ST_GeomFromWKB(geometry)) as geometry 
FROM 
    read_parquet('s3://overturemaps-us-west-2/release/2024-07- 
    22.0/theme=base/type=water/*', filename=true, hive_partitioning=1)
WHERE 
    bbox.xmin >= -91.3994
    and bbox.xmax <= -89.3864
    and bbox.ymin >= 29.152
    and bbox.ymax <= 30.5161

However, I am getting this error.

duckdb.duckdb.BinderException: Binder Error: No function matches the given name and argument types 'ST_GeomFromWKB(GEOMETRY)'. You might need to add explicit type casts.
    Candidate functions:
    ST_GeomFromWKB(WKB_BLOB) -> GEOMETRY
    ST_GeomFromWKB(BLOB) -> GEOMETRY

LINE 5:     ST_AsText(ST_GeomFromWKB(geometry)) as geometry

geometry is already a GEOMETRY type. Try: ST_AsText(geometry) as geometry? — Dogbert
– Dogbert, Commented Sep 14, 2024 at 16:35

Dogbert · Accepted Answer · 2024-09-14 16:47:40Z

The geometry column is already of type GEOMETRY, not a WKB BLOB:

DESCRIBE FROM read_parquet('s3://overturemaps-us-west-2/release/2024-07-22.0/theme=base/type=water/*', filename=true, hive_partitioning=1);

Output:

┌─────────────────┬────────────────────────────────────────────────────────────────────────────────────┬─────────┬─────────┬─────────┬─────────┐
│   column_name   │                                    column_type                                     │  null   │   key   │ default │  extra  │
│     varchar     │                                      varchar                                       │ varchar │ varchar │ varchar │ varchar │
├─────────────────┼────────────────────────────────────────────────────────────────────────────────────┼─────────┼─────────┼─────────┼─────────┤
│ id              │ VARCHAR                                                                            │ YES     │         │         │         │
│ geometry        │ GEOMETRY                                                                           │ YES     │         │         │         │
│ bbox            │ STRUCT(xmin FLOAT, xmax FLOAT, ymin FLOAT, ymax FLOAT)                             │ YES     │         │         │         │
│ version         │ INTEGER                                                                            │ YES     │         │         │         │
│ sources         │ STRUCT(property VARCHAR, dataset VARCHAR, record_id VARCHAR, update_time VARCHAR…  │ YES     │         │         │         │
│ subtype         │ VARCHAR                                                                            │ YES     │         │         │         │
│ class           │ VARCHAR                                                                            │ YES     │         │         │         │
│ names           │ STRUCT("primary" VARCHAR, common MAP(VARCHAR, VARCHAR), rules STRUCT(variant VAR…  │ YES     │         │         │         │
│ level           │ INTEGER                                                                            │ YES     │         │         │         │
│ source_tags     │ MAP(VARCHAR, VARCHAR)                                                              │ YES     │         │         │         │
│ wikidata        │ VARCHAR                                                                            │ YES     │         │         │         │
│ is_salt         │ BOOLEAN                                                                            │ YES     │         │         │         │
│ is_intermittent │ BOOLEAN                                                                            │ YES     │         │         │         │
│ filename        │ VARCHAR                                                                            │ YES     │         │         │         │
│ theme           │ VARCHAR                                                                            │ YES     │         │         │         │
│ type            │ VARCHAR                                                                            │ YES     │         │         │         │
├─────────────────┴────────────────────────────────────────────────────────────────────────────────────┴─────────┴─────────┴─────────┴─────────┤
│ 16 rows                                                                                                                            6 columns │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

So change:

ST_AsText(ST_GeomFromWKB(geometry)) as geometry

to:

ST_AsText(geometry) as geometry

and the query works.

Output (with LIMIT 10):

┌──────────────────────┬──────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────┐
│          id          │ primary_name │                                              geometry                                               │
│       varchar        │   varchar    │                                               varchar                                               │
├──────────────────────┼──────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 08b44713183b5fff00…  │              │ POLYGON ((-90.9721296 29.1793763, -90.9717518 29.1793975, -90.9715811 29.1795146, -90.9714836 29.…  │
│ 08b44713add08fff00…  │              │ POLYGON ((-90.9228761 29.1572309, -90.922751562 29.157288868, -90.922751562 29.157553177, -90.922…  │
│ 08b44713adc09fff00…  │              │ POLYGON ((-90.922951563 29.160931429, -90.9229337 29.1611889, -90.922951563 29.16143851, -90.9229…  │
│ 08b44713ad121fff00…  │              │ POLYGON ((-90.933937891 29.157614844, -90.928743541 29.157614844, -90.9287721 29.1579017, -90.929…  │
│ 08b44713ad083fff00…  │              │ POLYGON ((-90.922951563 29.168521028, -90.922777466 29.168801172, -90.922951563 29.168801172, -90…  │
│ 08b44713ada62fff00…  │              │ POLYGON ((-90.944924219 29.157614844, -90.933737891 29.157614844, -90.933737891 29.174238556, -90…  │
│ 08b447131a5a3fff00…  │              │ POLYGON ((-90.9339889 29.179462, -90.9339698 29.1794714, -90.933974 29.1794874, -90.9339731 29.17…  │
│ 08b44713ad0ddfff00…  │              │ POLYGON ((-90.928444727 29.168601172, -90.922901757 29.168601172, -90.9227772 29.1688016, -90.922…  │
│ 08b44713ad751fff00…  │              │ POLYGON ((-90.9244616 29.1738747, -90.9244393 29.1738803, -90.92441 29.1738898, -90.9243876 29.17…  │
│ 08b44713ad62cfff00…  │              │ POLYGON ((-90.923144216 29.174094336, -90.923125602 29.174094336, -90.9231268 29.1740994, -90.923…  │
├──────────────────────┴──────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 10 rows                                                                                                                         3 columns │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Collectives™ on Stack Overflow

Invalid DuckDB query of Overture Maps with Geometry types

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related