OSM Data parsing to get the nodes with child - store in a database

[dil_bert]

The following is a little OSM Overpass API example with PHP SimpleXML I've compiled because we do not have it here for PHP and I love OSM, so let's show some useful examples.

The first part shows how tro query an Overpass Endpoint with standard PHP.
For you the second part is more interesting. That is querying the XML data; This is most easily done with xpath, the used PHP XML library is based on libxml which supports XPath 1.0 which covers the various querying needs very well.


see the code: ... and

Goal: The output has to be stored into a mysql-db:

<?php
/**
 * OSM Overpass API with PHP SimpleXML / XPath
 *
 * PHP Version: 5.4 - Can be back-ported to 5.3 by using 5.3 Array-Syntax (not PHP 5.4's square brackets)
 */
//
// 1.) Query an OSM Overpass API Endpoint
//

$query = 'node
  ["amenity"~".*"]
  (-54.5247541978, 2.05338918702, 9.56001631027, 51.1485061713);
out;';

$context = stream_context_create(['http' => [
    'method'  => 'POST',
    'header' => ['Content-Type: application/x-www-form-urlencoded'],
    'content' => 'data=' . urlencode($query),
]]);

# please do not stress this service, this example is for demonstration purposes only.
$endpoint = 'http://overpass-api.de/api/interpreter';
libxml_set_streams_context($context);
$start = microtime(true);

$result = simplexml_load_file($endpoint);
printf("Query returned %2\$d node(s) and took %1\$.5f seconds.\n\n", microtime(true) - $start, count($result->node));

//
// 2.) Work with the XML Result
//

# get all school nodes with xpath
$xpath = '//node[tag[@k = "amenity" and @v = "school"]]';
$schools = $result->xpath($xpath);
printf("%d School(s) found:\n", count($schools));
foreach ($schools as $index => $school)
{
# Get the name of the school (if any), again with xpath
list($name) = $school->xpath('tag[@k = "name"]/@v') + ['(unnamed)'];
list($website) = $school->xpath('tag[@k = "website"]/@v') + ['(no website)'];
list($email) = $school->xpath('tag[@k = "contact:email"]/@v') + ['(no email)'];
printf("#%02d: ID:%' -10s  [%s,%s]  %s %s %s\n", $index, $school['id'], $school['lat'], $school['lon'], $name, $website, $email);
}


//node[tag[@k = "amenity" and @v = "school"]]
//tag[@k = "name"]/@v'

$query = 'node
  ["addr:postcode"~"RM12"]
  (51.5557914,0.2118915,51.5673083,0.2369398);
   node
  (around:1000)
  ["amenity"~"fast_food"];
           out;';

$context = stream_context_create(['http' => [
    'method'  => 'POST',
    'header' => ['Content-Type: application/x-www-form-urlencoded'],
    'content' => 'data=' . urlencode($query),
]]);

$endpoint = 'http://overpass-api.de/api/interpreter';
libxml_set_streams_context($context);

$result = simplexml_load_file($endpoint);
printf("Query returned %2\$d node(s) and took %1\$.5f seconds.\n\n", microtime(true) - $start, count($result->node));

so far so good: see the results:

#37158: ID:5243708521  [-3.6852719,33.4154523]  Buhangija primary school (no website) (no email)
#37159: ID:5243805321  [-1.5071460,31.1599459]  Kakiro Primary School (no website) (no email)
#37160: ID:5243883144  [-10.4578962,39.0935938]  Nangoo primary school (no website) (no email)
#37161: ID:5244163476  [-6.8637976,39.2586565]  PK Academic School (no website) (no email)
#37162: ID:5244163478  [-6.8603893,39.2564774]  Good Shepherd School (no website) (no email)
#37168: ID:5247155223  [-1.6856759,29.0159534]  Institut Miteetso (no website) (no email)
#37169: ID:5247165222  [-1.7063811,29.0168647]  Institut Lwanga-Bobandana (no website) (no email)
#37170: ID:5247312700  [9.0129509,38.7290080]  EiABC (Ethiopian Institute of Architecture Building Construction & City Devlopment) www.eiabc.edu.et (no email)
#37171: ID:5248364671  [-4.3973951,29.1373596]  École primaire Makama (no website) (no email)
#37172: ID:5248372022  [-1.7101942,29.0233846]  Institut Minova (no website) (no email)
#37173: ID:5248439027  [-4.2744570,35.7445025]  F . t . sumaye secondary school (no website) (no email)
#37177: ID:5248974492  [-4.5490651,29.1559410]  École primaire Kilicha (no website) (no email)
#37178: ID:5248974495  [-4.5476651,29.1508141]  École primaire Kazimia 2 (no website) (no email)
#37179: ID:5248974504  [-4.5543585,29.1479282]  École primaire Lwata (no website) (no email)

so - and now i need to store all that stuff into a mysql-db


i have to think "JSON". we need to have a closer look at http://php.net/json_decode So a idea for  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"
}

[requinix]

So... do you have a question?

[dil_bert]

hello dear requinix,

 

 

many thanks for the quick reply.

 

 

what do i do if i have no results for "all" not all entries

 

you see that we have lines that do not have the "full" dataset!

#37158: ID:5243708521  [-3.6852719,33.4154523]  Buhangija primary school (no website) (no email)
#37159: ID:5243805321  [-1.5071460,31.1599459]  Kakiro Primary School (no website) (no email)
#37160: ID:5243883144  [-10.4578962,39.0935938]  Nangoo primary school (no website) (no email)
#37161: ID:5244163476  [-6.8637976,39.2586565]  PK Academic School (no website) (no email)
#37162: ID:5244163478  [-6.8603893,39.2564774]  Good Shepherd School (no website) (no email)

so - that this have any "impact" on the setup of the database`?

 

regards

dil_bert

[requinix]

Are you talking about the missing websites and emails? Doesn't seem like it should matter to you since you aren't storing those in your database in the first place... Or maybe you're asking how they should be stored in the node table? I would just put them in the table and make the column nullable (and make sure to insert NULL when they're missing).

[dil_bert]

hello dear requinix  - many thanks for the reply

 

great to hear from you -

 

 

would just put them in the table and make the column nullable (and make sure to insert NULL when they're missing).

 

 

many many thanks!!!

 

This is such a great page -and so great support.

 

Many thanks for all you did