Shout out goes to Regina Obe and Paul Ramsey for their work and documentation on ogr_fdw. The ogr_fdw project can be found here: https://github.com/pramsey/pgsql-ogr-fdw.

ogr+fdw = ogr_fdw

In an earlier post, I showed you how to use the ogr2ogr (or as my geogeek friends like to call it “ogre to ogre”) command line tool to import features in different data formats to Postgres (e.g. shapefiles or geojson).

Foreign Data Wrappers (FDWs) allow you to connect to remote data sources from within Postgres. From there you can query them with SQL, join across disparate data sets, or join across different systems. There are FDW implementations to connect to MySQL, Oracle, SQLite, as well as flat files.

ogr_fdw allows you to connect your PostGIS database directly to an existing GIS file or database and read from it without importing the data.

It’s like getting a hug from an ogre!

Exercise: Intro to ogr_fdw

Exercise Prerequisites

ogr_fdw is packaged with BigSQL’s PostgreSQL installers. If you have not installed the BigSQL PostgreSQL distribution and setup your PostGIS database, start here.

The GDAL_DATA Directory Path

In order for ogr_fdw to properly work, you will need to set the GDAL_DATA environment variable to the location of the directory gdal (if you haven’t already).

Lucky for you, with the BigQL distribution this is as easy as running the following commands via a command line tool.

Linux / OSX:

 cd <directory of installation>/pg96
 source pg96.env

Windows:

 cd <directory of installation>\pg96
 pg96-env

Both of these commands set environment variables that will live during your current session. Note that if you close your terminal or command prompt, these variables are removed from memory.

It’s also possible to set GDAL_DATA as a persistant environment variable. But that is the beyond the scope of this tutorial.

Download the data

For this exercise, I downloaded the NYC Subway Lines shapefile available at the New York City Open Data site.

To download this data click here: nyc_subway_lines shapefile

ogr_fdw_info Commands

Begin by running the ogr_fdw_info command to show a list of supported formats:

ogr_fdw_info -f

Supported Formats:
  -> "PCIDSK" (read/write)
  -> "netCDF" (read/write)
  ...
  -> "HTTP" (readonly)

Navigate to the nyc_subway_lines directory you downloaded and run the following command to see the layers in the directory:

ogr_fdw_info -s <nyc_subway_lines directory>
    example OSX/Linux: ogr_fdw_info -s /Users/hollyorr/gis_data/nyc_subway_lines
    example Windows: ogr_fdw_info -s C:\gis_data\nyc_subway_lines 

Layers:
  nyc_subway

Now use the following command to read an OGR data source and output a server and table definition for this particular layer:

ogr_fdw_info -s <nyc_subway_lines directory> -l nyc_subway
    example OSX: ogr_fdw_info -s /Users/hollyorr/gis_data/nyc_subway_lines -l nyc_subway
    example Windows: ogr_fdw_info -s C:\gis_data\nyc_subway_lines -l nyc_subway

Output:

CREATE SERVER myserver
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource 'nyc_subway_lines',
    format 'ESRI Shapefile' );

CREATE FOREIGN TABLE nyc_subway (
  fid bigint,
  geom Geometry(LineString,4326),
  objectid real,
  shape_len real,
  url varchar,
  name varchar,
  rt_symbol varchar,
  id real
) SERVER myserver
OPTIONS (layer 'nyc_subway');

You can run the commands generated as output in pgAdmin or command line. In this exercise we will run the sql statement in command line with psql.

Connect to psql:

Linux / OSX:

 cd <directory of installation>/pg96
 source pg96.env    

Windows:

 cd <directory of installation>\pg96\bin

Open psql and connect to postgis database:

$ psql
postgres=# \connect postgis_sample
    You are now connected to database "postgis_sample" as user "postgres".

Create the remote server and foreign table:

CREATE SERVER myserver FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource '<location of nyc_subway_lines directory>', format 'ESRI Shapefile');

CREATE FOREIGN TABLE nyc_subway (fid bigint, geom Geometry(LineString,4326), objectid real, shape_len real, url varchar, name varchar, rt_symbol varchar, id real) SERVER myserver OPTIONS (layer 'nyc_subway');

Now open pgDevOps by BigSQL and switch to the pgAdmin4 Web view.

Check to see that your remote server was successfully created:

In the pgAdmin4 browser, navigate to:

Servers >> pg96 >> Databases >> postgis_sample >> Foreign Data Wrappers >> ogr_fdw >> Foreign Servers >> myserver

Check to see that your foreign table was successfully created:

In the pgAdmin4 browser, navigate to:

Servers >> pg96 >> Databases >> postgis_sample >> Schemas >> public >> Foreign Tables >> nyc_subway

Finally, query the nyc_subway table to see that your remote data using the ogr_fdw:

SELECT * FROM public.nyc_subway LIMIT 100;