GeoServer has always been good at simply publishing database tables. But anything more complex (e.g. pre-filtering data in a table, joining two tables together, or generating values on the fly) could be painful. With Geoserver 2.1 one can finally create a layer directly from an SQL query.
"Create New SQL View" interface
Even dynamic queries are possible, e.g.
select gid, state_name, the_geom from pgstates where persons between %low% and %high%
To select for example all states with 2 to 5 millions inhabitants, the following parameters can be added to the normal GetMap request:
Find more information on SQL layers in Geoserver 2.1 documentation.
The OGC filter encoding standard – for whatever reason – lacks the useful IN operator we know and love from other languages like SQL . Geoserver developers have therefore implemented this functionality as a non-standard SLD function . Unfortunately this implementation requires prior knowledge of the number of arguments in the IN clause and it’s limited to 10 arguments.
An example filter would look like this: