MIGRATION 7.18 KB
Newer Older
1 2 3
PostGIS 2.0 Migration Guide
===========================

Paul Ramsey's avatar
Paul Ramsey committed
4
PostGIS 2.0 introduces changes that may affect backwards compatibility
5 6 7
for some applications. This document lists those changes.


Paul Ramsey's avatar
Paul Ramsey committed
8 9 10
Deprecated Functions Removed
----------------------------

Sandro Santilli's avatar
Sandro Santilli committed
11 12 13 14
Since PostGIS 1.2, we have been encouraging users to use functions with
the ST_ prefix. The ST_ prefixed functions are part of the ISO SQL/MM
standard and used by other spatial SQL databases, so we want to conform
as closely to the industry usage as we can.
Paul Ramsey's avatar
Paul Ramsey committed
15

Sandro Santilli's avatar
Sandro Santilli committed
16 17 18
At 2.0, we have finally removed the old functions. If you have an
application that *still* requires the old function signatures, please
look to the legacy_*.sql files to add the old signatures back in.
Paul Ramsey's avatar
Paul Ramsey committed
19

Sandro Santilli's avatar
Sandro Santilli committed
20 21 22
If you are using MapServer, upgrade to the latest versions of the 6.0
or 6.2 releases. For earlier releases, you will have to install the
legacy.sql files.
Paul Ramsey's avatar
Paul Ramsey committed
23 24 25 26 27


Unknown SRID
------------

Sandro Santilli's avatar
Sandro Santilli committed
28 29 30
The "unknown SRID", assigned to geometries when SRID is not specified,
is now 0, not -1.  ST_SRID will now return 0 when called on a geometry
with no known SRID.
Paul Ramsey's avatar
Paul Ramsey committed
31 32 33 34 35


ST_AsBinary, ST_AsText and 3D
-----------------------------

Sandro Santilli's avatar
Sandro Santilli committed
36 37 38 39 40
Previous versions of PostGIS returned 2D versions of objects when
ST_AsBinary and ST_AsText were called. PostGIS 2.0 returns ISO SQL/MM
versions when 3D or 4D objects are used.  For example, a 3D point with a
Z dimension will return as 'POINT Z (0 0 0)'. A 4D point will return as
'POINT ZM (0 0 0 0)'.
Paul Ramsey's avatar
Paul Ramsey committed
41

Sandro Santilli's avatar
Sandro Santilli committed
42 43 44 45 46
For WKB, extra dimensionality is indicated in the type numbers. The Z,
M, and ZM type numbers are indicated by adding 1000, 2000, and 3000
respectively to the usual 2D type numbers. So, for example, a 2D Point
has a type number of 1. A 4D PointZM has a type number of 3001.  A 2D
polygon has a type number of 3. A PolygonZ has a type number of 2003.
Paul Ramsey's avatar
Paul Ramsey committed
47 48


49 50 51 52 53 54 55 56 57 58 59 60
geometry_columns
----------------

In PostGIS 2.0, the ``geometry_columns`` metadata has been changed from
a table to a view. 

  * For applications that manage ``geometry_columns``
    using the standard AddGeometryColumn() and other methods, there should
    be no change. 

  * Applications that have inserted directly into ``geometry_columns`` 
    will need to either move to the standard functions, or change
Paul Ramsey's avatar
Paul Ramsey committed
61
    their create table syntax to include type/srid/dimensionality
62 63 64 65 66 67 68 69 70 71 72 73
    information. 

    For example, to specify a 3D feature in WGS84::

      CREATE TABLE mytable (
        id SERIAL PRIMARY KEY,
        g Geometry(PointZ,4326)
      );

  * Applications reading metadata from ``geometry_columns`` should expect
    that the geometry type strings will be mixed case and include the
    dimensionality modifiers. For example: PointZM, MultiLineStringZ.
74
    
75 76
  * If you have views built on tables that were built
    the old constraint way or that use processing
77 78 79
    functions to return new geometries, they will not register correctly 
    in geometry_columns.  They will all appear as Geometry with srid=0.
    
80 81 82 83 84 85 86 87 88 89 90
  	In order to fix this, you have to cast the geometries in the view.
  	So for example, If you had a view that looked like this:
  	CREATE OR REPLACE VIEW vwparcels AS
  		SELECT gid, parcel_id, 
  			ST_Transform(geom, 26986) As geom_26986, -- a transformed geometry
  			geom, --one that used constraints before
  			ST_Centroid(geom) As cent_geom -- one that used processing function
  		FROM parcels;
  		
  	You will need to drop and recreate the view. 
  	Sadly CREATE OR REPLACE will not work since the casting
Sandro Santilli's avatar
Sandro Santilli committed
91 92
	  makes geometries be treated as a different type as far as
	  CREATE OR REPLACE is concerned.
93 94 95 96
  	
  	NOTE for this, it might help to lookup in geometry_columns 
  	    to see the raw table srid/type
  	Alternatively you can convert some of your table columns to typmod, 
Sandro Santilli's avatar
Sandro Santilli committed
97 98
  	then you would only need to CAST when doing processing. 
  	This however still requires you rebuild your dependent views
99 100 101
  	DROP VIEW vwparcels;
  	CREATE OR REPLACE VIEW vwparcels AS
  		SELECT gid, parcel_id, 
102 103
  		   ST_Transform(geom, 26986)::geometry(MultiPolygon,26986) 
  		      As geom_26986,
104 105 106
  			geom::geometry(MultiPolygon, 2249) As geom,
  			ST_Centroid(geom)::geometry(Point,2249) As cent_geom
  		FROM parcels;
107

108 109 110
    
Restoring data from prior versions -- GOTCHAS
-----------------------------------------------
111 112 113 114
The populate_geometry_columns() function in PostGIS 1.5 and below, 
used the deprecated functions ndims() and srid() for defining constraints.  
As a result these tables will not
restore into a fresh PostGIS 2.0 database unless you
115

116
1) Drop these contraints before you restore the data
117

118 119
or 

Sandro Santilli's avatar
Sandro Santilli committed
120 121
2) Install the legacy functions srid() and ndims() found in the legacy.sql
  file before you try to restore these tables.
122 123 124

Function is not unique after restore
-------------------------------------
125 126 127 128
After you restore old data into a fresh PostGIS 2.0 database, 
YOU MUST install the uninstall_legacy.sql
file after the restore.  If you don't you will run into 
"function is not unique" errors in your applications.
Sandro Santilli's avatar
Sandro Santilli committed
129 130 131
The other issue with having the old functions is the old functions you
restore will be bound to the old postgis library which is incompatible
with the new postgis geometry structures.
132

Sandro Santilli's avatar
Sandro Santilli committed
133 134
If you still require legacy functions, you can install the legacy.sql
file after the uninstall_legacy.sql.
135 136 137

GOTCHA with uninstall_legacy.sql
--------------------------------
138 139 140 141 142 143 144 145
The uninstall_legacy.sql will not be able to uninstall functions and will 
output errors detailing objects using these functions that are currently 
being used in views and sql functions.

If you run it in PgAdmin, the whole script will rollback not 
uninstalling anything. So you should run it in PSQL mode.
If you get errors in uninstall_legacy.sql -- convert those views and sql
functions to use the newer equivalent functions 
Regina Obe's avatar
Regina Obe committed
146 147 148 149
and then rerun the script again.

BENCHMARK NOTES
------------------------------
150 151
Querying a whole geometry_columns table of 200-240 someodd records 
(tables/views all constraint based)( On windows 9.1beta2)
Regina Obe's avatar
Regina Obe committed
152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171
Speed for this set of data I'm okay with though a bit slower
I think the main issue why fancy is slower is that to get the fancy name
I need to do a double call to get dims since fancy_name needs dims as well
This I expect to be much faster using typ_mod
SELECT * FROM geometry_columns_v; -- 2063 ms, 1063 ms (using fancy name)
SELECT * FROM geometry_columns_v2; -- 656 ms (this is not using fancy name) 

SELECT * FROM geometry_columns; -- original static table 31ms - 150 ms

-- Doing single selects of a table
-- constraint based one -- 76ms, 46ms
SELECT * FROM geometry_columns_v where f_table_name = 'streets';

-- One I converted to typmod -- 76 ms, 48 ms (first call was slower)
SELECT * FROM geometry_columns_v where f_table_name = 'buildings_1995';

--Filter query for schema --
-- these are constraint based (7 rows) -- 76 ms, 240 ms, 68 ms
SELECT * FROM geometry_columns_v where f_table_schema = 'tiger';

172 173
-- CONVERTING TO Typmod as I feared is slow for tables with data.  
   I think it follows the same model 
Regina Obe's avatar
Regina Obe committed
174 175 176
-- as converting varchar(100) to text or varchar(100) to varchar(300) etc.
-- This maybe can be remedied somehow in 9.1 since it hasn't been released
-- (haven't tried doing this exercise in 8.4, 9.0)
177 178 179
-- This took  36,018 ms for table of 350,572 records.  
I assume it will be linear with number of records.

Regina Obe's avatar
Regina Obe committed
180 181
ALTER TABLE buildings_1995 
    ALTER COLUMN the_geom TYPE geometry(MultiPolygon,2249);
Regina Obe's avatar
Regina Obe committed
182