Folks, I'm trying to get some data out of OSM using Overpass for a beta corona community leaflet distribution process. My aim is to eventually have a spreadsheet with the streets in 2 areas, lat/long data - then push that data to a map so folks can visualise which streets have been done.

I managed to get the query below to return results for Hollingdean, but not Hollingbury.

I'm a bit of a beginner to this (only come to Overpass API for this project), but I fiddled around a bit trying to get the ID of "Hollingbury" suburb to work and it didn't. Then I decided that it was due to the fact that maybe the Hollingbury search didn't show up any areas. I've now labelled an area (that was already drawn up) as Hollingbury, so the search pulls up an area, but still no search results returned.

  • Am I correct in the assumption that this search is searching for a residential area?
  • If yes, are the lack of results due to the system not being updated with the new label? This was done about 18hrs ago.
  • If no, what am I doing wrong? Why am I not getting any results for the "Hollingbury" search?
  • Finally, any folks have a way of interfacing spreadsheets (online) with a map? Google used to have a Spreadsheet Mapper but due to changes in the way that kml files work, I have to faff around importing into Google Earth then Maps, with a bunch of formatting lost in the process. An OSM solution would be great!

Thanks very much in advance for your help!

[out:csv("name", ::type, ::id, ::lat, ::lon)]
foreach(           // for each way tagged as highway=* do...
  out center;             // output way id

asked 28 Mar '20, 13:53

jt196's gravatar image

accept rate: 0%

Scratch that, either the DB was updated or I needed to refresh the page with another query, it's working now.

Any answers to my spreadsheet question, or do I have to figure out another API!? ;)

(28 Mar '20, 14:37) jt196

There are some leaflet plugins that will display a csv on a map. Haven't used one, so can't say if it works nice or not.

The repeated results come from the OSM data model. If something like the surface of the street varies, a new way is created for each different part. Depending on the scale of what you are doing, you may want to evaluate each one manually.

If you have many regions, there might be a way to get combined centers using aggregation functions:

(28 Mar '20, 19:51) maxerickson

@jt196: Please use the "add new comment" function or edit your question if you have additional questions or information. "Your answer" is reserved for answers resolving your question.

(28 Mar '20, 20:43) TZorn

I see what I've done there - I know the format, but assumed it was just usual forum rules. Can I reformat the answers to make it clearer?

(29 Mar '20, 12:24) jt196

Strange. I'm still getting an error atm.

Note, the foreach & [name] isn't required & I've removed ::type as only ways are being searched for.

[out:csv("name", ::id, ::lat, ::lon)];
out center;

What output format is needed? The lat/lon is the approximate centre point of linear ways. Would this be more useful (Click on the Data tab):

out meta geom;
out geom;


way[highway] {color:green}

Also, check out the 'export' tools.

permanent link

answered 28 Mar '20, 15:42

DaveF's gravatar image

accept rate: 6%

Thanks for this Dave!

The idea was to import it into a google spreadsheet in the following format:

area road name Latitude Longitude Hollingbury Bavant Road 50.8447577 -0.1484516

ID isn't super essential.

I need every road in the area - including ones on the border of the area, so I don't know whether I'd need to add any other search criteria or not?

I added the foreach command as I was getting multiple outputs for each street, that seemed to limit it to one (aside from a limited number of cases).

[out:csv("name", ::id, ::lat, ::lon)];
out meta geom;
out geom;


way[highway] {color:green}

Excuse the cut and paste ninja work, I'm really new at this, and having a little difficulty getting my head around the code, despite having read the manual!

This seems to work but I get this kind of output:

Lambourne Close 4748228 50.8447944  -0.1231223
    4748229 50.8461658  -0.1205447
    4748230 50.8439343  -0.1232559
    4748231 50.8438419  -0.1229539
    4748232 50.8441320  -0.1229460
(28 Mar '20, 16:17) jt196

Exporting data is great but I really need a way of casual folk being able to interact with the information, hence me extracting the data, then adding it to an online spreadsheet in order for folk to be able to edit. Importing that info into Gmaps is pretty straightforward after, even though I'd prefer not to use it.

(28 Mar '20, 16:20) jt196

Ah, I made a slight mistake, where I said you didn't need 'name'. way(area)[highway][name]; < this line returns all ways with a 'highway' tag that have a 'name' tag. This may be what you want, but it could include footways, tracks etc. Alternatively use way(area)[highway=residential]; which will restrict it to just residential roads. There is one without a name. if you know it please add it:

(28 Mar '20, 19:41) DaveF

In your latest output what your seeing is one named way & a few unnamed ways (probably footways). '4748229' are the IDs. Try this: I've removed the first line header (with 'false') & added ";" so it returns comma separated data.

(28 Mar '20, 19:56) DaveF

Dave I've added the street name (pulled from another source).

Your link had a "." at the end so confused me for a bit. Looks like it's working fine and dandy now thanks very much!

As I found before, when you change the search term (Hollingdean to Hollingbury), the Overpass API outputs nothing, then if you run one of the sample queries, then paste back in an updated query, it works again.

(29 Mar '20, 12:31) jt196

So, this was the working answer from Dave:

[out:csv("name", ::lat, ::lon; false; ",")];
out geom;
(30 Mar '20, 12:26) jt196
showing 5 of 6 show 1 more comments
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 28 Mar '20, 13:53

question was seen: 342 times

last updated: 30 Mar '20, 12:26

powered by OSQA