<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;"><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="border-left: 2px solid rgb(16, 16, 255); 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="yiv967091284">
<style><!--
#yiv967091284 .yiv967091284hmmessage P
{
margin:0px;padding:0px;}
#yiv967091284 .yiv967091284hmmessage
{
font-size:10pt;font-family:Tahoma;}
--></style>
<div dir="ltr">
<div><p class="yiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; margin: 0.2in 0in; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);">Hi All,</span></b></p><p class="yiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; margin: 0.2in 0in; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);"><br></span></b></p>
<p class="yiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; margin: 0.2in 0in; 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 ! important;"><span style="text-indent: 0px ! important; 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 ! important;"><span style="text-indent: 0px ! important; 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 ! important;"><span style="text-indent: 0px ! important; 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="yiv967091284MsoNormal" style="margin: 0.2in 0in;"><b><font class="yiv967091284Apple-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="yiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; margin: 0.2in 0in; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);"><br></span></b></p><p class="yiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; margin: 0.2in 0in; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);">Looking fwd..</span></b></p>
<p class="yiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; margin: 0.2in 0in; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);"> </span></b></p>
<p class="yiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; margin: 0.2in 0in; line-height: normal;"><b><span style="font-size: 10pt; color: rgb(102, 102, 102);">Thanks</span></b></p>
<p class="yiv967091284MsoNormal" style="font-size: 10pt; font-family: Tahoma; margin: 0.2in 0in; 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="plainMail">_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@postgis.refractions.net" href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></blockquote></td></tr></table>