[mapserver-users] MapFile DATA Syntax for PostGIS raster with FILTER

[Eichner, Andreas - SID]

Hallo Paul,

> -----Original Message-----
> In a vector PostGIS DB case a layer can be configured to have a dynamic
> client-side filter through the following base syntax, which is fine and
> works for vector tables in the DB:
> 
> DATA "geom FROM some_table using unique id using srid=4326"
> FILTER (id = '%id%')
> 
> As I understand it this in effect this generates an SQL statement where
> the FILTER is created as a where clause in the DATA SQL.

this only works for a vector layer.

> However, in a raster DB example the DATA syntax shown at this link
> [http://postgis.net/docs/RT_FAQ.html#idm28328] is as follows:
> 
> DATA "PG:host=localhost port=5432 dbname='some_db' user='some_user'
> password='some_password' schema='some_schema' table='some_table'
> where='id=12' mode='2' "

Raster layers use GDAL internally and AFAIK don't support FILTER at all. But GDAL allows to pass a WHERE clause via the DATA string as described at the PostGIS docs or at https://trac.osgeo.org/gdal/wiki/frmts_wtkraster.html#a3.2-Readingrasterdatafromthedatabase 
Since runtime substitution is applied by MapServer before using the DATA string you can use something like
  DATA "PG: [...] where='id=%id%' [...]"
Together with a proper validation as described by Steve L.:
  VALIDATION
    'id' '^[0-9]{1,3}$'
    'default_id' '-1'
  END

HTH