overpass-api or osmconvert: method to use for files from 10 to 400 mb?

new to openstreetmap and this technology.

i want to gather data from .o5m & pbf file - several ways are possible:

what is wanted: i look for restaurants - with all of the following tags:

@id @lon @lat amenity name adress [as town, street, housnumber] website and mailadress. 

All interests are based on the regions of Germany see http://download.geofabrik.de/europe/germany.html i have several files i want to work with - ranging form 10 MB (Bremen) to 390 MB (Nordrhein Westfalen) the osm.pbf-files are not too big;

**main question: **which method is the best and the most appropiate? - to store the results in a mysql-db or just have big calc-sheets (with csv-data)

To achieve this goal of having only certain features in my csv file, we will have to filter the csv file generated by osmconvert afterwards.
Also, the arguments to the --csv option of osmconvert are the tag keys we want to have in our csv file.
If we want “amenity” and “name” columns in addition to id, lat and lon, we specify “@id @lon @lat amenity name”, and then filter the CSV output.

**a. use osmconvert to convert osm **to .o5m and .pbf file it works fine but am having problem converting osm to .csv or excel file

**example: **

I can use

./osmconvert addis.osm --all-to-nodes --csv="@id @lon @lat amenity name" --out-csv -o=outfile.csv

and subsequently:

grep cafe outfile.csv > cafes.csv
grep restaurant outfile.csv > restaurants.csv
cat cafes.csv restaurants.csv > cafes_and_restaurants.csv

b. on the other hand i can do this way: use overpass-api:


<query type="node">
  <has-kv k="place" v="city"/>
  <has-kv k="name" v="any town "/>
</query>
<query type="node">
  <around radius="1000"/>
    <has-kv k="shop"/>
</query>
<print/>

question: are there more (effficient) ways!? i need to have the most efficient.

b. - the overpass-api-method creates a XML-file (more or less large )
for example a XML file which I created from the overpass api. This i would need to load into a mysql database.
btw: i use this to LOAD XML LOCAL INFILE ‘/home/jay/Downloads/interpreter1’ into table jayDB.xml1 (id);

I’m new to XML so not sure if this is possible.


<osm version="0.6" generator="Overpass API">
<note>
The data included in this document is from www.openstreetmap.org. The data is made available under ODbL.
</note>
<meta osm_base="2013-03-07T14:54:02Z" areas="2013-03-07T10:37:02Z"/>
<node id="240486180" lat="50.9744274" lon="3.0152858">
<tag k="addr:housenumber" v="9"/>
<tag k="addr:street" v="Marktplaats"/>
<tag k="amenity" v="cafe"/>
<tag k="email" v="vandaelekoen67@skynet.be"/>
<tag k="name" v="Paviljoentje"/>
<tag k="opening_hours" v="Mo-Su 09:00+; Tu off; Th 09:00-14:00"/>
<tag k="phone" v="+3251636211"/>
<tag k="website" v="http://www.paviljoentjestaden.be"/>
</node>
<node id="244312208" lat="51.2461401" lon="5.4390455">
<tag k="amenity" v="cafe"/>
<tag k="created_by" v="JOSM"/>
<tag k="name" v="De Club"/>
</node>

however i think due to the format of the file it thinks K and V are the columns. Where as I was amenity to be column and cafe to be the value
i tried to look atthe details https://dev.mysql.com/doc/refman/5.5/en/load-xml.html

as mentioned above: the **main question: **which method is the best and the most appropiate? - to store the results in a mysql-db or just have big calc-sheets (with csv-data)

love to hear from you

Any answer can only be quite subjectiv …

because I think teh most important point is: How do you want to process the data AFTER having them either in a MySQL database or in an CSV spreadsheet?

For my OSM purposes it is easier to filter and manipulate data in simple CSV format … because there is no need to learn complex database queries.

hello dear stephan75, many thanks for the reply :slight_smile:

subjectiv-answer; sure thing - youre right i only want to have overview - and shedules - so it is sure that i do not want to rework the data back into maps…
that means

  • only straightforward from files that
  • no backimport of data to OSM from a .csv file

i want to gather data from .o5m & pbf file - several ways are possible: what is wanted: i look for restaurants - with all of the following tags:

@id @lon @lat amenity name adress [as town, street, housnumber] website and mailadress. 

All interests are based on the regions of Germany see http://download.geofabrik.de/europe/germany.html i have several files i want to work with - ranging form 10 MB (Bremen) to 390 MB (Nordrhein Westfalen) the osm.pbf-files are not too big;

so the main question: which method is the best and the most appropiate? - to store the results in a mysql-db or just have big calc-sheets (with csv-data)

Well what i want to accomplish can be done with osmfilter/osmconvert too. So i guess that for my OSM purposes it is easier to filter and manipulate data in simple CSV format … because there is no need to learn complex database queries.

For example:


$ ./osmfilter bremen.o5m --keep="addr:country= and addr:city= and addr:street=" --ignore-dependencies | ./osmconvert - --csv="@oname @id @lon @lat addr:country addr:city addr:street"

we can do alot with osmconvert and osmfilter. :: using osmconvert to csv file… eg: . see the many sites that i get when searching for csv …they show that we can do lots of things with osmconvert… and filter. dont they!?

https://help.openstreetmap.org/questions/16102/using-osmconvert-to-csv-file-only-returns-id-lat-and-lon

but on the other handsite:

since i can do lots of things with overpass-api i wondder how good (and easy) i can transforme the data to the mysql-db.:

see the overpass-api request:

query type="node">
  <has-kv k="place" v="city"/>
  <has-kv k="name" v="any town "/>
</query>
<query type="node">
  <around radius="1000"/>
    <has-kv k="shop"/>
</query>
<print/>

and the outconme;


osm version="0.6" generator="Overpass API">
<note>
The data included in this document is from www.openstreetmap.org. The data is made available under ODbL.
</note>
<meta osm_base="2013-03-07T14:54:02Z" areas="2013-03-07T10:37:02Z"/>
<node id="240486180" lat="50.9744274" lon="3.0152858">
<tag k="addr:housenumber" v="9"/>
<tag k="addr:street" v="Marktplaats"/>
<tag k="amenity" v="cafe"/>
<tag k="email" v="vandaelekoen67@skynet.be"/>
<tag k="name" v="Paviljoentje"/>
<tag k="opening_hours" v="Mo-Su 09:00+; Tu off; Th 09:00-14:00"/>
<tag k="phone" v="+3251636211"/>
<tag k="website" v="http://www.paviljoentjestaden.be"/>
</node>
<node id="244312208" lat="51.2461401" lon="5.4390455">
<tag k="amenity" v="cafe"/>
<tag k="created_by" v="JOSM"/>
<tag k="name" v="De Club"/>
</node>

so - well what would you say…

i think i can reach the most of my desired goals with osmconvert and -osmfilter. Especially if i focus on the the regional germany dataset.
Note; All interests are based on the regions of Germany see http://download.geofabrik.de/europe/germany.html i have several
files i want to work with - ranging form 10 MB (Bremen) to 390 MB (Nordrhein Westfalen) the osm.pbf-files are not too big;

what do you advice - !? i guess that i can go with osmconvert and -filter to get csv-files. Since the files that result out of the GERMAN regional db are not toooo big!? What do you say!?

look foward to hear from you

greetings

1