DuckDB function ST_Read

Read and import a variety of geospatial file formats using the GDAL library.

The ST_Read table function is based on the GDAL translator library and enables reading spatial data from a variety of geospatial vector file formats as if they were DuckDB tables.

See ST_Drivers for a list of supported file formats and drivers.

Except for the path parameter, all parameters are optional.

Parameter	Type	Description
path	VARCHAR	The path to the file to read. Mandatory
sequential_layer_scan	BOOLEAN	If set to true, the table function will scan through all layers sequentially and return the first layer that matches the given layer name. This is required for some drivers to work properly, e.g., the OSM driver.
spatial_filter	WKB_BLOB	If set to a WKB blob, the table function will only return rows that intersect with the given WKB geometry. Some drivers may support efficient spatial filtering natively, in which case it will be pushed down. Otherwise the filtering is done by GDAL which may be much slower.
open_options	VARCHAR[]	A list of key-value pairs that are passed to the GDAL driver to control the opening of the file. E.g., the GeoJSON driver supports a FLATTEN_NESTED_ATTRIBUTES=YES option to flatten nested attributes.
layer	VARCHAR	The name of the layer to read from the file. If NULL, the first layer is returned. Can also be a layer index (starting at 0).
allowed_drivers	VARCHAR[]	A list of GDAL driver names that are allowed to be used to open the file. If empty, all drivers are allowed.
sibling_files	VARCHAR[]	A list of sibling files that are required to open the file. E.g., the ESRI Shapefile driver requires a .shx file to be present. Although most of the time these can be discovered automatically.
spatial_filter_box	BOX_2D	If set to a BOX_2D, the table function will only return rows that intersect with the given bounding box. Similar to spatial_filter.
keep_wkb	BOOLEAN	If set, the table function will return geometries in a wkb_geometry column with the type WKB_BLOB (which can be cast to BLOB) instead of GEOMETRY. This is useful if you want to use DuckDB with more exotic geometry subtypes that DuckDB spatial doesn't support representing in the GEOMETRY type yet.

Note that GDAL is single-threaded, so this table function will not be able to make full use of parallelism.

By using ST_Read, the spatial extension also provides “replacement scans” for common geospatial file formats, allowing you to query files of these formats as if they were tables directly.

SELECT * FROM './path/to/some/shapefile/dataset.shp';

In practice this is just syntax-sugar for calling ST_Read, so there is no difference in performance. If you want to pass additional options, you should use the ST_Read table function directly.

The following formats are currently recognized by their file extension:

Format	Extension
ESRI ShapeFile	.shp
GeoPackage	.gpkg
FlatGeoBuf	.fgb

Usage

ST_Read(
  col0,
  keep_wkb,
  max_batch_size,
  layer,
  sibling_files,
  allowed_drivers,
  open_options
)

Arguments

col0

VARCHAR

keep_wkb

BOOLEAN

max_batch_size

INTEGER

layer

VARCHAR

sibling_files

VARCHAR[]

allowed_drivers

VARCHAR[]

open_options

VARCHAR[]

Value

Unspecified.

Provided by

The spatial extension (LOAD spatial;).

SQL examples

-- Read a Shapefile
SELECT * FROM ST_Read('some/file/path/filename.shp');

-- Read a GeoJSON file
CREATE TABLE my_geojson_table AS SELECT * FROM ST_Read('some/file/path/filename.json');