[postgis-users] converting to lat long

[pcreso at pcreso.com]

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.

select addgeometrycolumn('','tablename','geom',4326,'POINT',2);

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.

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.

update tablename
set geom=setsrid(makepoint(substr(geocode,6,4)::decimal(6,2)/100,
                           substr(geocode,1,4)::decimal(6,2)/100),
                 4326);

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.

If you want to see the resulting lat/long values now stored in the geom column, try:

select geocode, astext(geom) from tablename;
or
select geocode, y(geom) as lat, x(geom) as lon from tablename; 


HTH,

  Brent Wood

--- On Wed, 7/13/11, Yamini Singh <yaminijsingh at live.com> wrote:

From: Yamini Singh <yaminijsingh at live.com>
Subject: [postgis-users] converting to lat long
To: "PostGIS User List" <postgis-users at postgis.refractions.net>
Date: Wednesday, July 13, 2011, 12:58 AM





Hi All,


I have a column 'geocode' in a table which has
attributes like 2329/4727 now I would like to convert these attributes in another
column to 'lat' and 'long'. For example, 'Lat' column will have attribute '23.29' and 'long' column will have attribute  '47.27'.

Is there a way through which this can be
converted automatically by a query or so.
Looking fwd..

   

Thanks 

YJ 		 	   		   

-----Inline Attachment Follows-----

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110712/e4adc615/attachment.html>