Hi,
Yes you can, you use the case statement.
Here is an example:
create table ttt (id serial primary key,
geocode varchar(12),
lat int,
lon int);
insert into ttt values (default, '1234/5678', null, null);
insert into ttt values (default, '1234N/5678', null, null);
update ttt set lat=(case when substr(geocode,5,1)='N' then
(substr(geocode,1,4)||'00')::decimal(6,2) else
('-'||substr(geocode,1,4)||'00')::decimal(6,2)
end);
select * from ttt;
id | geocode | lat | lon
----+------------+---------+-----
1 | 1234N/5678 | 123400 |
2 | 1234/5678 | -123400 |
Note you will also need to use a similar case statement for lon, as the substr
values will change due to the extra character offset, as below
update ttt set lon=(case when substr(geocode,5,1)='N' then
('-'||substr(geocode,7,4)||'00')::int else
('-'||substr(geocode,6,4)||'00')::int end);
select * from ttt;
id | geocode | lat | lon
----+------------+---------+---------
1 | 1234N/5678 | 123400 | -567800
2 | 1234/5678 | -123400 | -567800
HTH,
Brent
--- On Thu, 7/14/11, Yamini Singh <yaminijsi...@live.com> wrote:
From: Yamini Singh <yaminijsi...@live.com>
Subject: RE: [postgis-users] converting to lat long
To: pcr...@pcreso.com
Cc: "PostGIS User List" <postgis-users@postgis.refractions.net>
Date: Thursday, July 14, 2011, 11:24 PM
Hi Bret,
Thanks for your help. I am now
able to update lat and long column as explained by you.
Sometimes I attributes like ‘0002N/5155’
in geocode column. Now in the lat_dms column the attribute should be ‘000200’
and ‘-515500’in long_dms column. But how do I get the ‘N’ in ‘lat’ part
recognized
in query so that it is not placed as ‘-000020’ but as positive coordinate.
Also, the number of character will get changes in ‘lat’ it will now be 1 to 5
character
and ‘7 to 10’ in long.
Is there a possibility of having
one query that takes care of ‘geocode’ in 4 x 4 format as well as 5 x 4
----format
with fifth word as N?
+-----------------------------+
| Lat_dms | lat_dms |
-------------------------------
| -232900 | -472700 |
-------------------------------
| 000200 | -515500 |
+-----------------------------+
Thanks for your help.. actually I
am novice to GIS and postgres world….
Thanks,
YJ
Date: Wed, 13 Jul 2011 10:46:17 -0700
From: pcr...@pcreso.com
Subject: RE: [postgis-users] converting to lat long
To: yaminijsi...@live.com
CC: postgis-users@postgis.refractions.net
Glad it helped.
You can do that, but that is simply using Postgres columns, you do not require
Postgis & geometry capabilities to do that. I recommend you avoid upper case
characters in table & column names, otherwise you'll need to quote them.
alter table <tablename> add column lat_dms int;
update <tablename> set lat_dms=('-'||substr(geocode,1,4)||'00')::int;
The "||" operator is a string concatenation operator, so the sql starts with
'-', appends the
specified substring from geocode, appends two more zeros, then converts the
whole thing to an integer. If the columns you will be comparing them with are
strings, not numbers, then create these two columns as the same datatype &
don't do the "::int"
conversion.
Then do the same for lon - but remember to substr(geocode,6,4) instead
(or combine the sqls to do both in a single statement).
Note that my previous example creating a Postgis geometry assumed that 1234 was
decimal degrees (12.34deg). So if the numbers reflect degrees & minutes (12deg
34min) then the result is incorrect. You should substring the deg & min
separately, convert both to numeric, divide the minute value by 60 then add
them to get the decimal degree value.
Cheers,
Brent
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
