[mapserver-users] postgres fulltext search

[Andy Colson]

On 5/5/2010 9:14 AM, mattmendick wrote:
>
> We have set up a WFS service connected with postgres loaded up with some
> substantial datasets of ~120 million rows. We have run into users wanting to
> utilize the filter "PropertyIsLike". The search string and a wildcard
> character are specified, and mapserver does a search against postgres,
> something like:
>
> WHERE myColumn LIKE "SearchString%"
>
> if the search string was SearchString* and the wildcard set to *. There is
> an index on myColumn. The users then tried putting the wildcard at the
> beginning of the search string, and this query takes forever as the index on
> myColumn no longer matters.
>
> Postgres 8.3 has a full-text search feature (and I believe earlier versions
> had this functionality through the use of plugins and/or extensions).  The
> documentation is here:
> http://www.postgresql.org/docs/8.3/static/textsearch.html
>
> Basically, you index columns as before, but feeding it the output of a
> ts_vector() function, where the input is the column you are indexing.  In
> order to search on the column, you need to change the WHERE clause to
> something like:
>
> WHERE myColumnNowIndexed @@ ts_query('SearchString')
>
> Is there any way to accomplish this in mapserver? I understand from this
> page of ms documentation (http://mapserver.org/input/vector/postgis.html)
> that it is possible to substitute the bbox parameter of a search in the DATA
> entry of a mapfile. Is it possible to alter the WHERE clause of the search
> that deals with attribute matching?
>
> Thanks a lot!


How do you do it now?  Do you have something in place using:

 > WHERE myColumn LIKE "SearchString%"


I suppose what you're asking is, whats the corresponding variable to !BOX!?

-Andy