[mapserver-users] [EXTERNAL] Re: Mapserver 7+oracle - wfs getFeature with filter on numeric field
Smith, Michael ERDC-RDE-CRREL-NH
Michael.Smith at erdc.dren.mil
Tue May 19 09:24:47 PDT 2015
It looks like whats happening is that’s being converted into 3 Tokens and the oracle driver converts it into a number + comma + number.
The tokenizer is not treating that value as a single value.
Mike
From: "Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>" <Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>>
Date: Tuesday, May 19, 2015 at 11:44 AM
To: Michael Smith <michael.smith at erdc.dren.mil<mailto:michael.smith at erdc.dren.mil>>
Cc: "Rahkonen Jukka (MML)" <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>, MapserverList OSGEO <mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>>
Subject: Re: [mapserver-users] [EXTERNAL] Re: Mapserver 7+oracle - wfs getFeature with filter on numeric field
here is the mapserver log with <ogc:PropertyIsEqualTo matchCase="true"><ogc:PropertyName>LONG_STAT</ogc:PropertyName><ogc:Literal>-61,8031</ogc:Literal></ogc:PropertyIsEqualTo>
msOracleSpatialLayerWhichShapes. Using this Sql to retrieve the data: SELECT ID, NO_STAT_CLIM, NO_STAT_FADQ, NOM_STAT, LATI_STAT, LONG_STAT, ALTI_STAT, D_DEB, D_FIN, TIMB_MAJ, USAG_MAJ, rownum, GEOM_POIN FROM (select * FROM SMDVDP_STAT_METE WHERE EXTRACT(YEAR FROM D_DEB) = 2014) WHERE ( LONG_STAT = -61.000000,8031.000000 ) AND SDO_FILTER( GEOM_POIN, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') = 'TRUE'
The problem is
WHERE ( LONG_STAT = -61.000000,8031.000000 )
"Smith, Michael ERDC-RDE-CRREL-NH" <Michael.Smith at erdc.dren.mil<mailto:Michael.Smith at erdc.dren.mil>>
2015-05-19 09:42
A
"Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>" <Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>>, "Rahkonen Jukka (MML)" <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>
cc
MapserverList OSGEO <mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>>
Objet
Re: [mapserver-users] [EXTERNAL] Re: Mapserver 7+oracle - wfs getFeature with filter on numeric field
Steve,
Rerun this with DEBUG 3 on the LAYER and the generated SQL will show up in your error logs. Then it should show why the generated sql is not valid.
Mike
--
Michael Smith
Remote Sensing/GIS Center
US Army Corps of Engineers
From: "Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>" <Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>>
Date: Tuesday, May 19, 2015 at 9:37 AM
To: "Rahkonen Jukka (MML)" <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>
Cc: MapserverList OSGEO <mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>>, Michael Smith <michael.smith at erdc.dren.mil<mailto:michael.smith at erdc.dren.mil>>
Subject: Re: [mapserver-users] [EXTERNAL] Re: Mapserver 7+oracle - wfs getFeature with filter on numeric field
Thank you all for this discussion.
We had different problems in our use case coming from gdal, mapserv, oracle, ......
I don't know how gdal should manage this comma issue, but I would like to clarify our problem with mapserv and see if something must be done.
Resume
- We use MS7.1 with oraclespatial connection
- gml_type = auto, which returns <element name="LONG_STAT" minOccurs="0" type="double"/>
- matchCase =true
- LONG_STAT = -61,8031
The problem is
<ogc:PropertyIsEqualTo matchCase="true"><ogc:PropertyName>LONG_STAT</ogc:PropertyName><ogc:Literal>-61,8031</ogc:Literal></ogc:PropertyIsEqualTo>, crash with this error
<ows:Exception exceptionCode="NoApplicableCode" locator="mapserv">
<ows:ExceptionText>msWFSGetFeature(): WFS server error. FLTApplyFilterToLayer() failed
msOracleSpatialLayerWhichShapes(): OracleSpatial error. Check your data statement and server logs</ows:ExceptionText>
</ows:Exception>
But, using LIKE it Works ...Probably because it "convert" the value as a string
<ogc:PropertyIsLike matchCase="true" wildCard="*" singleChar="." escapeChar="!"><ogc:PropertyName>LONG_STAT</ogc:PropertyName><ogc:Literal>*-61,8031*</ogc:Literal></ogc:PropertyIsLike>
But why ogc:PropertyIsEqualTo is crashing?
"Rahkonen Jukka (MML)" <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>
2015-05-15 10:05
A
"Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>" <Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>>, "Smith, Michael ERDC-RDE-CRREL-NH" <Michael.Smith at erdc.dren.mil<mailto:Michael.Smith at erdc.dren.mil>>
cc
MapserverList OSGEO <mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>>
Objet
Re: [mapserver-users] [EXTERNAL] Re: Mapserver 7+oracle - wfs getFeature with filter on numeric field
Hi,
I think that the real trouble is that with certain database and client settings the numerical data that is stored into Oracle is not interpreted correctly and correct place to fix the issue is somewhere very close to Oracle. Not in making GML parser to accept commas or to make WFS string comparisons to work correctly with numerical data in Oracle.
GDAL had similar troubles for sure with Finnish, Italian and French locales and probably with all the other locales using comma as decimal separator as well. In practice it meant for me that GDAL truncated decimal numbers “1234,56” at comma “1234”. For many years I used some workarounds but after this GDAL fix it has not been necessary any more:
http://trac.osgeo.org/gdal/ticket/5709
I suggest to study the patch “'OCI: force NLS_NUMERIC_CHARACTERS to ". " (patch by giorgiomugnaini, #5709)'” and consider if it could be applied to the native Oracle driver in Mapserver. I guess and hope that Mapserver would recognize the datatype correctly if the Oracle layer is configured to read Oracle through OGR and GDAL 2.0 is in use.
Perhaps setting environment "ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. '" locally before starting Mapserver would be enough but I do not know how and where to set it.
-Jukka Rahkonen-
Steve.Toutant wrote:
Is there a documentation that says that it is not?
Folks from gdal list also think that it is not supported...I guess you are right. But as I said on the gdal list, many countries are using a comma as a decimal separator....what other people on the planet do?
Anyway the comma is another problem that I try to manage later......
For now, We have an application that generate and launch spatial queries on WFS, matchCase=false by default. Before modifying the code I want to understand what is going on....
It seems,when using numeric value, that matchCase as no impact in PostGIS, but it is important in oracle to be set to true
gml_type = auto, returns double in postgis and oracle, so that is fine
A WFS getFeature with the operator PropertyIsEqualTo AND matchCase=false
using this value -64.225
PostGis: it works
Oracle (from mike's test): it fails...matchCase must be set to true
A WFS getFeature with the operator PropertyIsEqualTo AND matchCase=false
using this value -64,225
PotsGis: not tested
Oracle: it fails...matchCase must be set to true
Is there a reason why, with oracle, matchCase should be set to true when using numeric value, or that could be fixed?
Regards
Steve
"Smith, Michael ERDC-RDE-CRREL-NH" <Michael.Smith at erdc.dren.mil<mailto:Michael.Smith at erdc.dren.mil>>
2015-05-14 09:17
A
"Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>" <Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>>
cc
MapserverList OSGEO <mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>>, thomas bonfort <thomas.bonfort at gmail.com<mailto:thomas.bonfort at gmail.com>>
Objet
Re: [EXTERNAL] Re: [mapserver-users] Mapserver 7+oracle - wfs getFeature with filter on numeric field
Its not clear to me that commas as the decimal separator is supported in XML/GML. I can't find any documentation to indicate that it is. Can you point some out to me?
In any event, I didn't test with comma decimal separators.
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>" <Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>>
Date: Thursday, May 14, 2015 at 8:54 AM
To: Michael Smith <michael.smith at erdc.dren.mil<mailto:michael.smith at erdc.dren.mil>>
Cc: MapserverList OSGEO <mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>>, thomas bonfort <thomas.bonfort at gmail.com<mailto:thomas.bonfort at gmail.com>>
Subject: Re: [EXTERNAL] Re: [mapserver-users] Mapserver 7+oracle - wfs getFeature with filter on numeric field
Thanks Mike for having a look at it.
Your decimals are separated by a comma or a dot?
"Smith, Michael ERDC-RDE-CRREL-NH" <Michael.Smith at erdc.dren.mil<mailto:Michael.Smith at erdc.dren.mil>>
2015-05-13 18:53
A
"Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>" <Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>>, thomas bonfort <thomas.bonfort at gmail.com<mailto:thomas.bonfort at gmail.com>>
cc
MapserverList OSGEO <mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>>
Objet
Re: [EXTERNAL] Re: [mapserver-users] Mapserver 7+oracle - wfs getFeature with filter on numeric field
Steve,
I tested this out with 7.1 with numeric type fields and it works for me as long as I don't use matchCase="false". That seems to kick it in to a string comparison. matchCase="true" and just PropertyIsEqualTo work fine.
In our metadata we use "gml_types" "auto" when the database knows the types and explicit declarations (eg "gml_attributename_type" "integer") when it doesn't work.
The key is that the field type has to report as a numeric type or the backend treats it as a string comparison.
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>" <Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>>
Date: Wednesday, May 13, 2015 at 10:35 AM
To: thomas bonfort <thomas.bonfort at gmail.com<mailto:thomas.bonfort at gmail.com>>
Cc: MapserverList OSGEO <mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>>
Subject: [EXTERNAL] Re: [mapserver-users] Mapserver 7+oracle - wfs getFeature with filter on numeric field
Resent-From: Michael Smith <michael.smith at usace.army.mil<mailto:michael.smith at usace.army.mil>>
Precision: we are using MS7.1
Removing matchcase part makes oracle, and postgis, crash.
I have this error:
<ows:ExceptionReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance<http://www.google.com/url?q=http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema-instance&sa=D&sntz=1&usg=AFQjCNFERp6A_kcvqihMCKJ7EHX8O14vIA>" xmlns:ows="http://www.opengis.net/ows<http://www.google.com/url?q=http%3A%2F%2Fwww.opengis.net%2Fows&sa=D&sntz=1&usg=AFQjCNE9_q7eSkcBl4XJimXTw1Zx0EiDow>" version="1.1.0" language="en-US"xsi:schemaLocation="http://www.opengis.net/ows< /a><http://www.google.com/url?q=http%3A%2F%2Fwww.opengis.net%2Fows&sa=D&sntz=1&usg=AFQjCNE9_q7eSkcBl4XJimXTw1Zx0EiDow>http://schemas.opengis.net/ows/1.0.0/owsExceptionReport.xsd<http://www.google.com/url?q=http%3A%2F%2Fschemas.opengis.net%2Fows%2F1.0.0%2FowsExceptionReport.xsd&sa=D&sntz=1&usg=AFQjCNGmnK6iSrh-cSWEtM2VV17uuSbVNA>">
<ows:Exception exceptionCode="NoApplicableCode" locator="mapserv">
<ows:ExceptionText>
msWFSGetFeature(): WFS server error. FLTApplyFilterToLayer() failed msOracleSpatialLayerWhichShapes(): OracleSpatial error. Check your data statement and server logs
</ows:ExceptionText>
The problem might be the decimals?
Here is the request
<wfs:Query typeName="feature:SMDVDP_STAT_METE" srsName="EPSG:32198">
<ogc:Filter xmlns:ogc="http://www.opengis.net/ogc">
<ogc:PropertyIsEqualTo>
<ogc:PropertyName>LONG_STAT</ogc:PropertyName>
<ogc:Literal>-61,8031</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter>
</wfs:Query>
</wfs:GetFeature>
thomas bonfort <thomas.bonfort at gmail.com<mailto:thomas.bonfort at gmail.com>>
2015-05-13 05:40
A
Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>
cc
MapserverList OSGEO <mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>>
Objet
Re: [mapserver-users] Mapserver 7+oracle - wfs getFeature with filter on numeric field
can you try the same request without the "matchCase" part and report
back please?
On 12 May 2015 at 18:11, <Steve.Toutant at inspq.qc.ca<mailto:Steve.Toutant at inspq.qc.ca>> wrote:
> I have a problem using a FILTER in a wfs getFeature request on an oracle DB
> with MS7
> When I launch a getFeature request with a Filter on a numeric field it
> doesn't work,
> <ogc:Filter xmlns:ogc="http://www.opengis.net/ogc"><ogc:PropertyIsEqualTo
> matchCase="false"><ogc:PropertyName>LONG_STAT</ogc:PropertyName><ogc:Literal>-61</ogc:Literal></ogc:PropertyIsEqualTo></ogc:Filter>
> here is the error we get in the browser
> <ows:Exception exceptionCode="NoApplicableCode" locator="mapserv">
> <ows:ExceptionText>msWFSGetFeature(): WFS server error.
> FLTApplyFilterToLayer() failed
> msOracleSpatialLayerWhichShapes(): OracleSpatial error. Check your data
> statement and server logs</ows:ExceptionText>
>
> from mapserver logs
> [Tue May 12 11:59:10 2015].177992 msOracleSpatialLayerTranslateFilter.
> String: ([LONG_STAT] =*-61)
>
> LONG_STAT is a NUMBER in the DB
> DescribefeatureType returns
> <element name="NO_STAT_CLIM" minOccurs="0" type="string"/>
> <element name="NO_STAT_FADQ" minOccurs="0" type="string"/>
> <element name="NOM_STAT" minOccurs="0" type="string"/>
> <element name="LATI_STAT" minOccurs="0" type="string"/>
> <element name="LONG_STAT" minOccurs="0" type="string"/>
> <element name="ALTI_STAT" minOccurs="0" type="string"/>
>
> I tried to add "gml_types" "auto" in the mapfile, but it still returns
> string as data type
> I tried "gml_LONG_STAT_type" = "Integer" in the mapfile, describeFeatureType
> returns "<element name="LONG_STAT" minOccurs="0" type="double"/> " but I
> still get an error in getFeature
>
> I'm using CONNECTIONTYPE oraclespatial in the mapfile
>
> I can provide more information....
>
> Is it a known issue or can I do something about that?
>
> Thank you!
> _______________________________________________
> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20150519/68071a2a/attachment-0001.html>
More information about the mapserver-users
mailing list