forked from postgis/postgis
183 lines
7.2 KiB
Plaintext
183 lines
7.2 KiB
Plaintext
PostGIS 2.0 Migration Guide
|
|
===========================
|
|
|
|
PostGIS 2.0 introduces changes that may affect backwards compatibility
|
|
for some applications. This document lists those changes.
|
|
|
|
|
|
Deprecated Functions Removed
|
|
----------------------------
|
|
|
|
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.
|
|
|
|
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.
|
|
|
|
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.
|
|
|
|
|
|
Unknown SRID
|
|
------------
|
|
|
|
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.
|
|
|
|
|
|
ST_AsBinary, ST_AsText and 3D
|
|
-----------------------------
|
|
|
|
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)'.
|
|
|
|
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.
|
|
|
|
|
|
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
|
|
their create table syntax to include type/srid/dimensionality
|
|
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.
|
|
|
|
* If you have views built on tables that were built
|
|
the old constraint way or that use processing
|
|
functions to return new geometries, they will not register correctly
|
|
in geometry_columns. They will all appear as Geometry with srid=0.
|
|
|
|
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
|
|
makes geometries be treated as a different type as far as
|
|
CREATE OR REPLACE is concerned.
|
|
|
|
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,
|
|
then you would only need to CAST when doing processing.
|
|
This however still requires you rebuild your dependent views
|
|
DROP VIEW vwparcels;
|
|
CREATE OR REPLACE VIEW vwparcels AS
|
|
SELECT gid, parcel_id,
|
|
ST_Transform(geom, 26986)::geometry(MultiPolygon,26986)
|
|
As geom_26986,
|
|
geom::geometry(MultiPolygon, 2249) As geom,
|
|
ST_Centroid(geom)::geometry(Point,2249) As cent_geom
|
|
FROM parcels;
|
|
|
|
|
|
Restoring data from prior versions -- GOTCHAS
|
|
-----------------------------------------------
|
|
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
|
|
|
|
1) Drop these contraints before you restore the data
|
|
|
|
or
|
|
|
|
2) Install the legacy functions srid() and ndims() found in the legacy.sql
|
|
file before you try to restore these tables.
|
|
|
|
Function is not unique after restore
|
|
-------------------------------------
|
|
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.
|
|
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.
|
|
|
|
If you still require legacy functions, you can install the legacy.sql
|
|
file after the uninstall_legacy.sql.
|
|
|
|
GOTCHA with uninstall_legacy.sql
|
|
--------------------------------
|
|
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
|
|
and then rerun the script again.
|
|
|
|
BENCHMARK NOTES
|
|
------------------------------
|
|
Querying a whole geometry_columns table of 200-240 someodd records
|
|
(tables/views all constraint based)( On windows 9.1beta2)
|
|
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';
|
|
|
|
-- CONVERTING TO Typmod as I feared is slow for tables with data.
|
|
I think it follows the same model
|
|
-- 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)
|
|
-- This took 36,018 ms for table of 350,572 records.
|
|
I assume it will be linear with number of records.
|
|
|
|
ALTER TABLE buildings_1995
|
|
ALTER COLUMN the_geom TYPE geometry(MultiPolygon,2249);
|
|
|