<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">Hi,<br><br>Yes you can, you use the case statement.<br><br><span style="font-style: italic;"></span>Here is an example:<br><br><span style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;">create table ttt (id serial primary key, <br> geocode varchar(12),<br> lat int,<br> lon int);</span><br style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"><span style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;">insert into ttt values (default, '1234/5678', null, null);</span><br style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"><span
style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;">insert into ttt values (default, '1234N/5678', null, null);</span><br style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"><span style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;">update ttt set lat=(case when substr(geocode,5,1)='N' then</span><br style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"><span style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"> (substr(geocode,1,4)||'00')::decimal(6,2) else</span><br style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"><span style="font-family: courier,monaco,monospace,sans-serif; font-style:
italic;"> ('-'||substr(geocode,1,4)||'00')::decimal(6,2) <br> end);<br>select * from ttt;<br> id | geocode | lat | lon <br>----+------------+---------+-----<br> 1 | 1234N/5678 | 123400 | <br> 2 | 1234/5678 | -123400 | <br></span><br><br>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<br><span style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"><br>update ttt set lon=(case when substr(geocode,5,1)='N' then</span><br style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"><span style="font-family:
courier,monaco,monospace,sans-serif; font-style: italic;"> ('-'||substr(geocode,7,4)||'00')::int else</span><br style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"><span style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"> ('-'||substr(geocode,6,4)||'00')::int end);</span><br><br><span style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;">select * from ttt;</span><br style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"><span style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"> id | geocode | lat | lon </span><br
style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"><span style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;">----+------------+---------+---------</span><br style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"><span style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"> 1 | 1234N/5678 | 123400 | -567800</span><br style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"><span style="font-family: courier,monaco,monospace,sans-serif; font-style: italic;"> 2 | 1234/5678 | -123400 | -567800</span><br><br>HTH,<br><br> Brent<br><br><br><br><br>--- On <b>Thu, 7/14/11, Yamini Singh <i><yaminijsingh@live.com></i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: Yamini Singh <yaminijsingh@live.com><br>Subject: RE: [postgis-users]
converting to lat long<br>To: pcreso@pcreso.com<br>Cc: "PostGIS User List" <postgis-users@postgis.refractions.net><br>Date: Thursday, July 14, 2011, 11:24 PM<br><br><div id="yiv694735096">
<style><!--
#yiv694735096 .yiv694735096hmmessage P
{
margin:0px;padding:0px;}
#yiv694735096 .yiv694735096hmmessage
{
font-size:10pt;font-family:Tahoma;}
--></style>
<div dir="ltr">
<p class="yiv694735096MsoNormal" style="line-height: 12.75pt;">Hi Bret,</p><p class="yiv694735096MsoNormal" style="line-height: 12.75pt;"><br></p>
<p class="yiv694735096MsoNormal" style="line-height: 12.75pt;">Thanks for your help. I am now
able to update lat and long column as explained by you. </p>
<p class="yiv694735096MsoNormal" style="line-height: 12.75pt;">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.</p>
<p class="yiv694735096MsoNormal" style="line-height: 12.75pt;">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?</p>
<p class="yiv694735096MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;">+-----------------------------+</p>
<p class="yiv694735096MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;">| Lat_dms | lat_dms |</p>
<p class="yiv694735096MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;">-------------------------------</p>
<p class="yiv694735096MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;">| -232900 | -472700 |</p>
<p class="yiv694735096MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;">-------------------------------</p>
<p class="yiv694735096MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;">| 000200 <span style=""> </span>| -515500 |</p>
<p class="yiv694735096MsoNormal" style="line-height: 12.75pt;">+-----------------------------+</p><p class="yiv694735096MsoNormal" style="line-height: 12.75pt;"><br></p>
<p class="yiv694735096MsoNormal" style="line-height: 12.75pt;">Thanks for your help.. actually I
am novice to GIS and postgres world….</p><p class="yiv694735096MsoNormal" style="line-height: 12.75pt;"><br></p>
<p class="yiv694735096MsoNormal" style="line-height: 12.75pt;">Thanks,</p>
<p class="yiv694735096MsoNormal" style="line-height: 12.75pt;">YJ</p><br><div><hr id="yiv694735096stopSpelling">Date: Wed, 13 Jul 2011 10:46:17 -0700<br>From: pcreso@pcreso.com<br>Subject: RE: [postgis-users] converting to lat long<br>To: yaminijsingh@live.com<br>CC: postgis-users@postgis.refractions.net<br><br><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td style="font: inherit;" valign="top"><br>Glad it helped.<br><br>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.<br><br><span style="font-style: italic;">alter table <tablename> add column lat_dms int;</span><br style="font-style: italic;"><span style="font-style: italic;">update <tablename> set lat_dms=('-'||substr(geocode,1,4)||'00')::int;</span><br><br>The "||" operator is a string
concatenation operator, so the sql starts with '-', appends the <br>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.<br><br>Then do the same for lon - but remember to substr(geocode,6,4) instead <br>(or combine the sqls to do both in a single statement).<br><br>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.<br><br><br>Cheers,<br><br>Brent<br><br><br><blockquote style="margin-left: 5px; padding-left: 5px;"><div id="yiv694735096ecxyiv159434206"><div dir="ltr"><div></div> </div>
</div></blockquote></td></tr></tbody></table></div> </div>
</div></blockquote></td></tr></table>