[OSM-talk] OSM2PQSQL / PostGis: Coordinate Conversion

[Jon Burgess]

On Wed, 2010-01-13 at 20:39 +0000, Jon Burgess wrote:
> On Wed, 2010-01-13 at 22:45 +0300, Alexander Menk wrote:
> > Hi!
> > 
> > how can I translate the coordinate from the database to "normal" GPS 
> > coordinates as they are used by OpenLayers etc.
> > 
> > 
> > SELECT ST_Transform(lat,4326) FROM planet_osm_nodes
> > 
> > ERROR:  function st_transform(double precision, integer) does not exist
> 
> Something like this should work:
> 
> select
> astext(ST_Transform(ST_SetSRID(ST_MakePoint(lon/100,lat/100),900913),4326)) from planet_osm_nodes;
> 
>                    astext
> --------------------------------------------
>  POINT(-0.233660788552329 51.6420473016351)
>  POINT(-0.323177906614839 51.6455034823677)
>  POINT(-0.490731673408812 51.6320228876355)
>  POINT(-0.415551667280849 51.6282701316099)

To improve the accuracy you should change the factor of 100 to 100.0,
this will force postgres to perform the calculation using floating
point.

select
id,astext(ST_Transform(ST_SetSRID(ST_MakePoint(lon/100.0,lat/100.0),900913),4326)) from planet_osm_nodes limit 10;
 id  |                   astext
-----+--------------------------------------------
  -1 | POINT(-0.233669322547528 51.6420490297913)
  -2 | POINT(-0.323178535435538 51.6455051546494)
  -3 | POINT(-0.490739848077898 51.6320247834516)

In case you are wondering, the factor of 100 allows osm2pgsql to store
the nodes positions using 4 byte ints instead of needing to use a double
which needs 8 bytes. This makes a real difference when storing 600
million nodes.

	Jon