MVW How to add a new PostGIS layer

This page shows how to add into the MapViewer a layer that loads data stored in a table of PostgreSQL/PostGIS. SPI is taken as a sample in the next lines.

The following steps must be done:

Find schema, table and geometry type of source data.
If the layer displays a time series, determine its periodicity and how the reference to a single date appears in the table structure.
Define the new layer in a MapServer mapfile, with settings to load source data, classify them and make them available to the MapViewer via WMS.
Define the new layer in mapConfig.js, setting its properties within the MapViewer.
If the layer displays a time series, add an entry in layerDates.js, in order to set the last available date.

Find schema, table and geometry type of source data

The new layer will load a spatial table from the PostgreSQL/PostGIS database. According to MapServer's types, geometries can be POINT, LINE or POLYGON. In our sample, SPI data are provided in a thematic table named GRID_1DD_SPI joined to a spatial grid named GRID_1DD. The join condition is GRID_1DD_SPI JOIN GRID_1DD ON GRID_1DD_SPI.G1D_ID = GRID_1DD.ID. Each cell of the grid is a geometry of type POLYGON.

Data will be extracted from the thematic table with SQL queries defined within the MapServer mapfile and displayed for the correspondent cells.

Data periodicity and how it affects the table structure

If source data we want to display in the new layer are a time series, e.g. containing monthly precipitation values from 1980 up to now, the reference to a particular date must be included into the SQL query within the mapfile. Usually we adopt separate variables for single pieces of date, i.e. one for year, one for month, one for dekad (see the table in MVW How to add a new file based layer for details about values used for 10-day periods), one for timescale.

Define the new layer in a MapServer mapfile

Within the MapServer mapfiles' directory, i.e. ./mapserver/mapfiles/mukau/, there are two mapfiles, base.map and mukau.map. The former is thought for layers used as geographic background (e.g. "Countries" or "Water surfaces"), the latter for drought indicators (e.g. "Monthly Precipitation" or "SPI 1 dd").

Therefore, let's use mukau.map to analyse the definition our SPI data. The code snippet defining the Layer Object is:

  LAYER
    NAME "SPI 1 dd"
    TYPE POLYGON
    STATUS OFF
    INCLUDE "./config/mpu.icn.map"
    DATA "corr_cell from (select g.id as id,g.corr_cell as corr_cell,coalesce(s.spi_%SELECTED_MONTH%%SELECTED_TIMESCALE%,-99) as current_spi from grid_1dd_spi s,grid_1dd g where s.year=%SELECTED_YEAR% and s.g1d_id=g.id) as subq using unique id using srid=4326"
    METADATA
      "wms_srs"	"EPSG:4326 EPSG:900913"
      "wms_abstract"	"Map of SPI for the given year, month and timescale"
      "wms_extent"	"21.0, -12.5, 52.0, 24.0"
      "wms_style_default_legendurl_format"	"png"
      "wms_timeextent"	"1950-01/2019-12/P1M"
      "wms_style_default_legendurl_height"	"45"
      "wms_style_default_legendurl_width"	"267"
      "wms_style_default_legendurl_href"	"/mukau/mapserver/legends/spi.png"
      "wms_title"	"Standardized Precipitation Index"
      "wms_style"	"default"
      INCLUDE "./config/disable.wms.shared.map"
      "LAST_UPDATE"	"2020/01/28"
    END
    PROJECTION
      "init=epsg:4326"
    END
    TILEITEM "location"
    UNITS METERS
    VALIDATION
      "SELECTED_MONTH"	"[0-9]{2}"
      "SELECTED_TIMESCALE"	"[0-9]{2}"
      "SELECTED_YEAR"	"[0-9]{4}"
    END
    INCLUDE "./classifications/spi.classes.map"
  END

The first three parameters set the name ("SPI 1 dd"), the type and the status of the layer respectively.

The next two parameters CONNECTIONTYPE and CONNECTION set respectively the third-party tool connected by MapServer - here POSTGIS - and the related connection string, containing host name, port, user name, password, and DB name in this case. These two parameters are not written directly within the Layer Object but included (by means of the INCLUDE keyword) from an external file named mpu.icn.map, which can be shared by all the layers that need to connect to the same database.

The sixth parameter, DATA, is a SQL-like string that actually deals with data retrieval, allowing variables if necessary. Variables are included into two % characters. In our case three variables define year (%SELECTED_YEAR%), month (%SELECTED_MONTH%) and timescale (%SELECTED_TIMESCALE%) respectively. Such variables are replaced by actual values set through the correspondent URL parameters included into the URL calling the layer's WMS service. For security reasons, the passed values must be validated against a range of allowed values, specified into the validation block by means of regular expressions. Analysing the value of DATA more in detail, we can distinguish this syntax: {geometry_column} from {subquery} as {alias} {USING instructions}. In our case the geometry column is corr_cell, that must be present in the subquery among requested columns. In that subquery we see the join between grid_1dd_spi and grid_1dd and the three date variables involving both the SPI's column (for month and timescale, spi_%SELECTED_MONTH%%SELECTED_TIMESCALE%) and the year column within the where clause (year=%SELECTED_YEAR%). subq is used as alias of the subquery, while two USING instructions are appended to apply a unique constraint on the id column and to use 4326 (i.e. WGS84) as spatial reference system. The layer's reference system is specified also in a dedicated Projection object.

The Metadata Object contains wms_* metadata, used for providing a WMS service, and additional custom metadata, like LAST_UPDATED, used to register the date of the last update of the layer definition. An important WMS metadata is wms_enable_request, included from the ancillary file disable.wms.shared.map: it can be used to enable and disable (prepending a !) WMS methods. For example, "wms_enable_request" "GetCapabilities GetMap !GetFeatureInfo GetLegendGraphic" means that GetCapabilities, GetMap and GetLegendGraphic are enabled, while GetFeatureInfo is disabled.

The classification of retrieved SPI values is done in spi.classes.map, an included separate file that can be shared by different layers. The piece of mapfile containing the classification is a list of Class Objects, one for each defined class. In turn, each class contains one or more Style Objects to define colours and other graphic properties. The classification we use for SPI is expressed like this:

CLASS
  NAME "No data"
  EXPRESSION ([current_spi]<=-99)
  STYLE
    COLOR 210 210 210 
    OUTLINECOLOR 210 210 210
  END 
END 
CLASS NAME "SPI &le; -2: Extremely dry" EXPRESSION ([current_spi]<=-2 AND [current_spi]>-99) STYLE COLOR 255 0 0 OUTLINECOLOR 255 0 0 END END 
CLASS NAME "-2 &lt; SPI &le; -1.5: Severely dry" EXPRESSION ([current_spi]<=-1.5 AND [current_spi]>-2) STYLE COLOR 255 170 0 OUTLINECOLOR 255 170  END END 
CLASS NAME "-1.5 &lt; SPI &le; -1: Moderately dry" EXPRESSION ([current_spi]<=-1 AND [current_spi]>-1.5 STYLE COLOR 255 255 0 OUTLINECOLOR 255 255 0 END END 
CLASS NAME  "-1 &lt; SPI &le; 1: Near Normal" EXPRESSION ([current_spi]<=1 AND [current_spi]>-1) STYLE COLOR 255 255 255 OUTLINECOLOR 255 255 255 END END 
CLASS NAME "1 &lt; SPI &le; 1.5: Moderately wet" EXPRESSION ([current_spi]<=1.5 AND [current_spi]>1) STYLE COLOR 233 204 249 OUTLINECOLOR 233 204 249 END END 
CLASS NAME "1.5 &lt; SPI &le; 2: Very wet" EXPRESSION ([current_spi]<=2 AND [current_spi]>1.5) STYLE COLOR 201 128 198 OUTLINECOLOR 201 128 198 END END 
CLASS NAME "SPI &gt; 2: Extremely wet" EXPRESSION ([current_spi]>2) STYLE COLOR 131 51 147 OUTLINECOLOR 131 51 147 END END

As you can see, the first class is displayed with one line for each parameter, while the others are in a more compact presentation. EXPRESSION is the core parameter of the Class Object, because it expresses the condition a data value needs to met to belong to the class, while NAME is used to display the class label in a legend. In a Style Object, COLOR and OUTLINECOLOR set the colour of the filling and the border of the geographical feature respectively.

Define the new layer in mapConfig.js

A layer must be defined in mapConfig.js to be displayed into the MapViewer, with at least three actions:

definition of the layer as an OpenLayers' layer;
addition of the layer to the map layers to be visualized within the map;
addition of the layer to the GeoExt store to be visualized within the layer tree.

In our case a JavaScript variable named spi_mon defines a layer titled "SPI 1 dd" and displaying the layer previously defined in mukau.map:

 var spi_mon = new OpenLayers.Layer.WMS(
    "SPI 1 dd",
	eiu(MUKAU_MSWMS,globalWmsEnv),
    {
        layers: "SPI 1 dd", format: "image/png", transparent: true
    },
	{
        isBaseLayer: false,visibility: true, opacity: globalRasterOpacity,
        firstYear: "1975", lastYear:2019, year:2019, month:12, timescale:"03", 
		metadata: ">FROM_MAPFILE;SPI 1 dd;wms_abstract", singleTile: false,
		legend: getLegendTag("spi.gif",10,true),
		wmsLayer: "SPI 1 dd", wmsTitle: "Standardized Precipitation Index"
    }
);

As you can see, the layer is of OpenLayers.Layer.WMS type, instantiated with four arguments: the desired layer name, the URL of the target WMS service, retrieved with the eiu() function, an object with properly WMS parameters (layers, with the reference to the mapfile layer "SPI 1 dd", format and transparent), and an object with optional parameters. For all the attributes present in this definition please see MVW mapConfig.js.

Once defined, the new layer must be added to the layers visualized into the map, just including the layer's JavaScript variable into the aLayersToAdd array:

  aLayersToAdd=[    
    // Base Layers
    natEarth,landmassSea  
    // Geographic Background, polygons or filled rasters
    ,fao_land_mgmt
    // Precipitation, polygons or raster
    ,monrain,spi_mon
    ...
  ];

At the end, the layer must be included into the MapViewer's layer tree too. For doing that, the layer's variable must be registered into the GeoExt store which deals with the layer tree group with. Our SPI layer is registered into the store of precipitation layers:

	precipStore = new GeoExt.data.LayerStore({
		layers: [spi_mon,monrain]
	});

If the store has been activated with this layer (i.e. it and its subgroup in the layer tree were not present before), its subgroup must be activated into the layer tree as well, with dedicated settings in the a_SUBGROUPGRAPHICS associative array:

a_SUBGROUPGRAPHICS["Precipitation"]={ keyimage_on:"precip-icon", treeExpanded:{monitoring:true,forecast:true}, text:"Precipitation" };

Define layer's date variables in layerDates.js

The object of custom attributes defined above contains also some date parameters specifying the extreme years of the time series (firstYear and lastYear) and year, month and timescale of the last available month (2019-12), to be sent to the WMS server as default date.

Such parameters can be more easily managed and updated if kept in a separate file and referenced with JavaScript variables from within mapConfig.js. The look-up file of last available dates is layerDates.js and contains all such date parameters excepting fistYear, since its value is fix.

For the "SPI 1 dd" layer, the following variables are defined in layerDates.js:

// SPI 1dd Monitoring ("SPI 1 dd")
var spi_mon_YEAR="2019"; 	// Updated 2020-01-10, 13:49
var spi_mon_MONTH="12"; 	// Updated 2020-01-10, 13:49
var spi_mon_LASTYEAR=spi_mon_YEAR;

A JavaScript comment can be optionally used to indicate the date and time when a variable has been updated the last time.

The layer definition in mapConfig.js varies in this way:

 var spi_mon = new OpenLayers.Layer.WMS(
    "SPI 1 dd", eiu(MUKAU_MSWMS,globalWmsEnv),
    { layers: "SPI 1 dd", format: "image/png", transparent: true },
	{
        isBaseLayer: false,visibility: true, opacity: globalRasterOpacity,
        firstYear: "1975", lastYear:spi_mon_LASTYEAR, year:spi_mon_YEAR, month:spi_mon_MONTH, timescale:"03", 
		...
    }
);

The values of year and month are set with the date present in layerDates.js only when the layer is turned on the first time. Subsequently, their values, together with timescale, can be changed at any time acting with the time selector. The values of such parameter are then sent to the WMS server to fill the URL parameters SELECTED_YEAR, SELECTED_MONTH and SELECTED_TIMESCALE seen before, in turn used to replace the variables in the DATA parameter of the correspondent layer in mukau.map.

Sections

Home

Navigation Menu

Uh oh!

MVW How to add a new PostGIS layer