Compiling a list of street names for several countries

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:

  1. 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.
  2. 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:

Maybe use Osmfilter to just extract all of these highways.
Then you can extract the names from all of these.

please go to and do a search there for street list or streetlist … there are some topics dealing with your aim.

One more place where streets live - associatedStreet relations


With GDAL and Spatialite it is pretty easy to get at least some results. I had a test with Finnish data and here are the steps to follow and modify.

Step 1: Create a Spatialite database from OSM data.

ogr2ogr -f SQLite -dsco spatialite=yes streetnametest.sqlite finland-latest.osm.pbf -gt 20000 -progress --config OGR_SQLITE_SYNCHRONOUS OFF --config OSM_COMPRESS_NODES YES

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:

Kevon reitti

Unfortunately highway names contain not only street names but bridge names , road names and so on

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 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?

I was also wondering, how could you adjust the command given in this link for the osmfilter so that each streetname also has a corresponding town reference?

Many thanks

So tell us please what you have exactly entered in the command line promt when starting osmfilter or osmconvert … what parameters have you given?

You are aware of description at the OSM wiki pages to each program of them??

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.

Do you have enough free space on your harddisk?

Or try that filtering with a smaller piece of OSM data … try to load a smaller file from e.g., 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,m.municipalityname
from lines l, municipality m
where l.highway is not null
and is not null
and ST_Intersects(m.geometry,l.geometry)
order by m.municipalityname,;

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,m.municipalityname
from lines l, municipality m
where l.highway is not null
and 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,

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?

Thank you for your ongoing support.