search

duckdb / duckdb_spatial

MIT License
483 stars 39 forks source link

COPY () TO WITH FORMAT OpenFileGDB failing #233

Closed mtravis closed 9 months ago

mtravis commented 9 months ago

When using trying to write out to OpenFileGDB an error occurs.

COPY (select * from *.parquet') to 'admins.gdb' with (format GDAL, DRIVER 'OpenFileGDB');`

produces

ERROR 6: Unsupported geometry type
Error: IO Error: Could not create layer

More detail in this discussion

Maxxen commented 9 months ago

The issue you have is different from the original one. When you COPY (select * FROM *.parquet) you get the geometries as a WKB-encoded BLOB (because DuckDB does not natively support geoparquet yet). When you feed this into the spatial gdal driver it gets confused since WKB is not the same as DuckDB's internal GEOMETRY encoding.

You need to convert the original geometry from WKB into GEOMETRY using ST_GeomFromWKB(blob)

E.g. 

COPY (select * exclude(geometry), st_geomfromwkb(geometry) as geometry from *.parquet') to 'admins.gdb' with (format GDAL, DRIVER 'OpenFileGDB');`

However, the original issue still remains. The problem is that File Geodatabases can't have mixed geometry types in a single layer, but DuckDB's GEOMETRY type is mixed by default. The solution is probably to add an additional optional parameter to st_write to "force" the layer to be created with a specific geometry type, or throw if a geometry of a different type is encountered.