database filtering with FILTER or DATA ?
woodbri at SWOODBRIDGE.COM
Mon Jan 7 22:04:41 EST 2008
> 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:
> 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
> 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
> 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:
> But this is not my impression from reading the docs:
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
More information about the mapserver-users