After installing PostgreSQL 9.4 and PostGIS following An Almost Idiot's guide to installing PostgreSQL, PostGIS, and pgRouting, on my CentOS 6.7 64-bit except replacing 9.3 references with equivalent 9.4 reference, I then proceeded to install ogr_fdw. To my disappointment, there are no binaries yet for that, which is not surprising, considering there aren't generally any binaries for any OS, except the windows ones I built which I will be packaging with PostGIS 2.2 windows bundle. Getting out of my windows comfort zone, I proceeded to build those on CentOS. Mainly because I have a client on CentOS where ogr_fdw I think is a perfect fit for his workflow and wanted to see how difficult of a feat this would be. I'll go over the steps I used for building and stumbling blocks I ran into in this article with hope it will be of benefit to those who find themselves in a similar situation.
yum install ogr_fdw94
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.
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
sudo yum install gcc-c++
Should fix that
If you don't have git client already installed, just run this:
sudo yum install git
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:
sudo yum list installed | grep gdal
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:
yum install gdal-devel
Similarly you don't need PostgreSQL development to run PostgreSQL, but need it for compiling extensions:
sudo yum list installed | grep postgresql94
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
sudo yum install postgresql94-devel
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
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).