COPY () TO WITH FORMAT OpenFileGDB

[BruceHarold]

Hello ducklings

I'm reading Overture Foundation parquet from S3, that works well. I'm attempting to use the spatial extension to write file geodatabase. This is using the Python API in a Notebook.

If I show the desired relation, all looks well, I can see the expected schema and a geometry column. Geometry is made from ST_GeomFromWKB.

┌──────────────────┬─────────┬──────────────────────┬───┬────────────────────┬────────────────┬──────────────────────┐
│ id │ version │ CAST(updateTime AS… │ … │ confidence │ brand_wikidata │ geometry │
│ varchar │ int32 │ date │ │ double │ varchar │ geometry │
├──────────────────┼─────────┼──────────────────────┼───┼────────────────────┼────────────────┼──────────────────────┤
│ 1030077443785857 │ 0 │ 2023-12-10 │ … │ 0.9849923430321592 │ NULL │ POINT (0.01765 51.… │
│ 476012882531094 │ 0 │ 2023-12-10 │ … │ 0.4800796812749004 │ NULL │ POINT (0.01765 51.… │
│ 133005663575482 │ 0 │ 2023-12-10 │ … │ 0.9849923430321592 │ NULL │ POINT (0.0180884 5… │
│ 158382421162912 │ 0 │ 2023-12-10 │ … │ 0.954954954954955 │ NULL │ POINT (0.0193693 5… │
│ 821534071367374 │ 0 │ 2023-12-10 │ … │ 0.8952380952380953 │ NULL │ POINT (0.0216293 5… │
├──────────────────┴─────────┴──────────────────────┴───┴────────────────────┴────────────────┴──────────────────────┤
│ 5 rows 7 columns (6 shown) │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

My COPY command is: copy ({}) to 'Places.gdb' with (format GDAL, DRIVER 'OpenFileGDB', SRS 'EPSG:4326');".format(sql)
However on COPY I get this error:

NotImplementedException: Not implemented Error: GDAL Error (6): Unsupported geometry type

Anyone had any success copying to file geodatabase? Thanks.

[mtravis]

I replicated this using DuckDB Cli v0.9.2

duckdb -c 
"install spatial; 
load spatial; 
create table admins as Select * from '20230725_211237_00132_5p54t_7dc54ea9-ab92-4741-8dae-56fa8593fe87.parquet'; 
COPY (select * from admins) to 'admins.gdb' with (format GDAL, DRIVER 'OpenFileGDB');"
ERROR 6: Unsupported geometry type
Error: IO Error: Could not create layer

[Maxxen]

This should really be an issue, but will have a look regardless!

[mtravis]

@Maxxen Ah, I hadn't clocked that it wasn't an issue. I'll create one and link to this.

[mtravis]

Now in #233

[BruceHarold]

Thanks. I have a workaround but it is very verbose.

[mtravis]

@BruceHarold Cool. Could you share please.

[BruceHarold]

OK, but it's ghastly; as I'm working in an ArcGIS Online hosted notebook I have the Esri stack available. Below is the first cell where I begin to construct my geodatabase:

`print('Extracting Places starting at {}'.format(getNow()))
if arcpy.Exists(r"/arcgis/Places.gdb"):
arcpy.management.Delete(r"/arcgis/Places.gdb")
sql = "create or replace view places_view as select * from read_parquet('{}', filename=false, hive_partitioning=1);".format(theme)
duckdb.sql(sql)

sql = """select id,version,try_cast(updateTime as datetime) as updateTime,categories.main as categories_main,confidence,
brand.wikidata as brand_wikidata,ST_AsText(ST_GeomFromWKB(geometry)) as wkt from places_view where {};""".format(whereExp)
relation = duckdb.sql(sql)
arcpy.env.workspace = arcpy.management.CreateFileGDB(out_folder_path=r"/arcgis",out_name="Places.gdb",out_version="CURRENT").getOutput(0)
places = arcpy.management.CreateFeatureclass(out_path="Places.gdb",out_name="Places",geometry_type="POINT",has_m="DISABLED",has_z="DISABLED",spatial_reference=sR).getOutput(0)
arcpy.management.AddField(in_table=places,field_name="id",field_type="TEXT",field_length=40,field_is_nullable="NULLABLE",field_is_required="REQUIRED")
arcpy.management.AddField(in_table=places,field_name="version",field_type="LONG",field_is_nullable="NULLABLE")
arcpy.management.AddField(in_table=places,field_name="updateTime",field_type="DATE")
arcpy.management.AddField(in_table=places,field_name="categories_main",field_type="TEXT",field_length=60)
arcpy.management.AddField(in_table=places,field_name="confidence",field_type="DOUBLE",field_is_nullable="NULLABLE")
arcpy.management.AddField(in_table=places,field_name="brand_wikidata",field_type="TEXT",field_length=100,field_alias="",field_is_nullable="NULLABLE",field_is_required="NON_REQUIRED")
print('Places workspace and feature class created at {}'.format(getNow()))
with arcpy.da.InsertCursor(places,["id","version","updateTime","categories_main","confidence","brand_wikidata","shape@"]) as iCursor:
row = relation.fetchone()
i = 1
if row:
while row:
if i % 10000 == 0:
print('Inserted {} rows at {}'.format(str(i),getNow()))
row = list(row)
row[-1] = arcpy.FromWKT(row[-1],sR)
iCursor.insertRow(row)
i+=1
row = relation.fetchone()
del iCursor
print('Places feature class populated at {}'.format(getNow()))`

[BruceHarold]

Resolved in a separate thread