[GIS] CGI mapserver with PostGIS layer: pass SQL WHERE clause in URL paramaters

mapserverpostgis

I use a third-party software to generate a mapfile and serve them on a OpenLayers based interface from a postgis database.

The postgis table, representing the movements of monks in a specific area, has the following structure (geom column trimmed and name starred for data anonymity):

 mmid | monk_code |  monk_name   | year |   geom                                            
------+-----------+--------------+------+--------------
    4 | 4910102   | ***          | 2008 | 0102...2E40
    1 | 4910102   | ***          | 2002 | 0102...2E40
    2 | 4910102   | ***          | 2006 | 0102...2E40
    5 | 4910102   | ***          | 2009 | 0102...2F40
    3 | 4910102   | ***          | 2007 | 0102...2E40
    7 | 4910201   | ***          | 2001 | 0102...2E40
    6 | 4910201   | ***          | 2001 | 0102...2E40
   25 | 4910302   | ***          | 2005 | 0102...2E40
   24 | 4910302   | ***          | 2002 | 0102...3040
   26 | 4910303   | ***          | 2006 | 0102...3040

The layer part of the mapfile generated by the middleware looks like this:

  LAYER
    CONNECTION "PG: dbname=kc_track user=*** password=*** host=localhost port=5432"
    CONNECTIONTYPE OGR
    DATA "movements_search"
    EXTENT -120 -37 144 51.9
    LABELITEM "year"
    METADATA
      "mm_group"        "Group"
      "ows_featureid"   "mmid"
      "gml_featureid"   "mmid"
      "ows_name"        "movements_search"
      "mmDSTN"  "kc_track"
      "ows_srs" "EPSG:4326 EPSG:900913"
      "ows_title"   "movements_search"
      "mmClass" "us"
      "gml_include_items"   "all"
      "gml_types"   "auto"
    END # METADATA
    NAME "movements_search"
    PROCESSING "CLOSE_CONNECTION=DEFER"

And the PNG is generated through the following URL:

http://mapmint.gaia.net.cias.kyoto-u.ac.jp/cgi-bin/mapserv?map=/var/www/mapmint/data/maps/search_testSearch2_movements_search.map&LAYERS=movements_search&FORMAT=image%2Fpng&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&STYLES=&SRS=EPSG%3A900913&BBOX=892784.49024685,-780269.18462653,19399106.279851,5809214.1488647&WIDTH=1891&HEIGHT=673

Which gives the following result:
request result

Here is the output of mapserv -v

MapServer version 6.0.3 OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=CAIRO SUPPORTS=FREETYPE SUPPORTS=ICONV SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI SUPPORTS=THREADS SUPPORTS=GEOS INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE

I would like to only show the lines respective to a specific monk (e.g. monk_code = '4910102'). Is there a way to include a filter, such a SQL WHERE clause, in the request URL?

I have full access to the database but the mapfile is generated automatically by the middleware. If there is no other solution, I can dive into the middleware source myself and change the code but I would prefer a solution that could use only the request part.

Edit:

The following WFS request returns the correct points:

http://mapmint.gaia.net.cias.kyoto-u.ac.jp/cgi-bin/mapserv?map=/var/www/mapmint/data/maps/search_testSearch2_movements_search.map&request=GetFeature&service=WFS&version=1.0.0&typename=movements_search&filter=%3CFilter%3E%3CPropertyIsLike%20wildcard=%27*%27%20singleChar=%27.%27%20escape=%27!%27%3E%3CPropertyName%3Emonk_code%3C/PropertyName%3E%3CLiteral%3E*4910201*%3C/Literal%3E%3C/PropertyIsLike%3E%3C/Filter%3E

But the following WMS request with the same filter still returns all the lines:

http://mapmint.gaia.net.cias.kyoto-u.ac.jp/cgi-bin/mapserv?map=/var/www/mapmint/data/maps/search_testSearch2_movements_search.map&request=GetMap&LAYERS=movements_search&service=WMS&version=1.1.1&typename=movements_search&filter=%3CFilter%3E%3CPropertyIsLike%20wildcard=%27*%27%20singleChar=%27.%27%20escape=%27!%27%3E%3CPropertyName%3Emonk_code%3C/PropertyName%3E%3CLiteral%3E*4910201*%3C/Literal%3E%3C/PropertyIsLike%3E%3C/Filter%3E&SRS=EPSG%3A900913&WIDTH=1891&HEIGHT=673&BBOX=892784.49024685,-780269.18462653,19399106.279851,5809214.1488647&FORMAT=image%2Fpng

Best Answer

In the mapfile you can use the clause FILTER, "a valid SQL string corresponding to the logic normally following the 'WHERE' keyword in a SQL query" like shown in this example (http://goo.gl/eUn1u):

  CONNECTION "user=dbuser dbname=gisdatabase host=bigserver"   
  PROCESSING "CLOSE_CONNECTION=DEFER"
  # Get the lines from the 'geom' column of the 'roads' table 
  DATA "geom from roads using srid=4326 using unique gid" 
  STATUS ON
  TYPE LINE 
  # Of the lines in the extents, only render the wide highways 
  FILTER "type = 'highway' and numlanes >= 4"
Related Question