GeoServer SQL views.

Posted by – Monday 2012-08-20

In this post we will see a simple example of how GeoServer’s SQL views [note 1] can be used to derive multiple layers from a single SQL table.

1. The arena.

I had a table in a PostGIS database that stores the geometries of all roads in the province of Lugo. Each road has a geometry and a set of attributes. One of these attributes is ‘owner’, which stores the road owner: a municipality, a province, a regional government or the central government.

From this table I needed to generate four WMS layers: roads owned by municipalities, roads owned by the the province, and so on.

2. GeoServer’s SQL views in action.

This can be accomplished by using GeoServer’s SQL views. Thus, a GeoServer SQL view like the shown below will retrieve the data for the ‘central government roads’ WMS layer:

SELECT * FROM roads WHERE owner = 'centr'

The code ‘centr’ is the one corresponding for roads owned by the central government.

A potential issue is that GeoServer might not automatically detect the coordinate reference system. Fortunately, it can be fixed by introducing its EPSG code, as shown below:

GeoServer SQL view screenshot

Once the SQL view has been created, the WMS layer for the roads owned by the central government is created as usual.


[note 1] GeoServer’s SQL views must not be confused with SQL views.

0 Comments on GeoServer SQL views.