ogr_fdw
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
ogr_fdw | 1.1.7 | GIS | MIT | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 1550 | ogr_fdw | No | Yes | No | Yes | No | Yes | - |
| Related | postgis file_fdw postgres_fdw postgis_topology postgis_raster postgis_sfcgal postgis_tiger_geocoder address_standardizer |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 1.1.7 | 1817161514 | ogr_fdw | - |
| RPM | PGDG | 1.1.7 | 1817161514 | ogr_fdw_$v | - |
| DEB | PGDG | 1.1.7 | 1817161514 | postgresql-$v-ogr-fdw | - |
Install
You can install ogr_fdw directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install ogr_fdw; # Install for current active PG version
pig ext install -y ogr_fdw -v 18 # PG 18
pig ext install -y ogr_fdw -v 17 # PG 17
pig ext install -y ogr_fdw -v 16 # PG 16
pig ext install -y ogr_fdw -v 15 # PG 15
pig ext install -y ogr_fdw -v 14 # PG 14
dnf install -y ogr_fdw_18 # PG 18
dnf install -y ogr_fdw_17 # PG 17
dnf install -y ogr_fdw_16 # PG 16
dnf install -y ogr_fdw_15 # PG 15
dnf install -y ogr_fdw_14 # PG 14
apt install -y postgresql-18-ogr-fdw # PG 18
apt install -y postgresql-17-ogr-fdw # PG 17
apt install -y postgresql-16-ogr-fdw # PG 16
apt install -y postgresql-15-ogr-fdw # PG 15
apt install -y postgresql-14-ogr-fdw # PG 14
Create Extension:
CREATE EXTENSION ogr_fdw;
Usage
OGR is the vector half of the GDAL spatial data access library. It allows access to a large number of GIS data formats using a simple C API. Since OGR exposes a simple table structure and PostgreSQL foreign data wrappers allow access to table structures, the fit is pretty perfect.
Quick Start
CREATE EXTENSION postgis;
CREATE EXTENSION ogr_fdw;
Use the ogr_fdw_info tool to read an OGR data source and output server/table definitions:
ogr_fdw_info -s /tmp/test -l pt_two
CREATE SERVER "myserver"
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '/tmp/test',
format 'ESRI Shapefile' );
CREATE FOREIGN TABLE "pt_two" (
fid integer,
"geom" geometry(Point, 4326),
"name" varchar,
"age" integer,
"height" real,
"birthdate" date )
SERVER "myserver"
OPTIONS (layer 'pt_two');
SELECT * FROM pt_two;
Filter pushdown is supported — both simple predicates and bounding box filters (&&):
SET client_min_messages = debug1;
SELECT name, age, height
FROM pt_two
WHERE height < 5.7
AND geom && ST_MakeEnvelope(0, 0, 1, 1);
DEBUG: OGR SQL: (height < 5.7)
DEBUG: OGR spatial filter (0 0, 1 1)
Limitations
- PostgreSQL 11 or higher required
- Limited non-spatial query restrictions are pushed down to OGR (only
>,<,<=,>=,=) - Only bounding box filters (
&&) are pushed down for spatial filtering - OGR connections are made for every query (no pooling)
- All columns are retrieved every time
Examples
WFS (Web Feature Service)
CREATE SERVER geoserver
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'WFS:https://demo.geo-solutions.it/geoserver/wfs',
format 'WFS' );
CREATE FOREIGN TABLE topp_states (
fid bigint,
the_geom Geometry(MultiSurface,4326),
gml_id varchar,
state_name varchar,
state_fips varchar,
state_abbr varchar,
land_km double precision,
persons double precision )
SERVER "geoserver"
OPTIONS (layer 'topp:states');
File Geodatabase
CREATE SERVER fgdbtest
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '/tmp/Querying.gdb',
format 'OpenFileGDB' );
CREATE FOREIGN TABLE cities (
fid integer,
geom geometry(Point, 4326),
city_name varchar,
state_name varchar,
elevation integer,
pop1990 integer )
SERVER fgdbtest
OPTIONS (layer 'Cities');
Advanced Features
Writeable Tables
If the OGR driver supports it, you can insert/update/delete records. Writeable tables require a fid column in the table definition.
ALTER SERVER myserver
OPTIONS (ADD updateable 'true');
Column Name Mapping
Map remote column names to local names:
CREATE FOREIGN TABLE typetest_fdw_mapped (
fid bigint,
supertime time OPTIONS (column_name 'clock'),
thebestname varchar OPTIONS (column_name 'name') )
SERVER wraparound
OPTIONS (layer 'typetest');
Automatic Table Import
Use IMPORT FOREIGN SCHEMA to auto-create foreign table definitions:
CREATE SCHEMA fgdball;
-- Import all tables
IMPORT FOREIGN SCHEMA ogr_all
FROM SERVER fgdbtest
INTO fgdball;
-- Import specific tables
IMPORT FOREIGN SCHEMA ogr_all
LIMIT TO(cities)
FROM SERVER fgdbtest
INTO fgdball;
GDAL Options
Control driver behavior with config and open options:
CREATE SERVER myserver_latin1
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '/tmp/test',
format 'ESRI Shapefile',
config_options 'SHAPE_ENCODING=LATIN1' );
Multiple config options can be passed as a space-separated list.
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.