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
<[email protected] <mailto:[email protected]>>
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
<[email protected] <mailto:[email protected]>
<mailto:[email protected] <mailto:[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]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[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] <mailto:[email protected]>
<mailto:[email protected] <mailto:[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]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[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] <mailto:[email protected]>
<mailto:[email protected] <mailto:[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] <mailto:[email protected]>
<mailto:[email protected] <mailto:[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]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[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]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[email protected]>>
>
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[email protected]>>
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[email protected]>>
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[email protected]>>
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[email protected]>>
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[email protected]>>
http://postgis.refractions.net/mailman/listinfo/postgis-users
------------------------------------------------------------------------
_______________________________________________
postgis-users mailing list
[email protected]
<mailto:[email protected]>
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Regards,
Chris Hermansen · [email protected]
<mailto:[email protected]> · 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
[email protected]
<mailto:[email protected]>
http://postgis.refractions.net/mailman/listinfo/postgis-users
------------------------------------------------------------------------
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
