[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