[OSM-dev] OSM data import using osm2pgsql... what's next.

[Stephan Bösch-Plepelits]

On Sat, Aug 02, 2014 at 11:52:58AM +0100, Éric Gillet wrote:
> > On Sat, Aug 2, 2014 at 9:52 AM, Christian Quest <cquest at openstreetmap.fr>
> > wrote:
> >> By default osm2pgsql reprojects into web mercator, but you can avoid it
> >> and ask to keep the spherical coordinates if this is a better choice for
> >> your use.
> May I ask how to use spherical coordinates ? I've hit the problem about
> distances being distorded with projections, and never managed to do it
> right with osm2pgsql based db.
Distance between Berlin and London:

select ST_Distance(Geography(ST_GeometryFromText('POINT(13.3888598981363
52.517036494754)')), Geography(ST_GeometryFromText('POINT(-0.127647399982231 51.5073218947764)')))
-> 933410.764104169 m

If you have the coordinates in spherical mercator, you need to reproject
them to WGS-84, using:

select name, way from planet_osm_point where osm_id='240109189'; -- Berlin
-> Berlin | 010100002031BF0D001C0FF01009BE3641B969376A934C5A41

select name, ST_astext(way) from planet_osm_point where osm_id='240109189';
-> Berlin | POINT(1490441.06616301 6894157.65963214)

select name, ST_astext(ST_Transform(way, 4326)) from planet_osm_point where osm_id='240109189';
-> Berlin | POINT(13.3888598981363 52.517036494754)

select name, ST_astext(Geography(ST_Transform(way, 4326))) from planet_osm_point where osm_id='240109189';
-> Berlin | POINT(13.3888598981363 52.517036494754)
(same as before, but can be used to make projected calculations)

greetings,
	Stephan
-- 
Seid unbequem, seid Sand, nicht Öl im Getriebe der Welt! - Günther Eich
,---------------------------------------------------------------------.
| Stephan Bösch-Plepelits,                                            |
| Technische Universität Wien   -    Studien Informatik & Raumplanung |
| Projects:                                                           |
| > openstreetbrowser.org > couchsurfing.org > tubasis.at > bl.mud.at |
| Contact:                                                            |
| > Mail: skunk at xover.mud.at > Blog: plepe.at                         |
| > Twitter: twitter.com/plepe > Jabber: skunk at jabber.at              |
`---------------------------------------------------------------------'