Maze Posted September 12, 2015 Share Posted September 12, 2015 (edited) dear community,how to extract this dataset?they are results that i get from a request at http://www.overpass-turbo.eua request on the openstreetmap-files: the db or the excel-sheet has got the following categories -...."tags": {"addr:city": "Madrid","addr:housename": "Centro de Salud Cceres","addr:housenumber": "4","addrostcode": "28045","addr:street": "Calle de Cceres","amenity": "hospital","name": "Centro de Salud Cceres" { "version": 0.6, "generator": "Overpass API", "osm3s": { "timestamp_osm_base": "2015-09-12T18:28:02Z", "timestamp_areas_base": "2015-09-11T13:16:03Z", "copyright": "The data included in this document is from www.openstreetmap.org. The data is made available under ODbL." }, "elements": [{ "type": "node", "id": 26209290, "lat": 40.4806985, "lon": -3.6881977, "tags": { "amenity": "hospital", "created_by": "Potlatch 0.5d", "name": "Hospital Universitario La Paz" }},{ "type": "node", "id": 98745210, "lat": 40.4634031, "lon": -3.6966769, "tags": { "addr:housenumber": "317", "addr:postcode": "28020", "addr:street": "Calle de Bravo Murillo", "amenity": "hospital", "name": "Centro de Especialidades José Marvá" }},{ "type": "node", "id": 151223409, "lat": 40.4866713, "lon": -3.6894109, "tags": { "amenity": "hospital", "name": "Dentista" }},{ "type": "node", "id": 151223415, "lat": 40.4856802, "lon": -3.6900103, "tags": { "amenity": "hospital", "name": "Clínica \"MEFRAN\"" }},{ "type": "node", "id": 162497349, "lat": 40.4843288, "lon": -3.6851321, "tags": { "amenity": "hospital", "name": "Centro de Salud Begoña" }},{ "type": "node", "id": 254920246, "lat": 40.4716064, "lon": -3.5819254, "tags": { "amenity": "hospital", "created_by": "Potlatch 0.8a", "name": "Centro de Salud de Barajas" }},{ "type": "node", "id": 301831952, "lat": 40.3792567, "lon": -3.7486322, "tags": { "amenity": "hospital" }},{ "type": "node", "id": 306497542, "lat": 40.3815448, "lon": -3.7597550, "tags": { "amenity": "hospital", "emergency": "no", "name": "Sanatorio Esquerdo" }},{ "type": "node", "id": 343644839, "lat": 40.3887951, "lon": -3.6671951, "tags": { "amenity": "hospital", "created_by": "Potlatch 0.10f", "name": "CEP Vicente Soldevilla" }},{ "type": "node", "id": 418882735, "lat": 40.4896271, "lon": -3.7156311, "tags": { "amenity": "hospital", "name": "Ruber Internacional" }},{ "type": "node", "id": 554167626, "lat": 40.4369220, "lon": -3.6960793, "tags": { "addr:housenumber": "14", "addr:street": "Calle de Modesto Lafuente", "amenity": "hospital", "name": "Clínica La Milagrosa", "website": "http://www.lamilagrosa.com/index.php" }},{ "type": "node", "id": 562624171, "lat": 40.4570659, "lon": -3.6812014, "tags": { "amenity": "hospital", "name": "Fraternidad-Muprespa" }},{ "type": "node", "id": 597375537, "lat": 40.4685882, "lon": -3.6364048, "tags": { "amenity": "hospital", "name": "centro de especialidades Emigrantes" }},{ "type": "node", "id": 601623889, "lat": 40.4551907, "lon": -3.5865399, "tags": { "amenity": "hospital", "name": "Centro de Salud de Alameda de Osuna" }},{ "type": "node", "id": 601650586, "lat": 40.4716063, "lon": -3.5819254, "tags": { "amenity": "hospital", "name": "Centro de Salud de Barajas" }},{ "type": "node", "id": 616223396, "lat": 40.4329668, "lon": -3.6948081, "tags": { "amenity": "hospital", "name": "Clínica Universal", "url": "http://www.elseguromedico.es/hospitales-grupo-cisne.html" }},{ "type": "node", "id": 616753867, "lat": 40.4016643, "lon": -3.7144315, "tags": { "addr:housenumber": "180", "addr:street": "Calle de Toledo", "amenity": "hospital", "name": "Centro de Salud Paseo Imperial" }},{ "type": "node", "id": 617052728, "lat": 40.4068044, "lon": -3.7127489, "tags": { "addr:housenumber": "52", "addr:street": "Ronda de Segovia", "amenity": "hospital", "name": "Centro de Salud Pontones" }},{ "type": "node", "id": 622424091, "lat": 40.4014463, "lon": -3.7507714, "tags": { "amenity": "hospital", "name": "Centro de Salud de La Laguna" }},{ "type": "node", "id": 747752260, "lat": 40.4724003, "lon": -3.6423344, "tags": { "amenity": "hospital", "designation": "Centro de Atención Primaria", "emergency": "yes", "name": "Ambulatorio Mar Báltico Hortaleza" }},{ "type": "node", "id": 845762122, "lat": 40.5069289, "lon": -3.6667094, "tags": { "amenity": "hospital", "name": "Sanitas Las Tablas" }},{ "type": "node", "id": 903735671, "lat": 40.4278860, "lon": -3.7168927, "tags": { "amenity": "hospital", "name": "Centro de Salud Argüelles" }},{ "type": "node", "id": 903736802, "lat": 40.4311587, "lon": -3.7134975, "tags": { "amenity": "hospital", "name": "Centro de Salud Guzmán el Bueno" }},{ "type": "node", "id": 914502455, "lat": 40.3820520, "lon": -3.7771888, "tags": { "amenity": "hospital", "name": "Centro de Salud General Fanjul" }},{ "type": "node", "id": 937066557, "lat": 40.4164536, "lon": -3.6989409, "tags": { "amenity": "hospital", "name": "Centro de Salud Las Cortes" }},{ "type": "node", "id": 1437313175, "lat": 40.3880668, "lon": -3.7449941, "tags": { "amenity": "hospital" }},{ "type": "node", "id": 1502885315, "lat": 40.4622833, "lon": -3.6953859, "tags": { "amenity": "hospital", "emergency": "no", "name": "Centro de Salud Bustarviejo" }},{ "type": "node", "id": 1558379893, "lat": 40.3654048, "lon": -3.6005187, "tags": { "amenity": "hospital", "name": "Clínicas dentales Familymed" }},{ "type": "node", "id": 1595009310, "lat": 40.4267353, "lon": -3.6162313, "tags": { "amenity": "hospital" }},{ "type": "node", "id": 1595068136, "lat": 40.4514854, "lon": -3.5832106, "tags": { "amenity": "hospital" }},{ "type": "node", "id": 1947229180, "lat": 40.4679316, "lon": -3.7267958, "tags": { "amenity": "hospital", "name": "Ambulatorio Isla de Oza" }},{ "type": "node", "id": 2320596216, "lat": 40.4123734, "lon": -3.7508480, "tags": { "amenity": "hospital" }},{ "type": "node", "id": 2418082558, "lat": 40.3984815, "lon": -3.6948436, "tags": { "addr:city": "Madrid", "addr:housename": "Centro de Salud Cáceres", "addr:housenumber": "4", "addr:postcode": "28045", "addr:street": "Calle de Cáceres", "amenity": "hospital", "name": "Centro de Salud Cáceres" } Edited September 12, 2015 by Maze Quote Link to comment Share on other sites More sharing options...
Maze Posted September 12, 2015 Author Share Posted September 12, 2015 (edited) hello dear all ` the db or the excel-sheet has got the following categories -.... "version": 0.6, "generator": "Overpass API", "osm3s": { "timestamp_osm_base": "2015-09-12T20:05:02Z", "timestamp_areas_base": "2015-09-11T13:16:03Z", "copyright": "The data included in this document is from www.openstreetmap.org. The data is made available under ODbL." }, "elements": [ { "type": "node", "id": 248528643, "lat": 41.9216283, "lon": 12.5403769, "tags": { "amenity": "hospital", "emergency": "yes", "name": "Pronto Soccorso" } }, { "type": "node", "id": 262114028, "lat": 41.8282625, "lon": 12.4184520, "tags": { "addr:city": "Roma", "addr:housenumber": "13", "addr:postcode": "00148", "addr:street": "Via Luigi Ercole Morselli", "amenity": "hospital", "name": "Ospedale San Giovanni Battista", "operator": "Associazione dei Cavalieri Italiani del Sovrano Ordine di Malta", "ref:vatin": "IT00995661006", "website": "http://www.ordinedimaltaitalia.org/acismom-attivita-sanitaria" } }, { "type": "node", "id": 301593099, "lat": 41.8747002, "lon": 12.4562978, "tags": { "amenity": "hospital", "name": "Clinica Città di Roma", "source": "PCN" } }, { "type": "node", "id": 365299638, "lat": 41.8357070, "lon": 12.4284693, "tags": { "amenity": "hospital", "created_by": "Potlatch 0.10f", "name": "Ospedale Israelitico" } }, { "type": "node", "id": 428571538, "lat": 41.8503399, "lon": 12.4208258, "tags": { "amenity": "hospital", "name": "Villa Sandra", "wheelchair": "yes" } }, { "type": "node", "id": 434692525, "lat": 41.8870757, "lon": 12.3983943, "tags": { "amenity": "hospital", "emergency": "yes", "name": "Aurelia Hospital", "url": "http://www.aureliahospital.com/", "wheelchair": "yes" } }, { "type": "node", "id": 677112577, "lat": 41.8663812, "lon": 12.4573888, "tags": { "addr:city": "Roma", "addr:housenumber": "292", "addr:street": "Via Portuense", "amenity": "hospital", "name": "I.N.M.I. L. Spallanzani", "website": "http://www.inmi.it/" } }, { "type": "node", "id": 677114577, "lat": 41.8689538, "lon": 12.4560799, "tags": { "amenity": "hospital", "name": "San Camillo", "website": "www.scamilloforlanini.rm.it/" } }, { i want to store all the results in a a. excel-sheet or b. mysql-db note: the db or the excel-sheet has got the following categories -.... "addr:city": "Madrid","addr:housename": "Centro de Salud Cceres","addr:housenumber": "4","addrostcode": "28045","addr:street": "Calle de Cceres","amenity": "hospital","name": "Centro de Salud Cceres" how to achieve that Edited September 12, 2015 by Maze Quote Link to comment Share on other sites More sharing options...
Maze Posted September 12, 2015 Author Share Posted September 12, 2015 (edited) hello - i ve gained some more ideas. What i ve done so far?! I tried to extract some data from an .Osm File. -.-you can run a demo-command here at overpass turbo i want to store the data in a database - (or at least in an excel-file)So I have - within an amount of xml-data - something like this: "tags": { "addr:city": "Barcelona", "addr:housenumber": "1", "addr:postcode": "08022", "addr:street": "Carrer de Torras i Pujalt", "amenity": "hospital", "contact:email": "info@clinicasagradafamilia.com", "contact:fax": "+34 932124050", "contact:phone": "+34 932122300", "contact:website": "http://csf.com.es/", "name": "Cliníca Sagrada Família" } So, the question is this: how can store all those values I tried to make some kind of foreach without sucess.And definitely I don't know why is suposse we have one Iteration that get all the values andwhy is not possible to store together.Well i heard about that we can use SimpleXMLElement::xpath here to get all the valuesand the corresponding values. For example : $raw = <<<EOF <root> { "type": "node", "id": 583257940, "lat": 41.4134446, "lon": 2.1426945, "tags": { "amenity": "hospital", "emergency": "yes", "name": "Clinica Delfos" } }, { "type": "node", "id": 618312181, "lat": 41.4138593, "lon": 2.1970778, "tags": { "addr:city": "Barcelona", "addr:housenumber": "211", "addr:postcode": "08020", "addr:street": "Carrer de Fluvià", "amenity": "hospital", "health_facility:type": "health_centre", "medical_system:western": "yes", "name": "CAP Sant Martí", "phone": "+93 307 07 66" } }, { "type": "node", "id": 876348001, "lat": 41.3841883, "lon": 2.1952253, "tags": { "amenity": "hospital", "emergency": "yes", "name": "Hospital del Mar", "website": "http://www.parcdesalutmar.cat/hospitals/hospital-del-mar/index.html", "wheelchair": "yes" } }, </root> EOF; $xml = simplexml_load_string($raw); foreach($xml->xpath("//way") AS $way){ $via = $way->xpath("tag[@k='name']/@v")[0]; foreach($way->nd AS $nd){ $idnode = $nd["ref"]; echo $idnode .", ". $via ."<br>"; } } question: how to use SimpleXMLElement::xpath here?btw - you can run a demo-command here at overpass turbo /* This has been generated by the overpass-turbo wizard. The original search was: “hospital in rome” */ [out:json][timeout:25]; // fetch area “” to search in {{geocodeArea:rome}}->.searchArea; // gather results ( // query part for:rome “hospital” node["amenity"="hospital"](area.searchArea); way["amenity"="hospital"](area.searchArea); relation["amenity"="hospital"](area.searchArea); ); // print results out body; >; out skel qt; then you get lots of results....question: how to store those results in either excel or mysql?love to hear from youbest matze btw: can have something to do with the following: well to create the db is no problem at all - but to put (port over the xml) seems to be a bit tricky.i have managed to get the easier part -eg the following_create a db-connect: ?php mysql_connect("mydb","user","passwd") or die ("no connecting possible");; mysql_select_db("name of the db") or die ("the db does not exist.");; mysql_query("SET NAMES 'utf8'"); ?>last line is necessary for ensuring that the UTF8-coded data are transferred correctly and subsequently another part - see the file for the request Edited September 12, 2015 by Maze Quote Link to comment Share on other sites More sharing options...
Barand Posted September 12, 2015 Share Posted September 12, 2015 As long as you carry on trying to process it as XML you are going to have a problem. Think "JSON". Use json_decode Quote Link to comment Share on other sites More sharing options...
Barand Posted September 12, 2015 Share Posted September 12, 2015 If you are writing to a db table CREATE TABLE `node` ( `id` bigint(20) NOT NULL, `amenity` varchar(30) DEFAULT NULL, `name` varchar(100) DEFAULT NULL, `housenumber` varchar(5) DEFAULT NULL, `housename` varchar(50) DEFAULT NULL, `street` varchar(50) DEFAULT NULL, `city` varchar(50) DEFAULT NULL, `postcode` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; this code will do it $jsn = 'INSERT YOUR DATA HERE'; $jsn = str_replace('addr:','',$jsn); // legalise db column names $data = json_decode($jsn,1); // create array from json data $template = array ( // template array for db inserts 'id' => '', 'amenity' => '', 'name' => '', 'housenumber' => '', 'housename' => '', 'street' => '', 'city' => '', 'postcode' => '' ); $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); // prepare the sql statement $sql = "INSERT INTO node (id, amenity, name, housenumber, housename, street, city, postcode) VALUES (?,?,?,?,?,?,?,?)"; $stmt = $db->prepare($sql); $stmt->bind_param('isssssss',$id, $amenity, $name, $housenumber, $housename, $street, $city, $postcode); // process the data foreach ($data['elements'] as $elem) { $tags = array_intersect_key($elem['tags'],$template); // get the tags we need $nodedata = array_merge($template,$tags); // add tag data to template array $nodedata['id'] = $elem['id']; // add id to template array extract($nodedata); $stmt->execute(); // insert in db table "node" } Quote Link to comment Share on other sites More sharing options...
Maze Posted September 15, 2015 Author Share Posted September 15, 2015 hello dear Barand many many thanks - great !! You have helped me alot! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.