Using Leaflet with a database

The previous two posts created a map with markers. The marker information was stored in a fixed geojson file. For the few markers that don't change much this is fine, but it would be much more flexible if the markers were in a database. If there are a large number of markers, say thousands, browsers might slow down showing them, even though many might not actually be visible. One way to help with this is to work out which markers would be visible in the current view and only show them. To do this we need to use some features of Leaflet and introduce Ajax. We will also need to store the marker information in a database, write some code to extract it and format it into the geojson format that we know works so well.

Ajax is a means of exchanging data between the client browser and the server without forcing a page reload. I tend to use jQuery to simplify the process of using Ajax and jQuery ensures that the process works on a wide range of browsers. We will request some data from the server with Ajax which can return data in a json format, which works with geojson too.

In the examples so far the files from the server have been simple files, not needing scripting or a database. In my examples I'm using PHP for script and MySQL for the database as this is a very common combination available from many hosts. In the GitHub repository there is a SQL file, plaques.sql, you can use to create a table called plaques in a MySQL database and import the same data that we have seen already.

To extract the data from the database we'll use a PHP script. It needs to receive a request for the bounding box and it will extract that, format the geojson result and return it to the client. The client then can display the markers. If the user scrolls the map or changes the zoom then a new Ajax request will get the markers that are in the new view and display them. This isn't really needed for the seventy or so markers in this example but it is very useful for a large number of markers.

Let's start with the PHP script to extract the data:


// uncomment below to turn error reporting on
ini_set('display_errors', 1);
error_reporting(E_ALL);

/*
 * ajxplaque.php
 * returns plaque points as geojson
 */

// get the server credentials from a shared import file
$idb= $_SERVER['DOCUMENT_ROOT']."/include/db.php";
include $idb;

if (isset($_GET['bbox'])) {
    $bbox=$_GET['bbox'];
} else {
    // invalid request
    $ajxres=array();
    $ajxres['resp']=4;
    $ajxres['dberror']=0;
    $ajxres['msg']='missing bounding box';
    sendajax($ajxres);
}
// split the bbox into it's parts
list($left,$bottom,$right,$top)=explode(",",$bbox);

// open the database
try {
    $db = new PDO('mysql:host=localhost;dbname='.$dbname.';charset=utf8', $dbuser, $dbpass);
} catch(PDOException $e) {
    // send the PDOException message
    $ajxres=array();
    $ajxres['resp']=40;
    $ajxres['dberror']=$e->getCode();
    $ajxres['msg']=$e->getMessage();
    sendajax($ajxres);
}

//$stmt = $db->prepare("SELECT * FROM hbtarget WHERE lon>=:left AND lon<=:right AND lat>=:bottom AND lat<=:top ORDER BY targetind");
//$stmt->bindParam(':left', $left, PDO::PARAM_STR);
//$stmt->bindParam(':right', $right, PDO::PARAM_STR);
//$stmt->bindParam(':bottom', $bottom, PDO::PARAM_STR);
//$stmt->bindParam(':top', $top, PDO::PARAM_STR);
//$stmt->execute();


try {
    $sql="SELECT plaqueid,lat,lon,plaquedesc,colour,imageid FROM plaques WHERE lon>=:left AND lon<=:right AND lat>=:bottom AND lat<=:top";
    $stmt = $db->prepare($sql);
    $stmt->bindParam(':left', $left, PDO::PARAM_STR);
    $stmt->bindParam(':right', $right, PDO::PARAM_STR);
    $stmt->bindParam(':bottom', $bottom, PDO::PARAM_STR);
    $stmt->bindParam(':top', $top, PDO::PARAM_STR);
    $stmt->execute();
} catch(PDOException $e) {
    print "db error ".$e->getCode()." ".$e->getMessage();
}
   
$ajxres=array(); // place to store the geojson result
$features=array(); // array to build up the feature collection
$ajxres['type']='FeatureCollection';

// go through the list adding each one to the array to be returned   
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $lat=$row['lat'];
    $lon=$row['lon'];
    $prop=array();
    $prop['plaqueid']=$row['plaqueid'];
    $prop['plaquedesc']=$row['plaquedesc'];
    $prop['colour']=$row['colour'];
    $prop['imageid']=$row['imageid'];
    $f=array();
    $geom=array();
    $coords=array();
   
    $geom['type']='Point';
    $coords[0]=floatval($lon);
    $coords[1]=floatval($lat);
   
    $geom['coordinates']=$coords;
    $f['type']='Feature';
    $f['geometry']=$geom;
    $f['properties']=$prop;

    $features[]=$f;
   
}
   
// add the features array to the end of the ajxres array
$ajxres['features']=$features;
// tidy up the DB
$db = null;
sendajax($ajxres); // no return from there

function sendajax($ajx) {
    // encode the ajx array as json and return it.
    $encoded = json_encode($ajx);
    exit($encoded);
}
?>


This is called ajxplaques.php in the folder ajax on the server, available in the GitHub repository.  The script needs a query string with bbox= in it. This defines the west,south,east and north longitude and latitude that bounds the current view of the map. It then queries the database for these items and returns the geojson of these limited markers. If the bounding box (BBOX) is big enough then all the markers will be returned and if the BBOX contains no markers then none are returned and that is fine too. I'm using MySQL and ignoring GIS functions as selecting points is quick and easy. If I was extracting polygons and using a powerful GIS database such as PostrgreSQL with the PostGIS extension then I would consider using a GIS function to find the polygons that intersect the BBOX.

To call the script from the JavaScript (example3.js) I use the ajax functions that are part of jQuery:

function askForPlaques() {
    var data='bbox=' + map.getBounds().toBBoxString();
    $.ajax({
        url: 'ajax/ajxplaques.php',
        dataType: 'json',
        data: data,
        success: showPlaques
    });
}

This creates the query string by using map.bounds() and formats into the format we need with toBBoxString(). The $.ajax() function uses the query string, requests json (of which geojson is just a special case) and will call the function showPlaques() when the data is returned.

function showPlaques(ajxresponse) {
    lyrPlq.clearLayers();
    lyrPlq.addData(ajxresponse);
}

The showPlaques() function is called when the data is returned from the script. The geojson data is in the ajxresponse. We delete all of the existing markers with clearLayers() and add the new data to the geojson layer. To trigger this process we need to call askForPlaques() every time the view of the map changes. We can ask the map object to trigger an event whenever this occurs. So after the map is displayed we add

map.on('moveend', whenMapMoves);

This calls the function whenMapMoves() when the event is triggered. That function simply calls  askForPlaques() to get the correct data for the view.

Two more things have changed. Firstly, when the geojson layer is created no data is added - it is called with null - so the plaques.js is not used at all. When the map is first displayed we need to call askForPlaques() once to get the first set of markers before the map is moved.

Now we have a much more dynamic map, using data from a database and potentially using a part of thousands of markers without overloading the browser.