Hi, me again!<div>Well at first it cannot be possible to have more than one projection. For clarity, let explain wht I did to reach at this point:</div><div>1- First I created a database 'mydistances' using postgis_template as template for it.</div>
<div>2-Create a table 'mypoints' with two columns: 'the_name' and "the_geom"</div><div>3- Load some GIS data:</div><div><span class="Apple-style-span" style="font-family: arial, sans-serif; font-size: 13px; border-collapse: collapse; "><p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">
</p><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">INSERT INTO mydistance ( the_geom, the_name ) VALUES ( ST_GeomFromText('POINT(-58.0 0.0)'), 'Punto 1-1');</span></font><p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">
<font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">INSERT INTO mydistance ( the_geom, the_name ) VALUES ( ST_GeomFromText('POINT(-57.0 0.0)'), 'Punto 1-2');</span></font></p><p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">
<font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">INSERT INTO mydistance ( the_geom, the_name ) VALUES ( ST_GeomFromText('POINT(-58.0 -45.0)'), 'Punto 2-1');</span></font></p>
<p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">INSERT INTO mydistance ( the_geom, the_name ) VALUES ( ST_GeomFromText('POINT(-57.0 -45.0)'), 'Punto 2-2');</span></font></p>
<p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">INSERT INTO mydistance ( the_geom, the_name ) VALUES ( ST_GeomFromText('POINT(-58.0 -90.0)'), 'Punto 3-1');</span></font></p>
<p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">INSERT INTO mydistance ( the_geom, the_name ) VALUES ( ST_GeomFromText('POINT(-57.0 -90.0)'), 'Punto 3-2');</span></font></p>
</blockquote><font class="Apple-style-span" face="Arial">It can be seen there's no SRID indication and that no value is out of the 4326 projection reference.</font><p></p><p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">
<font class="Apple-style-span" face="Arial">4- Tried to measure distances between those points usin ST_Distance. As all those data are in 4326 the retured values are in sexagesimal degrees.</font></p><p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">
<font class="Apple-style-span" face="Arial">5- Tried to use ST_Transform to UTM projections to get the distances in meters and all my problems began.</font></p><p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">
<font class="Apple-style-span" face="Arial">6- I want to get the distances in meters reprojecting the points and not calculating the meters form the degrees as this calculation depends of the UTM zone. I know which zone is in each case and there exists the ST_Transform function so, it is easy to avoid additional external calculations and obtain directly from the database my results.</font></p>
<p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font class="Apple-style-span" face="Arial">7- Tried all the recipes I got from you without success.</font></p><p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">
<font class="Apple-style-span" face="Arial"><br></font></p><p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font class="Apple-style-span" face="Arial">Can anybody see what it is the problem with this schema?</font></p>
</span><br><div class="gmail_quote">On Thu, Jan 21, 2010 at 4:07 AM, ibrahim saricicek <span dir="ltr"><<a href="mailto:ibrahimsaricicek@gmail.com">ibrahimsaricicek@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
Hi;<br><br>No you can change projection any time you want, the point on your table is;<br>You have objects in different projections but in one table. You have to find which objects are 4326 projection, and which are not...<br>
<br>Another method may be;<br><br>Select st_xmin(the_geom), st_ymin(the_geom) from mypoints<br><br>if the coordinates differ from --> <br><br>Longitude<br> 180 W = -180<br> 180 E = 180<br><br>Latitude<br> 90 N = 90<br>
90 S = -90<br><br>Namely your x not between -180 and 180 and y not between -90 and 90, the objects are in an another projection.<br><br>I mean firstly try to differ different projected objects..<br><br>Regards,<br>IBO... <br>
<div><div></div><div class="h5">
<br> <br><br><div class="gmail_quote">On Thu, Jan 21, 2010 at 2:01 AM, Oscar Zamudio <span dir="ltr"><<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204);margin:0pt 0pt 0pt 0.8ex;padding-left:1ex">
<div>Hi,</div><div>I tried:</div><div><br></div>UPDATE mypoints SET the_geom_4326=transform(the_geom,4326)<div><br>But again I got:</div><div><br></div><div><div><div>ERROR: Input geometry has unknown (-1) SRID</div>
<div><br>
</div></div><div class="gmail_quote">It seems that once the data is without explicit SRID, they cannot be processed later for asigning a proper SRID.</div><div class="gmail_quote">It is strange at first sight....but I'm a newbie so I don't know much about this.</div>
<div class="gmail_quote">Regards,</div><div class="gmail_quote"> Oscar</div><div><div></div><div><div class="gmail_quote"> </div><div class="gmail_quote">On Tue, Jan 19, 2010 at 6:27 AM, ibrahim saricicek <span dir="ltr"><<a href="mailto:ibrahimsaricicek@gmail.com" target="_blank">ibrahimsaricicek@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204);margin:0pt 0pt 0pt 0.8ex;padding-left:1ex"><div>Hi,</div>
<div> </div>
<div>Your table include objects, more than one projection.. So if there is any object in different projection you can't set your table projection to 4326...</div>
<div> </div>
<div>can you try this!!</div>
<div> </div>
<div>Create a new geometry column "the_geom_4326"</div>
<div> </div>
<div>then run this</div>
<div> </div>
<div>UPDATE table_name SET the_geom_4326=transform(the_geom,4326),</div>
<div> </div>
<div>then use ST_SetSRID for the new column...</div>
<div> </div>
<div>REgards,</div>
<div>ibo...</div><div><div></div><div>
<div> </div>
<div> </div>
<div class="gmail_quote">On Mon, Jan 18, 2010 at 7:23 PM, Oscar Zamudio <span dir="ltr"><<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a>></span> wrote:<br>
<blockquote style="border-left:1px solid rgb(204, 204, 204);margin:0px 0px 0px 0.8ex;padding-left:1ex" class="gmail_quote">Hi everybody.
<div>I had no success with any of the recipes received here.</div>
<div>Could it be that a table with data loaded without SRID explicit value has no chance of being updated later to the right one?</div>
<div>Regards,</div>
<div>Oscar</div>
<div><br><br>
<div class="gmail_quote">On Sun, Jan 17, 2010 at 1:08 PM, Oscar Zamudio <span dir="ltr"><<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a>></span> wrote:<br>
<blockquote style="border-left:1px solid rgb(204, 204, 204);margin:0px 0px 0px 0.8ex;padding-left:1ex" class="gmail_quote">
<div>Ben,</div>
<div>I tried your intruction:</div>
<div><br></div>
<div>UPDATE mypoints SET the_geom = ST_SetSRID(the_geom,4326);</div>
<div><br></div>
<div>But I got:</div>
<div><br></div>
<div>ERROR: Operation on two GEOMETRIES with different SRIDs</div>
<div><br></div>
<div>Regards,</div>
<div>Oscar</div>
<div>
<div></div>
<div><br>
<div class="gmail_quote">On Sat, Jan 16, 2010 at 10:48 PM, Ben Madin <span dir="ltr"><<a href="mailto:lists@remoteinformation.com.au" target="_blank">lists@remoteinformation.com.au</a>></span> wrote:<br>
<blockquote style="border-left:1px solid rgb(204, 204, 204);margin:0px 0px 0px 0.8ex;padding-left:1ex" class="gmail_quote">Oscar,<br><br>you have a couple of options as I see it. In Postgres you could just update the geometries first.<br>
<br>> UPDATE mytable SET the_geom = ST_SetSRID(the_geom,4326);<br><br><br>And for next time, assuming your data originally had some sort of coordinate system (and it must have if it was spatial data), you can / should use the -s flag in shp2pgsql to provide 'SRID awareness'<br>
<br>In your case, I would suggest -s 4326.<br><br>ie (using -d to drop and replace existing table<br><br>$ shp2pgsql -s 4326 -d myshapefile myoldtablename | psql mydatabase<br><br><br>In geometry terms, I think it is unlikely that your data was stored (spatially) as sexagesimal degrees, although it may be displayed as such in your GIS.<br>
<br>cheers<br><font color="#888888"><br>Ben<br></font>
<div>
<div></div>
<div><br><br><br><br><br>On 17/01/2010, at 2:54 , Oscar Zamudio wrote:<br><br>> Hi,<br>> I'm have problems to transform my spatial data to UTM. First, this instruction works OK:<br>><br>> SELECT ST_Transform(ST_GeomFromText('POINT(0.0 20)',4326),22171) from mypoints<br>
><br>> No matter if such a sentence has no meaning on what I tryin to do. But when I did the same with my own points:<br>><br>> SELECT ST_Transform(mypoints.the_geom,22171) from mypoints<br>><br>> I get the following error:<br>
> ERROR: Input geometry has unknown (-1) SRID<br>> SQL state: XX000<br>><br>> Ok, when I insert my points in my spatial enabled database I used:<br>><br>> INSERT INTO mydistance ( the_geom, the_name ) VALUES ( ST_GeomFromText('POINT(-58.0 0.0)'), 'Punto 1-1');<br>
><br>> without no spatial reference system explicit so I guess the SRID is set to "unknown" i.e. -1. Then naively I created a new column named srid for my table and put the right value of SRID for all my data which is 4326 (WGS84, sexagesimal degrees lat lon) in that column but still I get the same error. After that I created a constraint between this new srid column and the srid column from the spatial_ref_sys table but nothing happened, the error is still there.<br>
> It's important to me to solve this issue because all my spatial data was loaded from shapefiles without SRID awareness so they don't have any SRID column. I don't want to reload data changing this so I need a method to alter the tables in such a way to add this new srid column to them that is well related to the spatial context.<br>
> Thanks and regards,<br>> Oscar<br></div></div>
<div>
<div></div>
<div>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br></div></div></blockquote></div><br></div><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br></blockquote></div><br>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>