[postgis-users] Questions about using SRID not -1
Oscar Zamudio
cmntlk at gmail.com
Thu Jan 21 12:21:10 PST 2010
UpdateGeometry didn't work.
On Thu, Jan 21, 2010 at 4:34 PM, Chris Hermansen <
chris.hermansen at timberline.ca> 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 <
>> chris.hermansen at timberline.ca <mailto:chris.hermansen at timberline.ca>>
>> 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
>> <cmntlk at gmail.com <mailto:cmntlk at gmail.com>
>> <mailto:cmntlk at gmail.com <mailto: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
>> <mailto:ibrahimsaricicek at gmail.com>
>> <mailto:ibrahimsaricicek at gmail.com
>>
>> <mailto: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 <mailto:cmntlk at gmail.com>
>> <mailto:cmntlk at gmail.com <mailto: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
>> <mailto:ibrahimsaricicek at gmail.com>
>> <mailto:ibrahimsaricicek at gmail.com
>>
>> <mailto: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 <mailto:cmntlk at gmail.com>
>> <mailto:cmntlk at gmail.com <mailto: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 <mailto:cmntlk at gmail.com>
>> <mailto:cmntlk at gmail.com <mailto: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
>> <mailto:lists at remoteinformation.com.au>
>> <mailto:lists at remoteinformation.com.au
>> <mailto: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
>> <mailto:postgis-users at postgis.refractions.net>
>> <mailto:
>> postgis-users at postgis.refractions.net
>> <mailto:postgis-users at postgis.refractions.net>>
>>
>> >
>>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>>
>> postgis-users at postgis.refractions.net
>> <mailto:postgis-users at postgis.refractions.net>
>> <mailto:
>> postgis-users at postgis.refractions.net
>> <mailto:postgis-users at postgis.refractions.net>>
>>
>>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> <mailto:postgis-users at postgis.refractions.net>
>> <mailto:
>> postgis-users at postgis.refractions.net
>> <mailto:postgis-users at postgis.refractions.net>>
>>
>>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> <mailto:postgis-users at postgis.refractions.net>
>> <mailto:postgis-users at postgis.refractions.net
>> <mailto:postgis-users at postgis.refractions.net>>
>>
>>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> <mailto:postgis-users at postgis.refractions.net>
>> <mailto:postgis-users at postgis.refractions.net
>> <mailto:postgis-users at postgis.refractions.net>>
>>
>>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> <mailto:postgis-users at postgis.refractions.net>
>> <mailto:postgis-users at postgis.refractions.net
>> <mailto:postgis-users at postgis.refractions.net>>
>>
>>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> <mailto:postgis-users at postgis.refractions.net>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>> -- Regards,
>>
>> Chris Hermansen · chris.hermansen at timberline.ca
>> <mailto:chris.hermansen at timberline.ca> · 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
>> postgis-users at postgis.refractions.net
>> <mailto: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
>>
>>
>
>
> --
> Regards,
>
> Chris Hermansen · chris.hermansen at timberline.ca · 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
> 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/4a0a0510/attachment.html>
More information about the postgis-users
mailing list