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?
----- Original Message ---- From: "Obe, Regina" <[EMAIL PROTECTED]> To: PostGIS Users Discussion <[email protected]> Sent: Wednesday, 18 July, 2007 9:38:29 PM Subject: RE: [postgis-users] Transforming Coordinates DIV { MARGIN:0px;} 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. Would be something like this CREATE VIEW vwbusstopsgoogle As select x(transform(east_north, 4326)) As lon, y(transform(east_north,4326)) As lat, [other fields you need go here] FROM bus_stops1 The to use SELECT lon, lat from vwbusstopsgoogle To transform back it would be INSERT INTO bus_stops1(east_north) values(transform(setsrid(makepoint([lon],[lat]), 4326), 27700)) WHERE [lon], [lat] you replace with the x y you get from google. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alan Cunnane Sent: Wednesday, July 18, 2007 4:28 PM To: PostGIS Users Discussion Subject: [postgis-users] Transforming Coordinates Hi guys 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: SELECT AddGeometryColumn( 'bus_stops1', 'east_north', 27700, 'POINT', 2 ); UPDATE bus_stops1 SET east_north = PointFromText('POINT(' || easting || ' ' || northing || ')',27700); The table looks like this at the moment: easting | northing | east_north ---------+----------+---------------------------------------------------- 226965 | 676038 | 0101000020346C000000000000A8B40B41000000008CA12441 226761 | 675945 | 0101000020346C00000000000048AE0B4100000000D2A02441 226696 | 675893 | 0101000020346C00000000000040AC0B41000000006AA02441 226473 | 675777 | 0101000020346C00000000000048A50B4100000000829F2441 226465 | 675731 | 0101000020346C00000000000008A50B4100000000269F2441 226614 | 675611 | 0101000020346C000000000000B0A90B4100000000369E2441 226603 | 675605 | 0101000020346C00000000000058A90B41000000002A9E2441 226869 | 675621 | 0101000020346C000000000000A8B10B41000000004A9E2441 226883 | 675630 | 0101000020346C00000000000018B20B41000000005C9E2441 226544 | 674911 | 0101000020346C00000000000080A70B4100000000BE982441 226767 | 675293 | 0101000020346C00000000000078AE0B4100000000BA9B2441 226767 | 675303 | 0101000020346C00000000000078AE0B4100000000CE9B2441 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? 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 Yahoo! Mail is the world's favourite email. Don't settle for less, sign up for your free account today. 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. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users ___________________________________________________________ Yahoo! Mail is the world's favourite email. Don't settle for less, sign up for your free account today http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter07.html
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
