[postgis-users] Questions about using SRID not -1

Thu Jan 21 09:44:42 PST 2010

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 <cmntlk at gmail.com> 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 <
> ibrahimsaricicek at gmail.com> 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 <cmntlk at gmail.com> 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 <
>>> ibrahimsaricicek at gmail.com> 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 <cmntlk at gmail.com>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 <cmntlk at gmail.com>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 <
>>>>>> lists at remoteinformation.com.au> 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
>>>>>>> > postgis-users at postgis.refractions.net
>>>>>>> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> postgis-users mailing list
>>>>>>> postgis-users at postgis.refractions.net
>>>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at postgis.refractions.net
>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>>
>>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100121/295ef3a0/attachment.html>