THIS BLOGPOST WAS UPDATED ON DECEMBER 29, 2020, BECAUSE THE OLD API DIDN’T WORK ANYMORE. FROM NOW ON AN OFFICIAL GOOGLE API CAN BE USED. CHANGES WERE MARKED IN RED ON DECEMBER 29, 2020.
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.
The downside of the new official Google API is that you have to get an API key for the “Street View Static API” in the Google Cloud Console and you have to give Google your credit card details in the Billing module of Google Cloud Console. Fortunately it’s free to request metadata of the Streetview images. Requesting the Streetview images using thye API does cost money. Because an API key is personal and linked to a credit card, I replaced my API key by MyApiKey, but if you want to use this method, you’ll have to replace that with your own API key.
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"]);
$json = "https://maps.googleapis.com/maps/api/streetview/metadata?output=xml&location=$y,$x&key=MyApiKey";
$streetview=(json_decode(file_get_contents($json)));
$datestreetview = $streetview->date;
$xgoogle = $streetview->location->lng;
$ygoogle = $streetview->location->lat;
$now = date("Ymdhis");
if ($datestreetview == '') {
$datestreetview = 'no images';
$xgoogle = 0;
$ygoogle = 0;
}
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.
EDIT: (December 29, 2020): The above script was updated to the new Google API.
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.