I wanted to improve the area maps on londinium.com by adding a list of the websites nearby to each location. I started with the query I blogged about a few posts back, so I started with just the nodes, but large objects like hospitals didnt appear in the first list. This is because they are not nodes, rather ways and relations. The problem with these non-nodes is that they didnt have a lat/long in the same way the nodes did. But I found you could use the center output mode and replace out with out center so i my query for extracting websites is now put in a query file called query.osm like so:

data=[out:json];nwr[~"^(website|twitter|facebook|contact:website|contact:twitter|contact:facebook)$"~"."]  (51.11386850819646,-1.197509765625,51.92394344554469,0.85418701171875);out center;

Note that the export area is encoded in the query, and the output format is json.

To download the osm data with this command using the 'query.osm' file above run:

wget -O osm.json --post-file=query.osm "https://overpass-api.de/api/interpreter"

To parse the osm.json file to extract data and create an insert sql file, I amended my parseXml.php file like so

I am now donwloading json, so i needed to change the parse file a lot. It is much cleaner and easier with json to extract the data

<?php

error_reporting(E_ALL ^ E_WARNING); // disable warnings as this is an sql import

$json = file_get_contents('osm.json');
$phpDataArray = json_decode($json, false ); 

foreach($phpDataArray as $value){

    foreach($value as $row){

        if ($row->tags->website){    $website = $row->tags->website;}
        else    $website = $row->tags->{'contact:website'};

        // if the website is not valid or blank - continue, ignoring this row
        if (filter_var($website, FILTER_VALIDATE_URL) === false) {
            continue;
        }

        if ($row->id){
            $nwr = $row->type; // Node, Way, Relation

            if ($row->lat){
                $lat = $row->lat;
            }
            else
                $lat = $row->center->lat;

            if ($row->lon){
                $lon = $row->lon;
            }
            else
                $lon = $row->center->lon;


            $address = $row->tags->{'addr:housenumber'} . " " . $row->tags->{'addr:housename'} . " " .
            $row->tags->{'addr:unit'} . " " . $row->tags->{'addr:street'} . " " .
            $row->tags->{'addr:place'} . " " . $row->tags->{'addr:city'} . " " .
            $row->tags->{'addr:postcode'};            

            $feature = "";

            if($row->tags->railway){ $feature = $row->tags->railway;}
            if($row->tags->public_transport){ $feature = $row->tags->public_transport;}
            if($row->tags->highway){ $feature = $row->tags->historic;}
            if($row->tags->building && ($row->tags->building != "yes") ){ $feature = $row->tags->building;}
            if($row->tags->natural){ $feature = $row->tags->natural;}
            if($row->tags->sport){ $feature = $row->tags->sport;}
            if($row->tags->leisure){ $feature = $row->tags->leisure;}
            if($row->tags->landuse){ $feature = $row->tags->landuse;}
            if($row->tags->craft){ $feature = $row->tags->craft;}
            if($row->tags->office){ $feature = $row->tags->office;}
            if($row->tags->tourism){ $feature = $row->tags->tourism;}
            if($row->tags->shop){ $feature = $row->tags->shop;}
            if($row->tags->amenity){ $feature = $row->tags->amenity;}

            $amenity = "";
            if($row->tags->amenity){ $amenity = $row->tags->amenity;}

            $building = "";            
            if($row->tags->building && ($row->tags->building != "yes") ){ $building = $row->tags->building;}

            $tourism = "";            
            if($row->tags->tourism){ $tourism = $row->tags->tourism;}

            $craft = "";
            if($row->tags->craft){ $craft = $row->tags->craft;}

            //social

            if ($row->tags->twitter){    $twitter = $row->tags->twitter;}
            else    $twitter = $row->tags->{'contact:twitter'};

            if ($row->tags->facebook){    $facebook = $row->tags->facebook;}
            else    $facebook = $row->tags->{'contact:facebook'};

            if ($row->tags->instagram){    $instagram = $row->tags->instagram;}
            else    $instagram = $row->tags->{'contact:instagram'};

            if ($row->tags->linkedin){    $linkedin = $row->tags->linkedin;}
            else    $linkedin = $row->tags->{'contact:linkedin'};

            // make the sql using INSERT IGNORE to prevent errors
            echo "INSERT IGNORE INTO `points` (`id`, `website`, `address`, `feature` ,`amenity` ,`building` ,
            `tourism` ,`craft` , `twitter`,  `facebook`,  `linkedin`, `instagram`,  `name`, `nwr`, `point`) ";
            echo "VALUES(" . $row->id .", '" . addslashes($website) . "', '". addslashes(trim($address)) . "', '".
             addslashes($feature) . "', '" .addslashes($amenity) . "', '" .addslashes($building) . "', '" .
             addslashes($tourism) . "', '" .addslashes($craft) . "', '" . $twitter . "', '". $facebook 
             . "', '". $linkedin . "', '". $instagram . "', '" . addslashes($row->tags->name). "', '".
              $nwr . "', POINT(" . $lat . " , " . $lon ." ));"; 

            echo PHP_EOL;
        }
    }
}

To create the sql file, I ran it like this

parseJson.php > points.sql

I had to extract the nwr (Node/Way/Relation) to be able to link to the live db database on openstreetmap. I also renamed the field type as feature so the new database table is like so:

CREATE TABLE `points` (
  `id` varchar(11) NOT NULL,
  `nwr` varchar(255) NOT NULL,
  `feature` varchar(255) NOT NULL,
  `amenity` varchar(255) NOT NULL,
  `building` varchar(255) NOT NULL,
  `tourism` varchar(255) NOT NULL,
  `craft` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `website` varchar(255) NOT NULL,
  `twitter` varchar(255) NOT NULL,
  `facebook` varchar(255) NOT NULL,
  `linkedin` varchar(255) NOT NULL,
  `instagram` varchar(255) NOT NULL,
  `point` point NOT NULL,

  PRIMARY KEY (`id`),
  SPATIAL KEY `point` (`point`)
) ENGINE=InnoDB;

import into db using pipe viewer to see progress

pv points.sql | mysql -u root -p  londinium

And hey presto, a table full of data with each entity having geographic data.

There is still a number of 'dodgy' entries, which i plan to clean up at the next stage of this this import.

If you would like to contact me with this form on londinium.com, ilminster.net or via Twitter @andylondon