[postgis-users] converting to lat long
pcreso at pcreso.com
pcreso at pcreso.com
Tue Jul 12 10:28:35 PDT 2011
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>
More information about the postgis-users
mailing list