UpdateGeometry didn't work. <br><br><div class="gmail_quote">On Thu, Jan 21, 2010 at 4:34 PM, Chris Hermansen <span dir="ltr"><<a href="mailto:chris.hermansen@timberline.ca" target="_blank">chris.hermansen@timberline.ca</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">The problem is, the kind of transforms you want to do (to UTM for example) only work when there is a valid SRID in the table geometry and in the geometry_columns table, and the two agree. AFAIK anyway.<br>
<br>
I suggested that you try the UpdateGeometrySRID() function as per<br>
<br>
<a href="http://www.postgis.org/documentation/manual-1.4/UpdateGeometrySRID.html" target="_blank">http://www.postgis.org/documentation/manual-1.4/UpdateGeometrySRID.html</a><br>
<br>
but I don't see any comment on whether that worked for you.<br>
<br>
Also, did you try the manual process (you might have to do this prior to using UpdateGeometrySRID())<br>
<br>
<a href="http://www.postgis.org/documentation/manual-1.4/ch04.html#Manual_Register_Spatial_Column" target="_blank">http://www.postgis.org/documentation/manual-1.4/ch04.html#Manual_Register_Spatial_Column</a><br>
<br>
Oscar Zamudio wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div>
I already stated that this is only an experiment trying to solve the problem because my customer already loaded a lot of data WITHOUT expliciting SRID reference..... I mean, <br>
INSERT INTO mydistance (the_geom, the_name) VALUES (ST_GeomFromText('POINT(-58.0 0.0)',4326),'Punto 1-1');<br>
<br>
Is a step without SRID value that was taken years ago on their database. I'm trying to avoid the reloading process and taking in account that there are some functions that probably can make the task,,, I began to explore them without success up to now.<br>
<br>
Regards,<br>
<br></div><div><div></div><div>
On Thu, Jan 21, 2010 at 3:55 PM, Chris Hermansen <<a href="mailto:chris.hermansen@timberline.ca" target="_blank">chris.hermansen@timberline.ca</a> <mailto:<a href="mailto:chris.hermansen@timberline.ca" target="_blank">chris.hermansen@timberline.ca</a>>> wrote:<br>
<br>
Oscar;<br>
<br>
Read this:<br>
<br>
<a href="http://www.postgis.org/documentation/manual-1.4/ch04.html#Create_Spatial_Table" target="_blank">http://www.postgis.org/documentation/manual-1.4/ch04.html#Create_Spatial_Table</a><br>
<br>
Note the use of the AddGeometryColumn() to put a geometry column<br>
on your table and put an entry in the geometry_columns table,<br>
including setting the SRID correctly.<br>
<br>
So your example might be<br>
<br>
CREATE TABLE mydistance (the_name text);<br>
SELECT AddGeometryColumn('mydistance','the_geom',4326,'POINT',2);<br>
INSERT INTO mydistance (the_geom, the_name) VALUES<br>
(ST_GeomFromText('POINT(-58.0 0.0)',4326),'Punto 1-1');<br>
<br>
etc assuming you wanted SRID 4326.<br>
<br>
The next section tells you how to manually register a geometry<br>
column if you have created it manually.<br>
<br>
Oscar Zamudio wrote:<br>
<br>
Hi, Two things. First, the name of the table in the INSERTS<br>
sentences is 'mypoints', not 'mydistance'. Second: I tried<br>
what Ibrahim suggested:<br>
<br>
Select st_xmin(the_geom), st_ymin(the_geom) from mypoints<br>
<br>
<br>
And the results are:<br>
<br>
-58;0<br>
<br>
-57;0<br>
<br>
-58;-45<br>
<br>
-57;-45<br>
<br>
-58;-90<br>
<br>
-57;-90<br>
<br>
<br>
So, none of them (as was expected) is out of the geometry<br>
projection reference.<br>
<br>
<br>
On Thu, Jan 21, 2010 at 2:38 PM, Oscar Zamudio<br>
<<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a> <mailto:<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a>><br></div></div><div><div></div><div>
<mailto:<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a> <mailto:<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a>>>> wrote:<br>
<br>
Hi, me again!<br>
Well at first it cannot be possible to have more than one<br>
projection. For clarity, let explain wht I did to reach at<br>
this point:<br>
1- First I created a database 'mydistances' using<br>
postgis_template<br>
as template for it.<br>
2-Create a table 'mypoints' with two columns: 'the_name' and<br>
"the_geom"<br>
3- Load some GIS data:<br>
<br>
INSERT INTO mydistance ( the_geom, the_name ) VALUES (<br>
ST_GeomFromText('POINT(-58.0 0.0)'), 'Punto 1-1');<br>
<br>
INSERT INTO mydistance ( the_geom, the_name ) VALUES (<br>
ST_GeomFromText('POINT(-57.0 0.0)'), 'Punto 1-2');<br>
<br>
INSERT INTO mydistance ( the_geom, the_name ) VALUES (<br>
ST_GeomFromText('POINT(-58.0 -45.0)'), 'Punto 2-1');<br>
<br>
INSERT INTO mydistance ( the_geom, the_name ) VALUES (<br>
ST_GeomFromText('POINT(-57.0 -45.0)'), 'Punto 2-2');<br>
<br>
INSERT INTO mydistance ( the_geom, the_name ) VALUES (<br>
ST_GeomFromText('POINT(-58.0 -90.0)'), 'Punto 3-1');<br>
<br>
INSERT INTO mydistance ( the_geom, the_name ) VALUES (<br>
ST_GeomFromText('POINT(-57.0 -90.0)'), 'Punto 3-2');<br>
<br>
It can be seen there's no SRID indication and that no value<br>
is out<br>
of the 4326 projection reference.<br>
<br>
4- Tried to measure distances between those points usin<br>
ST_Distance. As all those data are in 4326 the retured<br>
values are<br>
in sexagesimal degrees.<br>
<br>
5- Tried to use ST_Transform to UTM projections to get the<br>
distances in meters and all my problems began.<br>
<br>
6- I want to get the distances in meters reprojecting the<br>
points<br>
and not calculating the meters form the degrees as this<br>
calculation depends of the UTM zone. I know which zone is<br>
in each<br>
case and there exists the ST_Transform function so, it is<br>
easy to<br>
avoid additional external calculations and obtain directly from<br>
the database my results.<br>
<br>
7- Tried all the recipes I got from you without success.<br>
<br>
<br>
Can anybody see what it is the problem with this schema?<br>
<br>
<br>
On Thu, Jan 21, 2010 at 4:07 AM, ibrahim saricicek<br>
<<a href="mailto:ibrahimsaricicek@gmail.com" target="_blank">ibrahimsaricicek@gmail.com</a><br>
<mailto:<a href="mailto:ibrahimsaricicek@gmail.com" target="_blank">ibrahimsaricicek@gmail.com</a>><br></div></div>
<mailto:<a href="mailto:ibrahimsaricicek@gmail.com" target="_blank">ibrahimsaricicek@gmail.com</a><div><div></div><div><br>
<mailto:<a href="mailto:ibrahimsaricicek@gmail.com" target="_blank">ibrahimsaricicek@gmail.com</a>>>><br>
<br>
wrote:<br>
<br>
Hi;<br>
<br>
No you can change projection any time you want, the<br>
point on<br>
your table is;<br>
You have objects in different projections but in one table.<br>
You have to find which objects are 4326 projection, and<br>
which<br>
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<br>
between -90<br>
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>
<br>
On Thu, Jan 21, 2010 at 2:01 AM, Oscar Zamudio<br>
<<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a> <mailto:<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a>><br></div></div><div>
<mailto:<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a> <mailto:<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a>>>> wrote:<br>
<br>
Hi,<br>
I tried:<br>
<br>
UPDATE mypoints SET<br>
the_geom_4326=transform(the_geom,4326)<br>
<br>
But again I got:<br>
<br>
ERROR: Input geometry has unknown (-1) SRID<br>
<br>
It seems that once the data is without explicit<br>
SRID, they<br>
cannot be processed later for asigning a proper SRID.<br>
It is strange at first sight....but I'm a newbie so I<br>
don't know much about this.<br>
Regards,<br>
Oscar<br>
On Tue, Jan 19, 2010 at 6:27 AM,<br>
ibrahim saricicek<br>
<<a href="mailto:ibrahimsaricicek@gmail.com" target="_blank">ibrahimsaricicek@gmail.com</a><br>
<mailto:<a href="mailto:ibrahimsaricicek@gmail.com" target="_blank">ibrahimsaricicek@gmail.com</a>><br></div>
<mailto:<a href="mailto:ibrahimsaricicek@gmail.com" target="_blank">ibrahimsaricicek@gmail.com</a><div><br>
<mailto:<a href="mailto:ibrahimsaricicek@gmail.com" target="_blank">ibrahimsaricicek@gmail.com</a>>>> wrote:<br>
<br>
Hi,<br>
Your table include objects,<br>
more than one projection..<br>
So if there is any object in different<br>
projection you<br>
can't set your table projection to 4326...<br>
can you try this!!<br>
Create a new geometry column<br>
"the_geom_4326"<br>
then run this<br>
UPDATE table_name SET<br>
the_geom_4326=transform(the_geom,4326),<br>
then use ST_SetSRID for the new<br>
column...<br>
REgards,<br>
ibo...<br>
On Mon, Jan 18,<br>
2010 at 7:23 PM, Oscar Zamudio<br>
<<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a> <mailto:<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a>><br></div><div>
<mailto:<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a> <mailto:<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a>>>> wrote:<br>
<br>
Hi everybody.<br>
I had no success with any of the recipes<br>
received<br>
here.<br>
Could it be that a table with data loaded<br>
without<br>
SRID explicit value has no chance of being<br>
updated<br>
later to the right one?<br>
Regards,<br>
Oscar<br>
<br>
<br>
On Sun, Jan 17, 2010 at 1:08 PM, Oscar Zamudio<br>
<<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a> <mailto:<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a>><br></div><div><div></div><div>
<mailto:<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a> <mailto:<a href="mailto:cmntlk@gmail.com" target="_blank">cmntlk@gmail.com</a>>>> wrote:<br>
<br>
Ben,<br>
I tried your intruction:<br>
<br>
UPDATE mypoints SET the_geom =<br>
ST_SetSRID(the_geom,4326);<br>
<br>
But I got:<br>
<br>
ERROR: Operation on two GEOMETRIES with<br>
different SRIDs<br>
<br>
Regards,<br>
Oscar<br>
<br>
On Sat, Jan 16, 2010 at 10:48 PM, Ben Madin<br>
<<a href="mailto:lists@remoteinformation.com.au" target="_blank">lists@remoteinformation.com.au</a><br>
<mailto:<a href="mailto:lists@remoteinformation.com.au" target="_blank">lists@remoteinformation.com.au</a>><br>
<mailto:<a href="mailto:lists@remoteinformation.com.au" target="_blank">lists@remoteinformation.com.au</a><br>
<mailto:<a href="mailto:lists@remoteinformation.com.au" target="_blank">lists@remoteinformation.com.au</a>>>> wrote:<br>
<br>
Oscar,<br>
<br>
you have a couple of options as I<br>
see it.<br>
In Postgres you could just update the<br>
geometries first.<br>
<br>
> UPDATE mytable SET the_geom =<br>
ST_SetSRID(the_geom,4326);<br>
<br>
<br>
And for next time, assuming your data<br>
originally had some sort of coordinate<br>
system (and it must have if it was<br>
spatial<br>
data), you can / should use the -s<br>
flag in<br>
shp2pgsql to provide 'SRID awareness'<br>
<br>
In your case, I would suggest -s 4326.<br>
<br>
ie (using -d to drop and replace<br>
existing<br>
table<br>
<br>
$ shp2pgsql -s 4326 -d myshapefile<br>
myoldtablename | psql mydatabase<br>
<br>
<br>
In geometry terms, I think it is<br>
unlikely<br>
that your data was stored<br>
(spatially) as<br>
sexagesimal degrees, although it may be<br>
displayed as such in your GIS.<br>
<br>
cheers<br>
<br>
Ben<br>
<br>
<br>
<br>
<br>
<br>
On 17/01/2010, at 2:54 , Oscar<br>
Zamudio wrote:<br>
<br>
> Hi,<br>
> I'm have problems to transform my<br>
spatial data to UTM. First, this<br>
instruction works OK:<br>
><br>
> SELECT<br>
ST_Transform(ST_GeomFromText('POINT(0.0<br>
20)',4326),22171) from mypoints<br>
><br>
> No matter if such a sentence has no<br>
meaning on what I tryin to do. But<br>
when I<br>
did the same with my own points:<br>
><br>
> SELECT<br>
ST_Transform(mypoints.the_geom,22171) from<br>
mypoints<br>
><br>
> I get the following error:<br>
> ERROR: Input geometry has unknown<br>
(-1) SRID<br>
> SQL state: XX000<br>
><br>
> Ok, when I insert my points in my<br>
spatial enabled database I used:<br>
><br>
> INSERT INTO mydistance ( the_geom,<br>
the_name ) VALUES (<br>
ST_GeomFromText('POINT(-58.0 0.0)'),<br>
'Punto 1-1');<br>
><br>
> without no spatial reference system<br>
explicit so I guess the SRID is set to<br>
"unknown" i.e. -1. Then naively I<br>
created<br>
a new column named srid for my<br>
table and<br>
put the right value of SRID for all my<br>
data which is 4326 (WGS84, sexagesimal<br>
degrees lat lon) in that column but<br>
still<br>
I get the same error. After that I<br>
created<br>
a constraint between this new srid<br>
column<br>
and the srid column from the<br>
spatial_ref_sys table but nothing<br>
happened, the error is still there.<br>
> It's important to me to solve<br>
this issue<br>
because all my spatial data was loaded<br>
from shapefiles without SRID<br>
awareness so<br>
they don't have any SRID column. I<br>
don't<br>
want to reload data changing this so I<br>
need a method to alter the tables<br>
in such<br>
a way to add this new srid column<br>
to them<br>
that is well related to the spatial<br>
context.<br>
> Thanks and regards,<br>
> Oscar<br>
><br>
_______________________________________________<br>
> postgis-users mailing list<br>
><br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>>><br>
<br>
><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>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>>><br>
<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>
<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>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>>><br>
<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>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>>><br>
<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>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>>><br>
<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>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>>><br>
<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>
<br>
------------------------------------------------------------------------<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>
<mailto:<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>
<br></div></div><div>
-- Regards,<br>
<br>
Chris Hermansen · <a href="mailto:chris.hermansen@timberline.ca" target="_blank">chris.hermansen@timberline.ca</a><br></div>
<mailto:<a href="mailto:chris.hermansen@timberline.ca" target="_blank">chris.hermansen@timberline.ca</a>> · skype:clhermansen<div><br>
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625<br>
Timberline Natural Resource Group Ltd · <a href="http://www.timberline.ca" target="_blank">http://www.timberline.ca</a><br>
401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5<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>
<mailto:<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>
------------------------------------------------------------------------<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>
<br>
</div></blockquote><div><div></div><div>
<br>
<br>
-- <br>
Regards,<br>
<br>
Chris Hermansen · <a href="mailto:chris.hermansen@timberline.ca" target="_blank">chris.hermansen@timberline.ca</a> · skype:clhermansen<br>
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625<br>
Timberline Natural Resource Group Ltd · <a href="http://www.timberline.ca" target="_blank">http://www.timberline.ca</a><br>
401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5<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>