Hi, I am currently working on a research project where we need to compile a list of all the street names in several countries. I have two questions:
How well-covered are european countries in terms of street names? I have tried using an xslt tool to extract the “addr:street” tag in a few periodic osm extracts. For Portugal, I was only left with a list of under 500 street names.
Are you able to provide me with a list of the street names, sorted by town/city?
Many thanks for your support. I have spent a lot of time trying to extract the relevant information and would be very grateful for any help.
For your first point, the “addr:street” tag is only used for addresses for houses/buildings etc. Some places don’t have many addresses mapped, and some addresses might just have the housename/number, not the street name.
To get all of the streetnames, you need to look at the “name” tag. But the name tag is used for all sorts of things, not just streets.
To just get streets, you will need to extract all of the ways tagged as highway=primary, highway=secondary, highway=tertiary, highway=residential etc. See this page for all of the highway values: http://wiki.openstreetmap.org/wiki/Key:highway
Step 2: Open the new Spatialite database with Spatialite-gui program. Use the SQL query window for sending a query that builds a list of distinct name tags which belong to lines which have some highway tag. Use only lines which are inside a given polygon. In this simplified example the polygon is a rectangle but it can polygon of any shape. With a polygon of huge amount of vertices the query will be slow, though.
select distinct name
from lines
where highway is not null
and intersects(geometry,geomfromtext('POLYGON ((27 69,27 70,28 70,28 69,27 69))'))
order by name;
Start of the list:
NULL
E6
E6;E75
Gáregasnjárgageaidnu
Jorri
Kevon reitti
Luossatie
Miessipolku
...
OSM tagging schema indeed makes some trouble. It is possible to get rid of bridges and tunnels by adding “and bridge!=‘yes’ and tunnel!=‘yes’” into the SQL statement but there are probably many other special cases too. Free tagging schema is something that belongs to OSM and is a part of the fun. Fortunately I do not need to use OSM data at work.
If only certain highway classes are important, use “where highway in (‘residential’,‘tertiary’,‘secondary’)”.
Read about osmconf.ini from http://gdal.org/ogr/drv_osm.html if there is a need to do complicated queries. Each attribute needed for filtering must be included in osmconf.ini.
Thank you for your quick and useful feedback. I think using osmfilter and then osmconvert to compile the dataset in a csv file would be ideal. I have tried to download the osmfilter binary, but I’m not sure what to do with the exe. file. I have tried to execute the commands in a command prompt but this doesn’t seem to work. Any ideas?
for now, I typed the following code, after changing the directory to the folder containing the converter and the osm file: osmfilter.exe belgium.osm --keep=“highway=residential =primary =secondary =tertiary =unclassified”
After loading for maybe 5 minutes, I got the following message: osmfilter Error: write error.
Or try that filtering with a smaller piece of OSM data … try to load a smaller file from e.g. geofabrik.de, or load from server and save to disk a small piece with JOSM editor.
It seems like virtual memory on my laptop may be the problem - we will try to use an iMac instead, which should resolve that problem. However, it doesn’t seem like there is a Mac version of osmfilter/osmconvert. Do you know if it is possible to use a Mac for this?
Perhaps GDAL is not perfect tool for playing with OSM data, but I would still consider it because it is easy to install at least on Windows and Linux and it is quite speedy with this kind of tasks. I woud use Spatialite as a temporaty storage and export into CSV from that. I took some timing with my laptop
Import germany-latest.osm.pbf into Spatialite with command
ogr2ogr -f sqlite -dsco spatialite=yes streetnames.sqlite germany-latest.osm.pbf
-dialect sqlite -sql "select * from lines where highway in ('residential','primary','secondary',
'tertiary','unclassified')" -progress --config OGR_SQLITE_SYNCHRONOUS OFF
--config OSM_COMPRESS_NODES YES -gt 60000 -lco spatial_index=no -nln streets
Time: 6 minutes
I created one index as
CREATE INDEX streets_names_idx ON streets (name);
After that queries by street names are very fast. Some statistics
Streets imported: 2440807
Streets with some name: 1951702
Distinct names on streets: 351804
Are you really interested only in street names? Doesn’t it matter where in Germany all the “Mainstreets” are?
select name, count(*) as count
from streets
group by name
order by count desc;
name count
Hauptstraße 23010
Dorfstraße 14462
Bahnhofstraße 14339
Schulstraße 8454
Gartenstraße 7065
Bergstraße 6967
The ideal final layout would be as a list within an Excel spreadsheet of all the streetnames in a particular country, with a column for street name and one for town/region.
If you could get your municipality borders as polygons into a spatial database the task would be easy and fast. For the Finnish OSM data it takes 15 seconds to create such a list with a laptop and Spatialite (320 munifipalities, 84000 distinct streetname-municipality combinations). Basic query would be like
SELECT distinct l.name,m.municipalityname
from lines l, municipality m
where l.highway is not null
and l.name is not null
and ST_Intersects(m.geometry,l.geometry)
order by m.municipalityname,l.name;
Streetname Municipality
======= =======
Aapiskuja Akaa
Ahlajärventie Akaa
Ahopolku Akaa
Ahtolantie Akaa
Ainolantie Akaa
Ajulantie Akaa
Akaantie Akaa
...
Wessmanninkatu Äänekoski
Yhdysraitti Äänekoski
Äänejärventie Äänekoski
Äänekoskentie Äänekoski
Ääneniementie Äänekoski
This query should work as is in PostGIS but I did not test it. For making the query fast both lines and municipality tables must have spatial indexes. In addition in Spatialite the spatial queries must be written in a bit peculiar way, like here:
SELECT distinct l.name,m.municipalityname
from lines l, municipality m
where l.highway is not null
and l.name is not null
and ST_Intersects(m.geometry,l.geometry)
and l.rowid in
(select rowid from spatialindex
where f_table_name='lines'
and search_frame=(m.geometry))
order by m.municipalityname,l.name
Unfortunately the hard part is to get municipality polygons out from OSM data. I used the municipality data of the National Land Survey of Finland.
I am struggling to import the OSM file into Spatialite - could you maybe send me the link to the version that I need to download and how to get to the command window? I am also not sure about how to use the polygon system which you have pointed out. Having municipalities is not crucial, so in such a case this additional complication would not be necessary?