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