QuickFire PostGIS visualisation: Using SQL views with Geoserver

One of the fastest ways of deploying the results of PostGIS queries on the web takes advantage of Geoserver SQL views.

The process is very straight forward once you have installed and configured a Geoserver.
The Geoserver layer from a PostGIS data store is just defined as an SQL query with placeholders for the variables that will be sent as viewparameters.

firequery

In this particular example the year and month will be filled in when a request for data is made.

select day,frp,geom,id
from global.modis_fire_am
where date(day) >= date('%yr%-%mn%-01') and date(day) < date('%yr%-%mn%-01') + interval '1 month'

Then a user can visualise fires in North and South American for any month between 2002 and 2012 by choosing from drop down menus on a webpage. Data from Modis QuickFire

Clicking on the image below should open the application running on the Ecosur server (there are known connectivity issues, so please try again at another time if the link fails) (or linked here)

firequery1

The web app calls a java script from an HTML page.

<html>
  <head>
    <title>Fires</title>

    <!-- ExtJS Scripts and Styles -->
    <script src="http://maps.google.com/maps/api/js?v=3.2&sensor=false"></script>
    <script type="text/javascript" src="http://cdn.sencha.com/ext/gpl/3.4.1.1/adapter/ext/ext-base.js"></script>
    <script type="text/javascript" src="http://cdn.sencha.com/ext/gpl/3.4.1.1/ext-all.js"></script>
    
    <link rel="stylesheet" type="text/css" href="http://cdn.sencha.com/ext/gpl/3.4.1.1/resources/css/ext-all.css" />
    <link rel="stylesheet" type="text/css" href="http://cdn.sencha.com/ext/gpl/3.4.1.1/examples/shared/examples.css" />
    <link rel="stylesheet" id="opengeo-theme" href="resources/css/xtheme-opengeo.css" />
    <!-- OpenLayers Script -->
    
    <script src="http://www.openlayers.org/api/2.12/OpenLayers.js"></script>

    <!-- GeoExt Script -->
    <script type="text/javascript" src="http://api.geoext.org/1.1/script/GeoExt.js"></script>

    <!-- Our Application -->
    <script type="text/javascript" src="Fires.js"></script>

  </head>
  <body>
  </body>
</html>

The code below pulls the month and year attributes from Json data stores.


// Base map
var FireYear = 2010;
var FireMonth = 04;




var osmLayer = new OpenLayers.Layer.OSM();

var sat = new OpenLayers.Layer.Google(
                "Google Satellite",
                {type: google.maps.MapTypeId.SATELLITE, numZoomLevels: 22}
            );
var ghyb = new OpenLayers.Layer.Google(
    "Google Hybrid",
    {type: google.maps.MapTypeId.HYBRID, numZoomLevels: 20}
);



var Fires = new OpenLayers.Layer.WMS("WMS", 
  "http://geoserv.ecosur.mx:8080/geoserver/wms", 
  {
    format: "image/png8",
    transparent: true,
    layers: "reddeam:Fires",
    viewparams: "yr:" + FireYear+";mn:"+FireMonth
  }, {
    opacity: 1,
  }
);

// Map object with projection
olMap = new OpenLayers.Map({
  projection: "EPSG:900913",
  units: "m",
  layers: [Fires, ghyb]
});



var Year = new Ext.data.JsonStore({
  autoDestroy: true,
  autoLoad:true,
  url: "Year.json",
  fields: ['Year'],
  idProperty: 'Year',  
});

var Month = new Ext.data.JsonStore({
  autoDestroy: true,
  autoLoad:true,
  url: "Month.json",
  fields: ['Month','Id','Mes'],
  idProperty: 'Id',  
});





var chooseYear = new Ext.form.ComboBox({
  autoSelect:true,
  mode: 'local',
  store: Year,
  displayField: "Year",
  valueField: 'Year',
  width: 200,
  triggerAction: "all",
  emptyText:"Escoge el año",
 listeners: {
    select: function(combo, rec, idx) {
      FireYear=rec.get("Year");
      var vp = { viewparams: "yr:" + FireYear+";mn:"+FireMonth };
      Fires.mergeNewParams(vp);
    
      // control.vendorParams = vp;
    }
  }
 
});


var chooseMonth = new Ext.form.ComboBox({
  autoSelect:false,
  mode: 'local',
  store: Month,
  displayField: "Mes",
  valueField: 'Id',
  width: 200,
  triggerAction: "all",
  emptyText:"Escoge el mes",
 listeners: {
    select: function(combo, rec, idx) {
      FireMonth=rec.get("Id");
      var vp2 = { viewparams: "yr:" + FireYear+";mn:"+FireMonth };
      Fires.mergeNewParams(vp2);
      
     
      // control.vendorParams = vp;
    }
  } 
 
});
	



// Viewport wraps map panel in full-screen handler
var viewPort = new Ext.Viewport({
  layout: "fit",
  items: [{
    xtype: "gx_mappanel",
    ref: "mappanel",
    title: "Fires",
    tbar: [chooseYear,chooseMonth],
    map: olMap
  }]
});

olMap.setCenter([-10364594.0, 2023072.0],6);



// Fire off the ExtJS 
Ext.onReady(function () {
  viewPort.show();
});

The json stores look like this.

[{
"Mes":"Enero",
"Month":"January",
"Id":"01"
},
 {
"Mes":"Febrero",
"Month":"February",
"Id":"02"
},
 {
"Mes":"Marzo",
"Month":"March",
"Id":"03"
},
 {
"Mes":"Abril",
"Month":"April",
"Id":"04"
},
 {
"Mes":"Mayo",
"Month":"May",
"Id":"05"
},
 {
"Mes":"Junio",
"Month":"June",
"Id":"06"
},
 {
"Mes":"Julio",
"Month":"July",
"Id":"07"
},
 {
"Mes":"Agosto",
"Month":"August",
"Id":"08"
},
 {
"Mes":"Septiembre",
"Month":"September",
"Id":"09"
},
 {
"Mes":"Octubre",
"Month":"October",
"Id":"10"
},
 {
"Mes":"Noviembre",
"Month":"November",
"Id":"11"
},
{
"Mes":"Diciembre",
"Month":"December",
"Id":"12"
}
]

4 thoughts on “QuickFire PostGIS visualisation: Using SQL views with Geoserver

  1. Hi Duncan – just ran across this map – very cool! I’m working on something similar for my grad school thesis project, and wonder if you might know any good documentation (other than the geoserver documentation which I’m having trouble following) for linking to the parameter views through checkboxes in openlayers? I’ve got a parameter view set up in geoserver, but I’m having trouble getting the parameter choices to display in my web app. Thanks for your help!

  2. The code above uses ExtFormComboBoxes to display parameter choices. You could copy it and adapt it. I changed an example I found on the boundless site.
    http://boundlessgeo.com/
    Another way is to use html list boxes outside the map frame in a div and then add something along these lines to call the update function.
    document.getElementById(“yourparam1”).addEventListener(“change”, function() {call your function});
    There should be some examples of this that you could adapt if you look around.

  3. Hi, Duncan

    I just have a simple question, in what place I should implement ExtFormComboBoxes to display the JSON parameters?, how I use this? and from what place i need to call the web service.

    • There are various ways to do this, but the example code should work if adapted to meet your needs. You have to load the parameters from a JSon file into the store. Look at the boundless example for more on this. They provide a perl script to make the stores, but I use the RJSon package. I might put up a post on how to do this if I find time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s