Extracting The Data From The Overpass API (Part 2) Extracting The Data From The Overpass API (Part 2)

December 23, 2021

laravel openstreetmap

Furthermore to the last blog, I updated the php script to output sql, created a new database table, imported it into the database and started to output the data.

Going to use the new(ish) point datatype in mysql.

CREATE TABLE `points` (
  `id` varchar(11) NOT NULL,
  `website` varchar(255) NOT NULL,
  `twitter` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `point` point NOT NULL,

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

I amended the php script in yesterdays post to output sql.

<?php

$xmlDataString = file_get_contents('osmData.xml');
$xmlObject = simplexml_load_string($xmlDataString);

$json = json_encode($xmlObject);
$phpDataArray = json_decode($json, false ); 

foreach($phpDataArray as $value){
    foreach($value as $row){
        $name = "";
        $twitter = "";

        foreach($row->{'tag'} as $attributes){
            if($attributes->{'@attributes'}->{'k'} == 'name'){
                $name = ($attributes->{'@attributes'}->{'v'});
            }

            if($attributes->{'@attributes'}->{'k'} == 'contact:twitter'){
                $twitter = ($attributes->{'@attributes'}->{'v'});
            }

            if($attributes->{'@attributes'}->{'k'} == 'website'){

                echo "INSERT INTO `points` (`id`, `website`, `twitter`, `name`, `point`) ";
                echo "VALUES(" . $row->{'@attributes'}->id .", '" . $attributes->{'@attributes'}->{'v'} . "', '". $twitter . "', '" . addslashes($name) . "', POINT(" . $row->{'@attributes'}->lat . " , " . $row->{'@attributes'}->lon ." ));"; 

                echo PHP_EOL;
            }
        }
    }
}

To save the sql i ran

php xmlParser.php > points.sql

To import it to the database i run

mysql -u root -p dbName < points.sql

There was a couple of problems due to 2 urls having ' in them! Neither actually worked (404 errors) so i manually deleted them from the import table, deleted the table contents and started again. Third time lucky, i had a new db table with over 10,000 records.

Now i can run a query like this:

SELECT * FROM points WHERE ST_Distance_Sphere(point, POINT(51.49591970845512, -0.26298522949218756)) <= 2000 ;

which returns 100 websites within 2500 metres of the point (in chiswick)! This works for all the points i tried including much shorter coordinates.

SELECT * FROM points WHERE ST_Distance_Sphere(point, POINT(51.49, -0.14)) <= 2000 ;

Now I wanted to improve it further and show and order by the distance from the centre point

SELECT id, name, website,
  ST_X(point) AS longitude,
  ST_Y(point) AS latitude,
  ST_DISTANCE_SPHERE(point, POINT(51.49455119685909, -0.14415264129638675)) AS dist
FROM points 
HAVING dist < 500
ORDER BY  dist;

Now i am going to improve the import parser to add the addresses to make it easier to see problems.

Changing the INSERT command to INSERT IGNORE to ignore errors


If you would like to contact me you can either use this form on londinium.com or via Twitter @andylondon