filter | Free and Open Source GIS Ramblings

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:

&viewparams=low:2000000;high:5000000

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 [1]. Geoserver developers have therefore implemented this functionality as a non-standard SLD function [2]. 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:

<ogc:Filter>
   <ogc:PropertyIsEqualsTo>
       <ogc:Function name="in3">
          <ogc:PropertyName>first_name</ogc:PropertyName>
          <ogc:Literal>Paul</ogc:Literal>
          <ogc:Literal>Mary</ogc:Literal>
          <ogc:Literal>Luke</ogc:Literal>
       </ogc:Function>
       <ogc:Literal>true</ogc:Literal>
   </ogc:PropertyIsEqualsTo>
</ogc:Filter>

[1] https://underdark.wordpress.com/2010/06/14/the-missing-in-operator-ogc-filter-standards/
[2] http://docs.geoserver.org/stable/en/user/styling/sld-tipstricks/mixed-geometries.html#using-non-standard-sld-functions