database filtering with FILTER or DATA ?

[Stephen Woodbridge]

Rich,

You can generate more complex sql queries in your mapfile like:

DATA "the_geom from (select .....) as foo ..."

in your sub-select you will need to make sure that you include all 
columns that mapserver might need.

This is a really good reference that you should download and read through:

http://mapserver.gis.umn.edu/community/conferences/MUM3/workshop/postgis/

-Steve W

rich.fromm wrote:
> My data is being queried from a postgis database.  The documentation here:
> http://mapserver.gis.umn.edu/docs/reference/mapfile/layer
> 
> suggests that the DATA directive within LAYER just specifies the column name
> and tablename:
> 
> --- begin ---
> If this is a PostGIS layer, the parameter should be in the form of
> "<columnname> from <tablename>", where "columnname" is the name of the
> column
> containing the geometry objects and "tablename" is the name of the table
> from
> which the geometry data will be read.
> --- end ---
> 
> and that any filtering that I want to do of the query is done with the
> FILTER
> directive within LAYER:
> 
> --- begin ---
> FILTER [string]
> 
>     This parameter allows for data specific attribute filtering that is done
>     at the same time spatial filtering is done, but before any CLASS
>     expressions are evaluated. For OGR and shapefiles the string is simply a
>     mapserver regular expression. For spatial databases the string is a SQL
>     WHERE clause that is valid with respect to the underlying database.
> 
>     For example: FILTER "type='road' and size <2"
> --- end ---
> 
> So, for example, I might have something like the following:
> 
> --- begin ---
>         DATA		"the_geom FROM landuseb"
>         FILTER		"the_geom && 'SRID=4326;LINESTRING(-122.3019062479681
> 37.83351284250283, -122.28009375203187 37.84648658222045)' AND
> feat_cod=2000123"
> --- end ---
> 
> 
> The following post seems to suggest that this is not a good thing to do. 
> That
> by using FILTER it is mapserver doing the filtering, and not the database. 
> It
> says to use a more complex DATA expression, including the restricted query. 
> I
> think it is implying that in this case there is no FILTER expression:
> 
> http://www.nabble.com/Variable-Substitution-in-Postgis-Query-to2951829.html#a2951829
> 
> Is this really true?  I realize the thread is nearly 2 years old, so things
> may have changed.
> 
> Furthermore, I tried all sorts of permutations of the expression for DATA to
> give me something that effectively executes the following postgis query:
> 
> SELECT the_geom FROM landuseb WHERE the_geom &&
> 'SRID=4326;LINESTRING(-122.3019062479681 37.83351284250283,
> -122.28009375203187 37.84648658222045)' AND feat_cod=2000123;
> 
> and I couldn't get anything to work.
> 
> So if this is a more efficient way to do filtering, any help on the syntax
> would also be most appreciated.
>