Table Of Contents

I'm still in the middle of building packages for our clients for the packages that aren't normally distributed for windows to make upgrading to PostgreSQL 9.4 smooth. One of those is PL/V8 which we use for some custom functions. I had mentioned how I build PL/V8 for PostgreSQL windows, and the instructions are a bit out of date., but I put more up to date instructions on my gist page. I tend to use gist a lot as a public scrap book with hopes someone else can learn from my toils and save them some trouble. At some point I should probably get more organized with my scrapbooks.

I've listed below PL/V8 binaries I built for PostgreSQL 9.4 for windows 64-bit and 32-bit. These I tested with EDB PostgreSQL 9.4 windows installs and seem to work fine. These were built with latest 1.4 branch (a little after 1.4.2 to grab the extra 9.4 fixes) of PL/V8.

PL/V8 PostgreSQL 9.4 binaries

• PostgreSQL 9.4 plv8 32-bit download - PL/V8 1.4.2ish
• PostgreSQL 9.4 plv8 64-bit download - PL/V8 1.4.2ish

We hope windows users find these useful.

As stated in last article, I've packaged FDW binaries for PostgreSQL 9.3 windows 32-bit and 64-bit and added in the ogr_fdw one. These we've tested with the standard EDB Vc++ built PostgreSQL windows installs and work fine with those.

This package is an updated list from ones we've distributed before that includes ogr_fdw and recompiled with latests source from www_fdw and file_textarray

Below are the zip files that contain the binaries and dependency files for our favorite FDWs (that aren't normally available for windows):

• PostgreSQL 9.3 w32 FDWs (zip) 7z version
• PostgreSQL 9.3 w64 FDWs (zip) 7z version

FDWs in the 9.3 downloads

• file_textarray_fdw for working with delimited text files, loads each line as a text array.
• https://github.com/cyga/www_fdw - for querying web services
• ogr fdw - A monster of an FDW that supports many spatial and non-spatial datasource. Have some weird data source/file you've never heard of before (especially spatial)? There's a good chance OGR FDW can query it. Note this GDAL build has more drivers than what gets packaged with the PostGIS version. I have also included ODBC, so full set of drivers supported is (read-only since this fdw only supports read at the moment): ogr_fdw related articles on our site with some examples.
  

  Supported Formats:
    -> "ESRI Shapefile" (read/write)
    -> "MapInfo File" (read/write)
    -> "UK .NTF" (readonly)
    -> "SDTS" (readonly)
    -> "TIGER" (read/write)
    -> "S57" (read/write)
    -> "DGN" (read/write)
    -> "VRT" (readonly)
    -> "REC" (readonly)
    -> "Memory" (read/write)
    -> "BNA" (read/write)
    -> "CSV" (read/write)
    -> "GML" (read/write)
    -> "GPX" (read/write)
    -> "KML" (read/write)
    -> "GeoJSON" (read/write)
    -> "GMT" (read/write)
    -> "ODBC" (read/write)
    -> "WAsP" (read/write)
    -> "PGeo" (readonly)
    -> "MSSQLSpatial" (read/write)
    -> "PCIDSK" (read/write)
    -> "OpenFileGDB" (readonly)
    -> "XPlane" (readonly)
    -> "AVCBin" (readonly)
    -> "AVCE00" (readonly)
    -> "DXF" (read/write)
    -> "Geoconcept" (read/write)
    -> "GeoRSS" (read/write)
    -> "GPSTrackMaker" (read/write)
    -> "PGDump" (read/write)
    -> "GPSBabel" (read/write)
    -> "SUA" (readonly)
    -> "OpenAir" (readonly)
    -> "PDS" (readonly)
    -> "WFS" (readonly)
    -> "HTF" (readonly)
    -> "AeronavFAA" (readonly)
    -> "Geomedia" (readonly)
    -> "EDIGEO" (readonly)
    -> "GFT" (read/write)
    -> "GME" (read/write)
    -> "SVG" (readonly)
    -> "CouchDB" (read/write)
    -> "Idrisi" (readonly)
    -> "ARCGEN" (readonly)
    -> "SEGUKOOA" (readonly)
    -> "SEGY" (readonly)
    -> "XLS" (readonly)
    -> "ODS" (read/write)
    -> "XLSX" (read/write)
    -> "ElasticSearch" (read/write)
    -> "PDF" (read/write)
    -> "Walk" (readonly)
    -> "CartoDB" (readonly)
    -> "SXF" (readonly)

One of the changes coming to you in PostGIS 2.2 are additional extensions. Two ones close to my heart are the address_standardizer (which was a separate project before, but folded into PostGIS in upcoming 2.2) and the SFCGAL extension for doing very advanced 3D stuff (was just an sql script in older versions, but made an extension in 2.2 and new functions added). We had a need to have address standardizer running on our Ubuntu box, but since PostGIS 2.2 isn't released yet, you can't get it without some compiling. Luckily the steps are fairly trivial if you are already running PostGIS 2.1. In this article, I'll walk thru just building and installing the address_standardizer extension from the PostGIS 2.2 code base. Though I'm doing this on Ubuntu, the instructions are pretty much the same on any Linux, just replacing with your Linux package manager.

Compiling and installing address_standard 2.2 on PostGIS 2.1

If you don't have PostGIS already, you can install it via PGDG. Instructions are here: PostGIS Ubuntu 9.3 Apt. You shouldn't really need PostGIS anyway except possibly to get past the PostGIS configure step.

1. In order to build PostgreSQL extensions, you need the PostgreSQL dev package which you install with:

   apt-get install postgresql-server-dev-9.3

2. In order to get past the configure step of PostGIS, you need these additional dev packages

   apt-get install libxml2-dev libgeos-dev libproj-dev libpcre3-dev
3. Next download the PostGIS 2.2 dev tar ball and follow below steps to make and install the address_standardizer extensions:
   
   Updated 2016-01-20 tarball link to reflect released PostGIS 2.2.1

   wget http://download.osgeo.org/postgis/source/postgis-2.2.1.tar.gz
   tar xvf postgis-2.2.1.tar.gz
   cd postgis-2.2.1
   ./configure --without-raster

   You should have an output that looks something like:

   PostGIS is now configured for x86_64-unknown-linux-gnu
   
    -------------- Compiler Info -------------
     C compiler:           gcc -g -O2
     C++ compiler:         g++ -g -O2
     SQL preprocessor:     /usr/bin/cpp -traditional-cpp -w -P
   
    -------------- Dependencies --------------
     GEOS config:          /usr/bin/geos-config
     GEOS version:         3.4.2
     PostgreSQL config:    /usr/bin/pg_config
     PostgreSQL version:   PostgreSQL 9.3.5
     PROJ4 version:        48
     Libxml2 config:       /usr/bin/xml2-config
     Libxml2 version:      2.9.1
     JSON-C support:       no
     PCRE support:       yes
     PostGIS debug level:  0
     Perl:                 /usr/bin/perl
   
    --------------- Extensions ---------------
     PostGIS Raster:       disabled
     PostGIS Topology:     enabled
     SFCGAL support:       disabled
     Address Standardizer support:       enabled
   
    -------- Documentation Generation --------
     xsltproc:
     xsl style sheets:
     dblatex:
     convert:
     mathml2.dtd:          http://www.w3.org/Math/DTD/mathml2/mathml2.dtd

4. Now we are ready to compile and install:

   cd extensions/address_standardizer
   make && make install

   If all goes well, your final output looks something like:

   /usr/bin/perl mk-sql.pl 'PostgreSQL 9.3.5' address_standardizer.sql > address_standardizer--2.2.0dev.sql
   /usr/bin/perl pagc-data-psql lex lexicon.csv > us-lex.sql
   /usr/bin/perl pagc-data-psql gaz gazeteer.csv > us-gaz.sql
   /usr/bin/perl pagc-data-psql rules rules.txt > us-rules.sql
   /bin/mkdir -p '/usr/lib/postgresql/9.3/lib'
   /bin/mkdir -p '/usr/share/postgresql/9.3/extension'
   /bin/mkdir -p '/usr/share/postgresql/9.3/extension'
   /bin/mkdir -p '/usr/share/doc/postgresql-doc-9.3/extension'
   /usr/bin/install -c -m 755  address_standardizer-2.2.so '/usr/lib/postgresql/9.3/lib/address_standardizer-2.2.so'
   /usr/bin/install -c -m 644 address_standardizer.control '/usr/share/postgresql/9.3/extension/'
   /usr/bin/install -c -m 644 address_standardizer--2.2.0dev.sql us-lex.sql us-gaz.sql us-rules.sql '/usr/share/postgresql/9.3/extension/'
   /usr/bin/install -c -m 644 README.address_standardizer '/usr/share/doc/postgresql-doc-9.3/extension/'

Using Address Standardizer

In order to enable the address standardizer in a specific database, connect to the database and run:

If you prefer the GUI guided tour, once you install address_standardizer binaries (as we did), you should see it in pgAdmin extension drop down options

I'm still in the middle of packaging the standardization data sets into a separate data extension for easier consumption by end-users. For now you can just load the us-lex.sql, us-gaz.sql, us-rules.sql files via PSQL that get installed in the /usr/share/postgresql/9.3/extension/ folder.

If you want to experiment with using the extension, refer to the PostGIS 2.2 dev manual: Installing and using Address Standardizer, which is still a bit of a work in progress.

More books coming

Leo and I are still very busy writing PostgreSQL related books, so I haven't had quite as much time to devote to PostGIS documentation as I would have liked. If you didn't know - our PostgreSQL: Up and Running, 2nd edition recently came out in Print. PostGIS in Action 2nd Edition is due out late February / Early March. We have also started our 3rd PostgreSQL/PostGIS book which we will announce once we've gotten further into it. If you've seen me on #postgis IRC and wondered Why is Regina so engrossed in Graphy theory, you can probably guess what that book is about. Part of the joy of writing is learning new things as you go along and pushing yourself to experiment in different ways with technologies you love.

One of the foreign data wrappers I included in the PostgreSQL 9.3 Windows FDW bag and PostgreSQL 9.4 Windows FDW bag is the www_fdw extension used for querying web services. Someone asked that since I didn't build curl with SSL support, they are unable to use it with https connections. The main reason I didn't is that the EDB installs come with ssleay32.dll and libeay32.dll (even the 64-bit) which are dependencies of curl when built with SSL support. I wanted to minimize the issue of distributing dlls that are packaged with Windows PostgreSQL installers already.

Though this article is specific to using www_fdw on Windows systems, many of the issues are equally applicable to other platforms, so may be worth a read if you are running into similar issues with using specialty SSL certificates on Linux/Unix/Mac.

You'll know you have curl built without ssl support, because when you try to create an https Foreign server, you'll get this error from WWW_FDW

ERROR:  Can't get a response from server: Protocol "https" not supported or disabled in libcurl

Unfortunately curl is not distributed with the EDB PostgreSQL windows builds. However you can easily swap out the non-ssl enabled curl binary I included in the FDW package with an SSL enabled one and get SSL support in the www_fdw as well as the ogc_fdw driver we included. Here's what you do:

Swapping out the non-ssl enabled curl with ssl-enabled curl

1. Download one of the windows binaries from http://curl.haxx.se/download.html. I'm running PostgreSQL 9.4 64-bit, so I chose Win64 - Mingw64 one. I chose it, not because it was Mingw64, but because it had the fewest number of dependencies.
2. This particular package also includes ssleay32.dll and libeay32.dll, but since EDB already has those libraries, I did not copy those files. The curl library is called libcurl.dll, but the name I bound with is libcurl-4.dll. So to swap out the one I provide with this SSL enabled one, just rename the libcurl.dll to libcurl-4.dll and then copy into your PostgreSQL/9.4/bin (or 9.3 if on 9.3) folder

Now you are almost ready to go. Your www_fdw understands SSL. There is now one more small problem of certificates to contend with.

Working with SSL certificates

Even after you install the version of curl with SSL support, if you create your www FDW server and foreign table with the Google search example code here: https://github.com/cyga/www_fdw/wiki/Documentation, you might get the error:

ERROR:  Can't get a response from server: SSL certificate problem: unable to get local issuer certificate

The simplest way I've discovered to resolve this issue on windows is to explicitly specify the certificate as part of the WWW FDW Server definition. So here's what you do

1. Download the ca-bundle.crt described in http://curl.haxx.se/docs/caextract.html
2. Copy that into a folder accessible by the postgres service account (you might have to grant permissions). I like to put it in a folder like C:\SSLCerts

3. Now create your WWW FDW Server with this SQL command and pass in the certificate using the www_fdw cainfo option

   CREATE SERVER www_fdw_google_search_server FOREIGN DATA WRAPPER www_fdw 
       OPTIONS (uri 'https://ajax.googleapis.com/ajax/services/search/web?v=1.0', 
           cainfo 'C:/SSLCerts/ca-bundle.crt');

4. If you had already created the SERVER and don't want to have to drop your Foreign tables, you can update the SERVER, as you can with any FDW server, with a command like this:

   ALTER SERVER www_fdw_google_search_server OPTIONS (ADD cainfo 'C:/SSLCerts/ca-bundle.crt');

   
   If you need to change the path of the certificate, say you moved it to D, you can use the SET command instead of ADD to change an exisiting option:
   

   ALTER SERVER www_fdw_google_search_server OPTIONS (SET cainfo 'D:/SSLCerts/ca-bundle.crt');

If you have the OGR_FDW we discussed in OGR FDW Windows first taste built with ODBC support, then you can access most any ODBC datasource from PostgreSQL. This is especially useful for Windows users. Two of the data sources I've been experimenting with are SQL Server and MS Access. In this article, I'll demonstrate how to connect to MS Access with PostgreSQL running on a windows box. I think there is an Access driver for Unix/Linux most robust utilizes java. I won't go there.

Querying MS Access with OGR_FDW

There are two ways you can specify an MS Access connection with OGR and by extension OGR FDW.

• You can specify just by the filename if your libgdal is version 1.10+, if its a .MDB file, AND you have a driver that happens to be called Microsoft Access Driver (*.mdb). Note that if you are on 64-bit windows or using the newer MS Access, your driver is probably called Microsoft Access Driver (*.mdb, *.accdb). I have a hack to get around this issue, but there is a committed fix for it which should make it in next release of GDAL documented here: http://trac.osgeo.org/gdal/ticket/5594. I'll probably recompile the windows builds with latest trunk to get this fix at some point.
• You can prefix with ODBC:the system dsn name goes here. This you can use for any datasource and requires you create a System DSN entry. Note there are 2 ODBC managers if you are on a 64-bit windows box. The one in path C:\Windows\SysWOW64\odbcad32.exe is for using with 32-bit system (so if you are using PostgreSQL 32-bit on a windows 64-bit, you'd use this one. The one in path C:\Windows\System32\odbcad32.exe (is the 64-bit version on a Windows-64bit OS and for 32-bit (is the only ODBC manager and 32-bit). Confused yet?

There is one big gotcha that affects many 64-bit windows users. So I'll highlight it here. Sadly I don't know an easy fix for this one. It's the same issue if you wanted to query an MS Access database or Excel file from SQL Server 64-bit. Luckily at least as far as ogr_fdw is concerned, ogr_fdw doesn't need ODBC to query excel files, so you are only screwed if you need to connect to MS Access. SQL Server 64-bit folks are doubly screwed since SQL Server needs the driver for both MS Access and Excel :). Of course a SQL Server user could do something somewhat Rube Goldbergish and install PostgreSQL, create a Foreign table to Excel from PostgreSQL, and then do a linked server or openquery call to the Foreign table excel file from SQL Server (I should chart this :) ). So here it is:

If you want to query MS Access from a 64-bit PostgresSQL, you will either need MS Access 64-bit or the freely available MS Access 2013 64-bit runtime or runtimes MS Access 2010 64-bit version. Make sure you install the 64-bit version. If you for some reason you have 32-bit Office installed on your server and you have PostgreSQL 64-bit, you are kinda screwed since the 64-bit runtime will not install on a machine with 32-bit Office. I should also add that MS Access 2013 runtime requires Windows 2008 R2 or above. So if you have Windows 2008 or lower, you need to use the MS Access 2010 or lower runtime.

Another bit of a gotcha, if you have your MS Access database on a network share, yes UNCs of the form \\myserver\myshare\myfolder\mydatabase work fine, if and only if your PostgreSQL server service account can access that path. That means generally you need to run your PostgreSQL service under a network account or at least an account with same user and password as is on the other server.

The registry hack

If you have the newer MS Access drivers and want to be able to use a DSNLess connection for MDB files, and aren't running GDAL trunk, you can get away by creating a text file with a .reg extension that has contents below and then right click install. You want to first verify the ACEODBC.dll path matches what you have installed. In my case I have the MS access 2013 64-bit runtine installed on my server. If you are using 32-bit this is a non-issue since Windows ships with the 32-bit MS Access driver.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Microsoft Access Driver (*.mdb)]
"UsageCount"=dword:00000003
"Driver"="C:\\PROGRA~1\\COMMON~1\\MICROS~1\\OFFICE15\\ACEODBC.DLL"
"Setup"="C:\\PROGRA~1\\COMMON~1\\MICROS~1\\OFFICE15\\ACEODBC.DLL"
"APILevel"="1"
"ConnectFunctions"="YYN"
"DriverODBCVer"="02.50"
"FileUsage"="2"
"FileExtns"="*.mdb"
"SQLLevel"="0"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers]
"Microsoft Access Driver (*.mdb)"="Installed"

When you go to ODBC manager in Control Panel (or via the paths I mentioned) -- you should see a new entry in Drivers section.

DSNLess (AKA the file path) Connection to MS Access

You can use the ogr_fdw_info tool to query MS Access (good old Northwind which I snagged an old version from http://www.geeksengine.com/article/northwind.html without a registered DSN with something like this:

And you'll get an output like this:

Layers:
  Categories
  Customers
  Employees
  Order Details
  Orders
  Products
  Shippers
  Suppliers
  Alphabetical List of Products
  Category Sales for 1997
  Current Product List
  Customers and Suppliers by City
  Employee Sales by Country
  Invoices
  Invoices Filter
  Order Details Extended
  Order Subtotals
  Orders Qry
  Product Sales for 1997
  Products Above Average Price
  Products by Category
  Quarterly Orders
  Quarterly Orders by Product
  Sales by Category
  Sales by Year
  Ten Most Expensive Products

This SQL Statement using the hack function I mentioned in prior article works in this case to: SELECT ogr_fdw_sql_table('C:/fdw_data/northwind.mdb');

To get the Orders table, structure do this

Which would output something like this (which I change myserver to Northwind):

CREATE SERVER northwind
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource 'C:/fdw_data/northwind.mdb',
    format 'ODBC' );

CREATE SCHEMA northwind;
CREATE FOREIGN TABLE northwind.orders (
  fid integer,
  geom bytea,
  orderid integer,
  customerid varchar,
  employeeid integer,
  orderdate timestamp,
  requireddate timestamp,
  shippeddate timestamp,
  shipvia integer,
  freight real,
  shipname varchar,
  shipaddress varchar,
  shipcity varchar,
  shipregion varchar,
  shippostalcode varchar,
  shipcountry varchar )
  SERVER northwind
  OPTIONS ( layer 'Orders' );
  
CREATE FOREIGN TABLE northwind.products
   (fid integer ,
    geom bytea ,
    productid integer ,
    productname character varying ,
    supplierid integer ,
    categoryid integer ,
    quantityperunit character varying ,
    unitprice real ,
    unitsinstock integer ,
    unitsonorder integer ,
    reorderlevel integer ,
    discontinued character varying )
   SERVER northwind
   OPTIONS (layer 'Products');

Sadly trying to read the table structure with my hack function from within PostgreSQL just returns NULL. Also note the unwelcomed geom column (which was represented as type geometry) and I changed to bytea, tagging along for the ride. To be fair MS Access databases are structures repurposed by GIS companies like ESRI and GeoMedia, so OGR blindly treats them as having spatial content. There is actually spatial content in the form of raster in the categories table, but that sadly has got an OLE wrapper around it that would require stripping the OLE header before you can use the PostGIS raster functions on them.

There is a current issue with the ogr_fdw driver that is can't handle timestamps for some reason so replace all timestamp with varchar. This is a similar issue with when connecting to SQL Server with ogr_fdw, though using the SQL Server 2008+ date type works and maps fine. I'll post new binaries once this issue is resolved. Cursory tests seem to suggest this timestamp issue is not limited to windows or ODBC though is more easily triggerable in this combination.

Very insane use case for this

Upon learning about being able to query MS Access from PostgreSQL, Leo's first reaction and excitement.

Didn't quite know what he meant by that until I saw him in action. Paul -- here's a wrap-around for you, Leo style :)

1. Create a PostgreSQL foreign table link to MS Access table.
2. Write a complicated view involving this foreign table and any of the gazillions of functions PostgreSQL has to offer in PostgreSQL.
3. Create a linked table back to this view from MS Access
4. Alas magic -- you update your MS Access table, the view data changes. A little slow for a lot of records, but still acceptable in many cases. Leo only tried a 30,000 record table.