<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Tahoma
}
--></style></head>
<body class='hmmessage'><div dir='ltr'>
<font style="font-size:10pt;" color="#366092" face="Arial" size="2">I've been <font style="font-size: 10pt;" size="2">changing the GeoAlche<font style="font-size: 10pt;" size="2">my<font style="font-size: 10pt;" size="2">.py a bit in this way,in order to keep using the operator 'ilike' but as plain<font style="font-size: 10pt;" size="2">to_tsquery</font>:<br><br>*** GeoAlchemy.py.bk 2012-12-15 23:15:46.000000000 +0100<br>--- GeoAlchemy.py 2012-12-19 12:00:13.000000000 +0100<br>***************<br>*** 57,65 ****<br> <br> def feature_predicate(self, key, operator_name, value):<br> if operator_name == 'like':<br>! return key.like('%'+value+'%')<br> elif operator_name == 'ilike':<br>! return key.ilike('%'+value+'%')<br> else:<br> return self.query_operators[operator_name](key,value)<br> <br>--- 57,65 ----<br> <br> def feature_predicate(self, key, operator_name, value):<br> if operator_name == 'like':<br>! return key.plainto_tsquery(''+value+'')<br> elif operator_name == 'ilike':<br>! return key.plainto_tsquery(''+value+'')<br> else:<br> return self.query_operators[operator_name](key,value)<br><br>and using the same call but wit<font style="font-size: 10pt;" size="2">h the tsvector column</font> (ie. </font></font></font></font><font style="font-size:10pt;" color="#366092" face="Arial" size="2"><font style="font-size: 10pt;" size="2"><font style="font-size: 10pt;" size="2"><font style="font-size: 10pt;" size="2"><font style="font-size:10pt" size="2"><font style="font-size:10pt" size="2"><font style="font-size:10pt" size="2">http://www.myweb.web/fs/Boreholes?format=GeoJSON&ts__ilike=ODP&queryable=ts), I get this:</font></font></font></font></font></font></font><br id="FontBreak"><pre>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'}
</pre><font style="font-size:10pt" color="#1F497D" face="Arial" size="2"><font style="font-size: 10pt;" size="2">if I add "<font style="font-size: 10pt;" size="2">@@<font style="font-size: 10pt;" size="2"> plainto_tsquery" instead, it gives a syntax er<font style="font-size: 10pt;" size="2">ror in t<font style="font-size: 10pt;" size="2">he fs_error<font style="font-size: 10pt;" size="2">.log. <font style="font-size: 10pt;" size="2">My idea is to replace<font style="font-size: 10pt;" size="2"> <font style="font-size: 10pt;" size="2">only</font></font> <font style="font-size: 10pt;" size="2">the return of<font style="font-size: 10pt;" size="2"> the</font> 'ilike'<font style="font-size: 10pt;" size="2"> </font>or 'like' operators (<font style="font-size: 10pt;" size="2">a<font style="font-size: 10pt;" size="2">nd not the name itsel<font style="font-size: 10pt;" size="2">f)</font></font></font> and use inst<font style="font-size: 10pt;" size="2">ead the '@@ plainto_<font style="font-size: 10pt;" size="2">tsquery</font></font></font></font></font></font></font></font></font></font><font style="font-size: 10pt;" size="2"><font style="font-size: 10pt;" size="2"></font></font>', so I can keep using the same call<font style="font-size: 10pt;" size="2">, ie<font style="font-size: 10pt;" size="2">:<br></font></font></font><br><font style="font-size:10pt" color="#1F497D" face="Arial" size="2"><font style="font-size: 10pt;" size="2"><font style="font-size: 10pt;" size="2"><font style="font-size:10pt;" color="#366092" face="Arial" size="2"><font style="font-size: 10pt;" size="2"><font style="font-size: 10pt;" size="2"><font style="font-size: 10pt;" size="2"><font style="font-size:10pt" size="2"><font style="font-size:10pt" size="2"><font style="font-size:10pt" size="2">http://www.myweb.web/fs/Boreholes?format=GeoJSON&ts__ilike=ODP&queryable=ts</font></font></font></font></font></font></font><br><br><font style="font-size: 10pt;" size="2">so,<br><br></font>it calls in <font style="font-size: 10pt;" size="2">SQL</font> (actually this <font style="font-size: 10pt;" size="2">doesn'<font style="font-size: 10pt;" size="2">t work in postgresql/postgis <font style="font-size: 10pt;" size="2">for tsvector <font style="font-size: 10pt;" size="2">type</font></font> "ERROR: operator does not exist: tsvector ~~* unknown)</font></font>:<br>select * from<font style="font-size: 10pt;" size="2"> b<font style="font-size: 10pt;" size="2">o<font style="font-size: 10pt;" size="2">reholes_table where ts ilike '%ODP%';</font></font></font><br><br><font style="font-size: 10pt;" size="2">and with the ch<font style="font-size: 10pt;" size="2">ange above <font style="font-size: 10pt;" size="2">(doesn<font style="font-size: 10pt;" size="2">'t work so far)<font style="font-size: 10pt;" size="2">, should call (this works in postgresql/postgis):</font></font></font></font></font><br><font style="font-size: 10pt;" size="2">select * from boreholes_<font style="font-size: 10pt;" size="2">table where ts @@ p<font style="font-size: 10pt;" size="2">lainto_tsquer<font style="font-size: 10pt;" size="2">y('ODP');</font></font></font></font><br><br><font style="font-size: 10pt;" size="2">Thanks in advance for any hint on this,<br><br></font><font style="font-size: 10pt;" size="2"><font style="font-size: 10pt;" size="2"><font style="font-size: 10pt;" size="2"><font style="font-size: 10pt;" size="2"></font></font></font></font>Gery<br></font></font>__________________________________________________________________________________________<br>Piensa en el medio ambie<font style="" face="Arial">nte - mantenlo en la pantalla. NO lo imprimas si NO es necesario.</font></font><font style="font-size:10pt" color="#1F497D" face="Arial" size="2"><br></font><font style="font-size:10pt" color="#1F497D" face="Arial" size="2">Think green - keep it on the screen. Do NOT print if it is NOT necessary.</font><font style="font-size:10pt" color="#1F497D" face="Arial" size="2"><br></font><font style="font-size:10pt" color="#1F497D" face="Arial" size="2"><span id="ecxresult_box" class="ecxshort_text" lang="de"><span class="ecxhps">Denken Sie an</span> <span class="ecxhps">die Umwelt</span></span></font><font style="font-size:10pt" color="#1F497D" face="Arial" size="2"><span id="ecxresult_box" lang="de"><span class="ecxhps"></span> <span class="ecxhps">-</span> <span class="ecxhps">bewahren Sie es</span> <span class="ecxhps">auf dem Bildschirm.</span> <span class="ecxhps">Drucken Sie NICHT</span><span>, wenn</span> <span class="ecxhps">es NICHT notwendig ist</span><span>.</span></span></font><br><br><br><div><div id="SkyDrivePlaceholder"></div><hr id="stopSpelling">From: gamejihou@hotmail.com<br>To: featureserver@lists.osgeo.org; iocast@me.com<br>Subject: query using tsvector<br>Date: Tue, 18 Dec 2012 16:02:17 +0000<br><br>
<style><!--
.ExternalClass .ecxhmmessage P
{padding:0px;}
.ExternalClass body.ecxhmmessage
{font-size:10pt;font-family:Tahoma;}
--></style>
<div dir="ltr">
<font style="font-size:10pt" color="#366092" face="Arial" size="2">Hello,<br><br><font style="font-size:10pt" size="2"><font style="font-size:10pt" size="2"><font style="font-size:10pt" size="2">I've <font style="font-size:10pt" size="2"><font style="font-size:10pt" size="2">been</font> testing <font style="font-size:10pt" size="2">FS <font style="font-size:10pt" size="2">with G<font style="font-size:10pt" size="2">A and </font></font></font></font></font></font></font></font>fou<font style="font-size:10pt" size="2">nd somethi<font style="font-size:10pt" size="2">ng that don't get how to pu<font style="font-size:10pt" size="2">t it.<br><br>For instance, this call works very well in FS:<br><br><font style="font-size:10pt" size="2">F<font style="font-size:10pt" size="2">irst example:</font></font><br>[code]<br>in FS<font style="font-size:10pt" size="2">: </font>http://www.myweb.web/fs/Boreholes?format=GeoJSON&source__ilike=ODP&queryable=source<br>in SQL: <font style="font-size:10pt" size="2">SELECT * <font style="font-size:10pt" size="2">FROM bor<font style="font-size:10pt" size="2">e<font style="font-size:10pt" size="2">holes_table <font style="font-size:10pt" size="2">wh<font style="font-size:10pt" size="2">e<font style="font-size:10pt" size="2">re source ilike '%ODP%'<br><font style="font-size:10pt" size="2"><font style="font-size:10pt" size="2"></font></font><font style="font-size:10pt" size="2">[/code]<br><br>and<font style="font-size:10pt" size="2"> what I'm looking f<font style="font-size:10pt" size="2">or <font style="font-size:10pt" size="2">is quer<font style="font-size:10pt" size="2">ying this table but using the t<font style="font-size:10pt" size="2">svector column,<font style="font-size:10pt" size="2"> so</font></font></font></font></font></font> this works in SQL but don't know how to <font style="font-size:10pt" size="2">put it in FS:<br><br><font style="font-size:10pt" size="2">Second example:</font><br><font style="font-size:10pt" size="2"><font style="font-size:10pt" size="2"></font>[code]<br><font style="font-size:10pt" size="2">in<font style="font-size:10pt" size="2"> FS: ??<br><font style="font-size:10pt" size="2">in SQL: select * from boreholes_<font style="font-size:10pt" size="2">table</font> where tsvector @@ plainto_tsquery('ODP');<br><font style="font-size:10pt" size="2"><font style="font-size:10pt" size="2">(in an<font style="font-size:10pt" size="2">y cas<font style="font-size:10pt" size="2">e this is for</font></font></font><font style="font-size:10pt" size="2"> GA: "</font></font>session.query(Boreholes).filter(Boreholes.tsvector.op('@@')(func.to_tsquery("ODP"))).all()<font style="font-size:10pt" size="2">"</font></font>)<br><font style="font-size:10pt" size="2">[<font style="font-size:10pt" size="2">/code]<br><br><font style="font-size:10pt" size="2"><font style="font-size:10pt" size="2"><font style="font-size:10pt" size="2">based on the first SQL (and FS) </font></font></font>example, how co<font style="font-size:10pt" size="2">uld I put<font style="font-size:10pt" size="2"> it for the second </font></font>SQL exa<font style="font-size:10pt" size="2">mple?</font><br><br><font style="font-size:10pt" size="2">Any hint <font style="font-size:10pt" size="2">is apprec<font style="font-size:10pt" size="2">iated, <font style="font-size:10pt" size="2">t</font></font></font>hanks in advance</font>,<br><br><font style="font-size:10pt" size="2">Best regards,<br><br><br></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font><font style="font-size:10pt" color="#1F497D" face="Arial" size="2">__________________________________________________________________________________________<br>Piensa en el medio ambie<font style="" face="Arial">nte - mantenlo en la pantalla. NO lo imprimas si NO es necesario.</font></font><font style="font-size:10pt" color="#1F497D" face="Arial" size="2"><br></font><font style="font-size:10pt" color="#1F497D" face="Arial" size="2">Think green - keep it on the screen. Do NOT print if it is NOT necessary.</font><font style="font-size:10pt" color="#1F497D" face="Arial" size="2"><br></font><font style="font-size:10pt" color="#1F497D" face="Arial" size="2"><span id="ecxresult_box" class="ecxshort_text" lang="de"><span class="ecxhps">Denken Sie an</span> <span class="ecxhps">die Umwelt</span></span></font><font style="font-size:10pt" color="#1F497D" face="Arial" size="2"><span id="ecxresult_box" lang="de"><span class="ecxhps"></span> <span class="ecxhps">-</span> <span class="ecxhps">bewahren Sie es</span> <span class="ecxhps">auf dem Bildschirm.</span> <span class="ecxhps">Drucken Sie NICHT</span><span>, wenn</span> <span class="ecxhps">es NICHT notwendig ist</span><span>.</span></span></font><br> </div></div> </div></body>
</html>