[OSM-dev] [OSM-talk] planet analysis

Robert (Jamie) Munro rjmunro at arjam.net
Wed Jan 17 17:43:45 GMT 2007


Robert (Jamie) Munro wrote:
> To do a total length of all segments, all you need is an sql query
> something like:
> 
> select KM_TO_MI(sum(DISTANCE(a.Lon,a.Lat,b.Lon,b.Lat))
>  from current_segments
>  inner join current_nodes as a on a.id=current_segments.node_a
>  inner join current_nodes as b on b.id=current_segments.node_b
> 
> (with the DISTANCE and KM_TO_MI functions coming from
> http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/, which is the first
> library I found that does it).

I've actually implemented the above using the data found at:
http://nick.dev.openstreetmap.org/downloads/planet/uk-061112.osm.bz2
(so these numbers are UK only and a couple of months out of date)

The final query I used was:

select
sum(ellipsoid_distance(a.longitude,a.latitude,b.longitude,b.latitude))
 from segments
 inner join nodes as a on a.id=segments.node_a
 inner join nodes as b on b.id=segments.node_b

which returns 112076km in only 2m52s.

It's also easy to restrict the query by adding a where clause with a
subquery in it. E.g. to find the length of segments that are part of at
least one way:

 where segments.id in (select segment_id from way_segments);

Note using a subquery like this doesn't duplicate segments that are part
of more than one way.

This returns 75315km, so we have 36761km of unwayed segments, which is
nearly 1/3rd of them. The query still only took 3m30s to run.

Summary in the UK there are:
Segments:                  112076km
Segments in ways:           75315km
Segments not in ways:       36761km
Highway tagged ways:        44683km
Non-highway tagged ways:    30632km
Ways with highway and name: 18534km

If I were Steve, I would say "In the UK we have about 80,000km (50,000
miles of road) of which about half has been tagged and shows on our main
maps".

For comparison, see:
http://mapzone.ordnancesurvey.co.uk/mapzone/didyouknow/howmany/q_14_64.html

motorways -                4 353 km   (2 705.41 miles)
A Roads -                 48 164 km  (29 934.12 miles)
B Roads -                 30 216 km  (18 779.37 miles)
minor public roads -     314 392 km (195 395.89 miles)
pedestrianised streets -     278 km     (172.78 miles)

Total:                   397 403 km (246 934.77 miles)

Of course, this doesn't include dual carriageways twice, like our
numbers do, but that probably only adds 30 000km or so (I'm guessing all
the motorways and approx half the A roads).

Robert (Jamie) Munro

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 249 bytes
Desc: OpenPGP digital signature
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20070117/49621149/attachment.pgp>


More information about the dev mailing list