database filtering with FILTER or DATA ?

[Stephen Woodbridge]

rich.fromm wrote:
> Stephen Woodbridge wrote:
>> 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/
>>
> 
> First of all, thank you for the pointer to the reference.  It is a bit
> helpful, and did clarify for me what the syntax should be.  (And might be
> helpful for other reasons too, unrelated to this.)
> 
> So, in this case, I can replace the following two lines:
> 
>         DATA "the_geom FROM landuseb"
>         FILTER "the_geom && 'SRID=4326;LINESTRING(-122.3019062479681
> 37.83351284250283, -122.28009375203187 37.84648658222045)' AND
> feat_cod=2000123"
> 
> With just the following line:
> 
>         DATA "the_geom FROM (SELECT gid,the_geom from landuseb WHERE
> the_geom && 'SRID=4326;LINESTRING(-122.3019062479681 37.83351284250283,
> -122.28009375203187 37.84648658222045)' AND feat_cod=2000123) AS foo USING
> SRID=4326 USING unique gid"
> 
> So no FILTER, just the DATA.
> 
> But my question is whether or not there is any advantage to doing this?  Is
> the latter more efficient than the former?  They both appear to me to be
> doing
> the same thing, although the mailing list posting I referenced previously
> seems to imply that the former does not do the filtering within postgis, but
> does it within mapserver:
> http://www.nabble.com/Variable-Substitution-in-Postgis-Query-to2951829.html#a2951829
> 
> But this is not my impression from reading the docs:
> http://mapserver.gis.umn.edu/docs/reference/mapfile/layer
> 

Exactly, the sub-query is doing the filtering in the database which is 
much faster that doing it in mapserver. On a related note, this method 
also lets you do joins on rendering which is inportant if you want to do 
thematic mapping.

-Steve