new to openstreetmap and this technology.
update: some mentioned postgis: well - The database schema of postgis allready contains the following tables: nodes, ways, relations, node_tags, way_tags, relation_tags, way_nodes.... well - can i do imports into postgis - as easy as into mysql?
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: [code]@id @lon @lat amenity name adress [as town, street, housnumber] website and mailadress. [/code]
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
I can use
./osmconvert addis.osm --all-to-nodes --csv="@id @lon @lat amenity name" --out-csv -o=outfile.csv
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="email@example.com"/> <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