From OpenStreetMap Wiki
Jump to: navigation, search

some interesting threads

soon more to come

more problem-solving things:

the problem and a solution - explained here in this thread:

see extract-this-dataset-xml-to-db

question: how to extract a dataset that is derived from a request at

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

Barants hints: As long as you carry on trying to process it as XML you are going to have a problem. Think "JSON". we need to have a closer look at 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`)

this code will do it


$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' => '' 


// 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
    $stmt->execute();                                      // insert in db table "node"