[Qgis-user] Questions QGIS webclient

Tue Feb 28 06:52:46 PST 2012

Am 28.02.2012 14:26, schrieb 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.

Maybe we could add both lines and people can try and see which works for 
them. Is this a version or a standard issue (or both)?
>
>
>>
>> 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.

ok, I see the point in this, you know people tend to think their setting 
is the same as anybody's :-)

>
>> 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.

in short: the current code works with place names as long as no "-" are 
included, it does not work with place names like 'Karl-Marx-Allee'

testing ILIKE is an option BUT slower:
I replaced the ts_vector search with
"searchstring ILIKE \'%"+querystrings[j]+"%\'"
this works but takes longer:
approx 17000 datasets (addresses, streets) in a view
my tsvector solution: 1.5 sec
ILIKE solution 2.1 sec

I created a table, filled in my view 6 times and created an index on 
searchfield_tsvector as described in the doc
ILIKE 0.5 sec
tsvector 0.26 sec

the backend is a dedicated server with several processors and loads of 
RAM; no idea what happens if you have less processing capacity or more 
datasets (either in a view or table) although I cannot imagine an 
application where one would have millions of place names.

Bernhard

>
> Andreas
>
> --
> Andreas Neumann
> Böschacherstrasse 10A
> 8624 Grüt (Gossau ZH)
> Switzerland
>
>
> ________ Information from NOD32 ________
> This message was checked by NOD32 Antivirus System for Linux Mail Server.
> http://www.nod32.com

-- 
Bernhard Ströbl
Anwendungsbetreuer GIS

Kommunale Immobilien Jena
Am Anger 26
07743 Jena

Tel.: 03641 49- 5190
E-Mail: bernhard.stroebl at jena.de
Internet: www.kij.de

Kommunale Immobilien Jena
Eigenbetrieb der Stadt Jena
Werkleiter: Thomas Dirkes

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