UpdateGeometry didn't work. On Thu, Jan 21, 2010 at 4:34 PM, Chris Hermansen < [email protected]> wrote:
> 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. > > I suggested that you try the UpdateGeometrySRID() function as per > > http://www.postgis.org/documentation/manual-1.4/UpdateGeometrySRID.html > > but I don't see any comment on whether that worked for you. > > Also, did you try the manual process (you might have to do this prior to > using UpdateGeometrySRID()) > > > http://www.postgis.org/documentation/manual-1.4/ch04.html#Manual_Register_Spatial_Column > > Oscar Zamudio wrote: > >> 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, >> INSERT INTO mydistance (the_geom, the_name) VALUES >> (ST_GeomFromText('POINT(-58.0 0.0)',4326),'Punto 1-1'); >> >> 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. >> >> Regards, >> >> On Thu, Jan 21, 2010 at 3:55 PM, Chris Hermansen < >> [email protected] <mailto:[email protected]>> >> wrote: >> >> Oscar; >> >> Read this: >> >> >> http://www.postgis.org/documentation/manual-1.4/ch04.html#Create_Spatial_Table >> >> Note the use of the AddGeometryColumn() to put a geometry column >> on your table and put an entry in the geometry_columns table, >> including setting the SRID correctly. >> >> So your example might be >> >> CREATE TABLE mydistance (the_name text); >> SELECT AddGeometryColumn('mydistance','the_geom',4326,'POINT',2); >> INSERT INTO mydistance (the_geom, the_name) VALUES >> (ST_GeomFromText('POINT(-58.0 0.0)',4326),'Punto 1-1'); >> >> etc assuming you wanted SRID 4326. >> >> The next section tells you how to manually register a geometry >> column if you have created it manually. >> >> Oscar Zamudio wrote: >> >> Hi, Two things. First, the name of the table in the INSERTS >> sentences is 'mypoints', not 'mydistance'. Second: I tried >> what Ibrahim suggested: >> >> Select st_xmin(the_geom), st_ymin(the_geom) from mypoints >> >> >> And the results are: >> >> -58;0 >> >> -57;0 >> >> -58;-45 >> >> -57;-45 >> >> -58;-90 >> >> -57;-90 >> >> >> So, none of them (as was expected) is out of the geometry >> projection reference. >> >> >> On Thu, Jan 21, 2010 at 2:38 PM, Oscar Zamudio >> <[email protected] <mailto:[email protected]> >> <mailto:[email protected] <mailto:[email protected]>>> wrote: >> >> Hi, me again! >> 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: >> 1- First I created a database 'mydistances' using >> postgis_template >> as template for it. >> 2-Create a table 'mypoints' with two columns: 'the_name' and >> "the_geom" >> 3- Load some GIS data: >> >> INSERT INTO mydistance ( the_geom, the_name ) VALUES ( >> ST_GeomFromText('POINT(-58.0 0.0)'), 'Punto 1-1'); >> >> INSERT INTO mydistance ( the_geom, the_name ) VALUES ( >> ST_GeomFromText('POINT(-57.0 0.0)'), 'Punto 1-2'); >> >> INSERT INTO mydistance ( the_geom, the_name ) VALUES ( >> ST_GeomFromText('POINT(-58.0 -45.0)'), 'Punto 2-1'); >> >> INSERT INTO mydistance ( the_geom, the_name ) VALUES ( >> ST_GeomFromText('POINT(-57.0 -45.0)'), 'Punto 2-2'); >> >> INSERT INTO mydistance ( the_geom, the_name ) VALUES ( >> ST_GeomFromText('POINT(-58.0 -90.0)'), 'Punto 3-1'); >> >> INSERT INTO mydistance ( the_geom, the_name ) VALUES ( >> ST_GeomFromText('POINT(-57.0 -90.0)'), 'Punto 3-2'); >> >> It can be seen there's no SRID indication and that no value >> is out >> of the 4326 projection reference. >> >> 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. >> >> 5- Tried to use ST_Transform to UTM projections to get the >> distances in meters and all my problems began. >> >> 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. >> >> 7- Tried all the recipes I got from you without success. >> >> >> Can anybody see what it is the problem with this schema? >> >> >> On Thu, Jan 21, 2010 at 4:07 AM, ibrahim saricicek >> <[email protected] >> <mailto:[email protected]> >> <mailto:[email protected] >> >> <mailto:[email protected]>>> >> >> wrote: >> >> Hi; >> >> No you can change projection any time you want, the >> point on >> your table is; >> You have objects in different projections but in one table. >> You have to find which objects are 4326 projection, and >> which >> are not... >> >> Another method may be; >> >> Select st_xmin(the_geom), st_ymin(the_geom) from mypoints >> >> if the coordinates differ from --> >> >> Longitude >> 180 W = -180 >> 180 E = 180 >> >> Latitude >> 90 N = 90 >> 90 S = -90 >> >> Namely your x not between -180 and 180 and y not >> between -90 >> and 90, the objects are in an another projection. >> >> I mean firstly try to differ different projected objects.. >> >> Regards, >> IBO... >> >> On Thu, Jan 21, 2010 at 2:01 AM, Oscar Zamudio >> <[email protected] <mailto:[email protected]> >> <mailto:[email protected] <mailto:[email protected]>>> wrote: >> >> Hi, >> I tried: >> >> UPDATE mypoints SET >> the_geom_4326=transform(the_geom,4326) >> >> But again I got: >> >> ERROR: Input geometry has unknown (-1) SRID >> >> It seems that once the data is without explicit >> SRID, they >> cannot be processed later for asigning a proper SRID. >> It is strange at first sight....but I'm a newbie so I >> don't know much about this. >> Regards, >> Oscar >> On Tue, Jan 19, 2010 at 6:27 AM, >> ibrahim saricicek >> <[email protected] >> <mailto:[email protected]> >> <mailto:[email protected] >> >> <mailto:[email protected]>>> wrote: >> >> Hi, >> 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... >> can you try this!! >> Create a new geometry column >> "the_geom_4326" >> then run this >> UPDATE table_name SET >> the_geom_4326=transform(the_geom,4326), >> then use ST_SetSRID for the new >> column... >> REgards, >> ibo... >> On Mon, Jan 18, >> 2010 at 7:23 PM, Oscar Zamudio >> <[email protected] <mailto:[email protected]> >> <mailto:[email protected] <mailto:[email protected]>>> wrote: >> >> Hi everybody. >> I had no success with any of the recipes >> received >> here. >> Could it be that a table with data loaded >> without >> SRID explicit value has no chance of being >> updated >> later to the right one? >> Regards, >> Oscar >> >> >> On Sun, Jan 17, 2010 at 1:08 PM, Oscar Zamudio >> <[email protected] <mailto:[email protected]> >> <mailto:[email protected] <mailto:[email protected]>>> wrote: >> >> Ben, >> I tried your intruction: >> >> UPDATE mypoints SET the_geom = >> ST_SetSRID(the_geom,4326); >> >> But I got: >> >> ERROR: Operation on two GEOMETRIES with >> different SRIDs >> >> Regards, >> Oscar >> >> On Sat, Jan 16, 2010 at 10:48 PM, Ben Madin >> <[email protected] >> <mailto:[email protected]> >> <mailto:[email protected] >> <mailto:[email protected]>>> wrote: >> >> Oscar, >> >> you have a couple of options as I >> see it. >> In Postgres you could just update the >> geometries first. >> >> > UPDATE mytable SET the_geom = >> ST_SetSRID(the_geom,4326); >> >> >> 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' >> >> In your case, I would suggest -s 4326. >> >> ie (using -d to drop and replace >> existing >> table >> >> $ shp2pgsql -s 4326 -d myshapefile >> myoldtablename | psql mydatabase >> >> >> 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. >> >> cheers >> >> Ben >> >> >> >> >> >> On 17/01/2010, at 2:54 , Oscar >> Zamudio wrote: >> >> > Hi, >> > I'm have problems to transform my >> spatial data to UTM. First, this >> instruction works OK: >> > >> > SELECT >> ST_Transform(ST_GeomFromText('POINT(0.0 >> 20)',4326),22171) from mypoints >> > >> > 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: >> > >> > SELECT >> >> ST_Transform(mypoints.the_geom,22171) from >> mypoints >> > >> > I get the following error: >> > ERROR: Input geometry has unknown >> (-1) SRID >> > SQL state: XX000 >> > >> > Ok, when I insert my points in my >> spatial enabled database I used: >> > >> > INSERT INTO mydistance ( the_geom, >> the_name ) VALUES ( >> ST_GeomFromText('POINT(-58.0 0.0)'), >> 'Punto 1-1'); >> > >> > 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. >> > 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. >> > Thanks and regards, >> > Oscar >> > >> >> _______________________________________________ >> > postgis-users mailing list >> > >> [email protected] >> <mailto:[email protected]> >> <mailto: >> [email protected] >> <mailto:[email protected]>> >> >> > >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> _______________________________________________ >> postgis-users mailing list >> >> [email protected] >> <mailto:[email protected]> >> <mailto: >> [email protected] >> <mailto:[email protected]>> >> >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> <mailto:[email protected]> >> <mailto: >> [email protected] >> <mailto:[email protected]>> >> >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> <mailto:[email protected]> >> <mailto:[email protected] >> <mailto:[email protected]>> >> >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> <mailto:[email protected]> >> <mailto:[email protected] >> <mailto:[email protected]>> >> >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> <mailto:[email protected]> >> <mailto:[email protected] >> <mailto:[email protected]>> >> >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> >> >> ------------------------------------------------------------------------ >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> <mailto:[email protected]> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> >> -- Regards, >> >> Chris Hermansen · [email protected] >> <mailto:[email protected]> · skype:clhermansen >> >> tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625 >> Timberline Natural Resource Group Ltd · http://www.timberline.ca >> 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5 >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> <mailto:[email protected]> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> ------------------------------------------------------------------------ >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> > > > -- > Regards, > > Chris Hermansen · [email protected] · skype:clhermansen > tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625 > Timberline Natural Resource Group Ltd · http://www.timberline.ca > 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5 > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
