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]> 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]> 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]> 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]> 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]>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]>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]> 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] >>>>>>> > http://postgis.refractions.net/mailman/listinfo/postgis-users >>>>>>> >>>>>>> _______________________________________________ >>>>>>> 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 >>>>> >>>>> >>>> >>>> _______________________________________________ >>>> 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 >>> >>> >> >> _______________________________________________ >> 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
