Jump to content
Sign in to follow this  
dil_bert

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

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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).

  • Like 1

Share this post


Link to post
Share on other sites

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 ;)

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.