php - Server side marker clustering

Question

I currently have a map on my website that is displaying 50,000 markers. These were un readable to the user due to cluttering so I implemented a clustering solution using google's markerclusterer. This works ok but due to the amount of markers, it is incrediably slow to load.

I would like to use a server side solution in the form of php but can not find out the best way to do this. This is my current setup

External php file markers-xml.php

<?php
$SQL = "SELECT MarkerName, Lat, Lng FROM TableName WHERE MarkerName !=''";
$Query = mysql_query($SQL);
$NumRows = mysql_num_rows($Query);
?>
<markers>
<?php       
for($i = 0; $i < $NumRows; $i++)
{
    $row = mysql_fetch_assoc($Query);
    $Lat = $row['Lat'];
    $Lng = $row['Lng'];
    $MarkerName = $row['MarkerName'];

    echo "<marker Lat='$Lat' Lng='$Lng' MarkerName='$MarkerName'> </marker>\n";
}
?>
</markers>

Main file conataining map

function initialize(mapvars) {
        var xmldata = "markers-xml.php";

        downloadUrl(xmldata, function(doc) {
            var xml = xmlParse(doc);
            var markersInfo = xml.documentElement.getElementsByTagName("marker");
            var markers = []; 
            var bounds = new google.maps.LatLngBounds();
            for (var i = 0; i < markersInfo.length; i++) {
                var Lat = parseFloat(markersInfo[i].getAttribute("Lat"));
                var Lng = parseFloat(markersInfo[i].getAttribute("Lng"));
                var point = new google.maps.LatLng(Lat,Lng);
                var MarkerName = markersInfo[i].getAttribute("MarkerName");  

                var marker = new google.maps.Marker({
                    position: point,
                    map: map,
                    title: MarkerName,
                    MarkerName: MarkerName
                });

            markers.push(marker);
            bounds.extend(point);
        }

        var markerCluster = new MarkerClusterer(map, markers);
    }

Please use [`htmlspecialchars`](http://php.net/htmlspecialchars) when outputting to HTML to prevent [XSS](https://www.owasp.org/index.php/Cross-site_Scripting_%28XSS%29). — Marcel Korpel, Nov 28 '13 at 15:25
Please don't use `mysql_*` functions anymore, they are deprecated. See [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for details. Instead you should learn about [prepared statements](http://bobby-tables.com/php.html) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you. If you pick PDO, [here is a good tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). — Marcel Korpel, Nov 28 '13 at 15:26
I currently have to use `mysql` for this, but will move to PDO soon — , Nov 28 '13 at 15:26

Dr.Molle · Answer 1 · 2013-11-29T16:37:20.717

1

I don't think that a serverside solution will improve the performance. It would be possible to request filtered data on every change of the viewport to reduce the number of used markers, but you will always have do request new data what will take some time.

I think the best solution would be to use a FusionTableLayer, it will work without problems for up to 100000 markers.

possible approach(Related to the comments):

when the viewport of the map changes, send the bounds of the viewport and the zoom-level to the server.

The database-query should

filter the results based on the given bounds
round the lat and lng (the accuracy of the rounding must be related to the zoom-level)
Group the results by the rounded latlngs
return the rounded latLng and the count of items for this latlng

send these data back to the client and create the markers(you may use a serverside solution to dynamically create the marker-images or use a custom overlay)

edited Nov 29 '13 at 16:37

answered Nov 28 '13 at 22:32

Dr.Molle

116,463
16
195
201

1

Here is an example of server side clustering http://jory.dk/AreaGMC/MapClustering.html It has 150,000+ markers and loads instantly. The only problem is that is is written in `c#` and I only know `php` – Nov 29 '13 at 16:19
sorry, I was thinking in the wrong direction(still returning single markers but filtered by the viewport). This shouldn't be hard to achieve, I've updated my answer – Dr.Molle Nov 29 '13 at 16:37
You're approach makes logical sense i'm not quite sure how it translates into php though. The day my php skills advance further can't come soon enough. – Nov 29 '13 at 19:01

score 1 · Answer 2 · answered Sep 03 '15 at 22:42

client side: generate a get request and pass in the bounds of the viewport

getclusters.php?&lat2=62_676438&lon2=-63_2085681&lat1=10_6655205&lon1=-135_7183337

server side: store your lat lon data in a table with a pk and a POINT data structure such that you can use a mysql spatial index (which uses r-trees rather than b-trees so grouping 50K points is seconds rather than minutes):

CREATE TABLE location (
 id int(11) unsigned NOT NULL
 , p POINT NOT NULL, PRIMARY KEY (id)
 , SPATIAL INDEX(p)) ENGINE=MyISAM;

load from another table:

INSERT INTO location 
select id, PointFromWKB(Point(latitude, longitude))
from table_with_lat_long

load from csv:

LOAD DATA LOCAL INFILE 'data.csv'
INTO TABLE location
COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
(id, @dummy, ..., @dummy, @latitude, @longitude)
set p = PointFromText(CONCAT('POINT(',@latitude,' ',@longitude,')'));

write appropriate SQL to group by subdividing the viewport into equal smaller squares or some minimal distance between points based on viewport size. as long as you are using the SPATIAL index it should run quick

your client side cluster marker javascript should render a marker based on count (1 for individual marker 2 or more for a cluster)

php - Server side marker clustering

2 Answers2