Table Of Contents

We just pushed out installers for PostGIS 2.2.0 for PostgreSQL 9.5beta2 windows both 32-bit and 64-bit on Application Stackbuilder. These installers are also available as standalone listed on PostGIS windows page. This is the first PostGIS 2.2.0 release for the PostgreSQL 9.5 32-bit and a rerelease for PostgreSQL 9.5 x 64-bit (this time compiled against beta2 instead of beta1).

On quick testing the PostGIS 2.2 beta1 release and pgRouting 2.1.0 worked fine on 9.5beta2, however you may want to reinstall anyway just to be safe. You can just reinstall over your existing install, no need to uninstall first. Similarly just upgrading a PostgreSQL 9.5beta1 to 9.5beta2 seemed to not require pg_upgrade or dump/restore, so safe to just upgrade from 9.5beta1 to 9.5beta2. Other notes about this 9.5beta2 PostGIS 2.2.0 release:

• The FDW API changed between PostgreSQL 9.5beta1 and PostgreSQL 9.5beta2, so the OGR_FDW, if you don't reinstall the bundle, will crash and burn in PostgreSQL 9.5beta2 (using PostGIS 2.2. beta1 executables). Similarly this newly compiled OGR_FDW will not work on PostgreSQL 9.5beta1 (so upgrade to 9.5beta2 first).
• The PostgreSQL 9.5betas (that includes both beta1 and beta2), are compiled against the pointcloud 1.1 master branch. This was required because the released pointcloud 1.0.1, does not compile against PostgreSQL 9.5
• The PostgreSQL 9.5beta2 PostGIS 2.2.0 release comes packaged with SFCGAL 1.2.2 (instead of 1.2.0 like the others versions) which fixes a crasher with ST_StraightSkeleton as noted in ticket - https://trac.osgeo.org/postgis/ticket/3324. Newer SFCGAL will be packaged with upcoming PostGIS 2.2.1, but if you are on an older edition and are using SFCGAL, you can always copy latest SFCGAL.dll binaries from the 2.2.1dev packages on PostGIS windows page http://postgis.net/windows_downloads/.

All this time we've been using COPY FROM PROGRAM without any additional adornments. Then we noticed for some things like directory listings in Microsoft windows, it would come across a sequence of inputs it mangled or you may even get an error such as ERROR: end-of-copy marker corrupt.

The trick to get around the issue is to use the FORMAT option. The default FORMAT for COPY FROM PROGRAM is 'text', which tries to escape out some things and in doing so it misinterprets windows slashes so you get weird stuff or no stuff at all.

DROP TABLE IF EXISTS dir_list;
CREATE TABLE dir_list(file_name text);
COPY dir_list FROM PROGRAM 'dir C:\gisdata /b /S'

You may end up with strange symbols in your table or the error: ERROR: end-of-copy marker corrupt

Replace your SQL statement with this:

DROP TABLE IF EXISTS dir_list;
CREATE TABLE dir_list(file_name text);
COPY dir_list FROM PROGRAM 'dir C:\gisdata /b /S'  WITH (format 'csv')

And you'll get something like this:

SELECT file_name 
    FROM dir_list 
    ORDER BY file_name LIMIT 10;

each file is a separate row as it should be:

C:\gisdata\boundaries.zip
C:\gisdata\BOUNDARY_ARC.dbf
C:\gisdata\BOUNDARY_ARC.prj
C:\gisdata\BOUNDARY_ARC.sbn
C:\gisdata\BOUNDARY_ARC.sbx
C:\gisdata\BOUNDARY_ARC.shp
C:\gisdata\BOUNDARY_ARC.shp.xml
C:\gisdata\BOUNDARY_ARC.shx
C:\gisdata\BOUNDARY_POLY.dbf
C:\gisdata\BOUNDARY_POLY.prj

PostgreSQL 9.5RC1 got released recently, and as with PostgreSQL 9.5beta2, the FDW API changed just enough so that the ogr_fdw I compiled for PostgreSQL 9.5beta2 no longer worked for PostgreSQL 9.5RC1. While patching up ogr_fdw to make it work with PostgreSQL 9.5RC1, I took a study of postgres_fdw to see how much effort it would be to implement this new PostgreSQL 9.5 Import Schema functionality for my favorite fdw ogr_fdw. Took me about a day's work, and if I was more experienced, it would have been probably only an hour to graft the logic from postgres_fdw and the ogr_fdw_info that Paul Ramsey had already done, to achieve Import Foreign Schema nirvana. Here's hoping my ogr_fdw patch gets accepted in some shape or form in time for PostgreSQL 9.5 release and in time to package for Windows PostGIS 2.2 Bundle for PostgreSQL 9.5.

I tested my patch on a couple of data sources and for the most part, I think it works pretty well and covers Import All data, LIMIT/EXCEPT, some IMPORT FOREIGN SCHEMA options for name preservation of table and column, and import partial via schema. You can look at the revised readme here. For the remote schema part, I took a bit of liberty in using that as a layer prefix filter instead of a true schema, since few OGR data sources support the concept of schemas, and some even have nested schemas like WFS sources which might have layer names like state.city.gov.tax.Parcels. So I figured using the remote schema to mean table prefix, would be way more useful.

A couple of caveats

• The remote schema ogr_all is a catchall to say don't apply any layer prefix check.
• The import schema implementation I have, launders table and column names to agree with the behavior of the ogr_fdw_info command-line tool. However I did define IMPORT FOREIGN SCHEMA statement options - launder_table_names, launder_column_names to turn this behavior off.
• For LIMIT and EXCEPT clauses are always based on the resulting table names. Since ogr_fdw launders the layer names by default to mash into more kosher table names, this means your LIMIT / EXCEPT may not be what you are expecting.

To demonstrate the coolness of this new feature, we shall demonstrate, with an exercise we did earlier, but instead of using our make shift ogr_fdw_sql_table function, We will perform the same feat with IMPORT FOREIGN SCHEMA.

Import tables from Spreadsheet Workbook

We performed this exercise earlier in Import Foreign Schema hack with OGR_FDW and reading LibreOffice calc workbooks and now we shall do something similar by importing 2 tables IMPORT FOREIGN SCHEMA.

CREATE SERVER svr_budget2015
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource 'C:/fdw_data/Budget2015.ods',
    format 'ODS' );

CREATE SCHEMA IF NOT EXISTS budget2015;

IMPORT FOREIGN SCHEMA ogr_all 
    LIMIT TO(dashboard, debt_analysis)
    FROM SERVER svr_budget2015 INTO budget2015;

Preserving case and other unkosher things

If you wanted to preserve the original table names and column names, as mentioned, it's the resulting table names that matter. So this means when using the LIMIT to clause, you would need to use the remote datasource's name and since it has mixed case, it needs to be quoted to be treated correctly.

IMPORT FOREIGN SCHEMA ogr_all 
    LIMIT TO("Dashboard", "Debt Analysis")
    FROM SERVER svr_budget2015 INTO budget2015
    OPTIONS(launder_table_names 'false', launder_column_names 'false');

Bringing tables in by prefix

To use the schema feature, since most data sources don't have schema feature, we use that to mean the start of a layer name. Note that in this case, you have to match the casing etc of the layer, not the final table. So we would do the following to bring in all tables that start with Form.

IMPORT FOREIGN SCHEMA "Form"
    FROM SERVER svr_budget2015 INTO budget2015;

Let's see what we have

To check what tables we have now we do this:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'budget2015'
ORDER BY table_name;

Which outputs:

         table_name
----------------------------
 dashboard
 Dashboard
 Debt Analysis
 debt_analysis
 form_2___equity
 form_3___income
 form_4___lump_sum_planning
 form_5___monthly_cash_flow
 form_7___allocation
 form_8___spending_details
 form_9___work_expenses
(11 rows)