Connecting to external data sources with foreign data wrappers

Since PostgreSQL 9.1, one can use Foreign Data Wrappers (FDW) in order to connect to the external data sources that are then treated as they were local tables. More information can be found on the PostgreSQL wiki: https://wiki.postgresql.org/wiki/Foreign_data_wrappers.

Querying the external files or databases seems to be standard these days. But how about querying WFS services or OSM directly? Now, this sounds cool, doesn't it? You should certainly have a look at some of the clever GEO data wrappers:

• ogr_fdw: https://github.com/pramsey/pgsql-ogr-fdw
• osm_pbf_fdw: https://github.com/vpikulik/postgres_osm_pbf_fdw

In this example, we'll use ogr_fdw to connect to some external data sources. Starting with PostGIS 2.2, it is a part of the bundle and there is no need to install it as it should already be available.

Examples shown in this section can be executed in both psql or in PgAdmin.

Connecting to SQL Server Spatial

First we need to create a server:

CREATE SERVER fdw_sqlserver_test 
  FOREIGN DATA WRAPPER ogr_fdw 
  OPTIONS ( 
    datasource 'MSSQL:server=CM_DOM\MSSQLSERVER12;database=hgis;UID=postgres_fdw;PWD=postgres_fdw', 
    format 'MSSQLSpatial'); 

You may have noticed I have created a postgres_fdw user with the same password.

If you're using Postgre SQL 9.5+, you can use the IMPORT SCHEMA command:

IMPORT FOREIGN SCHEMA "dbo.Wig100_skorowidz" 
FROM SERVER fdw_sqlserver_test INTO data_linked; 

Otherwise you will have to specify the table schema explicitly:

CREATE FOREIGN TABLE data_linked.dbo_wig100_skorowidz 
   (fid integer , 
    geom public.geometry , 
    oid integer , 
    gid integer , 
    version integer , 
    godlo character varying , 
    nazwa character varying , 
    nazwa2 character varying , 
    kalibracja character varying , 
    pas real , 
    slup real ) 
   SERVER fdw_sqlserver_test 
   OPTIONS (layer 'dbo.Wig100_skorowidz'); 

By default, PgAdmin does not display foreign tables, so you may have to go to File | Options and tick the Foreign Tables checkbox in the Browser node. In PgAdmin 4, foreign tables seem to be visible by default.

At this stage, you should be able to query the foreign table as if it was local.

Connecting to WFS service

This example is based on the ogr_fwd documentation, so it only shows the required stuff. A full example can be reviewed here:

https://github.com/robe2/pgsql-ogr-fdw

First let's create a foreign server:

CREATE SERVER fdw_wfs_test_opengeo 
  FOREIGN DATA WRAPPER ogr_fdw 
  OPTIONS ( 
    datasource 'WFS:http://demo.opengeo.org/geoserver/wfs', 
    format 'WFS'); 

Automagically bring in the schema:

IMPORT FOREIGN SCHEMA "topp:tasmania_cities" 
FROM SERVER fdw_wfs_test_opengeo INTO data_linked; 

And issue a query against the foreign WFS table:

select city_name from data_linked.topp_tasmania_cities; 

Since this dataset contains only one record, our result should be Hobart.