<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;">Hey Regina<br><br>That all makes perfect sense now thanks so much for your help,<br><br>Alan<br><br><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;">----- Original Message ----<br>From: "Obe, Regina" <robe.dnd@cityofboston.gov><br>To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>Sent: Thursday, 19 July, 2007 10:06:08 PM<br>Subject: RE: [postgis-users] Transforming Coordinates<br><br>
<style type="text/css">DIV {
MARGIN:0px;}
</style>
<div dir="ltr" align="left"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2">Alan,</font></span></div>
<div dir="ltr" align="left"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2">Sorry I didn't see this email until now. Still not
quite clear your question, so hopefully the explanation below will
help.</font></span></div>
<div dir="ltr" align="left"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2">1) You can either statically store the values in the table
doing</font></span></div>
<div dir="ltr" align="left"><span class="395575820-19072007"> <font color="#0000ff" face="Arial" size="2">UPDATE bus_stops1 SET xcoord =
x(transform(east_north, 4326)), ycoord =
y(transform(east_north,4326))</font></span></div>
<div dir="ltr" align="left"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2">where xcoord and ycoord are numeric or float fields you
create to store the values.</font></span></div>
<div dir="ltr" align="left"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2">Then for inserting you would do</font></span></div>
<div dir="ltr" align="left"><span class="395575820-19072007">
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2">INSERT INTO bus_stops1(east_north<span class="395575820-19072007">, xcoord, ycoord</span>)</font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2">
values(transform(setsrid(makepoint([lon],[lat]), 4326), 27700)<span class="395575820-19072007">, [lon], [lat]</span>)</font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2">where [lon] and
[lat] are the x and y coords you get from google map.</font></span></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2"></font></span></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2">2) Or
alternatively as I mentioned you would create a view and for most purposes
except for inserting ignore your original table. This is a bit slower, but
I don't think significantly slower.</font></span></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2"></font></span></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2">---Now some
explanation</font></span></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2"></font></span></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2">x is a function
that returns the x coordinate of a postgis point, y is a function that returns
the y coordinate of a postgis point geometry</font></span></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2"></font></span></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2">So what I am
doing here is first transforming to WGS 84 and then getting the x and y.
Since the dimensions of the transformed geometry are in degrees for 4326, then
the x and y returned will be in degrees as well.</font></span></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2"></font></span></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2">Hope that
helps,</font></span></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><span class="395575820-19072007"><font color="#0000ff" face="Arial" size="2">Regina</font></span></span></div></span></div><br>
<div class="OutlookMessageHeader" dir="ltr" align="left" lang="en-us">
<hr tabindex="-1">
<font face="Tahoma" size="2"><b>From:</b>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Alan
Cunnane<br><b>Sent:</b> Wednesday, July 18, 2007 4:49 PM<br><b>To:</b> PostGIS
Users Discussion<br><b>Subject:</b> Re: [postgis-users] Transforming
Coordinates<br></font><br></div>
<div></div>
<div style="font-size: 12pt; font-family: times new roman,new york,times,serif;">
<div style="font-size: 12pt; font-family: times new roman,new york,times,serif;">What
I need to do is create a new column in the bus_stops1 table with x and x
coordinates. If I use your suggestion below to transform the column east_north
to x and y wont both the new X and Y columns be the same value as it is the same
query?<br><br><br>
<div style="font-size: 12pt; font-family: times new roman,new york,times,serif;">-----
Original Message ----<br>From: "Obe, Regina"
<robe.dnd@cityofboston.gov><br>To: PostGIS Users Discussion
<postgis-users@postgis.refractions.net><br>Sent: Wednesday, 18 July, 2007
9:38:29 PM<br>Subject: RE: [postgis-users] Transforming Coordinates<br><br>
<style type="text/css">DIV {
MARGIN:0px;}
</style>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2">Not quite sure what you are asking. For this kind of
stuff, I usually create a view and use that for google maps if I have a lot of
apps that need it or you could alternatively just write the sql of the view each
time you need it. </font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2">Would be something like this</font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2">CREATE VIEW vwbusstopsgoogle As</font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2"> select x(transform(east_north, 4326)) As
lon, y(transform(east_north,4326)) As lat, [other fields you need go
here]</font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"> <font color="#0000ff" face="Arial" size="2">FROM bus_stops1</font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2">The to use</font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2">SELECT lon, lat from vwbusstopsgoogle</font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2">To transform back it would be</font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2">INSERT INTO bus_stops1(east_north)</font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2">
values(transform(setsrid(makepoint([lon],[lat]), 4326),
27700))</font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2">WHERE [lon], [lat] you replace with the x y you get from
google.</font></span></div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="541583120-18072007"><font color="#0000ff" face="Arial" size="2"></font></span> </div><br>
<div class="OutlookMessageHeader" dir="ltr" align="left" lang="en-us">
<hr tabindex="-1">
<font face="Tahoma" size="2"><b>From:</b>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Alan
Cunnane<br><b>Sent:</b> Wednesday, July 18, 2007 4:28 PM<br><b>To:</b> PostGIS
Users Discussion<br><b>Subject:</b> [postgis-users] Transforming
Coordinates<br></font><br></div>
<div></div>
<div style="font-size: 12pt; font-family: times new roman,new york,times,serif;">
<div>Hi guys<br><br>I have a table with easting northing coordinates and these
points in a geom column. I have 15,000 points with an easting and a northing
(brittish national grid) and created the geom column using these
commands:<br><br>SELECT AddGeometryColumn( 'bus_stops1', 'east_north', 27700,
'POINT', 2 );<br>UPDATE bus_stops1 SET east_north = PointFromText('POINT(' ||
easting || ' ' || northing || ')',27700);<br><br>The table looks like this at
the moment:<br><br> easting | northing
|
east_north<br>---------+----------+----------------------------------------------------<br>
226965 | 676038 |
0101000020346C000000000000A8B40B41000000008CA12441<br> 226761
| 675945 |
0101000020346C00000000000048AE0B4100000000D2A02441<br> 226696
| 675893 |
0101000020346C00000000000040AC0B41000000006AA02441<br> 226473
| 675777 |
0101000020346C00000000000048A50B4100000000829F2441<br> 226465
| 675731 |
0101000020346C00000000000008A50B4100000000269F2441<br> 226614
| 675611 |
0101000020346C000000000000B0A90B4100000000369E2441<br> 226603
| 675605 |
0101000020346C00000000000058A90B41000000002A9E2441<br> 226869
| 675621 |
0101000020346C000000000000A8B10B41000000004A9E2441<br> 226883
| 675630 |
0101000020346C00000000000018B20B41000000005C9E2441<br> 226544
| 674911 |
0101000020346C00000000000080A70B4100000000BE982441<br> 226767
| 675293 |
0101000020346C00000000000078AE0B4100000000BA9B2441<br> 226767
| 675303 |
0101000020346C00000000000078AE0B4100000000CE9B2441<br><br><br>Now what I want to
be able to do is to add another column or two columns with X and Y values in WGS
or the standard X and Y coordinates for entry into google maps. I dont want to
transform the east_north column so that I get another long string as is shown
above as I cannot then integrate them into google maps. Can this be done using
PostgreSQL?<br><br>Also I would like to do the opposite too. Take x and y
coordinates from google maps and transform them to easting northing as above.
Thanks for your help again<br><br></div></div><br>
<hr size="1">
Yahoo! Mail is the world's favourite email. Don't settle for less, <a rel="nofollow" target="_blank" href="http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter07.html">sign up for your free account today</a>.
<p></p>
<hr size="1">
<p></p>
<p><strong>The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure pursuant to
Massachusetts law. It is intended solely for the addressee. If you received this
in error, please contact the sender and delete the material from any computer.
</strong></p>
<div>_______________________________________________<br>postgis-users mailing
list<br>postgis-users@postgis.refractions.net<br><a rel="nofollow" target="_blank" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></div><br></div></div><br>
<hr size="1">
Yahoo! Mail is the world's favourite email. Don't settle for less, <a rel="nofollow" target="_blank" href="http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter07.html">sign
up for your free account today</a>.<div>_______________________________________________<br>postgis-users mailing list<br>postgis-users@postgis.refractions.net<br><a target="_blank" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></div><br></div></div><br>
<hr size=1>
Yahoo! Mail is the world's favourite email. Don't settle for less, <a
href="http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter07.html">sign up for your free
account today</a>.</body></html>