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

Wed Jul 22 08:36:52 PDT 2015

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.orghttp://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/26c32a05/attachment.htm>