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 with this form on londinium.com, ilminster.net or via Twitter @andylondon