Wildcard on Geoserver SQL View

Question

I have made the following SQL view on Geoserver:

SELECT *
FROM areas
WHERE landuse = '%landuse%'

This works perfectly fine when I specify the landuse in the URL, for example 'grass'. However when I want the query to return all features regardless the type of landuse I do not know what I have to enter in the URL.

Answer 1

The simplest answer is to use a CQL filter in the WMS request to restrict which landuse types are drawn on the map. There is a full tutorial available to get you going. However in your case it would be something like:

http://...../geoserver/wms?....&CQL_FILTER=landuse='grass' 

when you want to filter by grass or more complex options like:

http://...../geoserver/wms?....&CQL_FILTER=landuse='grass' or landuse='trees' 

Or just leave it off completely to show all types. Note you will need to make sure the URL is correctly encoded for the spaces and = etc.

Answer 2

In the past I've overcome this by replacing the whole where clause with a parameter, and then set the default to 1=1

SELECT *
FROM areas
WHERE %query&

you can then supply the full clause e.g. landuse = 'grass' within the parameter declaration in the URL.

I appreciate this is a slightly unsatisfactory, and there would be more elegant solutions depending on the underlying database. Are you using PostGIS?

And I forgot to mention, as @iant point out below, that you should really be using a CQL Filter, unless there's some reason it's not possible to use.