Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

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

share|improve this question

1 Answer 1

The XML output from Overpass Api has the same structure as all .osm files, so you can use osmconvert on it as well.

For populating a mysql database with it see 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. I don't know how much entries you expect. The whole of Germany might deliver too much of them.

You can use GDAL to import .osm data and export it to any other supported format (including CSV) as well.


EDIT

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

share|improve this answer
    
Well - do you mean postgis can store the XML in a better way. Or do i have t use other methods - beside osmconvert ...!? Hmm postgis might be a good solution... will digg deeper –  zero May 12 at 8:58
    
Postgis has a lot of spatial functions. It depends on what you want to do with the data, and how much data you have. The OSM folks started with MySQL, but switched to Postgis for performance issues. There are a lot of tools around that can utilize the OSM-Postgis toolchain. –  AndreJ May 12 at 10:13
    
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? –  zero May 12 at 10:37
    
With osm2pgsql. Note that you do not have a table for polygons. Restaurants can be found in the OSM data either as nodes, or as areas. These will be transformed from closed ways or multipolygons by osm2pgsql. For mysql, you have to do that on your own. If you want only nodes, you can calculate polygon centroids from the polygons using postgis functions, and add them to the node-only POIs. –  AndreJ May 12 at 11:00
    
hello - regarding the aim of my goals; i do not want to readd the data into maps. So the direction of processing is clear - from OSM to csv or sql. Guess that postgis might be of interesest for me. osm2pgsql too.... –  zero May 12 at 12:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.