[postgis-users] converting to lat long

[pcreso at pcreso.com]

Glad it helped.

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.

alter table <tablename> add column lat_dms int;
update <tablename> set lat_dms=('-'||substr(geocode,1,4)||'00')::int;

The "||" operator is a string concatenation operator, so the sql starts with '-', appends the 
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.

Then do the same for lon - but remember to substr(geocode,6,4) instead 
(or combine the sqls to do both in a single statement).

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.


Cheers,

  Brent

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

From: Yamini Singh <yaminijsingh at live.com>
Subject: RE: [postgis-users] converting to lat long
To: pcreso at pcreso.com
Date: Wednesday, July 13, 2011, 9:47 PM





Hi Brent,
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 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:
-----------------------------lat_DMS    | long_DMS  |-----------------------------		
-232900   | -472700    |------------------------
Is this possible with simple query...
RegardsYamini

--------------------------------------------------------------------------------------
Date: Tue, 12 Jul 2011 10:28:35 -0700
From: pcreso at pcreso.com
Subject: Re: [postgis-users] converting to lat long
To: postgis-users at postgis.refractions.net
CC: yaminijsingh at live.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




#yiv159434206 .yiv159434206ExternalClass #yiv159434206ecxyiv967091284 .yiv159434206ecxyiv967091284hmmessage P
{padding:0px;}
#yiv159434206 .yiv159434206ExternalClass #yiv159434206ecxyiv967091284 .yiv159434206ecxyiv967091284hmmessage
{font-size:10pt;font-family:Tahoma;}



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/20110713/f7590c7d/attachment.html>