Postgres OnLine Journal: June / July / August 2015
An in-depth Exploration of the PostgreSQL Open Source Database

Table Of Contents

What's new and upcoming in PostgreSQL
Using PostgreSQL Contribs

What's new and upcoming in PostgreSQL


PostgreSQL 9.5 Grouping Sets with PostGIS spatial aggregates

One of the features coming in PostgreSQL 9.5 is the triumvirate GROUPING SETS, CUBE, and ROLLUP nicely covered in Bruce's recent slide deck. The neatest thing about PostgreSQL development is that when improvements happen, they don't just affect the core, but can be taken advantage of by extensions, without even lifting a finger. Such is the case with these features.

One of the things I was curious about with these new set of predicates is Would they work with any aggregate function?. I assumed they would, so decided to put it to the test, by using it with PostGIS ST_Union function (using PostGIS 2.2.0 development). This feature was not something the PostGIS Development group planned on supporting, but by the magic of PostgreSQL, PostGIS accidentally supports it. The grouping sets feature is particularly useful if you want to aggregate data multiple times, perhaps for display using the same dataset. It allows you to do it with a single query that in other PostgreSQL versions would require a UNION query. This is a rather boring example but hopefully you get the idea.

Example of GROUPING SETS with ST_Union spatial aggregate

SELECT COALESCE('County ' || countyfp, 'State ' || statefp) As region,
    ST_Union(the_geom) As geom, count(*) AS cnt
FROM cousub
GROUP BY GROUPING SETS (countyfp, statefp)
ORDER BY countyfp, statefp;

Output looks like:

   region   |              geom               | count
 County 001 | 0103000020AD10000001000000EB00... |     3
 County 003 | 0103000020AD10000001000000B307... |     5
 County 005 | 0103000020AD100000010000005902... |     7
 County 007 | 0103000020AD100000010000006F07... |    16
 County 009 | 0106000020AD100000020000000103... |     9
 State 44   | 0106000020AD100000020000000103... |    40
(6 rows)

Note in the example, I use COALESCE for labeling because in the GROUPING SETS will force only one of the columns to be filled in that is for that group and the rest will be NULL.

Using PostgreSQL Contribs


PostgreSQL OGR FDW update and PostGIS 2.2 news

PostGIS 2.2 is planned to reach feature freeze June 30th 2015 so we can make the September PostgreSQL 9.5 curtain call with confidence. Great KNN enhancements for PostgreSQL 9.5 only users. I've been busy getting all my ducks lined up. A lot on tiger geocoder and address standardizer extension to be shipped with windows builds, story for later. One other feature we plan to ship with the windows PostGIS 2.2 builds is the ogr_fdw ogr_fdw Foreign data wrapper extension. I've been nagging Paul Ramsey a lot about issues with it, this in particular, and after some prodding, he finally put his nose in and fixed them and pinged Even Rouault for some help on a GDAL specific item.

Needless to say, I've been super happy with the progress and support I've gotten with ogr_fdw development and really enjoying my ogr_fdw use. The XLSX reading a file saved after the connection was open required a fix in GDAL 2.0 branch (which missed GDAL 2.0.0 release, so because of this, this new package contains a GDAL 2.0.1ish library. Hopeful GDAL 2.0.1 will be out before PostGIS 2.2.0 comes out so I can release without guilt with this fix.

What's packaged and where do I get it

One of the things we decided to take out of the new build is CURL support (it's still in for packaged www_fdw). We added in sqlite3 support (which opens up a couple of key spatial drivers needed by some of our clients). The main reason for the change is since we are shipping this with PostGIS 2.2, we didn't want the extra CURL dependency and none of our clients or users funding our work had a need for any of the ogr www based drivers (Google Fusion tables, CartoDb, WFS etc.). If you have issue with that, let us know. We've also included the executables ogr2ogr.exe, ogrinfo.exe which gets built with GDAL. This we did mostly so you can see available formats and do basic troubleshooting: with command like:

ogrinfo --formats

Which outputs:
Supported Formats:
  PCIDSK -raster,vector- (rw+v): PCIDSK Database File
  PDF -raster,vector- (w+): Geospatial PDF
  ESRI Shapefile -vector- (rw+v): ESRI Shapefile
  MapInfo File -vector- (rw+v): MapInfo File
  UK .NTF -vector- (ro): UK .NTF
  OGR_SDTS -vector- (ro): SDTS
  S57 -vector- (rw+v): IHO S-57 (ENC)
  DGN -vector- (rw+): Microstation DGN
  OGR_VRT -vector- (rov): VRT - Virtual Datasource
  REC -vector- (ro): EPIInfo .REC
  Memory -vector- (rw+): Memory
  BNA -vector- (rw+v): Atlas BNA
  CSV -vector- (rw+v): Comma Separated Value (.csv)
  GML -vector- (rw+v): Geography Markup Language (GML)
  GPX -vector- (rw+v): GPX
  KML -vector- (rw+v): Keyhole Markup Language (KML)
  GeoJSON -vector- (rw+v): GeoJSON
  OGR_GMT -vector- (rw+): GMT ASCII Vectors (.gmt)
  GPKG -raster,vector- (rw+vs): GeoPackage
  SQLite -vector- (rw+v): SQLite / Spatialite
  ODBC -vector- (rw+): ODBC
  WAsP -vector- (rw+v): WAsP .map format
  PGeo -vector- (ro): ESRI Personal GeoDatabase
  MSSQLSpatial -vector- (rw+): Microsoft SQL Server Spatial Database
  OpenFileGDB -vector- (rov): ESRI FileGDB
  XPlane -vector- (rov): X-Plane/Flightgear aeronautical data
  DXF -vector- (rw+v): AutoCAD DXF
  Geoconcept -vector- (rw+): Geoconcept
  GeoRSS -vector- (rw+v): GeoRSS
  GPSTrackMaker -vector- (rw+v): GPSTrackMaker
  VFK -vector- (ro): Czech Cadastral Exchange Data Format
  PGDUMP -vector- (w+v): PostgreSQL SQL dump
  OSM -vector- (rov): OpenStreetMap XML and PBF
  GPSBabel -vector- (rw+): GPSBabel
  SUA -vector- (rov): Tim Newport-Peace's Special Use Airspace Format
  OpenAir -vector- (rov): OpenAir
  OGR_PDS -vector- (rov): Planetary Data Systems TABLE
  HTF -vector- (rov): Hydrographic Transfer Vector
  AeronavFAA -vector- (rov): Aeronav FAA
  Geomedia -vector- (ro): Geomedia .mdb
  EDIGEO -vector- (rov): French EDIGEO exchange format
  SVG -vector- (rov): Scalable Vector Graphics
  Idrisi -vector- (rov): Idrisi Vector (.vct)
  ARCGEN -vector- (rov): Arc/Info Generate
  SEGUKOOA -vector- (rov): SEG-P1 / UKOOA P1/90
  SEGY -vector- (rov): SEG-Y
  XLS -vector- (ro): MS Excel format
  ODS -vector- (rw+v): Open Document/ LibreOffice / OpenOffice Spreadsheet
  XLSX -vector- (rw+v): MS Office Open XML spreadsheet
  Walk -vector- (ro): Walk
  SXF -vector- (ro): Storage and eXchange Format
  Selafin -vector- (rw+v): Selafin
  JML -vector- (rw+v): OpenJUMP JML
  TIGER -vector- (rw+v): U.S. Census TIGER/Line
  AVCBin -vector- (ro): Arc/Info Binary Coverage
  AVCE00 -vector- (ro): Arc/Info E00 (ASCII) Coverage

or try to verify a connection if you have issue with something like

ogrinfo ODBC:user/password@SQLServerDSNName -sql "SELECT table_name, column_name from information_schema.columns WHERE column_name = 'FirstName'"

The ogr_fdw_info doesn't have a --formats option

ODBC Support

The most sort after driver support at least for windows users is the ODBC support (particularly for connecting to SQL Server databases). Someone wrote me about this trying to use the old odbc_fdw we talked about a while ago, and I told him to use ogr_fdw instead since its much more robust and support 9.3 and 9.4

I mentioned in Querying MS Access and other ODBC data sources with OGR_FDW it's use with MS Access, but still plan to follow up soon with a SQL Server example. With SQL Server there are two drivers, which both rely on ODBC support. The ODBC connection form requires a system dsn. The MSSQLSpatial you can do with a DSNLess connection, but there is an extra environment variable you need to set. More on that in our followup article.

Using PostgreSQL Contribs


pgRouting 2.1 Beta released

pgRouting 2.1 Beta release is out. Full details at pgRouting 2.1 works with PostGIS 2.1 and 2.2.0dev.

Windows experimental pgRouting and PostGIS binaries for PostgreSQL 9.3 and 9.4 available at