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
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 │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘