how to extract this dataset

sayhello_to_the_world · 09-12-2015, 01:21 PM

dear community,

how to extract this dataset?

they are results that i get from a request at http://www.overpass-turbo.eu

a 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"

Quote:

{
"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

ostcode": "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": "Clnica "MEFRAN""
}
},
{
"type": "node",
"id": 162497349,
"lat": 40.4843288,
"lon": -3.6851321,
"tags": {
"amenity": "hospital",
"name": "Centro de Salud Begoa"
}
},
{
"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": "Clnica 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": "Clnica 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 Atencin Primaria",
"emergency": "yes",
"name": "Ambulatorio Mar Bltico 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 Argelles"
}
},
{
"type": "node",
"id": 903736802,
"lat": 40.4311587,
"lon": -3.7134975,
"tags": {
"amenity": "hospital",
"name": "Centro de Salud Guzmn 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": "Clnicas 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 Cceres",
"addr:housenumber": "4",
"addr

ostcode": "28045",
"addr:street": "Calle de Cceres",
"amenity": "hospital",
"name": "Centro de Salud Cceres"
}

how can i do that

Rinndalir · 09-12-2015, 02:32 PM

Seems to be the authority on that data, but maybe you are asking for how to turn it into something else.

http://overpass-api.de/output_formats.html#json

It's json and there are many ways to extract the data. What programming language do you know?

sayhello_to_the_world · 09-12-2015, 04:43 PM

hello dear Rinndalir

first of all - many many thanks for the quick answer.

i know php.... but i am no expert here... - unfortunatley...

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:

Code:


  "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 and why is not possible to store together.

Well i heard about that we can use SimpleXMLElement::xpath here to get all the values
and the corresponding values. For example :

Code:

$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

Code:

/*
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?

dear Rinndalir

i really love to hear from you

many thanks in advance

TB0ne · 09-13-2015, 08:39 PM

Quote:

Originally Posted by sayhello_to_the_world

hello dear Rinndalir
first of all - many many thanks for the quick answer.
i know php.... but i am no expert here... - unfortunatley...

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:

then you get lots of results....question: how to store those results in either excel or mysql?

Since you've been working with PHP, MySQL,and XML data for OVER A YEAR NOW, and have been given much advice, whats the issue? Also, you've said previously that you "do lots of work with PHP" (post #3 here)
http://www.linuxquestions.org/questi...db-4175506044/

...which ALSO asks about storing XML data in a MySQL database...like this OTHER thread asking the same thing.
http://www.linuxquestions.org/questi...ql-4175508017/

You STILL DO NOT show any effort of your own...where is the code that YOU wrote???? You were pointed to the (very clear) documentation over a year ago....did you read it? What have you tried???

Read the "Question Guidelines" link in my posting signature. You appear to be wanting someone to give you a handout and fix your problem for you, rather than showing any effort of your own towards solving it. After a years worth of effort, and ample documentation available to you, why have you made no progress?

Rinndalir · 09-16-2015, 01:36 PM

Quote:

Originally Posted by sayhello_to_the_world

hello dear Rinndalir

first of all - many many thanks for the quick answer.

i know php.... but i am no expert here... - unfortunatley...

First thing you need to understand and always live close to is this:
"Programmer traits:
Laziness
Hubris
Impatience"

In your case at least laziness may have kicked in, this is a good sign. Impatience may not have or you would have scoured the openstreetmap site looking for an excel export feature (a.k.a as CSV, hint-hint). Hubris is more advanced and so won't hold that against you at this point in time.

So it seems that, yes, there is an CSV export feature. Let us know if it works. Or if hubris kicks in just say heck with it and don't reply.

grail · 09-16-2015, 01:51 PM

I am with the above posts. Some nearly 200 posts on LQ and you still do not seem to know how to ask a question properly OR show any sign that you are attempting to solve the problem yourself.