GeoServer Where clause filter

Question

I use postgis, geoserver and openlayers for gis development.

I simply reach from goeserver to postgis table or view and show it with openlayers. but I need some filtering operation on my spatial data. For example I have historical spatial data and filter them to start_date and finish_date like

select ....... from ...... where date between :start_date and :finish_date

the problem is start_date and finish_date are dynamic. User change them from web my open layer web project. How can I do it? from geoserver?from postgis or openlayers?

thanks

Answer 1

You can use the CQL feature that is available in Geoserver to specify dynamic filters on your layers.

In your case you would have to get the dates as specified by the user in the UI and then through some javascript modify the layer parameters by appending a properly built CQL filter. The OpenLayer API call is like the following:

wms_layer.mergeNewParams({'CQL_FILTER': "your filter here"});
wms_layer.redraw();

Answer 2

You could use either CQL filter query or Parametric SQL Views of Geoserver.

With Geoserver SQL Views you can define parameters like:

select gid, geom, col1, col2 from table1 where date between %start_date% and %finish_date%

And with OpenLayers you query these with

var wms = new OpenLayers.Layer.WMS('name of layer',
                                   'http://urlToWms',
                                    {layers: 'NameOfTheViewLayer',
                                     viewparams: 'start_date:2013-1-15;finish_date:2013-1-18'
                                    });

When these have to updated dynamically

wms.mergeNewParams({viewparams: 'start_date:2013-2-5;finish_date:2013-3-12'});