[postgis-users] Re: Error messages in PostGIS (change SRID and change projections)
Rosangela Silva
rosangela.silva at zenitpolar.com.br
Fri May 30 12:50:25 PDT 2003
Hi all,
Sorry for only now I'm answering about my test to change SRID parameter.
I used the following commands, like you recommended and
it works well, thanks Chris, Tyler and strk (?) for helps!!
Only a question is how can I see the SRID for one element, to check it?? (Not
for table but for one element of the spatial table??)
------
ALTER TABLE mytable DROP CONSTRAINT "$1" RESTRICT;
UPDATE mytable SET the_geom = SETSRID (the_geom, 4326);
ALTER TABLE mytable ADD CONSTRAINT "$1" CHECK (SRID(the_geom)=4326);
-----
By the way, do you know how to change the projections, but change the values of the
coordenates too, not only the SRID values??
For example convert coords of Datum SAD69 (SRID=?) to Datum WGS84 (SRID=4326) ??
Thanks in advance,
Rosângela.
>Tyler, the problem with your suggestion is that there is a constraint on any
>table created using "AddGeometryColumn()" which requires all the >geometries in
>the column to have the srid specified in the the call to AddGeometryColumn. >You
>will probably need to drop this constraint using the DROP CONSTRAINT sql
>command, then do the update (as explained by Tyler), then add the constraint
>back, and then update the geometry_columns table to have the correct srid in >it
>for that column.
>
>An alternative would be to add another geometry column to the same table, >with
>the correct srid, and then set the value of the new geometry column equal to
>the old geometry column with the srid set, for example:
>UPDATE foo SET new_geom = setSRID( old_geom, 4326 );
>Then delete the old geometry column using "DropGeometryColumn()".
>HTH.
>Chris
>Quoting Tyler Mitchell <TMitchell at lignum.com>:
>
> Use the function:
> SetSRID(geometry) to update the geometry in your table, i.e.
> UPDATE mytable
> SET the_geom = SetSRID(the_geom)=4326
>
> I've never done it, anyone see problems with it?
>
> Tyler
>
>
>
> strk <strk at freek.keybit.net>
> Sent by: To:
> PostGIS Users Discussion
> postgis-users-bounces at postgis.refr
> <postgis-users at postgis.refractions.net>
> actions.net cc:
> Fax to:
> Subject: Re:
> [postgis-users] Re: Error messages in
> 05/23/2003 02:14 AM PostGIS
> Please respond to PostGIS Users
> Discussion
>
>
>
>
>
>
> rosangela.silva wrote:
> > Hi,
> >
> > Thanks for information. Is everything ok now.
> >
> > But, if I've already created a spatial table with SRID = -1
> > how can I convert this table (and all its elements) for SRID=4326?
> > Is it possible??
>
> Ok, this sounds like something that needs a better user interface to
> be done cleanly... AFAIK you have to hack it.
>
> I'd try to update the geometry_column first (setting the SRID to the value
> 4326) and then update the your spatial table using the setSRID() function
> on the geometry column.
>
> If the constraints dinamically check SRID from geometry_columns
> table that should work.
>
> --strk;
>
>
>
> >
> > Thanks
> > Rosângela
> > rosangela.silva wrote:
> > > Hello all,
> > >
> > > I'm having two problems with PostGIS that I don't know
> > > how to solve them. I don't have much experience with database
> > > management. Could you help me, please?
> > >
> > > I DROP a table continent, but I don't DROP the spatial
> > > index before, and when I try to execute the sql again
> > > I receive the message below:
> > >
> > > $ psql -d geobusca -f continent.sql
> > > ERROR: Relation 'continent' already exists
> > > psql:continent.sql:1: ERROR: Relation 'continent' already exists
> > > ERROR: Cannot insert a duplicate key into unique index
> geometry_columns_pk
> > > WARNING: Error occurred while executing PL/pgSQL function
> addgeometrycolumn
> > > WARNING: line 39 at execute statement
> > > psql:continent.sql:2: WARNING: line 39 at execute statementng
> PL/pgSQL function
> > > psql:continent.sql:2: ERROR: Cannot insert a duplicate key into
> unique index ge
> > > ometry_columns_pk
> >
> > You need to delete from geometry_columns where f_table_name
> = 'continent'.
> > This is done automatically if you use dropGeometryColumn() befor
> dropping
> > the table.
> >
> > >
> > > In other case, I delete a record from a spatial table and when I try
> to
> > > insert the same record again (with some changes) I received the
> message
> > > below:
> > >
> > > gisdb=# INSERT INTO wpt (wpt_waypointID, wpt_Name, the_geom) VALUES
> (9, 'TUN
> > > L25', GeometryFromText('POINT(-46.63758 -23.97345)', 4326));
> > > ERROR: ExecInsert: rejected due to CHECK constraint "$1" on "wpt"
> > > ERROR: ExecInsert: rejected due to CHECK constraint "$1" on "wpt"
> > > gisdb=#
> >
> > The CHECK constraint "$1" tries to force geometry SRID as specified in
> > the geometry_columns. You have removed the information related to
> > the specified table, thus the check is rejected.
> >
> > >
> > > I'm using LATLONG/WGS84 and the wpt table is:
> > > Table "public.wpt"
> > > Column | Type | Modifiers
> > > --------------------+---------------------+-----------
> > > wpt_waypointid | bigint |
> > > wpt_name | character varying |
> > > the_geom | geometry |
> > > Check constraints: "$1" (srid(the_geom) = -1)
> > > "$2" ((geometrytype(the_geom) = 'POINT'::text) OR
> (the_geom IS NULL))
> > >
> > > But I used the sintaxe bellow to create the table:
> > > CREATE TABLE wpt (wpt_waypointID bigint, wpt_Name varchar);
> > > SELECT AddGeometryColumn('geobusca','wpt','the_geom',
> 4326,'POINT',2);
> > >
> > > What can I have to do?
> >
> > The creation sql seems good, but you have to use DropGeometryColumn()
> > on the table before dropping the table itself.
> >
> > If the problems also occurs immediatly after table creation (you never
> > tried to drop anything) try omitting the first parameter from
> > AddGeometryColumn.
> >
> > --strk;
> >
> >
> >
> > ----- Original Message -----
> > From: Rosangela Silva
> > To: postgis-users at postgis.refractions.net
> > Sent: Thursday, May 22, 2003 12:02 PM
> > Subject: Error messages in PostGIS
> >
> >
> > Hello all,
> >
> > I'm having two problems with PostGIS that I don't know
> > how to solve them. I don't have much experience with database
> > management. Could you help me, please?
> >
> > I DROP a table continent, but I don't DROP the spatial
> > index before, and when I try to execute the sql again
> > I receive the message below:
> >
> > $ psql -d geobusca -f continent.sql
> > ERROR: Relation 'continent' already exists
> > psql:continent.sql:1: ERROR: Relation 'continent' already exists
> > ERROR: Cannot insert a duplicate key into unique index
> geometry_columns_pk
> > WARNING: Error occurred while executing PL/pgSQL function
> addgeometrycolumn
> > WARNING: line 39 at execute statement
> > psql:continent.sql:2: WARNING: line 39 at execute statementng PL/pgSQL
> function
> > psql:continent.sql:2: ERROR: Cannot insert a duplicate key into unique
> index ge
> > ometry_columns_pk
> >
> >
> > In other case, I delete a record from a spatial table and when I try to
> > insert the same record again (with some changes) I received the message
> > below:
> >
> > gisdb=# INSERT INTO wpt (wpt_waypointID, wpt_Name, the_geom) VALUES (9,
> 'TUN
> > L25', GeometryFromText('POINT(-46.63758 -23.97345)', 4326));
> > ERROR: ExecInsert: rejected due to CHECK constraint "$1" on "wpt"
> > ERROR: ExecInsert: rejected due to CHECK constraint "$1" on "wpt"
> > gisdb=#
> >
> > I'm using LATLONG/WGS84 and the wpt table is:
> > Table "public.wpt"
> > Column | Type | Modifiers
> > --------------------+---------------------+-----------
> > wpt_waypointid | bigint |
> > wpt_name | character varying |
> > the_geom | geometry |
> > Check constraints: "$1" (srid(the_geom) = -1)
> > "$2" ((geometrytype(the_geom) = 'POINT'::text) OR
> (the_geom IS NULL))
> >
> > But I used the sintaxe bellow to create the table:
> > CREATE TABLE wpt (wpt_waypointID bigint, wpt_Name varchar);
> > SELECT AddGeometryColumn('geobusca','wpt','the_geom', 4326,'POINT',2);
> >
> > What can I have to do?
> > Thanks
> >
> > Rosângela.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20030530/ca98afe9/attachment.html>
More information about the postgis-users
mailing list