Wednesday, 27 November 2013

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

 * 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'])) {
} else {
    // invalid request
    $ajxres['msg']='missing bounding box';
// split the bbox into it's parts

// open the database
try {
    $db = new PDO('mysql:host=localhost;dbname='.$dbname.';charset=utf8', $dbuser, $dbpass);
} catch(PDOException $e) {
    // send the PDOException message

//$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);

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

// go through the list adding each one to the array to be returned   
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

// add the features array to the end of the ajxres array
// 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);

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();
        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) {

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.

Thursday, 21 November 2013

Using Leaflet part 2

In the last post I described how to create a map with markers on it. I'm going to build on this to make some improvements. Firstly, the default markers are useful but it would be good to have some alternatives. I copied the example1.htm, .js and .css files to example2.x and made some changes there. I created three images which are blue, green and white disks and saved them in the images folder. To use these images we need to create a Leaflet Icon for each marker type. The variables used to store the Icon objects are declared in the global area and then the Icon is created before anything else is created so it is available whenever we need it.

        iconUrl: 'images/blueplaque.png',
        iconSize:[24, 24], // size of the icon
        iconAnchor:[12, 23] // point of the icon which will correspond to marker's location
        iconUrl: 'images/greenplaque.png',
        iconSize:[24, 24], // size of the icon
        iconAnchor:[12, 23] // point of the icon which will correspond to marker's location
        iconUrl: 'images/whiteplaque.png',
        iconSize: [24, 24], // size of the icon
        iconAnchor:[12, 23] // point of the icon which will correspond to marker's location

The icons use the images from the images folder. The icon anchor is important to make a marker work well especially when zooming. If your marker seems to slide around as you zoom in or out you probably don't have the anchor set correctly. It defaults to [0,0] which is top left and rarely what you want.

In the last example we used events to add popups to each markers, here we want to substitute the markers, so we need to change the behaviour, but not much. When we create the geojson layer we need to remove the onEachFeature and replace it with pointToLayer:

  lyrPlq = L.geoJson(plaques, {
        pointToLayer: setIcon

The pointToLayer event allows you to provide the marker you want to display for each feature. The function you define, setIcon in this case, gets passed the feature and the position that the marker need to be as a leaflet LngLat object. In the function you create a marker or some other Leaflet object and return that. Leaflet adds what you return to the layer.

function setIcon(feature,ll) {
    var plq;
    if ('green') {
        plq=L.marker(ll, {icon: greenicon});
    else if ('white') {
        plq=L.marker(ll, {icon: whiteicon});
    else {
        plq=L.marker(ll, {icon: blueicon});
    return p;

In this case we use one of the properties, colour, to choose the marker to display. We now need to add the popup to our newly created marker and return the marker for Leaflet to use.

You can see an example here. The source code is on GitHub.

More examples to come ...

Using Leaflet v0.7

The Leaflet JavaScript library has changed the way OpenStreetMap is being used, making it easy to use and offering all kinds of additional features and functions as plug-ins. I blogged about Leaflet soon after it was first released and that post has been read by a lot of people and has generated more comments than any other. Leaflet version 0.7 has just been released and when I was asked about using it I realised that my original post was badly out of date. I decided to use some local data to describe using Leaflet, including some plug-ins. I decided to use jQuery for a few features. It is widely used and is cross-platform, just like Leaflet. The jQuery files are in a folder called jquery, and the Leaflet files are all in a folder called leaflet.

All the example files are in a GitHub repository:

Leaflet displays a slippy map in an HTML div. It uses JavaScript to control the way the map behaves. The style, as you would expect, is controlled by CSS. Our first example displays a base map with an overlay of markers on it to show where blue plaques are around the UK city of Hull.  Take a look here. The HTML is really straightforward, take a look in the GitHub repository above.

In the head section there is a style sheet for leaflet (leaflet.css) and a script (leaflet.js). These are used in every example. I have also included leaflet-hash.js which is an example of a Leaflet plug-in. I like to store CSS and JavaScript in separate files, not in the HTML file, so I have also included example1.css and example1.js. The JavaScript names plaques.js holds the locations of the plaques to display, formatted as a geojson file. The CSS simply makes the div, with the id “mapdiv”, fill the page. The real work is in the javascript:

* global variables 
var map; // global map object 
var lyrOsm; // the Mapnik base layer of the map 
var lyrPlq; // the geoJson layer to display plaques with 

// when the whole document has loaded call the init function 

function init() { 
  // map stuff 
  // base layer 
  var osmUrl='http://{s}{z}/{x}/{y}.png';
  var osmAttrib='Map data © OpenStreetMap contributors';
  lyrOsm = new L.TileLayer(osmUrl, { minZoom: 9, maxZoom: 19, attribution: osmAttrib });

  // a geojson 
  layer lyrPlq = L.geoJson(plaques,{ 
    onEachFeature: makePopup

  // set the starting location for the centre of the map 
  var start = new L.LatLng(53.7610,-0.3529); 
  // create the map 
  map = new L.Map('mapdiv', { // use the div called mapdiv 
    center: start, // centre the map as above 
    zoom: 12, // start up zoom level 
    layers: [lyrOsm,lyrPlq] // layers to add

  // create a layer control 
  // add the base layers 
  var baseLayers = { "OpenStreetMap": lyrOsm }; 

  // add the overlays 
  var overlays = { "Plaques": lyrPlq }; 

  // add the layers to a layer control 
  L.control.layers(baseLayers, overlays).addTo(map); 

  // create the hash url on the browser address line 
  var hash = new L.Hash(map);

function makePopup(feature, layer) { 
  // create a popup for each point 
  if ( && {

The file starts with global variables. The map variable is the core of Leaflet, any name would do, but it is called map by convention. The map has two layers, one to display the base map and one to show the markers. These are defined by two variables lyrOsm and lyrPlq.

The first use for jQuery is:


This means that when the document is completely loaded and ready call the function init. Doing this is very useful as on a slow link, such as some mobile connections. It makes sure that all of the elements of the page are available before trying to use them.

The init function needs to create the layers, create the map itself and add the layers to the map. We need two different layers one is the base layer which is a set of square tiles  that Leaflet requests from the provider and arranges in the right place. We need to tell the layer where to get the tiles from, in this case the main OSM tile server.

    var osmUrl='http://{s}{z}/{x}/{y}.png'; 
We also need to add attribution – all OSM-based maps need attribution as part of the licence to use the data.

    var osmAttrib='Map data © OpenStreetMap contributors';

The tile provider may also require or request attribution. In one of the later examples will will use a different tile provider with a different attribution. We can now make a layer:

lyrOsm = new L.TileLayer(osmUrl, {
    minZoom: 9,
    maxZoom: 19,
    attribution: osmAttrib

This creates the layer of tiles, from the tile provider, which will zoom out only to zoom level 9 and in to level 19 and add the attribution to the bottom right of the map, by default.

We also need a layer to display the plaque markers. In the HTML a file called plaques.js was loaded. That contains a geojson format file which we will use in a geojson layer. Geojson is a very useful format that I use frequently and is supported well by Leaflet. Geojson allows a variety of objects to be passed to Leaflet for displaying, including points, lines, multiple lines, polygons and multipolygons and these can be mixed together as needed. With a simple set of points the loaded file can be simply used as a layer. The file plaques.js creates a variable called plaques that can be used directly:

    lyrPlq = L.geoJson(plaques, {
        onEachFeature: makePopup

The onEachFeature is an example of responding to an event. In this case as each feature of the geojson file is added the function makePopup is called. This allows us to use one of the properties in each feature in the geojson file to be used to make a popup if the marker is clicked.

We now create a LatLng object to use to centre the map and then the map object is created:

map = new L.Map('mapdiv', {        // use the div called mapdiv
    center: start,                   // centre the map as above
    zoom: 12,            // start up zoom level
    layers: [lyrOsm,lyrPlq]        // layers to add

This creates the map, centres it, zooms to level 12 and adds the two layers we created above.

That would be enough to create a slippy map, but I added a couple of extra features which are often useful. The first is a layers control which allows the layers on the map to be selected and hidden. There are two types of layer a base layer and an overlay and we have one of each. The two layers are created, with names that will appear in the layer control, and the control is then created with the layers added and then added to the map.

The last feature is a leaflet plug-in. I added the leaflet-hash.js file to the leaflet folder and loaded it in the HTML. The hash plug-in changes the URL displayed in the browser address line as the map is scrolled and zoomed so the address can always be used as a bookmark. It replaces the permalink used on earlier versions. A simple line adds the plug-in to your map.

Following posts will show how to change the icons that appear, customise the popup, use database data to display the markers, deal with a large number of markers including clustering them and how to respond to click or tap in other ways than just displaying a popup.