Re: [postgis-users] converting to lat long
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 wrote:
From: Yamini Singh
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: yaminijsi...@live.com
You are welcome :-)
Your turn to help other newcomers as they ask questions you can answer!
Cheers,
Brent
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
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 wrote:
From: Yamini Singh
Subject: RE: [postgis-users] converting to lat long
To: pcr...@pcreso.com
Cc: "PostGIS User List"
Date: Thursday, July 14, 2011, 11:24 PM
Hi Bret,
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 ‘-20’ 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: yaminijsi...@live.com
CC: postgis-users@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 add column lat_dms int;
update 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
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi Bret,
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 ‘-20’ 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: yaminijsi...@live.com
CC: postgis-users@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 add column lat_dms int;update 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
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Could the list admin kindly send this gentleman really on vacation please? On Jul 13, 2011, at 12:48 PM, David Kaplan wrote: > Hi, > > I will be on vacation with limited email from July 14 to August 7, 2011. > > Bonjour, > > Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
Hi, I will be on vacation with limited email from July 14 to August 7, 2011. Bonjour, Je serai en conge du 14 juillet jusqu'au 7 aout, 2011. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
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 add column lat_dms int;
update 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 wrote:
From: Yamini Singh
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...
RegardsYamini
----------------------
Date: Tue, 12 Jul 2011 10:28:35 -0700
From: pcr...@pcreso.com
Subject: Re: [postgis-users] converting to lat long
To: postgis-users@postgis.refractions.net
CC: yaminijsi...@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 wrote:
From: Yamini Singh
Subject: [postgis-users] converting to lat long
To: "PostGIS User List"
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-
__
Re: [postgis-users] converting to lat long
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 wrote:
From: Yamini Singh
Subject: [postgis-users] converting to lat long
To: "PostGIS User List"
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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
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 On 12/07/2011, at 8:58 PM, Yamini Singh wrote: > 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 > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
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 wrote: > *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* > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
I would suggest that first you check your data has a consistent format, i.e. "zero" is represented as , 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 From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of John Callahan Sent: Tuesday, July 12, 2011 9:13 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] converting to lat long 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 On Tue, Jul 12, 2011 at 8:58 AM, Yamini Singh mailto:yaminijsi...@live.com>> wrote: 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 ___ postgis-users mailing list postgis-users@postgis.refractions.net<mailto:postgis-users@postgis.refractions.net> http://postgis.refractions.net/mailman/listinfo/postgis-users 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.___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
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(, ,
, , )
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
On 12 July 2011 13:58, Yamini Singh wrote:
> 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
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] converting to lat long
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 On Tue, Jul 12, 2011 at 8:58 AM, Yamini Singh wrote: > *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* > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] converting to lat long
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___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
