Use geometry in spatial relationships - DuckDB

[callumscoby]

Hi, I've extracted all the points from the recent beta.0 places release using the duckdb client in Python, storing it as a number of local Parquet files:

COPY
    (WITH formatting AS(
        SELECT
            id,
            UNNEST([names], recursive := true),
            UNNEST([addresses], recursive := true),
            country,
            confidence,
            categories->>'main' AS category,
            UNNEST([brand], recursive := true),
            ST_GeomFromWKB(geometry) AS geom_p_4326
        FROM '{{raw_path}}' 

SELECT 
    id,
    "primary" as name,
     country,    
    category,
    primary_1 AS brand,
    confidence,
    geom_p_4326

FROM formatting)

TO 'global.parquet' (FORMAT 'parquet', COMPRESSION 'snappy', ROW_GROUP_SIZE 1000000, PER_THREAD_OUTPUT true);

I now need to query these Parquet files for a given country, and ensure that all the POI are within a polygon of the countries boundaries.

In these Parquet files, the geometries look like this:

bytearray(b'\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01\x00\x00\x00\xcd\xcc\xcc\xcc\xcclf\xc0333333U\xc0')

When I try to use them in ST_Within, I get the error that it doesn't support BLOB geometries:

WITH cte AS(
    SELECT * FROM '{{path}}' LIMIT 20)

SELECT * FROM cte
JOIN country_boundaries
ON ST_Within(cte.geom_p_4326, ST_GeomFromWKB(country_boundaries.geometry));


BinderException: Binder Error: No function matches the given name and argument types 'ST_Within(BLOB, GEOMETRY)'. You might need to add explicit type casts.
	Candidate functions:
	ST_Within(POINT_2D, POLYGON_2D) -> BOOLEAN
	ST_Within(GEOMETRY, GEOMETRY) -> BOOLEAN

So I try to convert them to geometries using ST_GeomFromWKB, which gives this error when testing the conversion:

SELECT ST_GeomFromWKB(geom_p_4326) from '{{path}}' LIMIT 2;

NotImplementedException: Not implemented Error: WKB Reader: Geometry type 255 not supported

I'm really at a loss of how to get the points converted to the GEOMETRY type, or how to get them to work in the ST_Within - any help or advice would be greatly appreciated!

[DeoLeung]

same error when i export a duckdb table with geometry into parquet, then read it back and failed to convert the geometry blob into geometry data type

[mtravis]

@DeoLeung

I'm wondering which version of DuckDB you're using as the newest versions export directly to geoparquet so no need for casting.

Could share the code you're using so someone could try replicating.

[DeoLeung]

i upgraded to 1.1.3 and it's working now, it should be ok 1.1.0 onward :)

[mtravis]

@DeoLeung great stuff!

@callumscoby does an upgrade to the latest version DuckDb also help you?