[postgis-users] converting to lat long

Yamini Singh

unread,

Jul 12, 2011, 8:58:35 AM7/12/11

to PostGIS User List

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

John Callahan

unread,

Jul 12, 2011, 9:12:30 AM7/12/11

to PostGIS Users Discussion

Looks like you already have lat/long coordinates and don't need to use ST_Transform. Make sure you know if the original values are in 'decimal degrees' or 'degrees minutes seconds'. If in DD, just divide your values by 100 which should be easy in SQL. If in DMS, then you need to parse the data first. I'm not sure how to do that in SQL but sure it's possible.

- John

_______________________________________________
postgis-users mailing list
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

James David Smith

unread,

Jul 12, 2011, 9:12:57 AM7/12/11

to PostGIS Users Discussion

YJ,

I'm a beginner, so take with a pinch of salt, but first I'd question
why you want/need to separate the latitude & longitude?

I would keep them together and store them as a point in a new column.
First create a column to store the Lat & Long in with something like
this:

SYNTAX: SELECT AddGeometryColumn(<table_name>, <column_name>,
<srid>, <type>, <dimension>)
EXAMPLE: SELECT AddGeometryColumn('parks', 'park_geom','4326', 'POINT', 2 );

Then you want to populate that column, with the existing data from
your geocode column. Using something like this:

UPDATE tablename
SET latlongcolumn = ST_GeomFromText('geocode', 4326)

I guess that there is a way to pull out the lat and long into seperate
columns, but I don't know how to do that I'm afraid.

Cheers

James

Gheorghiu, Mihai

unread,

Jul 12, 2011, 9:17:38 AM7/12/11

to PostGIS Users Discussion

I would suggest that first you check your data has a consistent format, i.e. “zero” is represented as 0000, 12.3 degrees is represented as 1230 and 1.23 degrees is represented as 0123.

Otherwise you need to do a little preparation work…

HTH,

Michael

The information contained in this transmission is to be considered CONFIDENTIAL and PROPRIETARY to Consortium Health Plans, Inc. and intended for the use of the Individual or Entity named above. If the reader of this message is not the Intended Recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is Strictly Prohibited. If you have received this transmission in error, please notify us immediately by telephone at 410-772-2900 or return email to sender immediately. Thank You.

MarkW

unread,

Jul 12, 2011, 9:18:50 AM7/12/11

to PostGIS Users Discussion

Just to add to the other good advice you've already received:

If the data are in the format with an actual slash, as a single string (not sure from your email), then string functions can help you get to the point where you can divide, or create geometry, if that's where you're at:
http://www.postgresql.org/docs/8.4/interactive/functions-string.html

Mark

On Tue, Jul 12, 2011 at 8:58 AM, Yamini Singh <yamini...@live.com> wrote:

Ben Madin

unread,

Jul 12, 2011, 10:22:54 AM7/12/11

to PostGIS Users Discussion

FWIW - PostGIS uses long lat format...

If you are confident about the data consistency, this should be a pretty straightforward task - if you are not sure, it might still be quite simple if you are prepared to look into the mystical world of regular expressions. PostgreSQL offers support for these on the previously recommended string expressions page, but you will also need to look at the pattern matching page.

If you haven't ever looked at regular expressions, there are a number of very good resources available on the web. They are annoyingly useful, so worth investing some time in learning about them.

cheers

Ben

pcr...@pcreso.com

unread,

Jul 12, 2011, 1:28:35 PM7/12/11

to PostGIS Users Discussion, yamini...@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 <yamini...@live.com> wrote:

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

pcr...@pcreso.com

unread,

Jul 13, 2011, 1:46:17 PM7/13/11

to Yamini Singh, postgi...@postgis.refractions.net

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 <yamini...@live.com> wrote:

From: Yamini Singh <yamini...@live.com>
Subject: RE: [postgis-users] converting to lat long
To: pcr...@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...

Regards
Yamini

--------------------------------------------------------------------------------------
Date: Tue, 12 Jul 2011 10:28:35 -0700
From: pcr...@pcreso.com
Subject: Re: [postgis-users] converting to lat long
To: postgi...@postgis.refractions.net
CC: yamini...@live.com

David Kaplan

unread,

Jul 13, 2011, 1:46:34 PM7/13/11

to pcr...@pcreso.com, PostGIS Users Discussion

Hi,

unread,

Jul 13, 2011, 1:48:23 PM7/13/11

to PostGIS Users Discussion

Mr. Puneet Kishor

Thanks for your help. I am now able to update lat and long column as explained by you.

Sometimes I attributes like ‘0002N/5155’ in geocode column. Now in the lat_dms column the attribute should be ‘000200’ and ‘-515500’in long_dms column. But how do I get the ‘N’ in ‘lat’ part recognized in query so that it is not placed as ‘-000020’ but as positive coordinate. Also, the number of character will get changes in ‘lat’ it will now be 1 to 5 character and ‘7 to 10’ in long.

Is there a possibility of having one query that takes care of ‘geocode’ in 4 x 4 format as well as 5 x 4 ----format with fifth word as N?

+-----------------------------+

| Lat_dms | lat_dms |

-------------------------------

| -232900 | -472700 |

-------------------------------

| 000200 | -515500 |

+-----------------------------+

Thanks for your help.. actually I am novice to GIS and postgres world….

Thanks,

YJ

Date: Wed, 13 Jul 2011 10:46:17 -0700
From: pcr...@pcreso.com
Subject: RE: [postgis-users] converting to lat long
To: yamini...@live.com
CC: postgi...@postgis.refractions.net

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

pcr...@pcreso.com

unread,

Jul 14, 2011, 2:23:31 PM7/14/11

to Yamini Singh, PostGIS User List

Hi,

Yes you can, you use the case statement.

Here is an example:

create table ttt (id serial primary key,
                  geocode varchar(12),
lat int,
lon int);
insert into ttt values (default, '1234/5678', null, null);
insert into ttt values (default, '1234N/5678', null, null);
update ttt set lat=(case when substr(geocode,5,1)='N' then
                    (substr(geocode,1,4)||'00')::decimal(6,2) else
                    ('-'||substr(geocode,1,4)||'00')::decimal(6,2)
                    end);
select * from ttt;
id | geocode   |   lat   | lon
----+------------+---------+-----
1 | 1234N/5678 | 123400 |
2 | 1234/5678 | -123400 |

Note you will also need to use a similar case statement for lon, as the substr values will change due to the extra character offset, as below

update ttt set lon=(case when substr(geocode,5,1)='N' then
                     ('-'||substr(geocode,7,4)||'00')::int else
                     ('-'||substr(geocode,6,4)||'00')::int end);

select * from ttt;
id | geocode   |   lat   |   lon
----+------------+---------+---------
1 | 1234N/5678 | 123400 | -567800
2 | 1234/5678 | -123400 | -567800

HTH,

Brent

--- On Thu, 7/14/11, Yamini Singh <yamini...@live.com> wrote:

pcr...@pcreso.com

unread,

Jul 15, 2011, 4:20:16 PM7/15/11

to Yamini Singh, postgi...@postgis.refractions.net

Not quite.

You need to reproject the lat long coords to 29101 before writing them to your table.

Your first SQL creating the column is fine, the second update needs changing.

UPDATE tablename SET geom_col = st_transform(st_setsrid(makepoint(long_dd,lat_dd),4326),29101);

this:
creates a point from two numbers (makepoint)
sets this to lat/long (setsrid to 4326)
reprojects to 29101 (st_transform to 29101)
updates each gem_col with this value

Cheers,

Brent

--- On Sat, 7/16/11, Yamini Singh <yamini...@live.com> wrote:

From: Yamini Singh <yamini...@live.com>
Subject: RE: [postgis-users] converting to lat long
To: pcr...@pcreso.com

Date: Saturday, July 16, 2011, 5:39 AM

Yes Bret, at-least on the same topic i can do that for novices ....

Now i have 'geom' column with SRID 4326 generated from two columns 'lat_dd' and 'long_dd' which since i dont know how to convert individual column lat_dd and long_dd to SRID 29101 i use query to directly generate geometry.
First i create geometry column using:

SELECT addgeometrycolumn('tablename', 'geom_col', '29101', 'POINT', 2);

then i generate geometry directly using sql below (if i understand correctly that if the geometry col (i.e., geom_col) is set to SRID 29101 then even if i use lat_dd and long_dd i should get geometry which is as per the SRID 29101 and not 4326)

UPDATE tablename SET geom_col = st_setsrid(makepoint(long_dd,lat_dd),29101);

hope this is correct.

Regards,
YJ

Date: Thu, 14 Jul 2011 23:54:48 -0700

From: pcr...@pcreso.com
Subject: RE: [postgis-users] converting to lat long
To: yamini...@live.com

You are welcome :-)

Your turn to help other newcomers as they ask questions you can answer!

Cheers,

Brent

Reply all

Reply to author

Forward