​
Overview

This code block demonstrates how to query a geospatial file. The supported file types are .geojson and .xlsx.

CREATE FOREIGN DATA WRAPPER <wrapper_name>
HANDLER spatial_fdw_handler
VALIDATOR spatial_fdw_validator;

CREATE SERVER <server_name>
FOREIGN DATA WRAPPER <wrapper_name>;

CREATE FOREIGN TABLE <table_name> ()
SERVER <server_name>
OPTIONS (files '<files>');
wrapper_name
required

Foreign data wrapper name. Can be any string.

server_name
required

Foreign server name. Can be any string.

table_name
required

Foreign table name. Can be any string.

files
required

For instance, s3://bucket/folder/file.geojson if the file is in Amazon S3, https://domain.tld/file.geojson if the file is on a HTTP server, or /path/to/file.geojson if the file is on the local file system.

​
Geospatial Options

There are a number of options that can be passed into the CREATE FOREIGN TABLE statement. These are the same options accepted by DuckDB’s st_read function in the spatial extension.

CREATE FOREIGN TABLE spatial_table ()
SERVER spatial_server
OPTIONS (
    files 's3://bucket/folder/file.geojson',
    layer 'layer_name'
);
files
required

The path of a single geospatial file. For instance, s3://bucket/folder/file.geojson if the file is in Amazon S3 or /path/to/file.geojson if the file is on the local file system.

sequential_layer_scan
default: "false"

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
default: "NULL"

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
default: "NULL"

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
default: "NULL"

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
default: "NULL"

A list of GDAL driver names that are allowed to be used to open the file. If empty, all drivers are allowed.

sibling_files
default: "NULL"

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
default: "NULL"

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
default: "false"

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.

​
Cloud Object Stores

The object stores documentation explains how to provide secrets and other credentials for geospatial files stored in object stores like S3.

Was this page helpful?

ParquetIceberg