Using Foreign Data Wrappers

Making data outside my PostGIS database

appear to be in my database.

What is "Foreign Data"?

Very simply Foreign data is data outside your current PostgreSQL/PostGIS data
- Data in another PostgreSQL/PostGIS database
- Data in another type of database
  - Data held in an Oracle database
  - Data in a SQL Server database
- File based data e.g. ESRI shape files,
  MapInfo TAB files, FGDB, etc. etc.
- Web services e.g. WFS
- etc. etc

Why not use one PostGIS database?

The "owner" of the data may not want to store their data in your database;
You have applications that don't support PostgreSQL/PostGIS;
You have application which require a different version of PostgreSQL/PostGIS;
You have applications only licensed to use a standalone database;
You have applications that only works with a file based data i.e. shape/tab files or even as web services.

What do people typically do?

What are Foreign Data Wrappers?

In 2003, a new specification "SQL Management of External Data" (SQL/MED) was added to the SQL standard.
It is a standardized way of handling access to remote objects from SQL databases.

In 2011, PostgreSQL 9.1 included FDWs with read-only support;
In 2013, write support was added with PostgreSQL 9.3;
In 2016, IMPORT FOREIGN SCHEMA was added with PostgreSQL 9.5;

FDWs make external data sources (text files, web services, etc.) look like tables in the database and so users have easy access to them using SQL commands.

But isn’t spatial special?

Very much so! In the early days of oracle_fdw, it did not support geometry.

So we had to

Create a view in the oracle database that converted to data into WKB using the oracle function sdo_util.to_wkbgeometry()

and

Create a view in the PostGIS database to convert it into geometry using PostGIS function st_geomfromwkb

Even then ...

wkb did not include the SRID
we could not edit the data as we had derived
columns
Oracle instead of triggers do not support returning clause.

We have spatial FDWs?

Spatial Foreign Data Wrappers

Postgres_FDW
www.postgresql.org/docs/9.6/static/postgres-fdw.html

Oracle_FDW

laurenz.github.io/oracle_fdw/

OGR_FDW
github.com/pramsey/pgsql-ogr-fdw

So how do we use FDWs?

PostgreSQL FDW
for foreign data in another PostgreSQL database

PostgreSQL FDW cont....

let's look at the database.....

PostgreSQL FDW (9.0/1.5)

let's look at the database and

PostgreSQL FDW (9.0/1.5) cont...

let's look at the database and

PostgreSQL FDW gotchas....

IMPORT FOREIGN SCHEMA will not bring geometry type or SRID from geometry_columns table in PostGIS 1.5.

Solution: Use CREATE FOREIGN TABLE command.
TIP! Use IMPORT FOREIGN SCHEMA to get basic CREATE FOREIGN TABLE command, drop the foreign table and then edit the SQL and re-run.
QGIS can't open foreign tables.

Solution: create a simple editable view e.g.
CREATE VIEW foreign_pg_90.v_ft_railwayline
AS SELECT * FROM foreign_pg_90.railwayline;

Oracle FDW
for foreign data in an Oracle database

Oracle FDW gotchas....

People don't often store the geometry type in the Oracle metadata:

Either use CREATE FOREIGN TABLE or cast when creating the view:
CREATE VIEW foreign_oracle.v_ft_boundaryline as
SELECT ogr_fid, fid, featurecode, version, versiondate, theme, accuracyofposition, changedate, reasonforchange, descriptivegroup, descriptiveterm, make, physicallevel, physicalpresence, style_code, style_description, geometry::Geometry(MULTILINESTRING, 27700) as wkb_geometry
FROM foreign_oracle.boundaryline;
QGIS can't open foreign tables.