Jukka Rahkonen wrote:
> For example, I use to import osm data into postgis and transform it it Finnish
> kkj projection. Now I can get the length (in meters) of all "highway" lines of
> the Finland.osm with query
> postgis=# select sum(length2d(way)) from osm_line where highway is not null;
> -----------------
> 120547380.61163
> (1 row)
>
> PostGIS gives much flexibility with queries, for example the length of unnamed
> unclassified highways in epsg:3067 projection
>
> postgis=# select sum(length2d(way2)) from
> (select transform(way,3067) as way2 from osm_line where highway='unclassified'
> and name is null) as foo;
> ------------------
> 42116706.9329842
> (1 row)
I think most people would be interested in real distances, and not
projected distances. Here's what I came up with (with a little help):
Given your average osm2pgsql'd postgis db in 900913, and a single way:
osm=# select ST_length2d(way) as length from planet_osm_line where
osm_id=-31332;
length
------------------
3594.94120060525
(1 row)
Which gives projected distances in spherical mercator, not the real
distance.
Transforming the way to the Dutch RD grid first:
osm=# select ST_length2d(transform(way,28992)) as length from
planet_osm_line where osm_id=-31332;
length
------------------
2254.71990206912
(1 row)
1340 meters less, but very accurate for The Netherlands. Breaks down the
further you get from the Dutch grid.
osm=# select ST_length_spheroid(transform(way,4326),
'SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]]') as
length from planet_osm_line where osm_id=-31332;
length
------------------
2254.59656279346
(1 row)
Works worldwide. And the best you can get from OSM data, since it
doesn't have elevation.
--
Lennard
_______________________________________________
talk mailing list
talk@openstreetmap.org
http://lists.openstreetmap.org/listinfo/talk
