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

Hi there.

I would like to rearrange xml files in excel in the following format:

lat | long | k | v | v

If you are familiar with the xml schema I cant repeat column v twice. Essentially what I want to do is make an excel file for all restaurants with lat, long, name and cuisine.

asked 04 Jul '11, 10:59

bulleh's gravatar image

bulleh
14222
accept rate: 0%

edited 04 Jul '11, 15:32

emacsen's gravatar image

emacsen
1.2k1623


Meanwhile the newly introduced --csv option of osmconvert will help you to create CSV files rather easily. For example:

./osmconvert shops.osm --all-to-nodes --csv="@id @lon @lat amenity shop name" --csv-headline
@id               @lon        @lat        amenity  shop       name
21548298          11.6122123  48.6884848  shop     bakery     Miller
21552613          9.0651970   49.9979332  shop     butcher    Jaeger
1000000168276611  6.6058085   51.4556093  shop     drugstore  AllForYou

To filter all restaurants out of an OSM file you may use osmfilter.

permanent link

answered 19 Sep '12, 00:11

Marqqs's gravatar image

Marqqs
448510
accept rate: 22%

The Ruby library OSMlib allows export into CSV.

The example in the documentation pointed to above has a hospital example that mirrors your restaurant example.

One thing to be aware of, as it sounds like you're looking to extract the data out of OSM, is that the license persists, and your new dataset in CSV form is still under the CC-BY-SA license (until OSM completes the license transition).

So if you distribute this file, it must be under the terms of the CC-BY-SA. If you make a new map, it's a derived work, and if you combine this data with other data, it's a derived work. You should read the various license pages on the OSM Wiki for more information about this, in particular the page on Common license interpretations may help.

OSM's a great resource for POI data. Good luck with your project!

permanent link

answered 04 Jul '11, 13:57

emacsen's gravatar image

emacsen
1.2k1623
accept rate: 13%

edited 04 Jul '11, 15:53

Richard's gravatar image

Richard ♦
30.9k44279412

Not that it's really anything to do with OSM, but the Excel data import wizard will import one row per tag combination. You can tell which tags are for the same item by looking at the "id" column.

How to rearrange the data in Excel is a question best asked somewhere like here.

permanent link

answered 04 Jul '11, 13:58

SomeoneElse's gravatar image

SomeoneElse ♦
36.9k71370866
accept rate: 16%

Your answer
toggle preview

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:

×315
×84
×42

question asked: 04 Jul '11, 10:59

question was seen: 8,952 times

last updated: 19 Sep '12, 00:11

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