[postgis-users] Re: Error messages in PostGIS (change SRID and change projections)
chodgson at refractions.net
chodgson at refractions.net
Mon Jun 2 10:07:29 PDT 2003
>From the postgis manual:
transform(geometry,integer)
Returns a new geometry with its coordinates transformed to the SRID referenced
by the integer parameter. The source and destination SRIDs must exist in the
SPATIAL_REF_SYS table.
Create a new geometry table, and fill it with stuff selected from your original
table and transformed to the new SRID - the trickiest part is determining the
SRIDs - and making sure they are in the spatial_ref_sys table. Look at
spatial_ref_sys.sql provided in the postgis distribution.
Chris
Quoting Rosangela Silva <rosangela.silva at zenitpolar.com.br>:
> 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
> >
>
>
> _______________________________________________
> 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