[OSM-talk] Road network length calculation

[Lennard]

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