[mapserver-users] [EXTERNAL] Re: ogc:PropertyIsEqualTo behaves like PropertyIsLike with oracle

Wed Jul 22 10:59:32 PDT 2015

Good idea we use POST, but I tried using GET and I still get 
FLTLayerApplyPlainFilterToLayer():  ("[NOM_STAT]" =*"Anto") in the log 
(with postgis and oracle)

Michael Smith <michael.smith.erdc at gmail.com> 
2015-07-22 12:02

A
"Steve.Toutant at inspq.qc.ca" <Steve.Toutant at inspq.qc.ca>
cc

Objet
Re: [EXTERNAL] Re: [mapserver-users] ogc:PropertyIsEqualTo behaves like 
PropertyIsLike with oracle

Only other thing I can think of are you doing a GET or POST? I'm doing 
GET. 

Michael Smith
Remote Sensing/GIS Center
US Army Corps of Engineers

On Jul 22, 2015, at 11:42 AM, Steve.Toutant at inspq.qc.ca wrote:

12C 

Michael Smith <michael.smith.erdc at gmail.com> 
2015-07-22 11:36 

A
"Steve.Toutant at inspq.qc.ca" <Steve.Toutant at inspq.qc.ca> 
cc
"mapserver-users at lists.osgeo.org" <mapserver-users at lists.osgeo.org> 
Objet
Re: [EXTERNAL] Re: [mapserver-users] ogc:PropertyIsEqualTo behaves like 
PropertyIsLike with oracle

What version of Oracle? Not that we have any version dependencies in the 
regexp code. 

I'm running 11.2 and 12.1. 

Michael Smith 
Remote Sensing/GIS Center 
US Army Corps of Engineers 

On Jul 22, 2015, at 11:30 AM, Steve.Toutant at inspq.qc.ca wrote:

oracle and postgis are on 2 differents servers physically on 2 cities, 
both has a mapserver installation made by 2 different person. One is on 
debian, and other on fedora 
There are using ms7 since other new wfs improvement made in MS7 are 
used... 

Michael Smith <michael.smith.erdc at gmail.com> 
2015-07-22 11:22 

A
"Steve.Toutant at inspq.qc.ca" <Steve.Toutant at inspq.qc.ca> 
cc
"mapserver-users at lists.osgeo.org" <mapserver-users at lists.osgeo.org> 
Objet
Re: [EXTERNAL] Re: [mapserver-users] ogc:PropertyIsEqualTo behaves like 
PropertyIsLike with oracle

Could you be hitting an old installed version?

Michael Smith 
Remote Sensing/GIS Center 
US Army Corps of Engineers 

On Jul 22, 2015, at 11:14 AM, Steve.Toutant at inspq.qc.ca wrote:

We are on linux as well. 
Ms7+ postgis.. it doesn't translate the query to regex, I have the same 
query than with Oracle... ("[NOM_STAT]" =*"Anto") 

Well...I'm more confused now...It doesn't make sens that our MS doesn't 
generate the same request... 
Can it be an installation problem, an option is missing when compiling? 

Michael Smith <michael.smith.erdc at gmail.com> 
2015-07-22 09:55 

A
"Steve.Toutant at inspq.qc.ca" <Steve.Toutant at inspq.qc.ca> 
cc
"mapserver-users at lists.osgeo.org" <mapserver-users at lists.osgeo.org> 
Objet
Re: [EXTERNAL] Re: [mapserver-users] ogc:PropertyIsEqualTo behaves like 
PropertyIsLike with oracle

Just tried with latest master (sha 
f13cd8825e117b09663bb325a03854c27e9cdd39) and got correct REGEXP_LIKE sql. 
Was using rel-7-0-0-beta1 before. Both same. 

I'm running on Linux although can't see how that would change the sql 
being generated. 

---- 
Michael Smith 
US Army Corps 
Remote Sensing GIS/Center 
michael.smith at usace.army.mil 

From: <mapserver-users-bounces at lists.osgeo.org> on behalf of "
Steve.Toutant at inspq.qc.ca" <Steve.Toutant at inspq.qc.ca>
Date: Wednesday, July 22, 2015 at 9:44 AM
To: "Steve.Toutant at inspq.qc.ca" <Steve.Toutant at inspq.qc.ca>
Cc: "mapserver-users at lists.osgeo.org" <mapserver-users at lists.osgeo.org>, "
mapserver-users-bounces at lists.osgeo.org" <
mapserver-users-bounces at lists.osgeo.org>
Subject: [EXTERNAL] Re: [mapserver-users] ogc:PropertyIsEqualTo behaves 
like PropertyIsLike with oracle
Resent-From: Michael Smith <michael.smith at usace.army.mil> 

using MapServer version 7.0.0-beta1 

Steve.Toutant at inspq.qc.ca@lists.osgeo.org
Envoyé par : mapserver-users-bounces at lists.osgeo.org 
2015-07-22 09:43 

A
Michael Smith <michael.smith.erdc at gmail.com> 
cc
mapserver-users at lists.osgeo.org 
Objet
Re: [mapserver-users] ogc:PropertyIsEqualTo behaves like PropertyIsLike 
with oracle

CONNECTIONTYPE oraclespatial
DATA "GEOM_POIN from (select * FROM SMDVDP_STAT_METE WHERE EXTRACT(YEAR 
FROM D_DEB) = 2015) USING SRID 4326" 

Michael Smith <michael.smith.erdc at gmail.com> 
2015-07-22 09:38 

A
<Steve.Toutant at inspq.qc.ca> 
cc
<mapserver-users at lists.osgeo.org> 
Objet
Re: [mapserver-users] ogc:PropertyIsEqualTo behaves like PropertyIsLike 
with oracle

Same result with 1.1.0 

Are you using an CONNECTIONTYPE oraclespatial or a CONNECTIONTYPE OGR to 
Oracle?

Mike 

---- 
Michael Smith 
US Army Corps 
Remote Sensing GIS/Center 
michael.smith at usace.army.mil 

From: <Steve.Toutant at inspq.qc.ca>
Date: Wednesday, July 22, 2015 at 9:34 AM
To: Michael Smith <michael.smith.erdc at gmail.com>
Cc: <mapserver-users at lists.osgeo.org>
Subject: Re: [mapserver-users] ogc:PropertyIsEqualTo behaves like 
PropertyIsLike with oracle

Michael we are using MS7 master and WFS 1.1.0
Can you try with this wfs version instead of 2.0 t osee if you get the 
same results? 

Michael Smith <michael.smith.erdc at gmail.com> 
2015-07-22 09:32 

A
<Steve.Toutant at inspq.qc.ca> 
cc
<mapserver-users at lists.osgeo.org> 
Objet
Re: [mapserver-users] ogc:PropertyIsEqualTo behaves like PropertyIsLike 
with oracle

When I do this on my system I get

request: 
service=WFS&version=2.0.0&request=GetFeature&typeNames=gridws_pointcloud&Filter=<Filter><PropertyIsEqualto%20MatchCase="FALSE"><PropertyName>FILE_NAME</PropertyName><Literal>test2.las</Literal></PropertyIsEqualTo></Filter>

[Wed Jul 22 09:26:31 2015].885667 got a IEQ comparison
[Wed Jul 22 09:26:31 2015].885676 closing RE comparison
[Wed Jul 22 09:26:31 2015].885681 msOracleSpatialLayerWhichShapes was 
called.
[Wed Jul 22 09:26:31 2015].885704 msOracleSpatialLayerWhichShapes. Using 
this Sql to retrieve the data: SELECT COLLECT_ID, COLLECTION, FILE_NAME, 
DATE_LOADED, PC_ID, X, Y, FILE_SIZE_MB, COLLECTDATE, DOWNLOAD_URL, 
SENSOR_NAME, rownum, geom WHERE  ( REGEXP_LIKE( FILE_NAME, 'test2.las','i' 
 ) )  AND SDO_FILTER( geom, MDSYS.SDO_GEOMETRY(2003, :srid, 
NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') 
= 'TRUE'

This shows that its translating the PropertyIsEqualTo with MatchCase false 
to a regexp_like case insensitive query with no wildcards. Not sure why 
you wouldn't get the same thing.

Mike

----
Michael Smith
US Army Corps
Remote Sensing GIS/Center
michael.smith at usace.army.mil

From: <Steve.Toutant at inspq.qc.ca>
Date: Wednesday, July 22, 2015 at 9:13 AM
To: Michael Smith <michael.smith.erdc at gmail.com>
Cc: <mapserver-users at lists.osgeo.org>
Subject: Re: [mapserver-users] ogc:PropertyIsEqualTo behaves like 
PropertyIsLike with oracle

Thanks for your help, here is the log
Steve
ul 22 08:14:09 2015].892715 msOracleSpatialLayerWhichShapes. Bind values: 
srid:4326 minx:-91.320741 miny:41.948470 maxx:-45.679259 maxy:63.664769
[Wed Jul 22 08:14:09 2015].894113 msOracleSpatialLayerWhichShapes bind by 
name and object.
[Wed Jul 22 08:14:09 2015].894134 msOracleSpatialLayerWhichShapes name and 
object now bound.
[Wed Jul 22 08:14:10 2015].77889 mapserv request processing time 
(msLoadMap not incl.): 0.203s
[Wed Jul 22 08:14:10 2015].77903 msOracleSpatialLayerClose was called. 
Layer: 0x1308650, Layer name: SMDVDP_STAT_METE
[Wed Jul 22 08:14:10 2015].78084 
msConnPoolRelease(SMDVDP_STAT_METE,USER/*************@DATABASE:1521/DEV.fadq.qc,0x1481850)
[Wed Jul 22 08:14:30 2015].200042 CGI Request 38 on process 14430
[Wed Jul 22 08:14:30 2015].200197 msOracleSpatialLayerOpen called with: 
GEOM_POIN from (select * FROM SMDVDP_STAT_METE WHERE EXTRACT(YEAR FROM 
D_DEB) = 2015) USING SRID 4326 (Layer pointer 0x142bbe0)
[Wed Jul 22 08:14:30 2015].200215 
msConnPoolRequest(SMDVDP_STAT_METE,USER/*************@DATABASE:1521/DEV.fadq.qc) 
-> got 0x1481850
[Wed Jul 22 08:14:30 2015].208656 msOracleSpatialLayerFreeItemInfo was 
called.
[Wed Jul 22 08:14:30 2015].208676 msOracleSpatialLayerGetItems was called.
[Wed Jul 22 08:14:30 2015].209323 msOracleSpatialLayerInitItemInfo was 
called.
[Wed Jul 22 08:14:30 2015].209369 msOracleSpatialLayerFreeItemInfo was 
called.
[Wed Jul 22 08:14:30 2015].209374 msOracleSpatialLayerClose was called. 
Layer: 0x142bbe0, Layer name: SMDVDP_STAT_METE
[Wed Jul 22 08:14:30 2015].209396 
msConnPoolRelease(SMDVDP_STAT_METE,USER/*************@DATABASE:1521/DEV.fadq.qc,0x1481850)
[Wed Jul 22 08:14:30 2015].210875 msOracleSpatialLayerOpen called with: 
GEOM_POIN from (select * FROM SMDVDP_STAT_METE WHERE EXTRACT(YEAR FROM 
D_DEB) = 2015) USING SRID 4326 (Layer pointer 0x142bbe0)
[Wed Jul 22 08:14:30 2015].210889 
msConnPoolRequest(SMDVDP_STAT_METE,USER/*************@DATABASE:1521/DEV.fadq.qc) 
-> got 0x1481850
[Wed Jul 22 08:14:30 2015].218325 msOracleSpatialLayerOpen called with: 
GEOM_POIN from (select * FROM SMDVDP_STAT_METE WHERE EXTRACT(YEAR FROM 
D_DEB) = 2015) USING SRID 4326 (Layer pointer 0x142bbe0)
[Wed Jul 22 08:14:30 2015].218345 msOracleSpatialLayerFreeItemInfo was 
called.
[Wed Jul 22 08:14:30 2015].218354 msOracleSpatialLayerGetItems was called.
[Wed Jul 22 08:14:30 2015].218946 msOracleSpatialLayerInitItemInfo was 
called.
[Wed Jul 22 08:14:30 2015].218980 msOracleSpatialLayerFreeItemInfo was 
called.
[Wed Jul 22 08:14:30 2015].218985 msOracleSpatialLayerClose was called. 
Layer: 0x142bbe0, Layer name: SMDVDP_STAT_METE
[Wed Jul 22 08:14:30 2015].219003 
msConnPoolRelease(SMDVDP_STAT_METE,USER/*************@DATABASE:1521/DEV.fadq.qc,0x1481850)
[Wed Jul 22 08:14:30 2015].219010 msOracleSpatialLayerOpen called with: 
GEOM_POIN from (select * FROM SMDVDP_STAT_METE WHERE EXTRACT(YEAR FROM 
D_DEB) = 2015) USING SRID 4326 (Layer pointer 0x142bbe0)
[Wed Jul 22 08:14:30 2015].219018 
msConnPoolRequest(SMDVDP_STAT_METE,USER/*************@DATABASE:1521/DEV.fadq.qc) 
-> got 0x1481850
[Wed Jul 22 08:14:30 2015].225298 msOracleSpatialLayerEnablePaging was 
called.
[Wed Jul 22 08:14:30 2015].225318 msOracleSpatialLayerFreeItemInfo was 
called.
[Wed Jul 22 08:14:30 2015].225328 msOracleSpatialLayerFreeItemInfo was 
called.
[Wed Jul 22 08:14:30 2015].225334 msOracleSpatialLayerGetItems was called.
[Wed Jul 22 08:14:30 2015].225868 msOracleSpatialLayerInitItemInfo was 
called.
[Wed Jul 22 08:14:30 2015].225892 msOracleSpatialLayerInitItemInfo was 
called.
[Wed Jul 22 08:14:30 2015].226421 msOracleSpatialLayerTranslateFilter. 
String: ("[NOM_STAT]" =*"Anto")
[Wed Jul 22 08:14:30 2015].226429 msOracleSpatialLayerTranslateFilter. 
There are tokens to process
[Wed Jul 22 08:14:30 2015].226435 got a IEQ comparison
[Wed Jul 22 08:14:30 2015].226440 closing RE comparison
[Wed Jul 22 08:14:30 2015].226444 msOracleSpatialLayerWhichShapes was 
called.
[Wed Jul 22 08:14:30 2015].226470 msOracleSpatialLayerWhichShapes. Us 

Michael Smith <michael.smith.erdc at gmail.com> 
2015-07-21 11:24 

A
<Steve.Toutant at inspq.qc.ca>, <mapserver-users at lists.osgeo.org> 
cc

Objet
Re: [mapserver-users] ogc:PropertyIsEqualTo behaves like PropertyIsLike 
with oracle

Steve,

When a case insensitive exact match is used,  the code should use a 
regexp_like constructor in Oracle with the case insensitive flag and an 
exact pattern.

Can you do DEBUG 3 and show the log?

Mike

-- 
Michael Smith
Remote Sensing/GIS Center
US Army Corps of Engineers

From: <mapserver-users-bounces at lists.osgeo.org> on behalf of <
Steve.Toutant at inspq.qc.ca>
Date: Tuesday, July 21, 2015 at 10:07 AM
To: <mapserver-users at lists.osgeo.org>
Subject: [mapserver-users] ogc:PropertyIsEqualTo behaves like 
PropertyIsLike with oracle

Hi,
Using MS7+oracle, I apply a wfs filter PropertyIsEqualTo on a layer 
attribute (string) with a truncated string, so it should returns 0 
features, but it returns 1 feature.
The complete value is "Antonin". I apply the filter using "Anto"

MS7+Postgis
<wfs:Query typeName="feature:SMDVDP_STAT_METE" 
srsName="EPSG:3857"><ogc:Filter xmlns:ogc="http://www.opengis.net/ogc
"><ogc:PropertyIsEqualTo 
matchCase="false"><ogc:PropertyName>NOM_STAT</ogc:PropertyName><ogc:Literal>
Anto</ogc:Literal></ogc:PropertyIsEqualTo></ogc:Filter></wfs:Query>
numberOfFeatures="0" , that is ok.
Ms log: FLTLayerApplyPlainFilterToLayer():  ("[NOM_STAT]" =*"Anto")

Ms7+Oracle
<wfs:Query typeName="feature:SMDVDP_STAT_METE" 
srsName="EPSG:32198"><ogc:Filter xmlns:ogc="http://www.opengis.net/ogc
"><ogc:PropertyIsEqualTo 
matchCase="false"><ogc:PropertyName>NOM_STAT</ogc:PropertyName><ogc:Literal>
Anto</ogc:Literal></ogc:PropertyIsEqualTo></ogc:Filter></wfs:Query>
numberOfFeatures="1" , BAD
Mslog: msOracleSpatialLayerTranslateFilter. String: ("[NOM_STAT]" 
=*"Anto") 

Is the problem MS o oracle? Should I open a Ms bug?
Regards! 
_______________________________________________ mapserver-users mailing 
list mapserver-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

_______________________________________________
mapserver-users mailing list
mapserver-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20150722/25e91e61/attachment.htm>