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

20 comments:

Rob Norris said...

Interesting work.

Is the next step to use marker clustering?

(i.e. https://github.com/Leaflet/Leaflet.markercluster)

Or is this left as an exercise for the reader ;)

Chris Hill said...

I want to add multiple base & overlay layers, filtering which markers to see, clustering markers (with the markerCluster plug-in) and responding to clicks with other than popups.

If you have other suggestions I'll be pleased to hear them.

lunkensblogg.net said...

Thanks for this mate... I succeeded in getting my Ushahidi-collected kayak locations from DB to leaflet without manually produce a geojson...

Keep up the good work :-)
kajakplatser.se/karta

Anonymous said...

Can i connect to the database using asp.net(C#) or thought javascript? instead of php???

Newbie.

Chris Hill said...

I don't do dotnet, but it can be used instead of PHP. You would need to find someone who knows dotnet to help though.

Anonymous said...

Hello, thanks a lot for this article! I have a question, what is the name of the database in your exemple ? When do you pass this name ? Which file? Thanks!

Chris Hill said...

The database information is loaded in the statement:

include $idb

Database name, userid and password are imported as variables. You can use your database by hard-coding the details if you want to.

Anonymous said...

what about .zip with sources? :)

Chris Hill said...

The Github link has zip download as an option.

Nick White said...

Thanks for this. It looks great.

I installed from github and can successfully site. Examples run fine but example 3 cannot load points from php. Inspection shows bbox is calculating properly but it simply can't retrieve ../ajax/ajxplaques.php (404 not found). The file names and directories have not changed or moved.

Any idea why the javascript can't locate the php file?

Chris Hill said...

A 404 means the script cannot be found. Is the script present in the directory called ajax in the directory that the example3.htm file is called from? Is PHP installed on your server?

Nick White said...

I resolved a configuration error. PHP is working fine now but data is still not displaying on map. Only other issue I see is a 404 on network request to get jquery-1.10.2.min.map

Same issue when I tried on different server. Is this the updated source?: https://github.com/chillly/plaques

I'll look into it further when I get time later.

Thanks again

Ramuald Zhabik said...

Copy all From Github to server, create DB, create db.php with params : $dbname='name'
$dbuser='user'
$dbpass='pass'. But map is blank

Joan Pescador said...

Very helpful code mate. Anyway, I'm getting unexpectec problem placing well known coordenates in USA. It's appear in Antartida??
My table is working fine in another map.
Maybe something about bbox?
Any Idea?
Thanks anyway.
Joan

Joan Pescador said...

Do you know how to change the coordenates format in your code? Thanks,

Chris Hill said...

@Joan Pescador:
The usual reason for markers appearing in completely strange places is because the longitude & latitude are the wrong way round.

If you get the geojson returned you can copy it into http://geojsonlint.com/ which will help you check your geojson, including the coordinate order.

If your coordinates are not true lon & lat they can be transformed with proj4js in the browser http://trac.osgeo.org/proj4js/ but I would do that work in the server.

HTH

Joan Pescador said...

Solved.
Working with he info in your reply me, I see you place the array for coordinates like this: long[0], lat[1].
I just change the order and works.
Thanks for reply. It is very useful code.

Joan Pescador said...

Hi Chris. Sorry for boring yo again. I can't see where is the problem.
I get the map working fine without filtering the data table but when include "top, bottom, left, right" filter I get an error with "bbox" data:
{"resp":4,"dberror":0,"msg":"missing bounding box"}.
I can't understand because didn't touch nothing in example3.js.
Any Idea?
Can I ask the bounding box directly from ajxplaques? I guess not but can not imagine what happen.
Can I ask bbox whit the name of js variable "data"?
Thanks

Chris Hill said...

@Joan
That error is caused because the AJAX call in your JavaScript did not include the bbox= parameter (or maybe it was mis-spelled).

I highly recommend a javascript debugger such as FireBug in FireFox. That lets you see the AJAX calls that get made and the returned data or any errors.

Joan Pescador said...

No way. I can't see any misspelled.
So sorry this is my first js project. Sure i'm missing something very basic.
Do you see something wrong?
javascript:
....function askForPlaques() {
var data='bbox=' + map.getBounds().toBBoxString();
$.ajax({
url: 'ajax/ajxplaques.php',
dataType: 'json',
data: data,
success: showPlaques
});
}

function showPlaques(ajxresponse) {
lyrPlq.clearLayers();
lyrPlq.addData(ajxresponse);
// map.spin(false);
}
.....

ajax:
.....map.on('moveend', whenMapMoves);

askForPlaques();
}

function whenMapMoves(e) {
askForPlaques();
}

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

function showPlaques(ajxresponse) {
lyrPlq.clearLayers();
lyrPlq.addData(ajxresponse);
// map.spin(false);
}

Thanks.