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: [email protected]
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
_______________________________________________
Qgis-user mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/qgis-user
