[Qgis-user] QGIS Webclient PostgreSQL search table

[Bernhard Ströbl]

Hi Dennis,

the documentation might be a bit misleading. In fact you have to insert 
with to_tsvector(somestring).
I normally use views as search relations so if I have a table 
streets(gid, the_geom, streetname)
it would be something like
CREATE OR REPLACE VIEW search_streets AS
  SELECT streetname AS searchstring,
streetname::text AS displaytext,
'02_Streets'::text AS search_category,
the_geom,
'MULTILINESTRING'::text AS geometry_type,
-- here it comes:
to_tsvector(streetname::text ) AS searchstring_tsvector
FROM streets

Bernhard

 >
 > displaytext text NOT NULL, --the display text for the search combobox,
 > e.g. "Z~ 1 richstrasse 46, 8610 Uster (address
 >
 > search_category text, --should have a leading two digit number:, e.g.
 > --"03_parcels", where 03 is the order of the search categories, the 
number
 > --should be unique across all search tables
 > the_geom geometry,
 > --the actual geometry
 > geometry_type text,
 > --the geometry type as returned by ST_GeometryType(the_geom)
 > searchstring_tsvector

Am 25.06.2012 14:30, schrieb Dennis Ströer:
> Am 25.06.2012 14:09, schrieb Bernhard Ströbl:
>> Hi Dennis,
>>
>> please try to execute SELECT to_tsvector('bla') in a SQL shell.
>> What happens?
>> Your error message indicates you call the funtion without an argument.
>> Maybe you should read the doc on the Full text Search
>> http://www.postgresql.org/docs/8.4/static/textsearch.html
>> and/or the tsvector data type
>> http://www.postgresql.org/docs/8.4/static/datatype-textsearch.html
>>
>> Bernhard
>>
>> Am 25.06.2012 13:11, schrieb Dennis Ströer:
>>> Hello everybody,
>>>
>>> I'm testing the QGIS webclient in the moment. Thanks for developing such
>>> a great project! Its a nice tool and the default settings and
>>> functionality works fine. Now I'm trying to realize the search with wsgi
>>> and a PostgreSQL table setup for searching like explained in the
>>> chapters 6 and 7 of the installation guide.
>>> I created a searchtable and now I want to fill it with some test data,
>>> but a problem appears. When I want to fill the field
>>> searchstring_tsvector with the value to_tsvector() I'm getting the
>>> following error:
>>>
>>> ERROR: function to_tsvector() does not exist
>>>
>>> I'm using PostgreSQL 8.4, so I think the tsearch package is already
>>> included?! And when I add a new column to a table I can select the data
>>> type tsvector...
>>>
>>> Do I have to create the function to_tsvector by hand?
>>>
>>> Does anybody know a solution for this error?
>>>
>>> Cheers,
>>> D. Stroeer
>>> _______________________________________________
>>> Qgis-user mailing list
>>> Qgis-user at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>>
>>>
>>
>>
>>
>>
>> ________ Information from NOD32 ________
>> This message was checked by NOD32 Antivirus System for Linux Mail Server.
>> http://www.nod32.com
> Hey guys,
>
> thanks for the quick response!
> Like Andreas told me, I executed the tsearch2.sql script which installed
> the data types and function. If I execute SELECT to_tsvector('bla') it
> returns 'bla' :1, so I think it works fine.
>
> But if I try to fill the searchtable, the same error message appears:
> ERROR: function to_tsvector() does not exist
>
> Like Bernhard mentioned, it seems that I call the funtion without an
> argument. But when I look at the example in the installation guide on
> page 16, it seems to me, that I have to:
>
>
> CREATE TABLE public.searchtable
> (
> searchstring text, --the search string (all lower case), e.g. "z~ 1
> richstrasse 46, 8610 uster"
>
> displaytext text NOT NULL, --the display text for the search combobox,
> e.g. "Z~ 1 richstrasse 46, 8610 Uster (address
>
> search_category text, --should have a leading two digit number:, e.g.
> --"03_parcels", where 03 is the order of the search categories, the number
> --should be unique across all search tables
> the_geom geometry,
> --the actual geometry
> geometry_type text,
> --the geometry type as returned by ST_GeometryType(the_geom)
> searchstring_tsvector tsvector, *-- be sure to fill this with to_tsvector()*
> CONSTRAINT searchtable_pkey PRIMARY KEY (displaytext)
> )
> WITH (
> OIDS=FALSE
> );
>
>
> Cheers,
> D. Stroeer
>
>
> *________ Information from NOD32 ________*
> This message was checked by NOD32 Antivirus System for Linux Mail Server.
> http://www.nod32.com
>
>



________ Information from NOD32 ________
This message was checked by NOD32 Antivirus System for Linux Mail Server.
http://www.nod32.com