[OSM-dev] Projection problems
Jon Burgess
jburgess777 at googlemail.com
Tue Dec 16 21:18:10 GMT 2008
On Tue, 2008-12-16 at 16:56 +0000, Bradley Kite wrote:
> The Lat/Long I am using is (bounding box around a street I know):
>
> -0.90362548828125,51.38892310050485 to
> -0.90087890625,51.38720911696496
>
> However, when converted to Mercator projection as per the above Java
> code, I get the following:
>
> -100591.12922329194,6656970.686752424 to
> -100285.38111015123,6656665.738991335
>
> The y values (6656970 - 6656665) do not match up with data the
> postgres database:
>
> LINESTRING(-100491.52 6690303.05,-100474.32 6690280.11,-100456.16
> 6690259.1,-100438.02 6690235.21,-100434.02 6690222.73,-100430.37
> 6690211.33,-100433.23 6690176.94,-100434.19 6690146.36,-100432.48
> 6690134,-100430.37 6690118.64,-100417.95 6690093.81,-100398.83
> 6690072.78,-100373.27 6690047.38,-100370.16 6690038.6,-100327.34
> 6689918.14,-100309.98 6689869.27,-100245.01 6689884.55)
>
> Note - all the Y values in the DB are ~6690000 not ~6650000 as
> returned by the mercY() function.
>
> The effect of this is that an SQL query to pull data from the database
> is not fetching the correct data.
>
> Would there happen to be an expert on this list that help out with the
> accuracy of the projection code at the above link? It only affects the
> Y coordinate, X seems fine.
A different approach is to add the projection into postgis[1] and then
get it to perform the translation for you.
gis=> select astext(transform(setSRID('BOX3D(-0.90362548828125 51.38892310050485,-0.90087890625 51.38720911696496)'::box3d,4326),900913));
astext
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POLYGON((-100591.129223292 6690074.46363176,-100591.129223292
6690380.30887971,-100285.381110152 6690380.30887971,-100285.381110152
6690074.46363176,-100591.129223292 6690074.46363176))
(1 row)
Or combine into a query:
gis=> select osm_id,name,highway from planet_osm_line where way &&
transform(setSRID('BOX3D(-0.90362548828125 51.38892310050485,-0.90087890625 51.38720911696496)'::box3d,4326),900913);
osm_id | name | highway
----------+-----------------------------+--------------
4680773 | Whitewater Stream |
4413806 | Langley Common Road | tertiary
-29509 | Round Berkshire Cycle Route |
28628603 | Baird Road | unclassified
4413805 | | primary
4413804 | | primary
28320636 | Baird Road | unclassified
28334054 | Bramshill Road | unclassified
28320706 | Valon Road | residential
28371170 | Valon Road | residential
28371187 | Valon Road | residential
28379459 | Hill Road | residential
Jon
1: http://wiki.openstreetmap.org/wiki/Mercator#PostGIS_.2F_SQL
More information about the dev
mailing list