Table Of Contents
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
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 https://github.com/pramsey/pgsql-ogr-fdw/issues/25, 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
- PostgreSQL 9.4 w32 FDWs (zip) 7z version
- PostgreSQL 9.4 w64 FDWs (zip) 7z version
- PostgreSQL 9.3 w64 FDWs (zip) 7z version
- 9.3 32-bit update coming soon
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:
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) Coverageor 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
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 https://lists.osgeo.org/pipermail/pgrouting-dev/2015-August/001569.html. 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 http://postgis.net/windows_downloads.