[postgis] preview of 0.6 install and 0.5->0.6 upgrade instructions
Dave Blasby
dblasby at refractions.net
Fri Aug 10 12:47:08 PDT 2001
I just finished the Proof-of-Concept draft of two FAQs:
1. Installing and using version 0.6 (the CVS version)
2. Upgrading an existing 0.5 database to version 0.6 (the CVS version)
I'm releasing them early because I noticed that quite a few people have
been downloading the CVS version and I wanted to make sure they knew
what to expect. Paul wants to major changes to them...
Find attached the RTF versions of these documents - you'll find them
much easier to read than the non-formated version below.
dave
=====================================================================================
Q. I've installed PostGIS 0.6, now I want to use it, what do I do?
A: Here's an annotated trascript;
1. First we create a database to work in
ox% psql template1
template1=# create database test_geom;
CREATE DATABASE
template1=# \q
2. Next, we have to make the database spatially aware by telling it
about all the PostGIS functions, etc... The SQL
script 'postgis.sql' will do this for you. PostGIS requires that the
plpgsql language is installed - if its not,
you'll get errors and instructions on how to install it.
In this example, plpgsql is not installed so I'll get the error
message.
ox% psql test_geom
test_geom=# \i postgis.sql
BEGIN
message
------------------------------------------------------------------
you must install plpgsql before running this sql file, or you will get
an error.
To install plpgsql:
1. Install the handler.
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
You might have to change the location of the .so file. Common places
are:
/usr/local/lib
/usr/local/pgsql/lib
/usr/lib/pgsql
Or where your postgresql lib directory is
2. Install the language
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
(1 row)
<LOTS of error messages complaining that the current transaction is in
the abort state>
So, follow the instructions. On my machine the plpgsql.so is in
/data3/postgresql-7.1.2/lib directory. I issue
these two commands to make the plpgsql language available.
test_geom=# CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
test_geom-# '/data3/postgresql-7.1.2/lib/plpgsql.so' LANGUAGE 'C';
CREATE
test_geom=# CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
test_geom-# HANDLER plpgsql_call_handler
test_geom-# LANCOMPILER 'PL/pgSQL';
CREATE
test_geom=#
Next, I run the 'postgis.sql' script again. This time it will not
complain about the plpgsql language not being
installed.
test_geom=# \i postgis.sql
BEGIN
message
----------------------
plpgsql is installed
(1 row)
<Lots of CREATE (and a few NOTICE) messages will be displayed>
3. Excellent, our database is now spatially aware! Two tables are
created, one containing information about the
geometry columns in the database, and one about the available spatial
referencing systems. They are both
unpopulated.
test_geom=# \d
List of relations
Name | Type | Owner
------------------+-------+----------
geometry_columns | table | postgres
spatial_ref_sys | table | postgres
(2 rows)
test_geom=# \d geometry_columns
Table "geometry_columns"
Attribute | Type | Modifier
-------------------+------------------------+----------
f_table_catalog | character varying(256) | not null
f_table_schema | character varying(256) | not null
f_table_name | character varying(256) | not null
f_geometry_column | character varying(256) | not null
coord_dimension | integer | not null
srid | integer | not null
type | character varying(30) | not null
Index: gc_pk
test_geom=# \d spatial_ref_sys
Table "spatial_ref_sys"
Attribute | Type | Modifier
-----------+-------------------------+----------
srid | integer | not null
auth_name | character varying(256) |
auth_srid | integer |
srtext | character varying(2048) |
Index: spatial_ref_sys_pkey
test_geom=#
4. Lets add a Spatial Referencing system for our data. For more
information, see section 3.4 (and 3.1.1.2) of the
OpenGIS Simple Feature Specifiction (http://www.opengis.org).
SRID-an integer value that uniquely identifies each Spatial Reference
System within a database. (1 in our example)
AUTH_NAME-the name of the standard or standards body that is being cited
for this reference system. (NULL in our
example)
AUTH_SRID-the ID of the Spatial Reference System as defined by the
Authority cited in AUTH_NAME. (NULL in our
example)
SRTEXT-The Well-known Text representation of the Spatial Reference
System. (UTM Zone 10N (NAD 1983) in our example)
test_geom=# INSERT INTO spatial_ref_sys VALUES (1,NULL,NULL,
test_geom=# 'PROJCS[''NAD_1983_UTM_Zone_10N'',
test_geom'# GEOGCS[''GCS_North_American_1983'',
test_geom'# DATUM[''D_North_American_1983'',
test_geom'# SPHEROID[''GRS_1980'',6378137,298.257222101]],
test_geom'# PRIMEM[''Greenwich'',0],
test_geom'# UNIT[''Degree'',0.0174532925199433]],
test_geom'# PROJECTION[''Transverse_Mercator''],
test_geom'# PARAMETER[''False_Easting'',500000.0],
test_geom'# PARAMETER[''False_Northing'',0.0],
test_geom'# PARAMETER[''Central_Meridian'',-123.0],
test_geom'# PARAMETER[''Scale_Factor'',0.9996],
test_geom'# PARAMETER[''Latitude_of_Origin'',0.0],
test_geom'# UNIT[''Meter'',1.0]]');
5. Now lets create a table for our data. We dont define the geometry
column.
test_geom=# create table geometry_test (gid integer,
test_geom(# info varchar(100) );
CREATE
test_geom=#
6. Now, we add a geometry column to the table. We use the
AddGeometryColumn(<db name>,<table name>,<column name>,
<srid>, <type>,<dim>) function. In this case "test_geom" is our
database name and "geometry_test" is our table
name. We want our geometry column to be called "mygeom", be of type
"POINT", be in UTM Zone 10N (NAD 1983), and
have at most 3 dimensions.
Notice that our table, "geometry_test", now has a geometry column and
two constraints. These constraints ensure
that only objects or type POINT with an SRID of 1 can be added into the
table.
test_geom=# select
AddGeometryColumn('test_geom','geometry_test','mygeom',1,'POINT',3);
addgeometrycolumn
-------------------------------------------------------------------------------------
Geometry column mygeom added to table geometry_test with a SRID of 1
and type POINT
(1 row)
test_geom=# \d geometry_test
Table "geometry_test"
Attribute | Type | Modifier
-----------+------------------------+----------
gid | integer |
info | character varying(100) |
mygeom | geometry |
Constraints: (srid(mygeom) = 1)
(geometrytype(mygeom) = 'POINT'::text)
7. Lets insert some data into the table.
test_geom=# insert into geometry_test values (1,'test point 1',
GeometryFromText('POINT(0 0)',1) );
INSERT 5044982 1
test_geom=# insert into geometry_test values (2,'test point 2',
GeometryFromText('POINT(0 10)',1) );
INSERT 5044983 1
test_geom=# insert into geometry_test values (3,'test point 3',
GeometryFromText('POINT(10 10)',1) );
INSERT 5044984 1
test_geom=# insert into geometry_test values (4,'test point 4',
GeometryFromText('POINT(10 0)',1) );
INSERT 5044985 1
8. Lets build a spatial index for this table. Note that you will have
to issue the "set enable_seqscan = off" to
actually tell postgresql to use the index.
test_geom=# create index geom_test_g_idx on geometry_test using gist
(mygeom gist_geometry_ops);
CREATE
test_geom=# \d geometry_test
Table "geometry_test"
Attribute | Type | Modifier
-----------+------------------------+----------
gid | integer |
info | character varying(100) |
mygeom | geometry |
Index: geom_test_g_idx
Constraints: (srid(mygeom) = 1)
(geometrytype(mygeom) = 'POINT'::text)
9. Lets use our spatial index to do a search. Note that your search box
has to also have a Spatial Reference
System. I use the set enable_seqscan = off command to force postgresql
to use the GiST index we created.
test_geom=# set enable_seqscan = off;
SET VARIABLE
test_geom=# select gid,info,astext(mygeom) from geometry_test where
mygeom && setSRID('BOX3D(-5 5,15
15)'::BOX3D,1);
gid | info | astext
-----+--------------+--------------
1 | test point 2 | POINT(0 10)
3 | test point 3 | POINT(10 10)
(2 rows)
test_geom=# explain select gid,info,astext(mygeom) from geometry_test
where mygeom && setSRID('BOX3D(-5 5,15
15)'::BOX3D,1);
NOTICE: QUERY PLAN:
Index Scan using geom_test_g_idx on geometry_test (cost=0.00..2.01
rows=1 width=28)
EXPLAIN
test_geom=# select gid,info,x(mygeom),y(mygeom) from geometry_test where
mygeom && setSRID('BOX3D(-5 5,15
15)'::BOX3D,1);
gid | info | x | y
-----+--------------+----+----
1 | test point 2 | 0 | 10
3 | test point 3 | 10 | 10
(2 rows)
=====================================================================================
Q. How do I upgrade my 0.5 database to the new 0.6 database?
A. The method has several steps, but each one is pretty basic.
Basically, (a) you pg_dump your 0.5 tables (b) create a new 0.6 database
(c) make a temporary copy of your old tables (d) properly re-create the
tables (e) transfer the data from the old tables to the new tables.
I'll take you step-by-step through transferring a table called
'ctivalue'.
(a) Back-up your old tables using the pg_dump program. For more details
on the pg_dump program, execute a 'pg_dump -help'. In this example, I'm
dumping the table 'ctivalue' from the database 'mapserv' which is on
port 5555 into the file "temporary".
% pg_dump -p 5555 -t ctivalue mapserv > temporary
(b) Create a new 0.6 database. See the 0.6 install documentation (don't
forget to turn plpgsql support on). I created a new database called
'mapserv2'
(c) Upload the table you dumped in step (a). You'll now have the
ctivalue table in the new database. We rename it to 'ctivalue2'.
%psql -p 5555 -U postgres mapserv2 < temporary
%psql -p 5555 -U postgres mapserv2
mapserv2=#alter table ctivalue rename to ctivalue2;
(d) We have to make a 0.6 compliant table.
(i) To do this, create the 'ctivalue' table without the geometry column.
Mapserv2=# CREATE TABLE "ctivalue" (
Mapserv2(# "geoid" integer,
Mapserv2(# "mslink" integer,
Mapserv2(# "hwy_rte" character varying,
Mapserv2(# "rd_net_cls" integer,
Mapserv2(# "ric_type" integer
Mapserv2(# );
(ii) Then add the geometry column using the AddGeometryColumn()
function. We set the SRID to 123, type 'GEOMETRY', and 3 dimensions.
At this point, we have the old table (ctivalue2) and an equivalent new
table (ctivalue).
Mapserv2=# select
AddGeometryColumn('mapserv2','ctivalue','geo_value',123,'GEOMETRY',3);
(e) Transfer the information from the old table to the new table,
tagging the old geometry values with the new SRID (123). Also, create
the spatial GiST index and delete the old table.
mapserv2=# insert into ctivalue select
geoid,mslink,hwy_rte,rd_net_cls,ric_type,setSRID(geo_value,123) from
ctivalue2;
mapserv3=# drop table ctivalue2;
mapserv3=# CREATE INDEX "quicky" on "ctivalue" using gist ( "geo_value"
"gist_geometry_ops" );
------------------------ Yahoo! Groups Sponsor ---------------------~-->
Small business owners...
Tell us what you think!
http://us.click.yahoo.com/vO1FAB/txzCAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->
To unsubscribe from this group, send an email to:
postgis-unsubscribe at yahoogroups.com
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: postgis_install06.rtf
Type: application/msword
Size: 11594 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20010810/d93f9f67/attachment.doc>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: postgis_upgrade.rtf
Type: application/msword
Size: 7822 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20010810/d93f9f67/attachment-0001.doc>
More information about the postgis-users
mailing list