2

I recently found this example: https://northlandia.wordpress.com/2015/04/20/connecting-postgis-to-leaflet-using-php/comment-page-1/#comment-627

It is very helpful and it works fine for one PostGIS table.

My question is: How can I fetch more than one table using this code?

This is my PHP script:

require('../php/connection.php');

$table = $_GET['table'];
$fields = $_GET['fields'];

$fieldstr ="";

foreach ($fields as $i => $field) {
    $fieldstr = $fieldstr. "l.$field, ";
}

$fieldstr = $fieldstr. "st_asgeojson(st_transform(l.geom,4326))";

$sql = "select $fieldstr from $table l";

//send the query
if (!$response = pg_query($conn, $sql)) {
    echo "A query error occured.\n";
    exit;
}
//echo the data back to the DOM
while ($row = pg_fetch_row($response)) {
    foreach ($row as $i => $attr){
        echo $attr.", ";
    }
    echo ";";
}

and this is my main app:

var map,
    fields = ["gid", "geom"],
    autocomplete = [];
$(document).ready(initialize);

function initialize() {
    map = L.map("map", {
        center: L.latLng(38.368858, 26.137269),
        zoom: 15
    });
    var tileLayer = L.tileLayer("http://{s}.tiles.mapbox.com/v3/mariosbatsaris.ilamhcgb/{z}/{x}/{y}.png").addTo(map);
    //next: add features to map
    getData();
};

function getData() {
    $.ajax("php/getData.php", {
        data: {
            table: "shelters_wgs84",
            fields: fields
        },
        success: function(data) {
            mapData(data);
        }
    })
};

function mapData(data) {
    //remove existing map layers
    map.eachLayer(function(layer) {
        //if not the tile layer
        if (typeof layer._url === "undefined") {
            map.removeLayer(layer);
        }
    });
    //create geojson container object
    var geojson = {
        "type": "FeatureCollection",
        "features": []
    };
    //split data into features
    var dataArray = data.split(", ;");
    dataArray.pop();
    //console.log(dataArray);
    //build geojson features
    dataArray.forEach(function(d) {
        d = d.split(", "); //split the data up into individual attribute values and the geometry
        console.log(d[2]);
        //feature object container
        var feature = {
            "type": "Feature",
            "properties": {}, //properties object container
            "geometry": JSON.parse(d[2]) //parse geometry
        };
        for (var i = 0; i < fields.length; i++) {
            feature.properties[fields[i]] = d[i];
        };
        //add feature names to autocomplete list
        if ($.inArray(feature.properties.gid, autocomplete) == -1) {
            autocomplete.push(feature.properties.gid);
        };
        geojson.features.push(feature);
    });
    //console.log(geojson);
    //activate autocomplete on featname input
    $("input[name=oid]").autocomplete({
        source: autocomplete
    });
    var mapDataLayer = L.geoJson(geojson, {
        pointToLayer: function(feature, latlng) {
            var markerStyle = {
                color: "#FFF",
                opacity: 0.8
            };
            return L.multiPolygon(latlng, markerStyle);
        },
        onEachFeature: function(feature, layer) {
            var html = "";
            for (prop in feature.properties) {
                html += prop + ": " + feature.properties[prop] + "<br>";
            };
            layer.bindPopup(html);
        }
    }).addTo(map);
};

function submitQuery() {
    //get the form data
    var formdata = $("form").serializeArray();
    //add to data request object
    var data = {
        table: "shelters_wgs84",
        fields: fields
    };
    formdata.forEach(function(dataobj) {
        data[dataobj.name] = dataobj.value;
    });
    //call the php script
    $.ajax("php/getData.php", {
        data: data,
        success: function(data) {
            mapData(data);
        }
    })
};
  • 1
    I would strongly suggest not to use this way of querying your data. Since your app allows to inject any kind of string into the database, this is prone to mistakes or even hacking. Use pg_query_params() instead. Read the warning on: php.net/manual/en/function.pg-query.php – tilt Jul 4 '17 at 8:23
  • 1
    On top of that, it is generally not a good idea to translate tablenames into query paremeters (I know it's tempting). It is better to create an interface with a limited (but configurable) set of tablenames that is being served in the API. – tilt Jul 4 '17 at 8:25
  • 1
    I can see that yo are building the geojson in your js, I think that building the geojson in the PHP is way easier link. In addition, if you'll have an array of table names and pass it to your PHP, you can loop on the table names and create your data/geojson in one ajax call. – NettaB Jul 7 '17 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.