gathering data from osm & overpass-API:: Methods and ways to accomplish

[tarifa]

dear community, 

gathering data from osm & pff-file:: Methods and ways to accomplish 

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[/CODE]

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/>[/CODE]

 

 

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

[B]b. -[/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 [url]www.openstreetmap.org[/url]. The data is made available under ODbL.

 

[CODE]
</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>

[/CODE]

note: The XML output from Overpass Api has the same structure as all .osm files, so we can use osmconvert on it as well.
Besides this: For populating a mysql database with it there is a [url]https://gis.stackexchange.com/questions/94534/export-xml-data-to-a-mysql-db[/url] 
(cf a XML file which was created from the overpass API. I'd like to load this data from this file into a MySQL database. I've been using this code to test, but none of the data loads.
LOAD XML LOCAL INFILE '/home/jay/Downloads/interpreter1' into table jayDB.xml1 (id); )

MySQL allows only the xml formats as stated in the linked document. Therefore we have to modify the osm xml into one of those. Alternatively, we can use osmosis or GDAL to do the import.
cf: Q&A for some guidance:
[url]https://stackoverflow.com/questions/20125242/import-openstreetmap-data-osm-file-to-a-mysql-database-sql-file[/url]
[url]http://forum.openstreetmap.org/viewtopic.php?pid=152695[/url]
[url]http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/[/url]

Export XML Data to a MySQL DB
A better solution is using a Postgis database populated by osm2pgsql, or a spatialite database.
CSV files might get rather unhandy if you got lots of rows. there is the question how much entries we can expect here.
we can use GDAL to import .osm data and export it to any other supported format (including CSV) as well.

Note: that a Postgis database populated by osmosis has a different setup than that of a osm2pgsql one.
It depends on the needs which one is better. I prefer the osm2pgsql version, made for GIS applications like QGIS and rendering tiles with mapnik.

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 at 
the details [url]https://dev.mysql.com/doc/refman/5.5/en/load-xml.html[/url]
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