This is a static archive of our old OpenStreetMap Help Site. Please post any new questions and answers at community.osm.org.

If I want to calculate total way length, is this query a good way to do it?

2

Hi,

If I wanted to calculate the total length of highway ways in OSM, would this query be a good way to achieve that if I have an osmosis (snapshot schema) database with linestring extension?

select tags->'highway', sum(st_length(linestring::geography)) from ways where tags?'highway' group by tags->'highway' order by sum desc;

Here are the top results for a small (Utah, USA) database:

?column? | sum -----------------------+------------------ residential | 77732401.8750182 track | 53492052.9473808 service | 6386628.52935351 tertiary | 4980880.41582732 unclassified | 4854814.04812725 secondary | 4620784.71714738 primary | 4097059.7290093 path | 3771029.8775133 motorway | 3259385.13873852 footway | 1672679.77904645

asked 31 Oct '14, 23:18

mvexel's gravatar image

mvexel
762●8●15●23
accept rate: 0%

edited 31 Oct '14, 23:21

2

This thread may be an interesting read:

http://gis.stackexchange.com/questions/63762/why-is-sum-of-st-length-of-segments-20-too-big

(01 Nov '14, 06:39) mmd

One Answer:

2

It certainly does look like it's working ;) If the reason you're doing this is that you want to compare with existing statistics, remember that your query will count the total way length in OSM, whereas official statistics will occasionally throw together both carriageways of a dual-carriageway road, yielding a smaller total length.

answered 01 Nov '14, 00:39

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k●92●720●1273
accept rate: 23%

Source code available on GitHub .