Table Of Contents

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.

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;

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;

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

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;

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;