Table Of Contents

PostGIS 2.1.1 maintenance release was released just yesterday. Details are on the PostGIS.net site: PostGIS 2.1.1 release notes.

For windows users, we have binaries available for PostgreSQL 9.0-9.3 (64-bit) and 9.2-9.3 (32-bit). Which are available in the winnie bot section of download page. We plan to have the windows installers available for 9.2 and 9.3 (both 32 and 64-bit) in the next week or so.

Alexander Kuznetsov on SQLblog.com has an interesting series going entitled with Learning PostgreSQL. In the series he focuses on what it takes to move a SQL Server database and App to PostgreSQL and highlights some of the key differences between the two platforms that you should watch out for. I recommend it to any SQL Server developer planning to make the switch to PostgreSQL or any PostgreSQL consultant tasked with the job and not familiar with the intricacies of SQL Server.

His PostgreSQL feed can be found here here

So far on his list of articles in the series:

1. Learning PostgreSQL: First Steps
2. Learning PostgreSQL: Reading and Writing from .NET
3. Learning PostgreSQL: Functions and refcursors
4. Learning PostgreSQL: bulk loading data
5. Learning PostgreSQL: overloading
6. Learning PostgreSQL: old versions of rows are stored right in the table

One of the features new to PostgreSQL 9.3 is the COPY FROM PROGRAM sql construct and in psql the equivalent \copy from program. Michael Paquier covered the COPY TO/FROM PROGRAM in hist Postgres 9.3 feature highlight: COPY TO/FROM PROGRAM. Depesz covered the companion psql construction in Support for piping copy to from an external program. Michael demonstrated an example using curl. I wanted to try something similar using wget since I have wget readily available on all my Linux and Unix boxes. For this example I'll demonstrate doing it on windows, but doing it on Linux is much the same and simpler since the wget and curl are generally already in Linux default path.

Wget call from PostgreSQL to load data

When you call CURL it naturally pipes the data to the stdout which is what is needed in order to insert the output to a PostgreSQL table. If you use wget, you need to add some additional commands to retarget it from file to stdout. In this example we'll load data using the World Bank API described at http://data.worldbank.org/node/11.

CREATE TABLE worldbank_json(data json);
COPY worldbank_json 
  FROM PROGRAM 'C:/wget/wget.exe -q -O - "$@" "http://api.worldbank.org/countries?format=json&per_page=100&page=1"';
COPY worldbank_json 
  FROM PROGRAM 'C:/wget/wget.exe -q -O - "$@" "http://api.worldbank.org/countries?format=json&per_page=100&page=2"';
COPY worldbank_json 
 FROM PROGRAM 'C:/wget/wget.exe -q -O - "$@" "http://api.worldbank.org/countries?format=json&per_page=100&page=3"';

Querying JSON data

The json datasets get dumped as single records for each call. To query the data we employ some PostgreSQL 9.3+ json goodie operators described in JSON Functions and Operators PostgreSQL manual page.

-- json is 0 index based, first element is page summary (0 index)
-- second element (index 1) is the data which itself is a json array
WITH je AS (SELECT json_array_elements(data->1) AS jd 
FROM  worldbank_json)
SELECT jd->>'id' AS id, jd->>'name' As country, 
  jd#>>'{adminregion,id}' As region_id
FROM je ;

The output of our query is partially shown below:

 id  |                        country                        | region_id
-----+-------------------------------------------------------+-----------
 ABW | Aruba                                                 |
 AFG | Afghanistan                                           | SAS
 AFR | Africa                                                |
 AGO | Angola                                                | SSA
 ALB | Albania                                               | ECA
 AND | Andorra                                               |
 ARB | Arab World                                            |
 ARE | United Arab Emirates                                  |
 ARG | Argentina                                             | LAC
 ARM | Armenia                                               | ECA
 ASM | American Samoa                                        | EAP
 ATG | Antigua and Barbuda                                   | LAC
 AUS | Australia                                             |
 AUT | Austria                                               |
 AZE | Azerbaijan                                            | ECA
 BDI | Burundi                                               | SSA

We have PostGIS intro presentation we did in September in 4 parts at https://www.youtube.com/playlist?list=PLHPJPLnP-bUWZShKrUIdbJM5dig8oavzQ.

In the Intro we covered PostGIS geometry, tiger geocoder, and raster. Sadly we didn't have time to cover geography or topology.

Code used in the presentation can be downloaded from our presentations page: http://www.postgis.us/presentations

Introduction to PostGIS Video: Parts 1-4

In PostgreSQL 9.3 Postgres_FDW: A Test Drive we talked about taking Postgres Foreign Data Wrapper for a test drive. One downside of the Postgres FDW and actually most PostgreSQL FDWs is that the foreign table structure can't be inspected from the source and the fields have to be explicitly stated in the foreign table definition. If you have a lot of tables to script, this can quickly become tedious. For our planned used cases, we plan to script Foreign tables from a source database for tables that are bulky and rarely change and then build materialized views against those for faster performance where needed. To help in this end, we wrote a quick SQL function that you install on the source database and when run will generate foreign table creation statements to install on a target database.

One thing that was important to us was that the function properly handle views and typmod types since PostGIS now uses typmod heavily and many of our databases have spatial data and complex views we'd like to link in as foreign tables.

Function to generate Foreign table create statements

The function script is shown here and should be installed on the database that you want to script the tables as foreign tables for future use on a target server.

CREATE OR REPLACE FUNCTION script_foreign_tables(param_server text
 , param_schema_search text
 , param_table_search text, param_ft_prefix text) RETURNS SETOF text
AS 
$$
-- params: param_server: name of foreign data server
--        param_schema_search: wildcard search on schema use % for non-exact
--        param_ft_prefix: prefix to give new table in target database 
--                        include schema name if not default schema
-- example usage: SELECT script_foreign_tables('prod_server', 'ch01', '%', 'ch01.ft_');
  WITH cols AS 
   ( SELECT cl.relname As table_name, na.nspname As table_schema, att.attname As column_name
    , format_type(ty.oid,att.atttypmod) AS column_type
    , attnum As ordinal_position
      FROM pg_attribute att
      JOIN pg_type ty ON ty.oid=atttypid
      JOIN pg_namespace tn ON tn.oid=ty.typnamespace
      JOIN pg_class cl ON cl.oid=att.attrelid
      JOIN pg_namespace na ON na.oid=cl.relnamespace
      LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
      LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
     WHERE 
     -- only consider non-materialized views and concrete tables (relations)
     cl.relkind IN('v','r') 
      AND na.nspname LIKE $2 AND cl.relname LIKE $3 
       AND cl.relname NOT IN('spatial_ref_sys', 'geometry_columns'
          , 'geography_columns', 'raster_columns')
       AND att.attnum > 0
       AND NOT att.attisdropped 
     ORDER BY att.attnum )
        SELECT 'CREATE FOREIGN TABLE ' || $4  || table_name || ' ('
         || string_agg(quote_ident(column_name) || ' ' || column_type 
           , ', ' ORDER BY ordinal_position)
         || ')  
   SERVER ' || quote_ident($1) || '  OPTIONS (schema_name ''' || quote_ident(table_schema) 
     || ''', table_name ''' || quote_ident(table_name) || '''); ' As result        
FROM cols
  GROUP BY table_schema, table_name
$$ language 'sql';

Setting up the Target database with postgres_fdw

The first step is to have a database with a postgres_fdw server and user setup. Basic steps are:

--script to run on target server --
CREATE EXTENSION postgres_fdw;
CREATE SERVER book_server
 FOREIGN DATA WRAPPER postgres_fdw 
  OPTIONS (host 'localhost', port '5432', dbname 'postgis_in_action');
  
CREATE USER MAPPING FOR public SERVER 
  book_server
  OPTIONS (user 'book_guest', password 'whatever');

Script tables and views as foreign tables

The next step is to run the function we described on the source server. For this exercise, we just want to script one schema and we want to give the tables a different name in case we want to create materialized views against these that have the original name.

SELECT script_foreign_tables('book_server', 'ch01', '%', 'ch01.ft_');

The output of the above query looks like this:

CREATE FOREIGN TABLE ch01.ft_highways (gid integer, feature character varying(80), name character varying(120)
 , state character varying(2), geom geometry(MultiLineString,2163))  
	 SERVER book_server  OPTIONS (schema_name 'ch01', table_name 'highways'); 

CREATE FOREIGN TABLE ch01.ft_lu_franchises (id character(3), franchise character varying(30))  
	 SERVER book_server  OPTIONS (schema_name 'ch01', table_name 'lu_franchises');
	 
CREATE FOREIGN TABLE ch01.ft_restaurants (id integer, franchise character(3), geom geometry(Point,2163))  
	 SERVER book_server  OPTIONS (schema_name 'ch01', table_name 'restaurants');