[GIS] Large loads geojson from thesql to leaflet

geojsonleafletMySQLPHP

I have a mysql database with +25K rows including geometry and other attributes.
Using a mysql2geojson php script I then added it to a leaflet map.

It is super slow now, what can I do to boost the performance?

I'm using www.one.com as host meaning I dont have access to anything else than php/mysql. I can't get postgis/geoserver, etc.

I also intend to design a search filter limiting which points to be shown on the map. For this purpose, is it best to do new calls to the database, or should I be working against a "local dump" for the searching?

To summarize I wonder how to best work with big point data sets with mysql/php/leaflet including searching. Any tips are welcome!

Best Answer

Here are a couple of thoughts on what you can do to boost performance:

Send a BBox to server and return only points within it
Use a library like turf.js http://turfjs.org/ on the client to only draw points within the maps current BBox
Marker Clustering https://github.com/Leaflet/Leaflet.markercluster
Use SVG icons instead of images
Only return geometry and unique identifier of points, than send a query to the server to get the details for that point. This will decrease the initial load of your json file and memory stored on the client.
Use web workers to download the json while running other functions

Ideally you would generate tiles for this large of a load, but the previous pointers should help guide to better performance.

Related Solutions

[GIS] MySQL to GeoJSON – Formatting Issue

I haven't been coding in PHP lately but I think you're just not layering your data structures properly. You are pushing the $feature onto $geoson every time. Try making 'features' an array like so:

$features = array();
$geojson = array(
    'type'      => 'FeatureCollection',
    'features'  => $features
 );

Then push each $feature in your loop onto $features instead of $geojson.

array_push($features, $feature);
instead of
array_push($geojson, $feature);

The quotes. I'm guessing your lat and lon are strings in the database so they show up as strings in your json . You should be able to cast them to a floating point number. see https://stackoverflow.com/questions/481466/php-string-to-float

[GIS] Submit form information from a leaflet popup to MySQL table

Several things seem to be wrong in your code.

When you have forms inside a Leaflet popup, you want to avoid form submission in the classic way. You typically need to submit using some AJAX behind the scenes that prevent reloading your page. In order to do this, you need to prevent the default behavior for form submission. This question discusses how to achieve this. In your case, it should be sufficient to add some code like:
```
$('#form').submit(function(e){
    e.preventDefault();
});
```
after the popup creation with the form, after your code
```
var popup = L.popup({maxWidth: 1000})
...
SKIPPED, INCLUDING YOUR FORM
...
.openOn(map);
```
This makes sure your form is not going to be submitted.
You need to write the code for submitting the form manually. You can add this code after the e.preventDefault(); line in the code above or define your function addMarker handling the form submission. Write this function in the global scope, e.g. before your $(document).ready(function (){

The form submission can be handled e.g. using jQuery's post or ajax function. This answer shows well how to do this part. The URL (action URL) would be your PHP script.
You need to handle the PHP part, i.e. accessing the submitted values and inserting them in the database. The name attribute of your form controls must match the $_POST keys in your PHP file. I think you did assume that the id attribute is defining the $_POST keys. Very often, this means the name attribute has the same value as the id attribute.

For instance, your line
```
<select class="form-control" id="arrest" name="text">
```
should be
```
<select class="form-control" id="arrest" name="arrest">
```
in order to be able to retrieve its value in PHP using
```
$_POST['arrest']
```
In case your code should not only be for developing purpose, please be aware of SQL injection issues and fix your code before production. Check out e.g. this and this to start.
You would need to do the necessary action in the Leaflet popup manually, such as closing it, or displaying the submitted values. You would do this typically in the success function you provide in the jQuery ajax or post function when submitting the form.

Best Answer

Related Solutions

[GIS] MySQL to GeoJSON – Formatting Issue

[GIS] Submit form information from a leaflet popup to MySQL table

Related Question