<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;"><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>--- On <b>Wed, 7/13/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>Date: Wednesday, July 13, 2011, 9:47 PM<br><br><div id="yiv159434206">
<style><!--
#yiv159434206 .yiv159434206hmmessage P
{
margin:0px;padding:0px;}
#yiv159434206 .yiv159434206hmmessage
{
font-size:10pt;font-family:Tahoma;}
--></style>
<div dir="ltr">
<font class="yiv159434206Apple-style-span" face="Tahoma" size="2">Hi Brent,</font><div style="font-family: Tahoma; font-size: 10pt;"><br></div><div><font class="yiv159434206Apple-style-span" face="Tahoma" size="2">Thanks for your reply. I was able to do the way you have explained. Actually, the 'geocode' column in my table contain attributes as string like '2329/4727' with slash with 4 by 4 format throughout, but i want them to be stored separately</font><font class="yiv159434206Apple-style-span" face="Tahoma" size="2"> in DMS in columns 'lat_DMS' and 'long_DMS'. The reason is that I will be matching this lat and long column with another table where coordinates are mentioned in two columns separately. Also, lat is S and long is W so both are in negative coordinates. so i am looking for column something like this:</font></div><div><font class="yiv159434206Apple-style-span" face="Tahoma" size="2"><br></font></div><div><font
class="yiv159434206Apple-style-span" face="Tahoma" size="2">-----------------------------</font></div><div><font class="yiv159434206Apple-style-span" face="Tahoma" size="2">lat_DMS | long_DMS |</font></div><div><font class="yiv159434206Apple-style-span" face="Tahoma" size="2">-----------------------------<span class="yiv159434206Apple-tab-span" style="white-space: pre;"> </span><br></font></div><div>-232900 | -472700 |</div><div>------------------------</div><div><br></div><div>Is this possible with simple query...</div><div><br></div><div>Regards</div><div>Yamini</div><div><br></div><div><br></div><div>--------------------------------------------------------------------------------------<br><div style="font-family: Tahoma; font-size: 10pt;">Date: Tue, 12 Jul 2011 10:28:35 -0700<br>From: pcreso@pcreso.com<br>Subject: Re: [postgis-users] converting to lat long<br>To: postgis-users@postgis.refractions.net<br>CC:
yaminijsingh@live.com<br><br><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td style="font: inherit;" valign="top"><br>Instead of storing lat & long columns of numbers, create a point geometry with Postgis, you can still select the lat & long values as shown below.<br><br><span style="font-family: courier,monaco,monospace,sans-serif;">select addgeometrycolumn('','tablename','geom',4326,'POINT',2);</span><br><br>To run this change 'tablename' to the name of the table with the geocode column. This will give you a Postgis geometry column to store lat/long points in.<br><br>geocode is presumeably a string datatype, and there are a number of ways to parse this, here is one example. I'm assuming each value will always be 4 digits then "/" then 4 digits, as in your example. If not, then you'll need another approach.<br><br><span style="font-family: courier,monaco,monospace,sans-serif;">update tablename<br></span><span style="font-family:
courier,monaco,monospace,sans-serif;">set geom=setsrid(makepoint(substr(geocode,6,4)::decimal(6,2)/100,<br> substr(geocode,1,4)::decimal(6,2)/100),<br> 4326);<br><br><span style="font-family: arial,helvetica,sans-serif;">This takes characters 6-9 of the geocode value, converts to a number, divides by 100, then does the same to characters 1-4, which generates your lon & lat (in xy order) values as you describe them, then assigns a srid of 4326 to tell postgis the coordinates are indeed lat/long ones, and sets the value of each point geometry to these coordinates.<br><br>If you want to see the resulting lat/long values now stored in the geom column, try:<br><br><span style="font-family:
courier,monaco,monospace,sans-serif;">select geocode, astext(geom) from tablename;</span><br style="font-family: courier,monaco,monospace,sans-serif;">or<br><span style="font-family: courier,monaco,monospace,sans-serif;">select geocode, y(geom) as lat, x(geom) as lon from tablename;</span> </span><br></span><br><br>HTH,<br><br> Brent Wood<br><br>--- On <b>Wed, 7/13/11, Yamini Singh <i><yaminijsingh@live.com></i></b> wrote:<br><blockquote style="margin-left: 5px; padding-left: 5px;"><br>From: Yamini Singh <yaminijsingh@live.com><br>Subject: [postgis-users] converting to lat long<br>To: "PostGIS User List" <postgis-users@postgis.refractions.net><br>Date: Wednesday, July 13, 2011, 12:58 AM<br><br><div id="yiv159434206ecxyiv967091284">
<style>
#yiv159434206 .yiv159434206ExternalClass #yiv159434206ecxyiv967091284 .yiv159434206ecxyiv967091284hmmessage P
{padding:0px;}
#yiv159434206 .yiv159434206ExternalClass #yiv159434206ecxyiv967091284 .yiv159434206ecxyiv967091284hmmessage
{font-size:10pt;font-family:Tahoma;}
</style>
<div dir="ltr">
<div><p class="yiv159434206ecxyiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);">Hi All,</span></b></p><p class="yiv159434206ecxyiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);"><br></span></b></p>
<p class="yiv159434206ecxyiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);">I have a column 'geocode' in a table which has
attributes like 2329/4727 now I would like to convert these </span></b><b style="text-indent: 0px;"><span style="text-indent: 0px; font-size: 10pt; font-family: Verdana,sans-serif; color: rgb(102, 102, 102);">attributes </span></b><b><span style="font-size: 10pt; color: rgb(102, 102, 102);">in another
column to 'lat' and 'long'. For example, 'Lat' column will have </span></b><b style="text-indent: 0px;"><span style="text-indent: 0px; font-size: 10pt; font-family: Verdana,sans-serif; color: rgb(102, 102, 102);">attribute '</span></b><b><span style="font-size: 10pt; color: rgb(102, 102, 102);">23.29' and 'long' column will have </span></b><b style="text-indent: 0px;"><span style="text-indent: 0px; font-size: 10pt; font-family: Verdana,sans-serif; color: rgb(102, 102, 102);">attribute </span></b><b><span style="font-size: 10pt; color: rgb(102, 102, 102);"> '47.27'.</span></b></p>
<p class="yiv159434206ecxyiv967091284MsoNormal" style=""><b><font class="yiv159434206ecxyiv967091284Apple-style-span" color="#666666" face="Verdana, sans-serif" size="2">Is there a way through which this can be
converted automatically by a query or so.</font></b></p><p class="yiv159434206ecxyiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);"><br></span></b></p><p class="yiv159434206ecxyiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);">Looking fwd..</span></b></p>
<p class="yiv159434206ecxyiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);"> </span></b></p>
<p class="yiv159434206ecxyiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);">Thanks</span></b></p>
<p class="yiv159434206ecxyiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);">YJ</span></b></p></div> </div>
</div><br>-----Inline Attachment Follows-----<br><br><div class="yiv159434206ecxplainMail">_______________________________________________<br>postgis-users mailing list<br><a rel="nofollow">postgis-users@postgis.refractions.net</a><br><a rel="nofollow" target="_blank" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></blockquote></td></tr></tbody></table></div></div> </div>
</div></blockquote></td></tr></table>