# [postgis-users] Transforming Coordinates

Alan Cunnane alan_cunnane at yahoo.co.uk
Thu Jul 19 14:09:53 PDT 2007

```Hey Regina

That all makes perfect sense now thanks so much for your help,

Alan

----- Original Message ----
From: "Obe, Regina" <robe.dnd at cityofboston.gov>
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Sent: Thursday, 19 July, 2007 10:06:08 PM
Subject: RE: [postgis-users] Transforming Coordinates

DIV {
MARGIN:0px;}

Alan,

Sorry I didn't see this email until now.  Still not
quite clear your question, so hopefully the explanation below will
help.

1) You can either statically store the values in the table
doing

UPDATE bus_stops1 SET xcoord =
x(transform(east_north, 4326)), ycoord =
y(transform(east_north,4326))

where xcoord and ycoord are numeric or float fields you
create to store the values.

Then for inserting you would do

INSERT INTO bus_stops1(east_north, xcoord, ycoord)

values(transform(setsrid(makepoint([lon],[lat]), 4326), 27700), [lon], [lat])

where [lon] and
[lat] are the x and y coords you get from google map.

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.

---Now some
explanation

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

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.

Hope that
helps,

Regina

From:
postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Alan
Cunnane
Sent: Wednesday, July 18, 2007 4:49 PM
To: PostGIS
Users Discussion
Subject: Re: [postgis-users] Transforming
Coordinates

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"
<robe.dnd at cityofboston.gov>
To: PostGIS Users Discussion
<postgis-users at postgis.refractions.net>
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:
postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 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
postgis-users at postgis.refractions.net
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._______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

___________________________________________________________
Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
now.
http://uk.answers.yahoo.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070719/94ba0628/attachment.html>
```

More information about the postgis-users mailing list