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

Hi

TLDR, I think my question below has possibly been answered here but it's too technical for me to understand.

I would like to get the lat-long of some POIs in csv format so I can compare them with a published dataset using Excel (that's all I can manage!)

I get the following result with Overpass Turbo:

"elements": [

{
  "type": "node",
  "id": 7841169184,
  "lat": 52.5786297,
  "lon": -0.2276290,
  "tags": {
    "addr:city": "Peterborough",
    "addr:postcode": "PE1 5EH",
    "addr:street": "Padholme Road",
    "amenity": "place_of_worship",
    "denomination": "sunni",
    "religion": "muslim"
  }

    }, 
{
  "type": "way",
  "id": 236058204,
  "nodes": [
    2441318658,
    5934211602,
    5934211603,
    2441318882,
    2441318621,
    2441318773,
    2441318658
  ],
  "tags": {
    "addr:city": "Peterborough",
    "addr:housenumber": "116",
    "addr:postcode": "PE3 6DD",
    "addr:street": "Midland Road",
    "amenity": "place_of_worship",
    "building": "yes",
    "denomination": "sunni",
    "name": "Madina Madrassa & Spiritual Centre",
    "religion": "muslim"
  }
},

[continues]

Can I efficiently identify the lat-long of the area, either interpolating the centre of the area or using (say) the first-listed node?

Can I force Overpass to output a csv file with one line per POI, rather than the line-separated text above?

Is it even possible for someone who uses iD for map editing, and Excel for database editing, to do this?

Many thanks.

asked 05 Sep '21, 15:34

eteb3's gravatar image

eteb3
295131524
accept rate: 6%

edited 05 Sep '21, 15:36


Further to AlaskaDave's post:

"By default all fields are separated by a tab character ("\t")." So isn't required.

If you wish to use another character, such as a comma, pipe etc., the column header display argument (false/true) has to be included:

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

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

https://wiki.openstreetmap.org/wiki/Overpass_API/Overpass_QL#CSV_output_mode

permanent link

answered 06 Sep '21, 14:53

DaveF's gravatar image

DaveF
3.3k8498133
accept rate: 16%

edited 06 Sep '21, 14:57

Can I force Overpass to output a csv file with one line per POI, rather than the line-separated text above?

Yes, you can. Here's a query I used to find trees I had tagged in Chiang Mai

You must put an escaped tab character, "\t", as the last argument). Also, note that all tags you wish returned must be enclosed in quotes.

[out:csv(::id,::lat,::lon,"species","species:en","species:th", "name:en","\t")][timeout:25];

{{geocodeArea:"Thailand"}}->.searchArea;

(

node["natural"="tree"](user:"AlaskaDave")(area.searchArea);

);

out qt;

The output is a nice CSV file with latitude/longitude coordinates in separate columns on each line of the output file. I merely select all the output, copy it, and save it as a CSV file. You can then open it in Excel.

permanent link

answered 05 Sep '21, 21:01

AlaskaDave's gravatar image

AlaskaDave
5.4k76107164
accept rate: 16%

Fantabulous - works perfectly. Thank you!

(05 Sep '21, 22:04) eteb3

What does the tab do? I'm struggling to see any difference.

(06 Sep '21, 13:07) DaveF

Re: the tab character

I cannot find the material I used in my example in the Wiki. IIRC, the "/t" argument forced the delimiter characters in the output file to be tabs. However, I noticed that the Overpass Turbo Wizard has changed a lot since I used that query — it's much more powerful now and allows the use of wildcards for key values, for example.

Perhaps that requirement has changed too?

I'm not an Overpass Query expert, far from it. I use the Overpass Turbo Wizard for almost all of my queries. I got the main part of the above query from a friend who knows how to use the standard Overpass better than me.

(06 Sep '21, 14:55) AlaskaDave
permanent link

answered 06 Sep '21, 20:07

DaveF's gravatar image

DaveF
3.3k8498133
accept rate: 16%

edited 06 Sep '21, 20:12

1

Thank you very much.

Do you know if there's an "Overpass QL for Dummies" page somewhere? I'm reading the wiki and documentation as I come across it, but it's stupendously (meant literally) technical. Maybe there's something like baby-food for a beginner?

(06 Sep '21, 20:58) eteb3
1

@eteb3: I agree — the standard Overpass API is incredibly opaque for non-programmers. I always use the Overpass Turbo Wizard. It's simpler and although one can't do everything the API can do with it, the barrier to entry is very low.

http://overpass-turbo.eu/

(06 Sep '21, 23:12) AlaskaDave
2

You're correct about the wiki reference pages. You have to understand OP fully to be able to decipher the OP wiki! An inline example or two wouldn't go amiss. Unfortunately that's always the case when the people who wrote the code also write the euphemistically named 'help' pages.

There's https://dev.overpass-api.de/blog/index.html, but the weird chapter names also makes it hard to follow. Plus it's not google searchable.

This one's a bit better https://dev.overpass-api.de/overpass-doc/en/index.html

This one is the nearest to a tutorial & probably the bes tplace to start https://osm-queries.ldodds.com/

Plus there's Stack Exchange family of sites to search for previously asked questions, but again, I've found you need to know a bit about the subject to ascertain the usefulness of the responses.

(06 Sep '21, 23:40) DaveF

This is all great on the csv output.

Could I flag here (or maybe I should ask again separately) the part of the question about getting the lat-lon of an area? Overpass will give me those for a node, but not otherwise. I only need it approximately - so lat-lon of a single node in the area or relation would be fine.

permanent link

answered 06 Sep '21, 17:54

eteb3's gravatar image

eteb3
295131524
accept rate: 6%

edited 06 Sep '21, 17:56

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:

×483
×97

question asked: 05 Sep '21, 15:34

question was seen: 2,767 times

last updated: 06 Sep '21, 23:40

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