[Qgis-user] Questions QGIS webclient

[Andreas Neumann]

 Hi,

 On Tue, 28 Feb 2012 13:53:01 +0100,  wrote:
> Hallo Andreas,
>
> seems to be related with http://hub.qgis.org/issues/3959
>
> the proposed double \\ works
>
> this also worked for me:
> was: ... 'BOX\(|\)','','g'),' ',',')||']'::text AS bbox ...
> now: ... 'BOX[(]|[)]','','g'),' ',',')||']'::text AS bbox ...
>
> found the hint in the first user comment at
> 
> http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP

 so it seems that this issue will go away once people are moving to 
 PostgreSQL >=9.0

 Maybe we should just add a commented line for standard conforming vs. 
 non-standard conforming PostgreSQL installations.


>
> standard_conforming_strings is not set in my postgresql.conf 
> (PostgreSQL 8.4)
>
> as we are into wsgi there are more questions (I have no clue about
> wsgi, but did some QGIS-extensions in Python):
>
> 1) why do you use psycopg2 instead of QtSql (additional installation
> needed as PyQt4 is installed because of QGIS anyhow)?

 Because psycopg2 has fewer dependencies and leaner. The script may run 
 on a different server where there is not qt installed. I agree that on 
 QGIS desktop qtsql makes more sense, but I am not so sure about the 
 server.

> 2) I got a runtime error because the try - except clauses in both
> scripts do continue even if the try is unsuccessful, e.g. the conn
> object is undefined if psycopg2.connect is unsuccessful. IMHO the 
> rest
> of the code must go into the try block or the except block must
> return. What happens with the string errorText? Is it dumped in some
> logfile?
>
> 3) Documentation says the search table is supposed to have a field
> searchstring_tsvector (type tsvector) but it is not used, instead
> searchstring::tsvector is used!
> I experimented with tsvector; the WHERE clause in search.wsgi which 
> reads
> "searchstring::tsvector @@ lower('"+querystrings[j]+":*')::tsquery"
>
> This works fine if you have a street name like 'Am alten Gaswerk':
> select 'Am alten Gaswerk'::tsvector  @@ 'Gaswerk'::tsquery returns 
> true
> select 'Am alten Gaswerk'::tsvector  @@ 'Am'::tsquery returns true
>
> BUT
> select 'Karl-Marx-Allee'::tsvector  @@ 'Marx'::tsquery returns false
> select 'Karl-Marx-Allee'::tsvector  @@ 'Karl'::tsquery returns false
>
> PostgreSQL provides to_tsvector and to_tsquery functions
> select to_tsvector('Karl-Marx-Allee') @@ to_tsquery('Karl')
> select to_tsvector('Karl-Marx-Allee') @@ to_tsquery('Marx')
> both return true
> select to_tsvector('Am alten Gaswerk') @@ to_tsquery('Gaswerk') 
> returns true
>
> BUT
> select to_tsvector('Am alten Gaswerk') @@ to_tsquery('Am') returns
> false :( because Am is skipped by to_tsvector
>
> EVEN WORSE
> select to_tsvector('german', 'Über den Teufelslöchern') returns
> 'teufelsloch':3, a user has to enter quite some characters before the
> query returns something and if he enters "Teufelslöcher" he won't get
> any result.
>
> So tsvector is fine for texts skipping all the fill words and
> reducing nouns to their single form but as we are dealing with place
> names we do not want this behaviour. We could use PostgreSQL's ILIKE
> but this would result in a full string comparison and I guess in a
> lower performance.
>
> My workaround was to use searchstring_tsvector and to use a different
> language for to_tsvector and to_tsquery:
> CREATE VIEW ... AS SELECT ... to_tsvector('spanish', stringfield) AS
> searchstring_tsvector ...
> and change search.wsgi accordingly:
> searchstring_tsvector @@ 
> to_tsquery(\'spanish\','"+querystrings[j]+":*')
>
> select to_tsvector('spanish', 'Über den TEufelslöchern') @@
> to_tsquery('spanish', 'Über') returns true
>
> I can create tickets (and provide patches) for these issues.

 yes - please provide patches if you have improvements here. I do not 
 fully understand the whole tsvector and tsquery thing. We may want to 
 experiment with ILIKE if it is fast enough for our purpose.

 Andreas

 --
 Andreas Neumann
 Böschacherstrasse 10A
 8624 Grüt (Gossau ZH)
 Switzerland