[FeatureServer] query using tsvector

Thu Dec 20 06:40:03 PST 2012

Hi Michel,

thanks for the answer, it could be cool to have some news on this, and of course it's nice to know that FS 2.0 is coming, sure it will be a great version.

Merry Xsmas and happy holydays to you and all FS people!

Cheers,

Gery

__________________________________________________________________________________________
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO es necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie NICHT, wenn es NICHT notwendig ist.

Subject: Re: [FeatureServer] query using tsvector
From: michel.ott at me.com
Date: Thu, 20 Dec 2012 06:02:26 +0100
CC: featureserver at lists.osgeo.org
To: gamejihou at hotmail.com

Hi Gery
I need to setup an example to reproduce your questions. Would take a while 'cause I'm not available the next days.I will write you if I have any news on that.
Release 2.0 should come out somewhere in February (hopefully). The main changes are that you are able to implement your own services as a plugin and the current term "service" is then called "outputFormat".
CheersMichel

On Dec 19, 2012, at 10:16 PM, Gery . <gamejihou at hotmail.com> wrote:Hi Michel,

Many thanks for your answer, that session.query works well in GA, for that I had to define a class TsVector for the tsvector type first, and after assigning it to my tsvector column (ts in my table), I run that line as part of my model.py (python my_model.py). I just tried your suggestion, but with no much luck:

http://www.web.web/fs/Boreholes?format=GeoJSON&ts__ilike=ODP&queryable=ts

using:

*** GeoAlchemy.py.bk    2012-12-15 23:15:46.000000000 +0100
--- GeoAlchemy.py       2012-12-19 20:41:32.000000000 +0100
***************
*** 57,65 ****

      def feature_predicate(self, key, operator_name, value):
          if operator_name == 'like':
!             return key.like('%'+value+'%')
          elif operator_name == 'ilike':
!             return key.ilike('%'+value+'%')
          else:
              return self.query_operators[operator_name](key,value)

--- 57,65 ----

      def feature_predicate(self, key, operator_name, value):
          if operator_name == 'like':
!             return key.ts.op('@@')(func.to_tsquery("value"))
          elif operator_name == 'ilike':
!             return key.ts.op('@@')(func.to_tsquery("value"))
          else:
              return self.query_operators[operator_name](key,value)

the error is similar to the one in my last email:An error occurred: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Boreholes.ts has an attribute 'ts'
  File "/var/www/html/mop/fs/web_request/handlers.py", line 230, in cgi
    accepts = accepts )
  File "/var/www/html/mop/fs/FeatureServer/Server.py", line 245, in dispatchRequest
    result = method(action)
  File "/var/www/html/mop/fs/FeatureServer/DataSource/GeoAlchemy.py", line 159, in select
    for k, v in action.attributes.iteritems()]
  File "/var/www/html/mop/fs/FeatureServer/DataSource/GeoAlchemy.py", line 62, in feature_predicate
    return key.ts.op('@@')(func.to_tsquery("value"))
  File "/usr/lib/python2.6/site-packages/sqlalchemy/orm/attributes.py", line 211, in __getattr__
    key)

{'queryable': 'ts', 'ts__ilike': 'ODP', 'format': 'GeoJSON'}
I think something else is necessary but no idea what it is. Thanks for putting this in your todo list, one question, do you havea date for FS 2.0?

Cheers,

Gery

__________________________________________________________________________________________
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO es necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie NICHT, wenn es NICHT notwendig ist.

Subject: Re: [FeatureServer] query using tsvector
From: michel.ott at me.com
Date: Wed, 19 Dec 2012 14:12:30 +0100
CC: featureserver at lists.osgeo.org
To: gamejihou at hotmail.com

Hi Gery
Your wrote that in GA it should look like this:session.query(Boreholes).filter(Boreholes.tsvector.op('@@')(func.to_tsquery("ODP"))).all()"
what if you change your plainto_tsquery to the same appearance as in your filter() example above? Something like the following line:key.tsvector.op('@@')(func.to_tsquery("value"))
CheersMichel
P.S.: I should put this on my todo list for release 2.0

On Dec 19, 2012, at 12:22 PM, Gery . <gamejihou at hotmail.com> wrote:I've been changing the GeoAlchemy.py a bit in this way,in order to keep using the operator 'ilike' but as plainto_tsquery:

*** GeoAlchemy.py.bk    2012-12-15 23:15:46.000000000 +0100
--- GeoAlchemy.py       2012-12-19 12:00:13.000000000 +0100
***************
*** 57,65 ****

      def feature_predicate(self, key, operator_name, value):
          if operator_name == 'like':
!             return key.like('%'+value+'%')
          elif operator_name == 'ilike':
!             return key.ilike('%'+value+'%')
          else:
              return self.query_operators[operator_name](key,value)

--- 57,65 ----

      def feature_predicate(self, key, operator_name, value):
          if operator_name == 'like':
!             return key.plainto_tsquery(''+value+'')
          elif operator_name == 'ilike':
!             return key.plainto_tsquery(''+value+'')
          else:
              return self.query_operators[operator_name](key,value)

and using the same call but with the tsvector column (ie. http://www.myweb.web/fs/Boreholes?format=GeoJSON&ts__ilike=ODP&queryable=ts), I get this:An error occurred: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Boreholes.ts has an attribute 'plainto_tsquery'
  File "/var/www/html/mop/fs/web_request/handlers.py", line 230, in cgi
    accepts = accepts )
  File "/var/www/html/mop/fs/FeatureServer/Server.py", line 245, in dispatchRequest
    result = method(action)
  File "/var/www/html/mop/fs/FeatureServer/DataSource/GeoAlchemy.py", line 159, in select
    for k, v in action.attributes.iteritems()]
  File "/var/www/html/mop/fs/FeatureServer/DataSource/GeoAlchemy.py", line 62, in feature_predicate
    return key.plainto_tsquery(''+value+'')
  File "/usr/lib/python2.6/site-packages/sqlalchemy/orm/attributes.py", line 211, in __getattr__
    key)

{'queryable': 'ts', 'ts__ilike': 'ODP', 'format': 'GeoJSON'}
if I add "@@ plainto_tsquery" instead, it gives a syntax error in the fs_error.log. My idea is to replace only the return of the'ilike' or 'like' operators (and not the name itself) and use instead the '@@ plainto_tsquery', so I can keep using the same call, ie:

http://www.myweb.web/fs/Boreholes?format=GeoJSON&ts__ilike=ODP&queryable=ts

so,

it calls in SQL (actually this doesn't work in postgresql/postgis for tsvector type "ERROR:  operator does not exist: tsvector ~~* unknown):
select * from boreholes_table where ts ilike '%ODP%';

and with the change above (doesn't work so far), should call (this works in postgresql/postgis):
select * from boreholes_table where ts @@ plainto_tsquery('ODP');

Thanks in advance for any hint on this,

Gery
__________________________________________________________________________________________
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO es necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie NICHT, wenn es NICHT notwendig ist.

From: gamejihou at hotmail.com
To: featureserver at lists.osgeo.org; iocast at me.com
Subject: query using tsvector
Date: Tue, 18 Dec 2012 16:02:17 +0000

Hello,

I've been testing FS with GA and found something that don't get how to put it.

For instance, this call works very well in FS:

First example:
[code]
in FS: http://www.myweb.web/fs/Boreholes?format=GeoJSON&source__ilike=ODP&queryable=source
in SQL: SELECT * FROM boreholes_table where source ilike '%ODP%'
[/code]

and what I'm looking for is querying this table but using the tsvector column, so this works in SQL but don't know how to put it in FS:

Second example:
[code]
in FS: ??
in SQL: select * from boreholes_table where tsvector @@ plainto_tsquery('ODP');
(in any case this is for GA: "session.query(Boreholes).filter(Boreholes.tsvector.op('@@')(func.to_tsquery("ODP"))).all()")
[/code]

based on the first SQL (and FS) example, how could I put it for the second SQL example?

Any hint is appreciated, thanks in advance,

Best regards,

__________________________________________________________________________________________
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO es necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie NICHT, wenn es NICHT notwendig ist.
_______________________________________________
FeatureServer mailing list
FeatureServer at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/featureserver
_______________________________________________
FeatureServer mailing list
FeatureServer at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/featureserver

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/featureserver/attachments/20121220/ff9e448f/attachment-0001.html>