Compiling and installing ogr_fdw on CentOS after Yum Install PostgreSQL PostGIS   

I should add I also experimented with building stuff from PGXN registry on my CentOS box, and one big peeve I have about that, is that for less than trivial extensions, you've got to go searching for the dependencies yourself. Not like some other extension systems like Node (NPM) and Python Pip that just install these things for you or warn you if they can't. At the very least it would be nice if it gave such a message rather than often cryptic ones. I know I'm spoiled, but I can dream. I'll save using PGXN for another article.

I'll start off with a couple of stumbling blocks you may have if you are new to compiling stuff on Linux. Most Linux/Unix folks can skip these sections, since they probably are already setup. Everytime I spin up a Linux VM, it for some reason never has these things installed.

You need gcc-c++ and git

As with all platforms where you intend to compile stuff like PostGIS and PostgreSQL extensions, you need a functioning gcc-c++. You know you don't have one when you get messages like: No way to build binaries

Should fix that

ogr_fdw not currently in pgxn, got to get from git repo

If you don't have git client already installed, just run this:

You need GDAL Develop and PostgreSQL develop

When you install PostGIS / PostgreSQL via Yum, it installs all needed to run PostGIS, which includes GDAL, but not necessarily GDAL development or PostgreSQL develop package. So if you do this:

gdal-devel.x86_64                           1.9.2-7.rhel6                @pgdg94
gdal-java.x86_64                            1.9.2-7.rhel6                @pgdg94
gdal-libs.x86_64                            1.9.2-7.rhel6                @pgdg94

and you don't see listed: gdal-devel.x86_64 (that is for x64, for 32-bit it would be i686), then you need to do a:

Similarly you don't need PostgreSQL development to run PostgreSQL, but need it for compiling extensions:

The 94 you should replace with the version you are compiling for.

Should include postgresql94-devel in there. Mine for reference looked like:

postgresql94.x86_64    9.4.4-1PGDG.rhel6
postgresql94-contrib.x86_64
postgresql94-devel.x86_64
postgresql94-libs.x86_64
postgresql94-server.x86_64

If you don't see it listed, then run

Ready to download, compile, and install ogr_fdw

mkdir /sources
chmod -R 777 /sources #this is needed for installcheck to work to give postgres daemon access
cd /sources
git clone -b master https://github.com/pramsey/pgsql-ogr-fdw.git pgsql_ogr_fdw
cd pgsql_ogr_fdw
export PATH=/usr/pgsql-9.4/bin:$PATH   # this is needed because pg_config is not in path
make && make install
export PGUSER=postgres
make installcheck

The make installcheck part is very finicky because the postgres service account needs rights to read the data folder to reference the test dbf and on top of that you need to be a superuser to build the foreign. So don't be too upset if your installcheck yields failure instead of:

(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test ogr_fdw                  ... ok

=====================
 All 1 tests passed.
=====================

Happy compiling

Using ogr_fdw in a database

Well first you install the extension in your database with a

/connect mydb
CREATE EXTENSION postgis; --only needed if you want to connect to a spatial datasource
CREATE EXTENSION ogr_fdw;

The rest of this I stole from Paul Ramsey's ogr_fdw page, adding a query for demonstration:

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

CREATE FOREIGN TABLE topp_states (
  fid integer,
  geom geometry,
  gml_id varchar,
  state_name varchar,
  state_fips varchar,
  sub_region varchar,
  state_abbr varchar,
  land_km real,
  water_km real,
  persons real,
  families real,
  houshold real,
  male real,
  female real,
  workers real,
  drvalone real,
  carpool real,
  pubtrans real,
  employed real,
  unemploy real,
  service real,
  manual real,
  p_male real,
  p_female real,
  samp_pop real )
  SERVER opengeo
  OPTIONS ( layer 'topp:states' );
  
SELECT ST_Area(geom), persons, families, p_male, p_female, state_abbr
FROM topp_states
LIMIT 2;

Outputs --

      st_area       |   persons   |  families   | p_male | p_female | state_abbr
--------------------+-------------+-------------+--------+----------+------------
    15.396467068064 | 1.14306e+07 | 2.92488e+06 |  0.486 |    0.514 | IL
 0.0177697208289998 |      606900 |      122087 |  0.466 |    0.534 | DC

My next test will be to experiment with UnixODBC to see if I can connect my windows SQL Server install to my CentOS PostgreSQL install.

For windows users, reading this. I've had a change of heart, and will be putting CURL support back in and with SSL support. I managed to get my curl binaries to use the packaged EDB ssleasy32 and libeay32.dll by making sure I compile against the same version (and no higher).