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

Table Of Contents

Using PostgreSQL Contribs

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

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.