Een kaart met de meest recente Streetview opnamedata per straat: de PHP en SQL scripts die ik daarvoor gebruik

DEZE BLOGPOST WERD OP 29/12/2020 GEÜPDATET, OMDAT DE OUDE API NIET MEER WERKTE. ER KAN VANAF NU EEN OFFICIËLE API VAN GOOGLE GEBRUIKT WORDEN. AANPASSINGEN WERDEN OP 29/12/2020 IN HET ROOD AANGEDUID.

Enkele jaren geleden schreef ik op deze blog een post waarin ik mijn manier van werken uit de doeken deed om overzichtskaarten te maken van wanneer de meest recente Google Streetview beelden zijn per gemeente. Deze controles voer ik nog steeds uit voor Antwerpen, Kontich en Edegem, zodat ik steeds een actueel overzicht van deze informatie heb. Recent kreeg ik de vraag of ik de scripts die ik daarvoor gebruik ter beschikking kan stellen aan anderen. Ik wilde dat wel doen, maar de PHP scripts die ik gebruik, waren een beetje rommelig opgebouwd, omdat ze alleen maar voor mezelf bedoeld waren. Daarom heb ik heb ze een wat herwerkt, zodat ze voor meer mensen bruikbaar zijn.

Om de controles uit te voeren, gebruik ik een MySQL-tabel en enkele PHP-scripts. Hieronder beschrijf ik hoe je deze kan gebruiken. Uiteraard kunnen gelijkaardige controles ook uitgevoerd worden met bijvoorbeeld Python en PostgreSQL. Als je dat wil doen, kan je onderstaande scripts als inspiratie gebruiken. Waarschijnlijk is het niet zo moeilijk om die scripts te vertalen naar de voor jou gewenste programmeertaal en databank.

De MySQL tabel aanmaken

Met onderstaand SQL script maak je een lege MySQL tabel aan. Je kan dit script uitvoeren in bijvoorbeeld phpMyAdmin of een andere MySQL beheertool.

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`);

De velden ‘x’ en ‘y’ zijn de coördinaten van de locaties die we invoeren in de API van Google. De velden ‘xgoogle’ en ‘ygoogle’ zijn de coördinaten van waar de Streetview opname effectief is gemaakt in de buurt van waarvoor we de vraag aan de API stelden. Het zijn die ‘xgoogle’ en ‘ygoogle’ coördinaten die we achteraf zullen gebruiken om de resultaten weer te geven op kaart.

De MySQL tabel vullen

Nu moet de lege tabel die we aangemaakt hebben, gevuld worden met locaties die we willen controleren. Daarvoor gebruiken we een stratenplan (Wvb uit het GRB in mijn geval) en zetten we die in QGIS om naar een puntenlaag.

Een stratenplan is meestal een polylijnen kaartlaag, maar voor onze bevragingen aan Google hebben we de XY-coördinaten nodig van punten. Daarom moeten we de kaartlaag van het stratenplan omzetten naar een puntenlaag. Hiervoor gebruiken we QGIS en de plugin “Locate points along lines” die te downloaden is via de officiële repository met QGIS plugins. Met behulp van deze plugin zetten we op alle straten om de 5 meter een punt en slaan al deze punten op in een nieuwe puntenkaartlaag. In die puntenkaartlaag voegen we 2 kolommen toe met daarin de X- en Y-coördinaten in WGS84 (EPSG:4326). Een kolom ‘fid’ wordt ook toegevoegd met daarin een unieke id per controlepunt. Omdat ik de informatie achteraf eenvoudig wil kunnen opsplitsen per gemeente, voegde ik ook het veld ‘municipality’ toe. Als dit voor jou niet van toepassing is, mag je dit veld leeg laten.

Om dan de gegevens uit die puntenkaartlaag over te zetten naar de MySQL tabel die we aangemaakt hebben, bestaan er verschillende manieren. Je kan bijvoorbeeld de puntenkaartlaag vanuit QGIS exporteren naar een CSV-bestand. Dat bestand kan je dan met behulp van phpMyAdmin importeren in je MySQL-tabel. Uiteraard zijn er nog heel wat andere manieren om dit te doen. Het is niet van belang hoe je dat doet, zolang de nodige informatie maar in je MySQL-tabel terechtkomt.

In de MySQL-tabel is het van belang dat zeker de velden ‘fid’, ‘x’, en ‘y’ zijn ingevuld voor elk punt. Het is uiteraard ook handig om nu al het veld ‘municipality’ in te vullen. De velden ‘xgoogle’, ‘ygoogle’, ‘lastcheck’ en ‘datestreetview’ mogen voorlopig nog leeg blijven, want die worden achteraf ingevuld door het controlescript in PHP.

Het controlescript in PHP

Voor al die locaties in onze MySQL tabel gaan we één voor één controleren of er een Streetview opname vlakbij gemaakt is. De API van Google die we hiervoor gebruiken, gebruikt een buffer waarbinnen ze gaan controleren waar de dichtstbijzijnde opname is gemaakt. Als er in de omgeving nog nooit een Streetview opname is gemaakt, worden de velden ‘xgoogle’ en ‘ygoogle’ allebei op 0 gezet en het veld ‘datestreetview’ krijgt de waarde ‘no images’.

Om dit te doen gebruiken we onderstaand PHP script. Met behulp van een cron job in Linux of task scheduler in Windows kunnen we het uitvoeren van dit script automatiseren. In het script moet je de gegevens van je MySQL database en tabel aanpassen en aangeven hoeveel punten er per keer gecontroleerd moeten worden. Wat er in het script gebeurd, wordt door comments in het script zelf uitgelegd.

Het nadeel van de nieuwe officiële Google API is wel dat je in de Google Cloud Console een API key voor de “Street View Static API” moet aanvragen en dat je je betaalgegevens moet ingeven in de “Billing” module van de Google Cloud Console. Gelukkig is het gratis om metadata van Streetview gegevens op te vragen. Het opvragen van Streetviewbeelden zelf via de API kost wel geld. Omdat een API key persoonlijk is en omdat daar betaalgegevens aan gekoppeld zijn, vervang ik mijn API key in dit artikel door MyApiKey, maar als je deze methode zelf wil gebruiken, moet je uiteraard je eigen API key gebruiken.

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 (23 maart 2020): De bovenstaande script werd aangepast door de functie bind_param toe te voegen om het geheel veiliger te maken. Met dank aan Johan Van de Wauw voor de tip.

Edit (29 december 2020): De bovenstaande script werd aangepast aan de nieuwe API van Google.

Een GeoJSON bestand maken met PHP

Met onderstaand PHP script worden de gegevens in de MySQL-tabel omgevormd naar een GeoJSON-bestand. De url van dat bestand kan je rechtstreeks inlezen in QGIS om er een kaart van te maken. Om je database niet te zwaar te belasten als er heel veel punten in je tabel zitten, kan je het resultaat ook opslaan op je computer naar een bestand met de extensie geojson. Dat soort bestanden kan je eenvoudig inladen in QGIS.

De werking van onderstaand script wordt met behulp van comments in het script zelf toegelicht.

geojson.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);
?>

Uiteraard zijn dit maar eenvoudige scripts om tot resultaten te komen. Je kan ze uiteraard nog uitbreiden en aanpassen om ze naar je hand te zetten. De scripts zijn ook terug te vinden op mijn GitLab pagina, zodat ze eenvoudig te downloaden zijn.