[postgis-users] Questions about using SRID not -1
Chris Hermansen
chris.hermansen at timberline.ca
Thu Jan 21 10:55:43 PST 2010
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>> 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>>
> 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>> 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>> 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>> 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>> 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>> 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>
> >
> 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
>
>
>
>
> _______________________________________________
> 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
> <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
>
>
>
> _______________________________________________
> 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