Mapping data drawn from a mySQL database

Previous examples of web maps generated using Leaflet have relied on GeoJSON files for their overlay data. Here data is extracted from a mySQL database containing some UK crime data. The data was downloaded from the Police website as a CSV file and then imported into mySQL using the Table Data Import Wizard in the mySQL Workbench. I would have preferred to use PostgreSQL, it’s more familiar, but my hosted web space only offers mySQL.

The first step is to produce a php script, getcrimes.php, that queries the mySQL database and returns JSON code.

<?php
    $db = mysqli_connect('000.000.000.000','**********','*!*!*!*','db)name')
  or die('Error connecting to MySQL server.');
  $query = "SELECT Longitude AS lng,Latitude AS lat, `Crime type` AS type FROM crime";
    $result = mysqli_query($db, $query) or die('Error querying database.');
  $myArray = array();
    while($row = mysqli_fetch_assoc($result)){
      $myArray[] = $row;
  }
  echo json_encode($myArray);
  mysqli_close($db);
?>

The script uses the mysqli database driver and loops through the results, adding them to any array, myArray, and then encoding the array in JSON format. The output of the script can be seen here.

In Leaflet, utilising the JSON from a script is not that different from using a GeoJSON file; in both cases the jQuery getJSON method is used.  However, since we are using JSON data rather than geoJSON data, and the objects in JSON are not specifically geographic features, we have to know what columns in the data refer to the geometry  (lat and lng).  We loop through the JSON objects and, for each,  add a marker to a marker cluster group named crimes.

       var crimes = L.markerClusterGroup();
       var crimePoints = $.getJSON("getcrimes.php", function(data) {                     
            for (var i = 0; i < data.length; i++) {
              var location = new L.LatLng(data[i].lat, data[i].lng);
              var type = data[i].type;
              var marker = new L.Marker(location, {
                title: type
              });
              var message = 'Crime type: ' +type;
              marker.bindPopup(message);
              crimes.addLayer(marker);
              if (data[i].lat < SWlat) {SWlat = data[i].lat}
              if (data[i].lng < SWlng) {SWlng = data[i].lng}
              if (data[i].lat > NElat) {NElat = data[i].lat}
              if (data[i].lng > NElng) {NElng = data[i].lng}
            }
       });

One complication is the seeming lack of a getBounds method for the markerClusterGroup. It is possible to determine the bounds of individual clusters but not the whole dataset. No matter, it is easy enough to write some code to do it, or so I thought. The bounds consist of two points – the SW corner and the NE corner of a bounding box. As each marker was added, the code uses some if statements to compare the point to the current values.  However, when the web page is opened the values for the latitude and longitude of the two corners were still at the starting defaults.

Unfortunately, I hadn’t realised that getJSON is an asynchronous method.  So the getJSON call is made, execution starts,  and the control of the script is passed on. This is where my bounds were calculated based on the starting values. This is easily solved by calculating the bounds withing the .done() method, which is only triggered after the call to getJSON has completed.

crimePoints.done(function() {
         locSW = new L.LatLng(SWlat, SWlng);
         locNE = new L.LatLng(NElat, NElng);
         mapBounds = L.latLngBounds(locSW, locNE);
         myMap.fitBounds(mapBounds);
       });
       myMap.addLayer(crimes);

Once the JSON data has been processed and the locations of two bounding corners identified, the map is fitted to the bounds and finally the crimes layer added to the map.

The final map… is not that interesting… all the interesting stuff is “under the hood” but here it is anyway…

Crime data provided under a UK Open Government Licence. If you want to see the source code for the map then then view the map separately.