[postgis-users] Re: Error messages in PostGIS (change SRID and change projections)
Rosangela Silva
rosangela.silva at zenitpolar.com.br
Fri May 30 12:56:22 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??
------
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 of a table?
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
>
More information about the postgis-users
mailing list