1

I want to display the contents of my PostGIS database of various shape-files in leaflet pop-up dynamically. I know there are static methods available that retrieves attributes. But I need to by passing SQL Queries to PostGIS database and retrieve the result as a GeoJSON file and display it in my map using leaflet. Is there any method to retrieve SQL queries into a GeoJSON file using JSP ?

  • Using ST_AsGeoJSON perhaps? – John Powell May 26 '15 at 19:25
  • GeoServer WFS with GeoJSON as outputformat and with CQL_FILTER gets rather close. Try demo.opengeo.org/geoserver/…. Naturally the native XML filters in WFS do just the same but they are harder to write. – user30184 May 26 '15 at 20:34
  • I did something similar with a php request and the AJAX-plugin, using SQL Queries and the St_AsGeoJSON as mentioned by @JohnBarça. Would that be something you can envisaged doing? – Djana May 28 '15 at 6:18
  • @Djana, i think thts what I want, can you please help me with the code ? – Kc Yoosuf May 29 '15 at 6:50
1

The solution I proposed here is based on an AJAX request, using a a php file to query and return data from the database.

So you will need:

  • a local server to work on (for the php to work)
  • the AJAX-plugin for Leaflet
  • Knowledge of SQL and php
  • Connection parameters to your database ( I suggest creating a "readonly" user for that purpose)

Example of the PHP file (the xxx are where your connection parameters are going and the 'pg_query' is where you can get your SQL query). !! this is an experimental script, no guaranty regarding security!!

<?
$status=$_GET["status"];
//echo "<pre>Simple check to see if connection works :-)</pre>\n"; flush();
error_reporting(E_ALL); ini_set('display_errors', true);    
extension_loaded('pgsql') || die('pgsql module unavailable');

// If you will send SQL queries as parameters please read beforehand http://en.wikipedia.org/wiki/SQL_injection !!!!!!
  $link = pg_Connect("host=xxx port=xxx dbname=xxx user=xxx password=xxx");
 /* if(!$link){
    //echo  "Couldn't make a connection! ". pg_last_error();
    exit;
    }
  else {
    //echo 'connected to server';
    }*/

  $result = pg_query($link, 'select name,country,status,st_asgeojson(the_geom) as geojson from tablename where status=' . $status);
  $numrows = pg_numrows($result);

  // Output Array as GeoJson
  //$resultArray = pg_fetch_all($result);
  //echo json_encode($resultArray);

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

   // Add edges to GeoJSON array
   while($edge=pg_fetch_assoc($result)) {

      $feature = array(
         'type' => 'Feature',
         'geometry' => json_decode($edge['geojson'], true),
         'crs' => array(
            'type' => 'EPSG',
            'properties' => array('code' => '4326' )),
         'properties' => array(
    'name' => $edge['name'],
    'status' => $edge['status'])
      );

      // Add feature array to feature collection array
      array_push($geojson['features'], $feature);
   }

//close database connectin
pg_close($link);

// Return routing result
header('Content-type: application/json',true);
echo json_encode($geojson);

?>

The code part to use it in Leaflet is as follow ( for sake of simplicity, I am using a hard-coded value for the parameter "status" - see the php request)

var statusA= "'active'";
var geojson = new L.geoJson.ajax("php/request.php?status="+statusA, {
    style:style,
    onEachFeature:popUp,
    pointToLayer:function(feature,latlng){
        return L.circleMarker(latlng)}});

Then you can define a style. But you are also interested in how to display information when you click on the features: this is done with the line

onEachFeature:popUp

You need to define what your popUp function is. Here you have an example:

// function for the popup window
function popUp(feature,layer){
    layer.bindPopup('<b>' + feature.properties.name + '</b></br><small>('+ feature.properties.status + ' )</small>');
    layer.on('mouseover', function(e){
        this.openPopup();
    });
};

And this should do the trick.

Look closely at how the PHP builds the geojson so that you can define which attributes you want in the geojson you are building.

And don't forget to add your layer to the map afterwards.

geojson.addTo(map);

I hope it helps.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.