[postgis] itasca load issues
John Reid
jgreid at uow.edu.au
Wed Feb 13 05:04:06 PST 2002
Hi,
I managed to load the data using the procedure outlined below. I have
posted the relevant preliminary files at
http://www.uow.edu.au/~jgreid/postgis/. Some difficulties encountered
are noted as well. These were using v0.6.2, don't know what
implications for cvs version are.
Also, a couple of queries about the data:
- what was the source (mapserver demo)?
- how did you load it (from demo shapefiles using shp2pgsql)?
- what is the coordinate system is the data in? I take it from the
demo.map file distributed with the mapserver demo that it is the following?
#PROJECTION
# "proj=utm"
# "ellps=GRS80"
# "zone=15"
# "north"
# "no_defs"
#END
cheers,
John
Procedure followed:
Preliminary:
1. removed type definitions from load file
2. replace references to root with pgisuser
3. modified the postgis_groups.sql file (including granting update
priviledge on geometry_tables for pgisusers)
Steps followed to load data:
$ su postgres
(or user with the usecreatedb privilege)
$ createdb itasca
$ psql -f /usr/lib/postgresql/share/contrib/postgis.sql -d itasca
(modify path to point to the local postgresql installation. The user
performing this operation requires usecatupd (and others?) privilege? I
can't find description of these privileges in the postgresql docs)
$ psql -ef postgis_groups.sql -d itasca
$ su pgisuser
$ psql -ef itasca.sql itasca
Couple of other matters:
Existing gives 2 classes of errors:
1. User does not have rights to update geometry_columns when loading
data from a dump file.
If I grok the purpose of this relation correctly, the most elegant
solution here would be tuple level access control - users should have
add (but not modify rights to existing tuples), with full control on an
individual tuple granted to the creator/owner. Also, if (when?) support
for SQL information schemas is provided by PostgreSQL this might not be
an issue. However I am not sure what the best solution is at the moment.
I have added the statement "GRANT INSERT ON geometry_columns TO GROUP
pgis_users;" to postgis_groups.sql. However this is not a good
solution, because if a table containing geometry is dropped this will
leave the database in an inaccurate state. On the gripping hand, this
still gives the following errors, which is *not* what I would expect
from the reference page for GRANT :-( Looks like UPDATE might be needed
as well... try that.... Yep. That works. What gives with INSERT
though????
INSERT INTO "geometry_columns" VALUES
('','itasca','ctybdpy2','the_geom',2,-1,'MULTIPOLYGON');
psql:itasca.jgr.sql:302: ERROR: geometry_columns: Permission denied.
INSERT INTO "geometry_columns" VALUES
('','itasca','twprgpy3','the_geom',2,-1,'MULTIPOLYGON');
psql:itasca.jgr.sql:303: ERROR: geometry_columns: Permission denied.
INSERT INTO "geometry_columns" VALUES
('','itasca','mcdrdln3','the_geom',2,-1,'MULTILINESTRING');
psql:itasca.jgr.sql:304: ERROR: geometry_columns: Permission denied.
INSERT INTO "geometry_columns" VALUES
('','itasca','lakespy2','the_geom',2,-1,'MULTIPOLYGON');
psql:itasca.jgr.sql:305: ERROR: geometry_columns: Permission denied.
INSERT INTO "geometry_columns" VALUES
('','itasca','stprkpy3','the_geom',2,-1,'MULTIPOLYGON');
psql:itasca.jgr.sql:306: ERROR: geometry_columns: Permission denied.
INSERT INTO "geometry_columns" VALUES
('','itasca','mcd90py2','the_geom',2,-1,'MULTIPOLYGON');
psql:itasca.jgr.sql:307: ERROR: geometry_columns: Permission denied.
INSERT INTO "geometry_columns" VALUES
('','itasca','majrdln3','the_geom',2,-1,'MULTILINESTRING');
psql:itasca.jgr.sql:308: ERROR: geometry_columns: Permission denied.
INSERT INTO "geometry_columns" VALUES
('','itasca','ctyrdln3','the_geom',2,-1,'MULTILINESTRING');
psql:itasca.jgr.sql:309: ERROR: geometry_columns: Permission denied.
INSERT INTO "geometry_columns" VALUES
('','itasca','twprdln3','the_geom',2,-1,'MULTILINESTRING');
psql:itasca.jgr.sql:310: ERROR: geometry_columns: Permission denied.
INSERT INTO "geometry_columns" VALUES
('','itasca','dlgstln2','the_geom',2,-1,'MULTILINESTRING');
psql:itasca.jgr.sql:311: ERROR: geometry_columns: Permission denied.
INSERT INTO "geometry_columns" VALUES
('','itasca','plsscpy3','the_geom',2,-1,'MULTIPOLYGON');
psql:itasca.jgr.sql:312: ERROR: geometry_columns: Permission denied.
INSERT INTO "geometry_columns" VALUES
('','itasca','airports','the_geom',2,-1,'MULTIPOINT');
psql:itasca.jgr.sql:313: ERROR: geometry_columns: Permission denied.
2. Problem with the dataset? (due to "open" polygon or overshoot?)
INSERT INTO "lakespy2" VALUES
(676,4204838.29762,22453.89536,421,'31065300',1,'NORTHSTAR',25,1358,1039.016,73671.231,'SRID=-1;MULTIPOLYGON(((451130.744395544
5265876.06652514,
<snip>
,449376.413561779 5265970.38791527)))');
psql:itasca.jgr.sql:1963: ERROR: polygon has ring where first point !=
last point
INSERT INTO "ctybdpy2" VALUES
(52,1518752180.39514,179570.98876,'Traverse',78,'TRAV','N',155,53,'SRID=-1;MULTIPOLYGON(((247193.116832133
5101650.47120163,247192.24185142 5101634.50237652,
<snip>
,245591.122091334 5101713.47320567,247193.116832133 5101650.47120163)))');
psql:itasca.jgr.sql:467: ERROR: polygon has ring where first point !=
last point
--
----------------------------------------------------------------------
john reid e-mail jgreid at uow.edu.au
uproot your questions from their ground and the dangling roots will be
seen. more questions!
-mentat zensufi
apply standard disclaimers as desired...
----------------------------------------------------------------------
------------------------ Yahoo! Groups Sponsor ---------------------~-->
Sponsored by VeriSign - The Value of Trust
Do you need to encrypt all your online transactions? Find
the perfect solution in this FREE Guide from VeriSign.
http://us.click.yahoo.com/jWSNbC/UdiDAA/yigFAA/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/
More information about the postgis-users
mailing list