<HTML dir=ltr><HEAD><TITLE>RE: [postgis-users] couldn't get the correct projected values</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.2900.3314" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText47318 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Bob,</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>Actually there is a function that does what I think you are talking about and that would be the right way to implement what Andy is suggesting (rather than as you stated ST_SetSRID)</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>Its called UpdateGeometrySRID. In this case, you would use it like so</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>SELECT UpdateGeometrySRID('public', '<FONT face="Times New Roman">il_izmir3</FONT>', 'the_geom', 4326);</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>What the above does in 1 step is the following:</FONT></DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>
<OL>
<LI>
<P>Drop geometry SRID constraint of table public.il_izmir3 for geometry field called the_geom.</P>
<LI>
<P>Update all records SRIDs of the_geom field in this table to 4326</P>
<LI>
<P>Readd the constraint </P>
<LI>
<P>Correct the geometry_columns meta table with the new SRID</P></LI></OL></DIV>
<P>Hope that clears things up,</P>
<P>Regina</P>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> postgis-users-bounces@postgis.refractions.net on behalf of Burgholzer,Robert<BR><B>Sent:</B> Fri 5/16/2008 2:27 PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> RE: [postgis-users] couldn't get the correct projected values<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>I see what you're saying, but if you used shp2pgsql to import your shapes, and didn't specify a geometry, it sets the SRID = -1, and this is how it will appear in the Rules entry for your table, AND therefore, it should throw an error if you set the SRID -- or maybe that trigger only fires on an insert not an update.<BR><BR>Either way, I don't know what will happen if you do not update the entry in the geometry_columns table, which holds your SRID, maybe nothing, or maybe there are functions inside PostGIS that look at the SRID entry in the geom. Table. If there are functions that look to the table for the SRID, that could be problematic.<BR><BR>Thus, still best practice to keep all things in SRID agreement, that is: geometry_column entry, SRID on geometries, and in Rules. It will save you headaches down the road. And setSRID only changes one of those, the one on the geometries themselves.<BR><BR>Robert W. Burgholzer<BR>Surface Water Modeler<BR>Office of Water Supply and Planning<BR>Virginia Department of Environmental Quality<BR>rwburgholzer@deq.virginia.gov<BR>804-698-4405<BR>Open Source Modeling Tools:<BR><A href="http://sourceforge.net/projects/npsource/">http://sourceforge.net/projects/npsource/</A><BR><BR>-----Original Message-----<BR>From: postgis-users-bounces@postgis.refractions.net [<A href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of Andy Anderson<BR>Sent: Friday, May 16, 2008 2:15 PM<BR>To: PostGIS Users Discussion<BR>Subject: Re: [postgis-users] couldn't get the correct projected values<BR><BR>But Regina's thought was that this *was* the original geometry, just <BR>not documented as such by setting the SRID. That was the basis for my <BR>comment.<BR><BR>-- Andy<BR><BR>On May 16, 2008, at 1:46 PM, Burgholzer,Robert wrote:<BR><BR>> Andy,<BR>>> fix the original table:<BR>>> update il_izmir3 set the_geom = ST_SetSRID(the_geom, 4326);<BR>><BR>> Unfortunately, no. As Regina pointed out, all this does is to <BR>> change the SRID number, it doesn't actually transform (reproject, <BR>> that is) the geometry. SetSRID is usually used when importing from <BR>> some WKT or other geometry operation that yields a geom descriptot <BR>> without an SRID set (or possibly with an improper one set).<BR>><BR>> To be honest, I would be surprised if "set the_geom = setSRID<BR>> (the_geom)" did not yield an error, as the table is created with <BR>> rules that mandate the geometry be of a certain SRID to prevent you <BR>> from inadvertently mixing shapes with different SRIDs in a given <BR>> table. At least, this is the default behaviour if you create your <BR>> tables from shapefiles (shp2pgsql).<BR>><BR>> The one thing I have found lacking (perhaps if I was truly <BR>> bothered, I would write a function), is a function such as <BR>> "transformGeomCol(geomcolname, newSrid)" which would update the <BR>> Restriction on the column and then execute a transform on the <BR>> column. When I first started using PostGIS I imported a whole <BR>> bunch of shapefiles and neglected to set the SRID, and then had to <BR>> go back later and manually redefine everything.<BR>><BR>> Robert W. Burgholzer<BR>> Surface Water Modeler<BR>> Office of Water Supply and Planning<BR>> Virginia Department of Environmental Quality<BR>> rwburgholzer@deq.virginia.gov<BR>> 804-698-4405<BR>> Open Source Modeling Tools:<BR>> <A href="http://sourceforge.net/projects/npsource/">http://sourceforge.net/projects/npsource/</A><BR>><BR>> -----Original Message-----<BR>> From: postgis-users-bounces@postgis.refractions.net [<A href="mailto:postgis-">mailto:postgis-</A><BR>> users-bounces@postgis.refractions.net] On Behalf Of Andy Anderson<BR>> Sent: Friday, May 16, 2008 11:38 AM<BR>> To: PostGIS Users Discussion<BR>> Subject: Re: [postgis-users] couldn't get the correct projected values<BR>><BR>> On May 16, 2008, at 6:55 AM, Obe, Regina wrote:<BR>><BR>>> What is the SRID of your il_izmir3 geometry to begin with?<BR>>><BR>>> If you imported it and incorrectly stated it as 32635 rather than<BR>>> 4269 or 4326, then presumably the transform will do nothing since<BR>>> it will see 32635 -> 32635 (you are already - nothing to do here).<BR>><BR>> Excellent idea! But if this is the case, shouldn't the first thing to<BR>> do be to fix the original table:<BR>><BR>> update il_izmir3 set the_geom = ST_SetSRID(the_geom, 4326);<BR>><BR>> and then Ahmet's original step 1 should work.<BR>><BR>> -- Andy<BR>><BR>>><BR>>> I am going to guess that the SRID of your il_izmir3 is incorrectly<BR>>> stated and that to fix, scrap the second statement and change your<BR>>> first statement to (note if your data is NAD83 longlat rather than<BR>>> WGS long lat change the 4326 to 4269)<BR>>><BR>>> create table izmir_il4 as select<BR>>> gid,il_kod,iladi,transform(ST_SetSRID(the_geom,4326),32635) as<BR>>> the_geom from il_izmir3;<BR>>><BR>>> or alternatively change your second statement to<BR>>><BR>>> UPDATE izmir_il4 SET the_geom=Transform(ST_SetSRID(the_geom,4326),<BR>>> 32635);<BR>>><BR>>> Note its safer to recreate the table since who knows what<BR>>> projection you stated the first as so you are still transforming a<BR>>> guess to a guess.<BR>>><BR>>> Hope that helps,<BR>>> Regina<BR>>><BR>>><BR>>><BR>>> -----Original Message-----<BR>>> From: postgis-users-bounces@postgis.refractions.net [<A href="mailto:postgis-">mailto:postgis-</A><BR>>> users-bounces@postgis.refractions.net] On Behalf Of Paragon<BR>>> Corporation<BR>>> Sent: Friday, May 16, 2008 6:18 AM<BR>>> To: 'PostGIS Users Discussion'<BR>>> Subject: RE: [postgis-users] couldn't get the correct projected <BR>>> values<BR>>><BR>>> You are confusing setsrid with transform.<BR>>><BR>>> Remember setsrid - doesn't change the geometry it simple certifies<BR>>> that a<BR>>> geometry is of a particular projection - basically<BR>>><BR>>> I am because I say I am<BR>>><BR>>><BR>>> Transform - moves a geometry from one projection to another<BR>>><BR>>> I am not , but please make me so. Of course the please make me so<BR>>> will fail<BR>>> if you don't know what it is to begin with<BR>>><BR>>><BR>>> So all being said, your statement 2 is wrong and should be<BR>>><BR>>> update izmir_il4 set the_geom=Transform(the_geom,32635);<BR>>><BR>>> Hope that helps,<BR>>> Regina<BR>>><BR>>> -----Original Message-----<BR>>> From: postgis-users-bounces@postgis.refractions.net<BR>>> [<A href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of<BR>>> orkun<BR>>> Sent: Friday, May 16, 2008 4:00 AM<BR>>> To: postgis-users@postgis.refractions.net<BR>>> Subject: [postgis-users] couldn't get the correct projected values<BR>>><BR>>> hello<BR>>><BR>>> whatever I have done, I couldn't get the correct projected values<BR>>> in postgis<BR>>> environment.<BR>>><BR>>> 1. # lon/lat to utm projection<BR>>><BR>>> create table izmir_il4 as select<BR>>> gid,il_kod,iladi,transform(the_geom,32635) as the_geom from <BR>>> il_izmir3;<BR>>><BR>>> 2.<BR>>> update izmir_il4 set the_geom=setsrid(the_geom,32635);<BR>>><BR>>> 3.<BR>>><BR>>> select extent(the_geom) from izmir_il4;<BR>>><BR>>> extent<BR>>> ---------------------------------------------------------------------<BR>>> -<BR>>> ----<BR>>> BOX(26.2226982116699 37.8668251037598,28.4587249755859<BR>>> 39.3925666809082)<BR>>><BR>>> but these are not utm values. They are still in lon/lat values<BR>>><BR>>> I checked extent values with proj values. I got correct values:<BR>>><BR>>> echo "26.2226982116699 37.8668251037598" | proj +proj=utm +zone=35<BR>>> +ellps=WGS84 +datum=WGS84<BR>>> 431631.23 4191323.84<BR>>> orkun@orkun-desktop:/usr/local/izmir/veri/izmir3$ echo<BR>>> "28.4587249755859<BR>>> 39.3925666809082" | proj +proj=utm +zone=35 +ellps=WGS84 +datum=WGS84<BR>>> 625615.27 4361356.53<BR>>><BR>>><BR>>> how can get values generated from "proj" in postgis environment ?<BR>>> what am I doing wrong ?<BR>>><BR>>> here is the srid values of 32635 :<BR>>> 32635 | +proj=utm +zone=35 +ellps=WGS84 +datum=WGS84 +units=m <BR>>> +no_defs<BR>>><BR>>> regards<BR>>><BR>>> --<BR>>> Ahmet Temiz<BR>>> Jeo. Müh.<BR>>> Afet Isleri Gen. Md.lügü<BR>>> Deprem Ar. D.<BR>>><BR>>> Ahmet Temiz<BR>>> Geo. Eng.<BR>>> General Dir. of<BR>>> Disaster Affairs<BR>>><BR>>><BR>>><BR>>> --<BR>>> This message has been scanned for viruses and<BR>>> dangerous content by MailScanner, and is<BR>>> believed to be clean.<BR>>><BR>>> _______________________________________________<BR>>> postgis-users mailing list<BR>>> postgis-users@postgis.refractions.net<BR>>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>><BR>>><BR>>><BR>>> _______________________________________________<BR>>> postgis-users mailing list<BR>>> postgis-users@postgis.refractions.net<BR>>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>> -----------------------------------------<BR>>> The substance of this message, including any attachments, may be<BR>>> confidential, legally privileged and/or exempt from disclosure<BR>>> pursuant to Massachusetts law. It is intended<BR>>> solely for the addressee. If you received this in error, please<BR>>> contact the sender and delete the material from any computer.<BR>>><BR>>> _______________________________________________<BR>>> postgis-users mailing list<BR>>> postgis-users@postgis.refractions.net<BR>>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>> _______________________________________________<BR>> postgis-users mailing list<BR>> postgis-users@postgis.refractions.net<BR>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>> _______________________________________________<BR>> postgis-users mailing list<BR>> postgis-users@postgis.refractions.net<BR>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR>_______________________________________________<BR>postgis-users mailing list<BR>postgis-users@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>_______________________________________________<BR>postgis-users mailing list<BR>postgis-users@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV></BODY></HTML>