Postgres OnLine Journal: August 2013 / September 2013
An in-depth Exploration of the PostgreSQL Open Source Database
 

Table Of Contents

What's new and upcoming in PostgreSQL
Using PostgreSQL Contribs
Product Showcase

What's new and upcoming in PostgreSQL

 

PostGIS 2.1.0rc2 released



The 2.1.0rc2 release of PostGIS is now available.

The PostGIS development team is proud to release a release candidate version of upcoming PostGIS 2.1.0. As befits a minor release, the focus is on speed improvements, more features, and bug fixes. We expect this to be the final release candidate before we officially release 2.1.0 slated for Aug 12th, 2013. We’d appreciate it if you test it before final release and report back with any issues you run into so we can have a smooth release.

If you are currently using PostGIS 2.0 (or 2.1.0 beta3+) using extension support, you can go the soft upgrade path:

ALTER EXTENSION postgis UPDATE TO "2.1.0rc2";

If you are running 2.1.0 of beta or an unreleased version (and the above step fails with error “extension postgis has no update path from version x.x.x to version 2.1.0rc2”) , you need to first copy the file in share\extensions\postgis—2.0.3—2.1.0rc2.sql and change the 2.0.3 to the x.x.x noted in the error you are running. Then follow the above upgrade step again.

Users of 1.5 and below will need to go the hard-upgrade path.

Best served with a bottle of GEOS 3.4.0 (planned release Aug 11th) and PostgreSQL 9.3beta2.

http://download.osgeo.org/postgis/source/postgis-2.1.0rc2.tar.gz

html doc download pdf doc download epub doc download

This release contains bug fixes completed since 2.1.0rc1 release:

Bug Fixes

  • #2401 pagc_address_parser: Add is_custom options to pagc_rules table
  • #2230 loader/dumper: pgsql2shp fails with named schema on windows 64
  • #2381 pagc_address_parser: change all casing to uppercase
  • #2389 documentation: shp2pgsql_gui misses man page
  • #2380 pagc_address_parser: Fix pagc lookup tables resolves BAY STATE incorrectly
  • #2274 pagc_address_parser: pagc doesn’t compile with 9.3 development
  • #2384 raster: [raster] Problem in ST_Neighborhood(raster, integer, geometry, integer, integer, boolean)
  • #2383 raster: [raster]: Create extension with backslash_quote off
  • #2379 topology: Topology regression failure in regress/topogeo_addlinestring
  • #1754 postgis: in_gml regress check crashes on vc++ 64 edb (only 9.3)

Team PostGIS

View all closed tickets.


What's new and upcoming in PostgreSQL

 

Caching data with materialized views and statement level triggers Intermediate



One exciting new feature coming in PostgreSQL 9.3 is materialized views. Materialized views is really a mechanism for caching data of a query. It is especially useful if you have long running queries where the answers change infreqently. There are many things unfortunately that materialized views won't do where you are still better off with regular views.

  • They don't refresh themselves automatically. Thus requiring a cron job/pgagent job or a trigger on something to refresh.
  • They can't be user dependent or time dependent. For example if you have a view that does something like WHERE user=current_user(), then a materialized view is out of the question.

For PostGIS work we need some views that take more than 10 seconds to compute. For example for one project we have a base parcel table and tables broken out by fiscal year that inherit from the base parcel table. We also have housing and neighborhood revitalization projects whose parcels are keyed based on parcel id. Housing and revitalization projects by their very nature change the landscape by creating and destroying parcels. So the parcel set you started out with may not be what you end up with. In order to reference an older project that has changed the parcel divide landscape, we sometimes need to reference old no longer existent parcels. What we do to achieve this is to have a table, We'll call it parcelmax that contains 3 columns -- the fiscal year a parcel was last seen, the parcel id, and the geometry of the parcel. Quering our parcel hierarchy takes too long so as part of the process of adding a new table we always rebuild this table from the parcel hierarchy. We, now in PostgreSQL 9.3, have the option to use a materialized view.

Here is structures of our tables just for last 2 fiscal years.

CREATE TABLE parceltime(pid varchar(20), geom geometry(MultiPolygon,26986)
  , fy integer
  ,   CONSTRAINT pk_parceltime PRIMARY KEY (pid, fy));

CREATE TABLE parcel_2013(
  CONSTRAINT pk_parcel2013 PRIMARY KEY (pid,fy)
  ) INHERITS (parceltime);
ALTER TABLE parcel_2013 ALTER COLUMN fy SET DEFAULT 2013;
ALTER TABLE parcel_2013 ADD CONSTRAINT chk_fy CHECK (fy = 2013);

CREATE TABLE parcel_2012(
  CONSTRAINT pk_parcel2012 PRIMARY KEY (pid,fy)
  ) INHERITS (parceltime);
ALTER TABLE parcel_2012 ALTER COLUMN fy SET DEFAULT 2012;
ALTER TABLE public.parcel_2012 ADD CONSTRAINT chk_fy CHECK (fy = 2012);

Building the materialized view

The materialized view to replace our old parcelmax looks like this:

DROP MATERIALIZED VIEW IF EXISTS parcelmax;
CREATE MATERIALIZED VIEW parcelmax AS
SELECT DISTINCT ON(pid) pid, fy, geom
  FROM parceltime
  ORDER BY pid, fy DESC;
  
CREATE UNIQUE INDEX iudx_parcelmax_pid ON parcelmax (pid);
CREATE INDEX idx_parcelmax_geom ON parcelmax USING gist(geom);

So now whenever we update our parcel hierarchy or add a new table to the hierarchy, we need to run:

REFRESH MATERIALIZED VIEW parcelmax;

Triggering automagical rebuild

This is a smidge better than what we used to do, but frankly not that much better. Most of the time where we forget to refresh our table happens when we have to correct geometries in a table or delete an invalid record. It's these times we wish something automagical would happen. Luckily PostgreSQL has something called statement triggers. We've never had much need for statement level triggers except for logging. However the marriage between materialized views and statement level triggers seems like a good one especially for tables you update infrequently.

Here is the code to cause the view to rebuild in event any dependency tables change:

CREATE OR REPLACE FUNCTION trig_refresh_parcelmax() RETURNS trigger AS
$$
BEGIN
    REFRESH MATERIALIZED VIEW parcelmax;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql ;


CREATE TRIGGER trig_01_refresh_parcelmax AFTER TRUNCATE OR INSERT OR UPDATE OR DELETE
   ON parcel_2013 FOR EACH STATEMENT
   EXECUTE PROCEDURE trig_refresh_parcelmax();

CREATE TRIGGER trig_01_refresh_parcelmax AFTER TRUNCATE OR INSERT OR UPDATE OR DELETE
   ON parcel_2012 FOR EACH STATEMENT
   EXECUTE PROCEDURE trig_refresh_parcelmax();

Test out our statement makeup

To test this architecture out, we'll add some records to both tables.

-- Yields 0 records --
SELECT COUNT(*) FROM parcelmax;
-- testing (10500 records added)
INSERT INTO parcel_2012(pid, geom)
SELECT (i::text || size::text)::varchar(20)
  , ST_Multi(ST_Buffer(
        ST_Transform(
           ST_SetSRID(
             ST_Point(-71.0574 + i*0.00001,42.3581 + i*0.00001)
             ,4326)
             ,26986), size))
FROM generate_series(1,1000,2) i , generate_series(100,300,10) As size;

-- yields 10500 records --
SELECT COUNT(*) FROM parcelmax;

--testing add to parcel_2013 (12600 records added)
INSERT INTO parcel_2013(pid, geom)
SELECT (i::text || size::text)::varchar(20)
  , ST_Multi(ST_Buffer(
        ST_Transform(
           ST_SetSRID(
             ST_Point(-71.0574 + i*0.00001,42.3581 + i*0.00001)
             ,4326)
             ,26986), size))
FROM generate_series(1,1200,2) i , generate_series(100,300,10) As size;

-- yields 12600 records --
SELECT COUNT(*) FROM parcelmax;


What's new and upcoming in PostgreSQL

 

Materialized geometry_columns using Event Triggers Intermediate



One of the big changes in PostGIS 2.0 was that the geometry_columns table became a read only view. This change was not without sacrifice. On the plus it meant a table no longer needed to be maintained which was a relief for those who had to constantly ensure the sideline table was up to date. Also on the plus it meant we could base the views on the current user so that if a user connected, they would only see tables they had access to. The sacrifice made was a bit of speed. In most use cases, the speed difference is hardly noticeable and only noticeable if you have a query constantly polling this table. However if you have a database of 15,000 geometry tables it could take as long as 450ms as noted in this ticket.

So just playing with ideas, could the new PostgreSQL 9.3 event triggers and materialized views provide a solution. Keep in mind this is just food for thought. We're not condoning people go out and hack their PostGIS install. We suspect if we implement such a thing in PostGIS core it will change from what we propose here. If you saw our prior article Caching data with materialized views and statement level triggers you know where this is going. We'll add yet another concept to this cocktail and that is what we'll call schema hiding which by that we mean counting on the order of a search_path to hide a named table/view you don't want.

Step 1: Install PostGIS in its own schema

Step one is to install PostGIS in its own schema and make sure it comes after the public schema. This insures that if you do for some reason create a geometry_columns view or table or what have you in public, it will be used before the postgis packaged one.

CREATE SCHEMA postgis;
CREATE EXTENSION postgis SCHEMA postgis;
ALTER DATABASE test_gis SET search_path = "$user",public,postgis;

Step 2: Create materialized geometry_columns

Now we'll create a geometry_columns materialized view in the public scheam. Note that because in step 1 we set the search path such that the geometry_columns view provide by postgis would be used after one in the user's schema or public schema. So now we are in position to put forth an alternative geometry_columns and one that will be used instead of the installed one.

The view we build is almost identical to the one provided with postgis except we took out the logic that limits the list of tables to just those the current user has select rights to. We don't want the materialized view to be user dependent.

CREATE MATERIALIZED VIEW public.geometry_columns AS 
 SELECT current_database()::character varying(256) AS f_table_catalog, 
    n.nspname::character varying(256) AS f_table_schema, 
    c.relname::character varying(256) AS f_table_name, 
    a.attname::character varying(256) AS f_geometry_column, 
    COALESCE(NULLIF(postgis.postgis_typmod_dims(a.atttypmod), 2)
    , postgis.postgis_constraint_dims(n.nspname::text, c.relname::text, a.attname::text), 2) AS coord_dimension, 
    COALESCE(NULLIF(postgis.postgis_typmod_srid(a.atttypmod), 0)
    , postgis.postgis_constraint_srid(n.nspname::text, c.relname::text, a.attname::text), 0) AS srid, 
    replace(replace(COALESCE(NULLIF(upper(postgis.postgis_typmod_type(a.atttypmod)), 'GEOMETRY'::text)
      , postgis.postgis_constraint_type(n.nspname::text, c.relname::text, a.attname::text)::text
      , 'GEOMETRY'::text), 'ZM'::text, ''::text), 'Z'::text, ''::text)::character varying(30) AS type
   FROM pg_class c, 
    pg_attribute a, 
    pg_type t, 
    pg_namespace n
  WHERE t.typname = 'geometry'::name 
     AND a.attisdropped = false 
     AND a.atttypid = t.oid 
     AND a.attrelid = c.oid 
     AND c.relnamespace = n.oid 
     AND (c.relkind = 'r'::"char" OR c.relkind = 'v'::"char") 
     AND NOT pg_is_other_temp_schema(c.relnamespace) 
     AND NOT (c.relname = 'raster_columns'::name) ;

Step 3: Create Event trigger on table CREATE, ALTER, DROP, CONSTRAINT ..

For this magic step, we are going to define a data definition language trigger (DDL) that refreshes our view whenever a new table is created or altered or we add constraints. We ned the constraint one to catch constraint based geometry tables.

CREATE OR REPLACE FUNCTION trig_refresh_geometry_columns() RETURNS event_trigger AS $$
BEGIN
    IF tg_tag IN('CREATE TABLE','CREATE TABLE AS'
        , 'CREATE FOREIGH TABLE', 'DROP TABLE', 'ALTER TABLE'
        , 'CREATE VIEW', 'ALTER VIEW') THEN
      REFRESH MATERIALIZED VIEW public.geometry_columns;
    END IF;
END;
$$ LANGUAGE plpgsql;
 
CREATE EVENT TRIGGER trig_01_refresh_geometry_columns ON ddl_command_end 
 EXECUTE PROCEDURE trig_refresh_geometry_columns();     

Step 4: Test Drive

CREATE TABLE testgeom(gid serial primary key, geom geometry(POINT,4326) );
ALTER TABLE testgeom ADD COLUMN geom2 geometry(POLYGON, 4326);

SELECT f_table_name As tname, f_geometry_column As gc, srid, type 
 FROM public.geometry_columns;
  tname   |  gc   | srid |  type
----------+-------+------+---------
 testgeom | geom  | 4326 | POINT
 testgeom | geom2 | 4326 | POLYGON

Test 5: Speed test

The script below generates 1000 geometry tables.

 
DO LANGUAGE 'plpgsql'
$$
DECLARE i int;
BEGIN
  FOR i IN 1 .. 1000 LOOP
      EXECUTE 'CREATE TABLE gtest' || i::text || '(gid serial primary key, geom geometry(POINT,4326))';          
  END LOOP;
        
END 

$$

If run with the trigger in place, it takes 63062 ms (a little over a minute to finish). Without the trigger it takes 3,831ms ~ 3seconds). This is because each call to CREATE TABLE causes a REFRESH VIEW. So moral of that story, if you plan to create 1000 tables in one go, then disable the trigger and manually refresh the view yourself.

ALTER EVENT TRIGGER trig_01_refresh_geometry_columns DISABLE;
--do your crazy thing -
ALTER EVENT TRIGGER trig_01_refresh_geometry_columns ENABLE;
REFRESH MATERIALIZED VIEW geometry_columns;

Now to test speed of querying. Since we put the public first in our search_path, when we connect to database, the public.geometry_columns one will be used if we don't schema qualify.

-- 11ms
SELECT count(*) from geometry_columns; 

-- 41ms 
SELECT count(*) from postgis.geometry_columns;

-- this uses the one in public - takes 11 ms
SELECT srid, type, count(*) as tot 
  FROM geometry_columns
  GROUP BY srid,type;


--this uses the one in postgis takes 61 ms
SELECT srid, type, count(*) as tot 
  FROM postgis.geometry_columns
  GROUP BY srid,type;

What's new and upcoming in PostgreSQL

 

PostGIS 2.1.0 released



The 2.1.0 release of PostGIS is now available.

The PostGIS development team is proud to release PostGIS 2.1.0. As befits a minor release, the focus is on speed improvements, more features, and bug fixes.

If you are currently using PostGIS 2.0+, you can go the soft upgrade path:

ALTER EXTENSION postgis UPDATE TO "2.1.0";
 
If you are running 2.1.0 of beta or an unreleased version (and the above step fails with error “extension postgis has no update path from version x.x.x to version 2.1.0”) , you need to first copy the file in share\extensions\postgis—2.0.3—2.1.0.sql and change the 2.0.3 to the x.x.x noted in the error you are running. Then follow the above upgrade step again.

Users of 1.5 and below will need to go the hard-upgrade path documented in manual: http://postgis.net/docs/manual-2.1/postgis_installation.html#hard_upgrade

Best served with a bottle of GEOS 3.4.1 and PostgreSQL 9.3beta2 (planned release September).

http://download.osgeo.org/postgis/source/postgis-2.1.0.tar.gz

html doc download pdf doc download epub doc download

This release contains a ton of speed improvements, function additions , and super sexy new features. It has been over a year in the making. Most notable are significantly improved raster support,significantly improved 3D support (requires compile with SFCGAL), distance support for curves, improved geography distance, tiger geocoder install with extension support.

New functions itemized in http://postgis.net/docs/manual-2.1/PostGIS_Special_Functions_Index.html#NewFunctions21

For more details refer to http://postgis.net/2013/08/17/postgis-2-1-0

What's new and upcoming in PostgreSQL

 

PostGIS 2.1 windows bundle



PostgreSQL 9.3 came out today and we are excited to start using the new features. PostGIS 2.1.0 came out about 2 weeks ago and pgRouting just cut the RC 3 release. For windows PostGIS users who are impatient to try the new suite, binaries can be found on the Unreleased versions of PostGIS.net windows page.

We are planning an official release sometime probably next week on StackBuilder. We are waiting for release of pgRouting 2.0 before we do which should be out next week. This new 2.1 release will be dubbed the PostGIS 2.1 Bundle since it will have more than just PostGIS. It will include postgis extensions (postgis which includes geometry,raster, geography) , postgis_topology, postgis_tiger_geocoder), address_standardizer extension (a companion to tiger geocoder), and pgRouting 2.0.

For those people running PostGIS 2.0 and PostgreSQL 9.0+, especially (raster and geography) users, I highly recommend you jump to PostGIS 2.1. PostGIS 2.1 is a soft upgrade from 2.0. For raster there are enormous speed improvements and new functions. The ones we are most excited about in raster are the much much much faster ST_Clip and ST_Union functions (which now does multi-band in addition to being faster). These two functions are highly important since they are generally the first step in many raster workflows. Geography has speed improvements for point in poly and a ST_Segmentize function done on the spheroid (important for long range). Geometry has a couple of new functions. The Enhanced 3D functionality provided by SFCGAL is brand new and probably won't be distributed by many package maintainers until PostGIS 2.2 where it will garner a few more features and stability improvements.

Bad and not so bad news for windows PostgreSQL 9.1 and below users

After ruminating the number of users running older platforms and the amount of effort it takes to keep those versions around and create an installer package for permutations of 32 bit and 64-bit for all supported PostgreSQL versions, the windows PostGIS 2.1 will only be available on StackBuilder for PostgreSQL 9.2 and 9.3. You can still (if you are running 64-bit), download the 9.0 and 9.1 x 64-bit binaries from the PostGIS windows page.pgRouting for 9.0 and 9.1 windows x 64-bit are available there as well.

Note that this does not effect PostGIS 2.0.4 which we are in the middle of preparing windows stackbuilder packages for and should have within next 2 weeks as well. 2.0.4 we'll have packages for 8.4-9.2. Yikes 9 packages.


Using PostgreSQL Contribs

 

Raster Words using PostGIS 2.1 Advanced



Two of the big changes in PostGIS 2.1 raster are the improved speed and functionality of the raster ST_Union function and ST_Clip. Aside from speed, the big thing with ST_Union in 2.1 is that it applies operations to all bands by default. These are our most favorite funcitons of all. This is a continuation of Word Play with spatial SQL, except we'll be generating rasters instead of geometries and exercising some raster functions in addition to geometry functions.

Although these SQL statements look long and somewhat complicated, they are easily wrappable in an SQL function. We have, for example, an sql function to write letters on parcels that just takes as input the parcel id and the words to write.

This uses the postgis_letters extension, which we've finally put up on github postgis_letters. For rendering the images, we used our quickie viewer which relies on ASP.NET or PHP and JQuery. We'll be putting that up on github as well once we've dusted it off a bit.

If you are interested in the aerial and parcel geometry data we are using here, we grabbed it from MassGIS for Cambridge, Massachusetts area. You might recognize the base query in our upcoming DZone PostGIS refCard.

So here it goes. An exercise in raster expression.

Spatial SQLWord Image
WITH mit AS 
 (SELECT 
  -- union clipped resized tiles 
 ST_Union(
  -- clip to 100 meter of parcel boundary
    ST_Clip(
  --resize to 25% of original
      ST_Resize(a.rast,0.25,0.25)
          ,ST_Expand(p.geom,100))) AS rast
   FROM aerials As a 
         INNER JOIN 
          parcels As p 
    -- select tiles within 100 meters of mit parcel
      ON ST_DWithin(a.rast::geometry, p.geom, 100)
          WHERE p.pid = '57-169E' )
   , word AS (
       SELECT  ST_LettersAsGeometry('Clip', 'kankin'
          , ST_SRID(rast), 150, rast::geometry) As geom
          FROM mit)
   SELECT ST_Clip(mit.rast, word.geom ) As rast
        FROM mit CROSS JOIN word;
WITH mit AS 
 (SELECT ST_Union(ST_Clip(ST_Resize(a.rast,0.25,0.25)
          ,ST_Expand(p.geom,100))) AS rast
   FROM aerials As a 
         INNER JOIN 
          parcels As p 
      ON ST_DWithin(a.rast::geometry, p.geom, 100)
          WHERE p.pid = '57-169E' )
      , word AS (SELECT  
      ST_AsRaster(ST_LettersAsGeometry('Mean', 'kankin', ST_SRID(rast)
            , 50
   -- start writing 50 meters (horizontally) before centroid
     , ST_Translate(ST_Centroid(rast::geometry),-50,0) )
      , rast
   -- convert to 3 banded initialize 3 bands and make 0 no data value
      , '{8BUI,8BUI,8BUI}'::text[]
      , '{200,100,50}'::integer[]
      , '{0,0,0}'::integer[]) As rast
      FROM mit)
   SELECT ST_Union(rast, 'MEAN') As rast
        FROM (SELECT rast FROM mit
        UNION ALL 
        SELECT rast
        FROM word) AS final;
WITH mit AS 
 (SELECT ST_Union(ST_Clip(ST_Resize(a.rast,0.25,0.25)
          ,ST_Expand(p.geom,100))) AS rast
   FROM aerials As a 
         INNER JOIN 
          parcels As p 
      ON ST_DWithin(a.rast::geometry, p.geom, 100)
          WHERE p.pid = '57-169E' )
      , word AS (SELECT  ST_AsRaster(
        ST_LettersAsGeometry('Last', 'kankin', ST_SRID(rast)
            , 50
            , ST_Translate(ST_Centroid(rast::geometry),-50,0) )
            , rast
            , '{8BUI,8BUI,8BUI}'::text[]
            , '{200,100,50}'::integer[]
            , '{0,0,0}'::integer[]) As rast
          FROM mit)
   SELECT ST_Union(rast, 'LAST') As rast
        FROM (SELECT rast FROM mit
        UNION ALL 
        SELECT rast
        FROM word) AS final;
WITH mit aS 
  (SELECT ST_Union(ST_Clip(ST_Resize(a.rast,0.25,0.25)
          ,ST_Expand(p.geom,100))) AS rast
   FROM aerials As a 
         INNER JOIN 
          parcels As p 
      ON ST_DWithin(a.rast::geometry, p.geom, 100)
          WHERE p.pid = '57-169E' )
      , word AS (SELECT  ST_AsRaster(
        ST_LettersAsGeometry('RANGE', 'kankin', ST_SRID(rast)
            , 50
           -- start writing 50 meters to left of centroid
            , ST_Translate(ST_Centroid(rast::geometry),-50,0) )
            , rast, '{8BUI,8BUI,8BUI}'::text[]
            , '{200,100,50}'::integer[]
            , '{0,0,0}'::integer[]) As rast
          FROM mit)
   SELECT ST_Union(rast, 'RANGE') As rast
        FROM (SELECT rast FROM mit
        UNION ALL 
        SELECT rast
        FROM word) AS final;
-- since SUM would exceed 255 
-- when R band of RGB is added, R in area gets ceiling 255
-- this giving the sum a redish color
WITH mit aS 
  (SELECT ST_Union(ST_Clip(ST_Resize(a.rast,0.25,0.25)
          ,ST_Expand(p.geom,100))) AS rast
   FROM aerials As a 
         INNER JOIN 
          parcels As p 
      ON ST_DWithin(a.rast::geometry, p.geom, 100)
          WHERE p.pid = '57-169E' )
       , word AS (SELECT  ST_AsRaster(ST_LettersAsGeometry('SUM', 'kankin', ST_SRID(rast)
            , 50
           -- start writing 50 meters to left of centroid
            , ST_Translate(ST_Centroid(rast::geometry),-50,0) )
            , rast, '{8BUI,8BUI,8BUI}'::text[], '{200,100,50}'::integer[], '{0,0,0}'::integer[]) As rast
          FROM mit)
   SELECT ST_Union(rast, 'SUM') As rast
        FROM (SELECT rast FROM mit
        UNION ALL 
        SELECT rast
        FROM word) AS final;

Using PostgreSQL Contribs

 

CREATE SCHEMA IF NOT EXISTS in 9.3 and tiger geocoder



One of the new features in PostgreSQL 9.3 is CREATE SCHEMA IF NOT EXISTS someschema;. We were so excited about this new feature that we started using it in the tiger geocoder loader routine. For some reason we thought it was available since 9.1 which gained CREATE TABLE IF NOT EXISTS sometable; which we noted in Sweat the small stuff, it really matters

Luckily someone noticed. Fix is already in PostGIS 2.1.1 and 2.2. dev branch. We are planning for upcoming PostGIS 2.1.1 to be out in another 2-3 weeks.

PostGIS tiger geocoder installed as extension

One of the new features in 2.1 which is my favorite and the one we worked on the most is that tiger geocoder is now installable as an extension

CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;

-- bonus if you compiled address_standardizer 
-- (this will come with windows stackbuilder installs and is already available 
-- with experimental windows builds
CREATE EXTENSION address_standardizer;

PostGIS 2.2 - Tiger 2013

For PostGIS 2.2., tiger geocoder will be set to load US Census TIGER 2013. US Census Tiger 2013 came out recently August 22, 2013. In fact it does now as far as I can tell. As far as I can tell, not much has changed in this new release that prevents the present loader packaged in 2.1 from loading it. All you need to change is the tiger.loader_variables table change tiger_year to 2013 and website_ftproot to ftp://ftp2.census.gov/geo/tiger/TIGER2013. W'ell be comparing geocoding between old data set and new before we announce victory.

A quick spot check loading Massachusetts state data shows more records in ma_addr and ma_featnames, but fewer in ma_edges.

We still need to go thru some regressions to see how much has changed and if we need to make adjustments to logic.


Product Showcase

 

PostGIS 2.1 Bundle for PostgreSQL 9.3 Windows Stackbuilder



PostGIS 2.1 bundle for PostgreSQL 9.3 Windows is now available on StackBuilder for windows users. You will find both the 32-bit and 64-bit versions. The bundle includes the following features:

  • PostGIS 2.1 with extensions support for postgis (which has geometry/geography/raster), postgis_tiger_geocoder, and postgis_topology.
  • pgRouting 2.0.0 with extension support -- just do CREATE EXTENSION pgRouting
  • address_standardizer, which is needed for better tiger geocoding normalizing. Installed with CREATE EXTENSION address_standardizer

One thing that is different with this packaging is that we are no longer providing a template_postgis database. However if you want your database created for you, you can check the Create spatial database option which will create a regular old database with all the packaged extensions under the sun created for you.

If you get an error of the form Invalid access to memory location in postgis-2.1.dll as noted in this ticket http://trac.osgeo.org/postgis/ticket/2476, give your PostgreSQL service a restart. Haven't determined what is causing this, but have a suspicion it has something to do with permissions or the environment setting for GDAL not being picked up until service restart. If restarting doesn't resolve the issue, please put a note in that ticket what you were doing to trigger the error.

DZone Essential PostGIS

On a related topic. Our DZone Essential PostGIS refcard has been published which is mostly focused on using PostGIS 2.1. We are working on an updated Essential PostgreSQL refcard with planned release around the same time as our second edition of PostgreSQL Up and Running. Both the updated refcard and new edition of book will be focused on PostgreSQL 9.3 and 9.2 features as well as other important features from previous editions. Yes these do require a free registration to get the free cards, if you are not a DZone member. Save your free speeches. We heard them last time.