[postgis-users] Questions about using SRID not -1
Chris Hermansen
chris.hermansen at timberline.ca
Thu Jan 21 11:34:23 PST 2010
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
More information about the postgis-users
mailing list