[postgis-users] converting to lat long

[pcreso at pcreso.com]

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 <yaminijsingh at live.com> wrote:

From: Yamini Singh <yaminijsingh at live.com>
Subject: RE: [postgis-users] converting to lat long
To: pcreso at pcreso.com
Cc: "PostGIS User List" <postgis-users at 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: pcreso at pcreso.com
Subject: RE: [postgis-users] converting to lat long
To: yaminijsingh at live.com
CC: postgis-users at 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


 		 	   		   
 		 	   		   
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101014/75a4f4f7/attachment.html>