User:Tagtheworld/CSV-Export

From OpenStreetMap Wiki
Jump to: navigation, search

important steps towards CSV-outputs

cfː page for outputs etc


Sehr interessant bei der Abfrage ist es einen speziellen Ländercode - als BB-Code zu verwenden.

z.B. so:

 area["ISO3166-1"="AT"]->.a;

das sieht dann so aus: wie hier in diesem Thread besprochen: https://forum.openstreetmap.org/viewtopic.php?id=59980

wichtig ist der sogenannte Ländercode: http://wiki.openstreetmap.org/wiki/Overpass_API/Language_Guide#Selecting_areas_by_name

Weil overpass turbo hier eine Nominatim Abfrage durchführt, und von dort das "erste" Ergebnis nimmt, was z.B. bei Frankreich NICHT das country ist! (Wobei Italien und Spanien eigentlich passen müsste). Zielführender sollte die Abfrage nach dem jeweiligen Isocode sein. Siehe dazu auch Select areas by name: das hat ein AHA-Effekt aus geloest: mit France lässt sich die obige Abfrage auch sehr gut machen. nehme jetzt die Bezeichnungen von hier: http://www.geonames.org/countries/

allerdings - Spain oder Germany funktionieren nicht - da bricht dann die Abfrage ab.



[out:csv(::id,::type,"name","addr:postcode","addr:city","addr:street","addr:housenumber","website"," contact:email=*")][timeout:600];
area[name="Spain"]->.a;
( node(area.a)[amenity=hospital];
  way(area.a)[amenity=hospital];
  rel(area.a)[amenity=hospital];);
out;



oder hier:

[out:csv(::id,::type,"name","addr:postcode","addr:city","addr:street","addr:housenumber","website"," contact:email=*")][timeout:600];
area[name="Gernany"]->.a;
( node(area.a)[amenity=hospital];
  way(area.a)[amenity=hospital];
  rel(area.a)[amenity=hospital];);
out;




Nimm die in OSM im Namen hinterlegten Bezeichnungen in Verbindung mit geocodeArea, z.B.


Zur einfacheren Bearbeitung von Requests - mit nationalem Suchbegriff: vgl: http://www.geonames.org/countries/

{{geocodeArea:Deutschland}}->.a;
oder
{{geocodeArea:España}}->.a;


im Einzelnen sieht das dann so aus:


[out:csv(::id,::type,"name","addr:postcode","addr:city",
"addr:street","addr:housenumber","website"," contact:email=*")][timeout:50];
 area["ISO3166-1"="AT"]->.a;
( node(area.a)[amenity=hospital];
  way(area.a)[amenity=hospital];
  rel(area.a)[amenity=hospital];);
out;

mehr hierzo:

  area["ISO3166-1"="CH"];
  area["ISO3166-1"="DE"];
  area["ISO3166-1"="FR"];
  area["ISO3166-1"="NL"];
  area["ISO3166-1"="SP"];
  area["ISO3166-1"="RU"];
  area["ISO3166-1"="NL"];


vergleiche auch


[out:csv(::id,::type,"name","website","adress")];
area[name="Heidelberg"]->.a;
( node(area.a)[amenity=hospital];
  way(area.a)[amenity=hospital];
  rel(area.a)[amenity=hospital];);
out;
CSV output mode

CSV output format returns OSM data as csv document, which can be directly opened in tools like LibreOffice. It requires additional configuration parameters to define a list of fields to display, as well as two optional parameters for csv headerline, as well as the separator character.

Format:

[out:csv( fieldname_1 [,fieldname_n ...] [; csv-headerline [; csv-separator-character ] ] )]


List of field names

Besides normal OSM field names the following special fields are available:
Special fieldname 	Description
 ::id 	OSM Object ID
 ::type 	OSM Object type: node, way, relation
 ::otype 	OSM Object as numeric value
 ::lat 	Latitude (available for nodes, or in out center mode)
 ::lon 	Longitude (available for nodes, or in out center mode)
The following meta information fields are only available, if out meta; is used to output OSM elements.
 ::version 	OSM object's version number
 ::timestamp 	Last changed timestamp of an OSM object
 ::changeset 	Changeset in which the object was changed
 ::uid 	OSM User id
 ::user 	OSM User name

Note that all of these special fields needs to be prefixed by two colons "::".


Example:

[out:csv(::"id", amenity, name, operator, opening_hours, "contact:website", "contact:phone", brand, dispensing, lastcheck)];

Railway stations in Bonn:

[out:csv(::id,::type,"name")];
area[name="Bonn"]->.a;
( node(area.a)[railway=station];
  way(area.a)[railway=station];
  rel(area.a)[railway=station]; );
out;

see more cfː page for outputs etc


my own stepsː̠ all running well

csv-output: heidelberg 45 lines hamburg 90 lines new york 600 lines


Heidelbergː

[out:csv(::id,::type,"name","website","adress")];
area[name="Heidelberg"]->.a;
( node(area.a)[amenity=hospital];
  way(area.a)[amenity=hospital];
  rel(area.a)[amenity=hospital];);
out;

Parisː

[out:csv(::id,::type,"name","website","adress")];
area[name="Paris"]->.a;
( node(area.a)[amenity=hospital];
  way(area.a)[amenity=hospital];
  rel(area.a)[amenity=hospital];);
out;


weitere Abfragen

cf Poi-Kindertagesstätten

amenity=kindergarten
    name=*
    contact:website=*
    contact:phone=*
    capacity=*
    religion=* (optional)
    denomination=* (optional)
    operator=*

Abfrage vorhanden:

[out:csv(::id,::type,"name","website"," contact:email=*")];
area[name="Heidelberg"]->.a;
(node(area.a)[amenity=kindergarten];
  way(area.a)[amenity=kindergarten];
  rel(area.a)[amenity=kindergarten];);
out;



expprt mit der kompletten Adresse


übersicht auf die adresstags hilfe zu timeout


[out:csv(::id,::type,"name","addr:postcode","addr:city","addr:street","addr:housenumber","website"," contact:email=*")][timeout:300];
area[name="Bayern"]->.a;
( node(area.a)[amenity=kindergarten];
  way(area.a)[amenity=kindergarten];
  rel(area.a)[amenity=kindergarten];);
out;


output: 145 lines


[out:csv(::id,::type,"name","addr:postcode","addr:city","addr:street","addr:housenumber","website"," contact:email=*")][timeout:300];
area[name="Paris"]->.a;
( node(area.a)["shop"="car"];
  way(area.a)["service"="dealer"];
  rel(area.a)["service"="repair"];
  node(area.a)["brand"="renault"];);
  out;


/*
This has been generated by the overpass-turbo wizard.
The original search was:
“(service~repair or shop=car_repair) and brand~Renault in Germany”
*/
[out:json][timeout:100];
// fetch area Germany to search in
{{geocodeArea:Germany}}->.searchArea;
// gather results
(
  // query part for: service~repair and brand~Renault
  node["service"~"repair"]["brand"~"Renault"](area.searchArea);
  way["service"~"repair"]["brand"~"Renault"](area.searchArea);
  relation["service"~"repair"]["brand"~"Renault"](area.searchArea);
  // query part for: shop=car_repair and brand~Renault
  node["shop"="car_repair"]["brand"~"Renault"](area.searchArea);
  way["shop"="car_repair"]["brand"~"Renault"](area.searchArea);
  relation["shop"="car_repair"]["brand"~"Renault"](area.searchArea);
);
// print results
out body;
>;
out skel qt;

and here one more example


[out:csv(::id,::type,"name","addr:postcode","addr:city","addr:street","addr:housenumber","website"," contact:email=*")][timeout:300];
area[name="Bayern"]->.a;
( node(area.a)[amenity=townhall];
  way(area.a)[amenity=townhall];
  rel(area.a)[amenity=townhall];);
out;