NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

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
76281523
accept rate: 0%

edited 31 Oct '14, 23:21

2
(01 Nov '14, 06:39) mmd

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.

permanent link

answered 01 Nov '14, 00:39

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k927201273
accept rate: 23%

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×134
×18
×16

question asked: 31 Oct '14, 23:18

question was seen: 6,349 times

last updated: 01 Nov '14, 06:41

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum