User:Tagtheworld/CSV-Export
Jump to navigation
Jump to search
important steps towards CSV-outputs
Ausgabe ohne CVS - aber mit Kartendarstellung.
[out:csv(::id,::type,::lon,::lat,amenity,name,"addr:postcode","addr:city","addr:street","addr:housenumber","contact:website",website,"contact:email")]
[timeout:600];
rel[boundary=administrative][admin_level=6][name="München"] -> .city;
(nwr[amenity=hospital][name](around.city:2000);
nwr[amenity=school][name](around.city:2000););
out center;
wichtige Erläuterungen; https://forum.openstreetmap.org/viewtopic.php?id=71531 Wenn [timeout:600]; in einer eigenen Zeile steht, lässt sich mit "//" vor [out:csv(... einfach in den Grafik-Modus umschalten. Das tag website ist (noch) wesentlich gebräuchlicher als contact:website, gilt wohl auch für email. Die zusätzliche Ausgabe von amenity vereinfacht ein späteres Sortieren. In Deiner Liste gab es einige Zeilen ohne irgendwelch Info nach lan/lot. Ich hab mal eines angeschaut: es ist ein way mit amenity=school um mehrere Schulen herum. Die Abfrage auf [name] eliminiert diese.
und weitere Befunde:
[timeout:600];
area["ISO3166-1"="BR"]->.brazil;
area["ISO3166-1"="AR"]->.argentina;
area["ISO3166-1"="VE"]->.venezuela;
area["ISO3166-1"="PE"]->.peru;
area["ISO3166-1"="CL"]->.chile;
/* more areas..*/
(
nwr[amenity=hospital](area.brazil);
nwr[amenity=hospital](area.argentina);
nwr[amenity=hospital](area.venezuela);
nwr[amenity=hospital](area.peru);
nwr[amenity=hospital](area.chile);
/* other queries... */
);
out center;
Sehr interessant bei der Abfrage ist es einen speziellen Ländercode - als BB-Code zu verwenden.
[out:csv(::id,::type,::lon, ::lat, "name","addr:postcode","addr:city","addr:street","addr:housenumber","contact:website"," contact:email=*")][timeout:600];
area["ISO3166-1"="BR"]->.brazil;
area["ISO3166-1"="AR"]->.argentina;
area["ISO3166-1"="VE"]->.venezuela;
area["ISO3166-1"="PE"]->.peru;
area["ISO3166-1"="CL"]->.chile;
/* more areas..*/
(
nwr[amenity=hospital](area.brazil);
nwr[amenity=hospital](area.argentina);
nwr[amenity=hospital](area.venezuela);
nwr[amenity=hospital](area.peru);
nwr[amenity=hospital](area.chile);
/* other queries... */
);
out center;
@id @type @lon @lat name addr:postcode addr:city addr:street addr:housenumber contact:website contact:email=*
35332685 node -43.9485880 -19.8175998 Hospital Risoleta Tolentino Neves Belo Horizonte Rua das Gabirobas 1
35332689 node -43.9518360 -19.9178800 Prontocor
53254282 node -43.9319894 -19.9260406 Hospital Semper
75600076 node -43.9590472 -19.9505820 Hospital Luxemburgo
78719634 node -43.9528392 -19.9239539 Hospital Vera Cruz Belo Horizonte Avenida Barbacena 653
257565325 node -46.7675910 -1.0538793 Hospital Geral
258373353 node -46.7687408 -1.0502734 Hospital Santo Antônio
258373354 node -46.7639489 -1.0575169 Hospital das Clínicas
264803415 node -71.5444561 -32.9982109 Hospital Naval Almirante Nef Viña del Mar Subida Alessandri S/n
274842924 node -71.5317150 -33.0274915 Clínica Miraflores
274844236 node -71.5227694 -33.0320309 Clinica Los Abetos
302101961 node -62.2772347 -38.7112157 Municipal de Agudos Dr. Leónidas Lucero - Guardia Bravard 37
316377282 node -71.5400129 -33.0281148 Hospital de Niños
316804832 node -62.2724729 -38.7129502 Español - Guardia Casanova 25
316810199 node -62.2491985 -38.7150331 Unidad Sanitaria Delegación Bella Vista Charcas 906
321448936 node -50.5176971 -23.1476022 Hospital Municipal Santa Alice 86350-000 Santa Mariana Rua Manoel da Silva Machado 550
321510735 node -44.0815901 -19.9321119 Posto de Saúde Bernardo Monteiro
330051401 node -70.5573288 -33.5687867 Integramedica Puente Alto Avenida Camilo Henríquez 3296
331107838 node -43.1904033 -22.9367950 Instituto Nacional de Cardiologia
338511118 node -70.5813935 -33.5745464 Hospital de Niños
344023732 node -43.1995077 -22.9530371 Instituto Estadual de Cardiologia Aloysio de Castro
415797968 node -43.1769401 -22.9533746 Hospital Municipal Rocha Maia
416969462 node -43.1788391 -22.9507470 Policlínica de Botafogo 22290-240 Rio de Janeiro Avenida Pasteur 72
428845265 node -65.4048573 -27.0526861 Hospital Parajón Ortíz 4132 Famaillá Belgrano 210
439239039 node -51.5144238 -29.1675219 24h Pronto Atendimento - Hospital Tacchini
440760740 node -51.1942008 -30.0408021 Hospital Petrópolis Rua Coronel Lucas de Oliveira 2040 http://www.hospitalpetropolis.com.br
446894591 node -51.1071545 -29.6887312 Hospital Unimed 93540-290 Novo Hamburgo Rua Waldemar Geib 161
448816561 node -43.1885707 -22.9123555 Instituto Nacional do Câncer (INCa)
450799288 node -43.1906078 -22.9134234 Hospital Espanhol Rio de Janeiro Rua Riachuelo 302
452854210 node -51.8150178 -30.8483932 Hospital Municipal Nossa Senhora Aparecida 96180000 Camaquã Rua Cristóvão Gomes de Andrade 665
453229829 node -44.0470889 -19.9398175 Iria Diniz
453234829 node -44.0441197 -19.9398351 Hospital Santa Helena Contagem - MG Rua Casuarinas 64
453686209 node -44.0259611 -19.9490305 Pronto Socorro Unimed Contagem Avenida Babita Camargos 1695
453690483 node -44.0150767 -19.9654834 Hospital São José
453690501 node -44.0148323 -19.9660607 Hospital Santa Rita
454742102 node -72.5937057 -13.1287556
461805311 node -43.1851671 -22.9330047 Maternidade Escola da UFRJ
475257295 node -62.2352436 -38.7199649 Unidad Sanitaria
476647429 node -51.1982455 -30.0371370 Unidade Álvaro Alvim - HCPA 90420-020 Porto Alegre Rua Professor Álvaro Alvim 400
477364089 node -38.5581389 -3.7446813 Hospital São José Fortaleza Rua Nestor Barbosa 315
492635628 node -47.8084557 -21.1856911 Hospital das Clinicas UE
492636301 node -47.8091569 -21.1852211 Hospital São Francisco
495418452 node -47.4029830 -23.4936943
496267216 node -46.6639769 -23.5094698
496303640 node -47.4269642 -23.5028386 Hospital Psiquiátrico Teixeira Lima
496497349 node -43.3660580 -22.9502944 Casa Santa Ana
501619315 node -35.2048436 -5.8162456 Hospital da UNIMED
505204836 node -48.4829433 -1.4479952 Beneficente Portuguesa
506540736 node -35.2241860 -5.8669350 UMS Cidade Satélite
506577204 node -35.1980430 -5.7816536 Maternidade Escola Januário Cicco 59012-310 Natal - RN Avenida Nilo Peçanha 259
506849960 node -43.1834769 -22.9681155 Hospital Galdino Campos
528921479 node -51.1614296 -29.8391039 São Camilo
530313758 node -53.4052903 -27.4786433 Fundação Hospital Pio XII
548919020 node -43.1919774 -22.9271159 Hospital de Clínicas IV Centenário Rio de Janeiro
559264606 node -76.9788632 -6.0296601 Clínica San Lucas Moyobamba Jr. Alonso de Alvarado 1280
559272981 node -76.9828624 -6.0273680 Lluyllucucha Jirón Dos de Mayo
561522283 node -34.9007258 -8.0511015 Hospital Jaime da Fonte
570751791 node -66.5200994 -33.1397802 Centro de Salud
570758408 node -66.8445685 -32.8779091 Centro de Salud
573601863 node -65.7798270 -28.4719638 IGOM Mota Botello 456
580269641 node -65.7859886 -28.4749041 Hospital Sanitario Interzonal De Niños Eva Peron 4700 San Fernando del Valle de Catamarca
581423130 node -53.4890276 -28.2095797 Hospital Beneficente de Condor 98290-000 Condor Rua Germano Keller
583339444 node -39.7094822 -18.4205525
585189900 node -38.9785283 -12.2417176 Hospital da Mulher
588236075 node -49.2551680 -16.7091744 HUGO - Hospital de Urgências de Goiânia
595234316 node -66.1645893 -25.1198231 Hospital Dr.Arne Hoygaard 4417 Cachi
595348032 node -59.9637115 -3.0320550 Hospital Francisca Mendes
596705527 node -65.1081393 -43.2968073 Hospital Sub Zonal Santa Teresita De Rawson Rawson
598367703 node -67.5108389 -29.1583278
598666734 node -53.1687579 -27.4713597
598666735 node -53.1718649 -27.3653006
607513165 node -43.1083757 -22.7612282 Uisma Villaboin
616070202 node -58.3915642 -34.6340444 Hospital de Gastroenterología Doctor Carlos Bonorino Udaondo Avenida Caseros 2061
619006646 node -38.0830995 -12.6445228 Posto de Medico de Monte Gordo
621904318 node -59.7789879 -35.2688584 San Roque 6663 Norberto de la Riestra Güemes
623200433 node -38.5063563 -3.7307265 Hospital São Raimundo Fortaleza Rua Doutor José Lourenço 777
628210040 node -65.0448873 -43.3107555 Mini Hospital Playa Unión 9103 Rawson Avenida Juan Manuel de Rosas 450
631057851 node -38.4946207 -3.7371699 Gastroclínica Emergência Fortaleza
631057857 node -38.4943910 -3.7365671 Gastroclínica Fortaleza Rua Maria Tomásia 814
635114211 node -35.7436349 -9.6272677 Hospital Sanatório
638322741 node -67.7234995 -49.3051680 Hospital Distrital Dr. Miguel A. Lombardich 9310 Puerto San Julian
647106084 node -46.5850511 -23.5440866 Hospital Avicena 03303-000 São Paulo Rua Padre Adelino 901 http://www.aviccena.com.br/
652262766 node -42.4986828 -22.9316004 Posto Municipal de Saúde 24 Horas
661983064 node -72.4025364 -35.3392666 Hospital Constitución
668437060 node -70.6196044 -33.4276491 Clinica Plus Medica Providencia Almirante Pastene 71
668990429 node -49.2727747 -25.4594035 Maternidade Curitiba
680002885 node -58.4018651 -34.5938678 Clinica Suizo Argentina 1118 Buenos Aires Avenida Pueyrredón 1461
687148571 node -71.2654006 -32.9894395 Capredena
698803827 node -43.1866948 -22.9562591 Hospital Pró-Cardíaco 22280-003 Rio de Janeiro Rua General Polidoro 192
698804332 node -43.1906762 -22.9513221 Clínica Sorocaba
713261830 node -35.2251955 -5.8247616 SESI Clínica
723185196 node -58.7967416 -34.3514037 Clínica Fatima 1625 Belén de Escobar Spadaccini 1084
750041665 node -49.2456307 -25.4359254 Hospital Universitário Cajuru
790023635 node -36.0174265 -9.2477042
791438160 node -36.4774663 -9.3168796
791743847 node -64.1873361 -31.4246194 Sanatorio Allende 5000 Córdoba Avenida Hipólito Yrigoyen 384
791746743 node -64.1839240 -31.4253730 Clínica del Niño
795332305 node -46.6428989 -23.6436999 Hospital São Luiz Unidade Jabaquara Rua das Perobas 344
795934615 node -35.8539095 -9.4827869
796372966 node -35.7313033 -9.6579612
810378135 node -43.1814820 -22.9318854 Rio Laranjeiras
813263723 node -35.0920405 -6.1877608 Unidade Mista de Saúde
815566756 node -43.2325194 -22.9209978 Prontobaby
815701429 node -43.2271781 -22.9255966 Santa Terezinha
815701434 node -43.2262876 -22.9251618 Tijucor
815701435 node -43.2276287 -22.9256163 Panamericano
816125484 node -43.2393550 -22.9286348 Tijutrauma
816213010 node -43.2239934 -22.9154544 Hospital Israelita Albert Sabin
817621066 node -43.2325718 -22.9149394 Hospital Doutor Badim
819284886 node -43.2084922 -22.9250651 Hospital do Amparo Feminino
819284917 node -43.2116357 -22.9245512 Casa de Portugal
829527060 node -43.2765982 -22.8958773 Hospital 25 de Dezembro
830435597 node -43.2681899 -22.9061876 Hospital Vital
831326539 node -35.4575903 -8.8411543
840695877 node -46.6435335 -23.5977766 Hospital São Paulo 04024-002 Rua Napoleão de Barros 715
840722121 node -46.6433940 -23.6000575 04040-032 Rua Loefgren 1991
842078
z.B. so:
area["ISO3166-1"="AT"]->.a;
ein Beispiel:
[out:csv(::id,::type,"name","addr:postcode","addr:city","addr:street","addr:housenumber","website"," contact:email=*")][timeout:600];
area["ISO3166-1"="AT"]->.a;
( node(area.a)[amenity=hospital];
way(area.a)[amenity=hospital];
rel(area.a)[amenity=hospital];);
out;
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"];
Wichtig: man bekommt die DATEN zur Boundary Box für Deutschland z.B. hier: http://osmtipps.lefty1963.de/2008/10/bundeslnder.html
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
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;