[OSM-dev] Projection problems

[Jon Burgess]

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