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

Wed Jul 22 06:55:38 PDT 2015

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.org2015-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 <mailto:Steve.Toutant at inspq.qc.ca> >
> Date: Wednesday, July 22, 2015 at 9:34 AM
> To: Michael Smith <michael.smith.erdc at gmail.com
> <mailto:michael.smith.erdc at gmail.com> >
> Cc: <mapserver-users at lists.osgeo.org <mailto: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
> <mailto:michael.smith.erdc at gmail.com> >2015-07-22 09:32
> A
> <Steve.Toutant at inspq.qc.ca <mailto:Steve.Toutant at inspq.qc.ca> >
> cc
> <mapserver-users at lists.osgeo.org <mailto: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&Filte
> r=<Filter><PropertyIsEqualto%20MatchCase="FALSE"><PropertyName>FILE_NAME</Prop
> ertyName><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 <mailto:michael.smith at usace.army.mil>
> 
> 
> From: <Steve.Toutant at inspq.qc.ca <mailto:Steve.Toutant at inspq.qc.ca> >
> Date: Wednesday, July 22, 2015 at 9:13 AM
> To: Michael Smith <michael.smith.erdc at gmail.com
> <mailto:michael.smith.erdc at gmail.com> >
> Cc: <mapserver-users at lists.osgeo.org <mailto: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.q
> c,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.q
> c) -> 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.q
> c,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.q
> c) -> 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.q
> c,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.q
> c) -> 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
> <mailto:michael.smith.erdc at gmail.com> >2015-07-21 11:24
> A
> <Steve.Toutant at inspq.qc.ca <mailto:Steve.Toutant at inspq.qc.ca> >,
> <mapserver-users at lists.osgeo.org <mailto: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
> <mailto:mapserver-users-bounces at lists.osgeo.org> > on behalf of
> <Steve.Toutant at inspq.qc.ca <mailto:Steve.Toutant at inspq.qc.ca> >
> Date: Tuesday, July 21, 2015 at 10:07 AM
> To: <mapserver-users at lists.osgeo.org <mailto: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 <http://www.opengis.net/ogc>
> "><ogc:PropertyIsEqualTo
> matchCase="false"><ogc:PropertyName>NOM_STAT</ogc:PropertyName><ogc:Literal>An
> to</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
> <http://www.opengis.net/ogc> "><ogc:PropertyIsEqualTo
> matchCase="false"><ogc:PropertyName>NOM_STAT</ogc:PropertyName><ogc:Literal>An
> to</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 <mailto:mapserver-users at lists.osgeo.org>
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
> <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
> <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/fc9cb599/attachment.htm>