[GIS] Fetching postgis data through php json and passing to leaflet map

ajaxgeojsonleafletPHPpostgis

I am new to this and trying to explore leaflet and geojson a bit. Please help me with this.

This is my php page which I am using to fetch data from PostGIS. (the data opened successfully in the browser)

$sql = "SELECT *, (ST_AsGeoJSON(geom)) AS Geojson FROM
   public.village_boundary_metro";
$rs = pg_query($dbconn4, $sql);
if (!$rs) {
   echo "An SQL error occured.\n";
   exit;
}
$output    = '';
$rowOutput = '';

while ($row = pg_fetch_assoc($rs)) {
  $rowOutput = (strlen($rowOutput) > 0 ? ',' : '') . '{"type": "Feature", "geometry": ' . $row['geojson'] . ', "properties": {';
  $props = '';
  $id    = '';
  foreach ($row as $key => $val) {
      if ($key != "geojson") {
        $props .= (strlen($props) > 0 ? ',' : '') . '"' . $key . '":"' . escapeJsonString($val) . '"';
      }
      if ($key == "id") {
        $id .= ',"id":"' . escapeJsonString($val) . '"';
     }
   }

  $rowOutput .= $props . '}';
  $rowOutput .= $id;
  $rowOutput .= '}';
  $output .= $rowOutput;
}
$output = '{ "type": "FeatureCollection", "features": [ ' . $output . ' ]}';
echo json_encode($output);

And now I try to extract this resultant data through Ajax as a geojson layer on a leaflet powered map.(ajax code follows)

$.ajax({
  type: "GET",
  url: 'geojson_entry_postgis.php',
  data: data,
  dataType: 'json',
    success: function (data) {

  var geojson = new L.geoJson(data).addTo(map);
}})

Also tried this way..

var geojson = L.geojson('geojson_entry_postgis.php?geotable=village_boundary_metro&geomfield=geom', 
    { style: {"color":"#ff7800","weight":2}, maxZoom: 6, onEachFeature:
       onEachFeature }).addTo(map);

However the data fetched from postgis does not display as geojson layer on the map as expected. Please let me know where I am going wrong and Am I following the right process??

P.S. Ajax function runs fine in other examples.

Best Answer

If you get results from your php code, copy that json result into the tester on geojson.io or geojsonlint.com to check the format, once you confirm the data, take a close look to the "coordinates" values, sometimes PostGIS brings that data in a format that leaflet cant draw. If coordinates are alright, then someting in the code html code is the problem. One commom issue is the scope of variables if you create one inside a function, load the response data in it and try to manage that layer from a leaflet control, then it will not be show. In your code: 

success: function (data) {
var geojson = new L.geoJson(data).addTo(map);
}

this will be add the layer but it will not be controled by L.LayerControl.

I`ll recommend:

success: function (data) {var geojson = new L.geoJson(data);}

and outside function ajax:

 $.ajax({
...
}});
geojson.addTo(map);
Related Question