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
_______________________________________________
talk mailing list
talk@openstreetmap.org
http://lists.openstreetmap.org/listinfo/talk
