javascript – Get spatial data from PostGIS on-demand to Leaflet-ThrowExceptions

Exception or error:

In my hands, I have a relatively big project (for me, at least), that requires to get 100’s of layers from a PostGIS database.
My solution is to use PHP to fetch the data, and then using an L.geoJSON function in Leaflet to load the data in the map.
I can’t use Geoserver, I don’t have that kind of access to the server and I can’t really ask for installation. It would quickly solve my problem, but right now is out of the table.

My problem is that I designed the system to load all layers when the website opens. And it’s completely unnecessary and overloads the page with unnecessary data.
I canĀ“t figure out how to only load the layers that I want when I need them.
The objective was to only fetch the data when the layer was activated in the layer control.
Also, because of this system, I need a PHP file for each layer… That is also really inefficient.

This code was glued together from different tutorials. It worked great to load a few layers, but this doesn’t scale well to load 100’s of layers.

Here is the code to load one layer:

JAVASCRIPT (The layer is added only when I click on the layer control in Leaflet)

const limitStyle = {
    color: '#000',
    fillOpacity: 0,
};

const limitRomano = L.geoJSON(null, {
    style: limitStyle,
});
$.post('php/herdade_romano/limite.php', function(response) {
    const geojson = JSON.parse(JSON.parse(response));
    limitRomano.addData(geojson);
});

PHP

<?php

// error_reporting(0);
// ini_set('display_errors', '1');
// ini_set('error_reporting', E_ALL);

$config = include('../config.php'); /* This file contains the database credentials */

$server = $config['server'];
$username = $config['username'];
$password = $config['password'];
$database = $config['database'];
$port = $config['port'];

$dbconn = pg_connect(
    "host=$server
    dbname=$database
    user=$username
    password=$password
    port=$port"
) or die('connection error');


$query = "SELECT row_to_json(fc)
FROM (
    SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
    FROM (SELECT 'Feature' As type,
    ST_AsGeoJSON(tb.geom, 4)::json As geometry
    FROM herdade_romano.limite As tb ) As f 
) As fc;";

$result = pg_query($query) or die('Query error');

while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
    foreach ($line as $col_value) {
        echo json_encode($col_value);
    }
}

pg_close($dbconn);
?>

Any help is appreciated!

How to solve:

Leave a Reply

Your email address will not be published. Required fields are marked *