[postgis] Re: itasca load issues
Tyler Mitchell
tmitchell at lignum.com
Thu Feb 14 10:45:35 PST 2002
Hi John,
Thanks for testing this out.
>Also, a couple of queries about the data:
>- what was the source (mapserver demo)?
Source was the Mapserver 3.5 demo data
>- how did you load it (from demo shapefiles using shp2pgsql)?
Yes, I used shp2pgsql to create sql files, then ran them.
>- 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
Yup, same as the demo settings.
Anyone give me more suggestions on how to "properly" export/dump the data
to avoid the problems John has mentioned?
Tyler
John Reid
<jgreid at uow.ed To: Tyler Mitchell <tmitchell at lignum.com>
u.au> cc: postgis at yahoogroups.com
Fax to:
02/13/2002 Subject: itasca load issues
05:04 AM
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
Pinpoint the right security solution for your company - FREE
Guide from industry leader VeriSign gives you all the facts.
http://us.click.yahoo.com/pCuuSA/WdiDAA/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