DuckDB OpenFileGDB Extension

A DuckDB extension for reading and writing Esri File Geodatabases (.gdb) using GDAL's OpenFileGDB driver.

Installation

INSTALL openfgdb FROM community;
LOAD openfgdb;

Features

• ATTACH geodatabases as DuckDB databases
• Read layers with full type mapping (integers, floats, strings, dates, timestamps, geometry)
• Write to existing geodatabases (CREATE TABLE, INSERT, CREATE TABLE AS)
• COPY TO export query results to new .gdb files
• Standalone functions for quick inspection without ATTACH

Usage

ATTACH a geodatabase

ATTACH 'path/to/data.gdb' AS mydb (TYPE fgdb);
SHOW TABLES;
SELECT * FROM mydb.my_layer LIMIT 10;

Standalone table functions

-- List all layers in a geodatabase
SELECT * FROM fgdb_tables('path/to/data.gdb');

-- Read a specific layer directly (no ATTACH needed)
SELECT * FROM fgdb_read('path/to/data.gdb', 'layer_name');

Write to an existing geodatabase

ATTACH 'path/to/existing.gdb' AS mydb (TYPE fgdb);

-- Create a new table
CREATE TABLE mydb.new_layer (name VARCHAR, value INTEGER, shape GEOMETRY);

-- Insert data
INSERT INTO mydb.new_layer (name, value, shape)
VALUES ('point_a', 42, 'POINT(-117.5 34.0)'::GEOMETRY);

-- Create table from query
CREATE TABLE mydb.subset AS
SELECT * FROM mydb.existing_layer WHERE population > 10000;

COPY TO a new geodatabase

COPY (SELECT name, geom FROM my_table)
TO 'output.gdb' (FORMAT openfgdb, LAYER 'exported_layer');

Type Mapping

ArcGIS / Geodatabase Type	OGR Type	DuckDB Type
Short Integer / Long Integer	OFTInteger	INTEGER
Big Integer	OFTInteger64	BIGINT
Float / Double	OFTReal	DOUBLE
Text	OFTString	VARCHAR
Date (date + time)	OFTDateTime	TIMESTAMP
DateOnly	OFTDate	DATE
TimeOnly	OFTTime	TIME
TimestampOffset (date + time + UTC offset)	OFTDateTime	TIMESTAMP
Binary / BLOB	OFTBinary	BLOB
Geometry (Point, Polygon, etc.)	—	GEOMETRY (WKB)

Note: TimestampOffset fields are read as TIMESTAMP (the UTC offset from the OGR tz_flag is available but not currently mapped to TIMESTAMP_TZ).

Limitations

• Auto-creation of new geodatabases on ATTACH is not yet supported (use COPY TO for creating new .gdb files)
• DELETE and UPDATE are not supported (OpenFileGDB driver limitation)
• Write operations are single-threaded
• Geometry type defaults to WGS84 (EPSG:4326) for new layers

Building from Source

See SETUP.md for development environment setup instructions.

License

MIT