A map with the most recent Streetview capture dates per street: the PHP and SQL scripts I use

A couple of years ago I wrote a blog post about my way of creating overview maps of the availability of the most recent Google Streetview images per municipality. I still do these checks these days for Antwerp, Kontich and Edegem, so that way I always have up-to-date information about the availability of these images. Recently I got the asked (in Dutch) to share my scripts I use for this task. I wanted to do so, but these PHP scripts I use, were a bit messy, because I was the only one using them. That’s why I updated them a little, so they can be used by more people.

To do these checks, I use a MySQL table and some PHP scripts. Below I describe how you can use these. It is also possible to do the same checks using for example Python and PostgreSQL. If you want to do that, you can use the scripts below as inspiration. It probably isn’t that difficult to translate these scripts to your favorite programming language and database.

Making the MySQL table

With the SQL code below, you can make an empty MySQL table. You can run this script in for example phpMyAdmin or any other MySQL management tool.

createtable.sql

CREATE TABLE `StreetViewData` (
  `fid` int(11) NOT NULL DEFAULT '0',
  `x` double NOT NULL DEFAULT '0',
  `y` double NOT NULL DEFAULT '0',
  `xgoogle` double NOT NULL DEFAULT '0',
  `ygoogle` double NOT NULL DEFAULT '0',
  `lastcheck` mediumtext,
  `datestreetview` mediumtext,
  `municipality` varchar(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `StreetViewData`
  ADD PRIMARY KEY (`fid`),
  ADD UNIQUE KEY `fid` (`fid`);

The fields ‘x’ and ‘y’ are the coordinates of the locations I want to enter into the Google API to do the checks on. The fields ‘xgoogle’ and ‘ygoogle’ are the coordinates of where the Streetview images were captured near the location where we asked the API information about. It’s these ‘xgoogle’ and ‘ygoogle’ coordinates we’ll use afterwards to show the results on a map.

Fill the MySQL tabel

Now we have to fill the empty table we made, with the locations we want to check. To do so we use a road map (Wvb from the GRB in my case) and convert it to a points layer in QGIS.

A road map is usually a polylines layer, but for our requests to Google we’ll need the X and Y coordinates of points. That’s why we’ll have to convert the road map to a points layer. To do so, we’ll use QGIS and the plugin “Locate points along lines” that can be downloaded from the official QGIS plugins repository. With this plugin we’ll add every 5 meter a point to our new points layer. In that points layer we add data to 2 columns for the X and Y coordinates in WGS84 (EPSG:4326). A fid is also added to have a unique id per point. Because I want to able to split up the data per municipality, I also added the field ‘municipality’. If you don’t need this, you can leave this field empty.

To copy the data from the points layer to the MySQL table we created, there are several different possibilities. For example you can export the data from your points layer to a csv file. That file can be imported into your MySQL with for example phpMyAdmin. There are for example many other ways to achieve this. It isn’t important how you do it, as long as your points data as added to your MySQL table.

In the MySQL table it is important that the fields ‘fid’, ‘x’ and ‘y’ contain data for each point. If the municipality is important for you, you should add that information now too. The fields ‘xgoogle’, ‘ygoogle’, ‘lastcheck’ and ‘datestreetview’ can be left empty for now, because these fields will be filled with data by the PHP checking script.

The PHP script used to do the checks

We will check all the points in our MySQL table one by one to know if a Streetview image is available nearby. The Google API we use, uses a buffer to check where a Streetview image is captured nearby. If in the proximity of the checked point there was never captured a Streetview image, the fields ‘xgoogle’ and ‘ygoogle’ are set to 0 and the field ‘datestreetview’ gets the value ‘no images’.

To get to this result, we use the PHP script below. By using a cron job in Linux or the task scheduler in Windows, we can automate running this script. In the script you have to adjust the settings to that of your MySQL database and table. You also have to change the number of points you want to check each time the script is run. What happens in the script is explained in the comments in the script.

check.php

<?php
//// DB settings ////
$DB_HOST="localhost"; // Your MySQL host
$DB_NAME="mydatabase"; // Your MySQL database
$DB_USER="myusername"; // Your MySQL username
$DB_PASS="mypassword"; // Your MySQL password
$DB_TABLE="StreetViewData"; // Your MySQL table
/////////////////////////

//// Set number of checks ////
$numberofchecks=10;
//////////////////////////////

///// Get locations to check from MySQL database /////
$mysqli = mysqli_connect($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
$requestquery = "SELECT * FROM " . $DB_TABLE . " ORDER BY lastcheck LIMIT ".$numberofchecks;
$result = $mysqli->query($requestquery);
/////////////////////////////////////////////

///// Check each location //////
while ($row = mysqli_fetch_array($result)) {
    $fid = urlencode($row["fid"]);
    $x = urlencode($row["x"]);
    $y = urlencode($row["y"]);
    $xmlurl = "http://maps.google.com/cbk?output=xml&ll=$y,$x";
    $xml=file_get_contents($xmlurl);
    libxml_use_internal_errors(true);
    $simplexml=simplexml_load_string($xml);
    if ($simplexml === false) {
        $datestreetview='';
        $xgoogle='';
        $ygoogle='';
    }
    else{
        $attributes=$simplexml->data_properties->attributes();
        $datestreetview = $attributes->image_date;
        $xgoogle = $attributes->lng;
        $ygoogle = $attributes->lat;
    }
    $now   = date("Ymdhis");
    if ($datestreetview == '') {
        $datestreetview = 'no images';
    }
    echo("<strong>Checked location id: ".$fid."</strong><br />");
    echo("Checked locatien: $x - $y<br />");
    echo("Date of Streetview Image: $datestreetview<br />");
    echo("Streetview Image found at $xgoogle - $ygoogle<br />");
    echo("<hr />");
////////////////////////////////////

    ///// Update info for each location in MySQL table //////
    $updatequery = $mysqli->prepare("UPDATE " . $DB_TABLE . " SET `lastcheck`='" . $now . "', `datestreetview`=?, `xgoogle`=?, `ygoogle`=? WHERE `fid`=?");
    $updatequery->bind_param("ssss",$datestreetview, $xgoogle, $ygoogle, $fid);

    if (!$updatequery->execute()) {
        printf("Errormessage: %s\n", $updatequery->error);
    }

    $counter++; //////////////////////////////////////////////////////////
 
}


//// Close database connection /////
mysqli_close($mysqli);
?> 

EDIT (March 23, 2020): The above script was updated by adding the bind_param function to make it more secure. Thank you Johan Van de Wauw for the suggestion.

The PHP script used to make a GeoJSON file

With the script below the data in the MySQL table is converted into a GeoJSON file. The url of that file can directly be read by QGIS to create a map. If your MySQL table contains many points, make sure you don’t overload your database. To avoid too many requests to the database, you can save the result to your computer with the extension geojson. These kind of files can easily be read by QGIS.

geosjon.php

<?php
//// DB settings ////
$DB_HOST="localhost"; // Your MySQL host
$DB_NAME="mydatabase"; // Your MySQL database
$DB_USER="myusername"; // Your MySQL username
$DB_PASS="mypassword"; // Your MySQL password
$DB_TABLE="StreetViewData"; // Your MySQL table
/////////////////////////

////////  Connect to MySQL database
$mysqli = mysqli_connect($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

////////  Query the data (and remove doubles and points with no images)
$sql = 'SELECT min(fid) as fid, xgoogle, ygoogle, municipality, datestreetview FROM '.$DB_TABLE.' WHERE xgoogle!=0 AND ygoogle!=0 GROUP BY xgoogle, ygoogle, datestreetview, municipality';
$result = $mysqli->query($sql);

////////  Build GeoJSON feature collection array
$geojson = array(
   'type'      => 'FeatureCollection',
   'features'  => array()
);
////////  Loop through rows to build feature arrays
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $properties = $row;

    ////////  add year to the properties   
    $properties['yearstreetview'] = strval(substr($properties['datestreetview'], 0, 4));

    ////////  Rename properties
    $properties['x'] = $properties['xgoogle'];
    $properties['y'] = $properties['ygoogle'];
    unset($properties['xgoogle']);
    unset($properties['ygoogle']);

    ////////  Add geometry for the point
    $feature = array(
        'type' => 'Feature',
        'geometry' => array(
            'type' => 'Point',
            'coordinates' => array(
                $row['xgoogle'],
                $row['ygoogle']
            )
        ),
        'properties' => $properties
    );
    ////////  Add feature arrays to feature collection array 
    array_push($geojson['features'], $feature);
}

/// Display GeoJSON
header('Content-type: application/json');
echo json_encode($geojson, JSON_NUMERIC_CHECK);

//// Close database connection /////
mysqli_close($mysqli);
?>

These are very simple scripts to get to the results I wanted. If you want to, you can adapt them to be more advanced, so they do exactly what you want. The scripts can also be found o my GitLab page, so they can be downloaded easily.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.