ogr_fdw
ogr_fdw : foreign-data wrapper for GIS data access
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 1550 | ogr_fdw
|
ogr_fdw
|
1.1.7 |
GIS
|
MIT
|
C
|
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-d-r
|
No
|
Yes
|
No
|
Yes
|
yes
|
no
|
| Relationships | |
|---|---|
| See Also | postgis
file_fdw
postgres_fdw
postgis_topology
postgis_raster
postgis_sfcgal
postgis_tiger_geocoder
address_standardizer
|
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | PGDG
|
1.1.7 |
18
17
16
15
14
|
ogr_fdw |
- |
| RPM | PGDG
|
1.1.7 |
18
17
16
15
14
|
ogr_fdw_$v |
- |
| DEB | PGDG
|
1.1.7 |
18
17
16
15
14
|
postgresql-$v-ogr-fdw |
- |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
el8.aarch64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
el9.x86_64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
el9.aarch64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
el10.x86_64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
el10.aarch64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
d12.x86_64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
d12.aarch64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
d13.x86_64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
d13.aarch64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
u22.x86_64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
u22.aarch64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
u24.x86_64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
u24.aarch64
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
PGDG 1.1.7
|
Source
Install
Make sure PGDG repo available:
pig repo add pgdg -u # add pgdg repo and update cacheInstall this extension with pig:
pig install ogr_fdw; # install via package name, for the active PG version
pig install ogr_fdw -v 18; # install for PG 18
pig install ogr_fdw -v 17; # install for PG 17
pig install ogr_fdw -v 16; # install for PG 16
pig install ogr_fdw -v 15; # install for PG 15
pig install ogr_fdw -v 14; # install for PG 14Create this extension with:
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_twoCREATE 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.