[mapserver-users] postgres fulltext search

[mattmendick]

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!
-- 
View this message in context: http://osgeo-org.1803224.n2.nabble.com/postgres-fulltext-search-tp5009033p5009033.html
Sent from the Mapserver - User mailing list archive at Nabble.com.