How to write to geoparquet format?

[aborruso]

Hi,
I have tried running

COPY  table TO 'table.geoparquet' WITH (FORMAT GDAL, DRIVER 'parquet');

but I have Error: IO Error: Could not open driver.

Is not natively supported?

Thank you

[mtravis]

Parquet isn't yet available as a format in the spatial extension

https://github.com/duckdblabs/duckdb_spatial#embedded-gdal-based-inputoutput-functions

What are you trying to achieve as you maybe able to do it using parquet and accessing the geometry as WKB.

[aborruso]

Thank you @mtravis

I have created a spatial table in this way:

CREATE TABLE mytable AS SELECT * EXCLUDE wkb_geometry, ST_GeomFromWKB(wkb_geometry) AS geom FROM ST_Read('input.shp');

And then I have saved it as parquet in this way

COPY  mytable  TO 'mytable.parquet' (FORMAT PARQUET, CODEC 'ZSTD');

Now, if I rum

duckdb -csv -c "load spatial;select ST_AREA(geom) from 'mytable.parquet' limit 10"

I get

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

LINE 1: select ST_AREA(geom) from 'mytable

How to write properly my wrong sample select ST_AREA command?

Thank you again

[mtravis]

I think this may work.

ST_Area(ST_GeomFromWKB(geom))

[aborruso]

No, I have

Error: Not implemented Error: Geometry type not supported

[Maxxen]

The recommended way to work with parquet for now is to export the geometry columns as wkb (with ST_AsWKB) and then import from wkb (with ST_GeomFromWKB). This will give you something similar to GeoParquet, except that we don't write the additional metadata required by the spec.

e.g.

INSTALL spatial; INSTALL parquet; LOAD spatial; LOAD parquet;

--- export
COPY  (SELECT * REPLACE (ST_AsWKB(geom) AS wkb_geom) FROM mytable)  TO 'mytable.parquet' (FORMAT PARQUET, CODEC 'ZSTD');

--- import
SELECT * REPLACE (ST_GeomFromWKB(wkb_geom) as geom) FROM 'mytable.parquet';

Getting the spatial and parquet extensions to work better together is on the roadmap.

[aborruso]

Thank you very much