[mapserver-dev] [EXTERNAL] Problem with filter in Oracle
Michael Smith
michael.smith.erdc at gmail.com
Sat Jan 30 10:39:13 PST 2016
Track with
https://github.com/mapserver/mapserver/issues/5229
Mike
----
Michael Smith
US Army Corps
Remote Sensing GIS/Center
michael.smith at usace.army.mil
From: Michael Smith <michael.smith.erdc at gmail.com>
Date: Friday, January 29, 2016 at 3:27 PM
To: Björn Danielsson <bjorn.danielsson at falubo.se>, Michael Smith
<michael.smith.erdc at gmail.com>
Cc: "mapserver-dev at lists.osgeo.org" <mapserver-dev at lists.osgeo.org>
Subject: Re: [mapserver-dev] [EXTERNAL] Problem with filter in Oracle
> Yeah, the token processing is going wrong there.
>
> Can you create a mapserver ticket with a layer snippet to create the problem
> and assign it to me (or let me know which number it is) and I'll work on
> fixing it.
>
> Mike
> ----
> Michael Smith
> US Army Corps
> Remote Sensing GIS/Center
> michael.smith at usace.army.mil
>
>
> From: mapserver-dev <mapserver-dev-bounces at lists.osgeo.org> on behalf of
> Björn Danielsson <bjorn.danielsson at falubo.se>
> Date: Friday, January 29, 2016 at 3:22 AM
> To: Michael Smith <michael.smith.erdc at gmail.com>
> Cc: "mapserver-dev at lists.osgeo.org" <mapserver-dev at lists.osgeo.org>
> Subject: Re: [mapserver-dev] [EXTERNAL] Problem with filter in Oracle
> Resent-From: Michael Smith <michael.smith at usace.army.mil>
>
>> Hi again,
>>
>> I have encountered another difference between postgres and oracle where
>> postgres works ok but oracle does not, and may have an angle towards filter.
>>
>> As I mentioned earlier I have two identical databases, one in postgres and
>> the other in oracle.
>>
>> It concerns the function queryByAttributes. The documentation of
>> queryByAttributes is a bit confusing and contradicting so I have more or
>> less used trial and error to get it working. I started with postgres
>> and finally got it working. But what worked in postgres caused error in
>> oracle.
>>
>> Mapfile:
>> LAYER
>> NAME "AVD"
>> GROUP "AVD"
>> CONNECTIONTYPE postgis
>> CONNECTION "host=localhost port=5432 dbname=fwt_prod user=c2
>> password=C2"
>> STATUS ON
>> TYPE POLYGON
>> MINSCALEDENOM 1500
>> MAXSCALEDENOM 500000
>> LABELMAXSCALEDENOM 21000
>> POSTLABELCACHE FALSE
>> PROCESSING "LABEL_NO_CLIP=ON"
>> LABELCACHE ON
>> TEMPLATE "void"
>> # DEBUG 5
>> PROJECTION
>> "init=epsg:3006"
>> END
>>
>> php-code for postgres:
>>
>> $oLayer= $oMap->getLayerByName("AVD");
>> if ($bDebug) {
>> fwrite($f,"Layername: ".$oLayer->name."\r\n");
>> }
>> $data = "kartobj FROM ".$dbowner.".AVD*AVD USING SRID=".$srid." USING UNIQUE
>> avd";
>> $data = str_replace("*","$",$data);
>> $ab = $oLayer->set("data", $data);
>> $filter = "([orgid]='".$orgid."' and [fv]='".$fv."' and [bev]='".$bev."' and
>> [skifte]='".$skifte."')";
>> $aa = $oLayer->setFilter($filter);
>>
>> $ac = $oLayer->setConnectionType(MS_POSTGIS);
>> $oLayer->set("connection", $conn);
>> $res = @$oLayer->queryByAttributes("avd", "'".$avd."'", MS_SINGLE);
>> if ($bDebug) {
>> fwrite($f,"avd-res: ".$res."\r\n");
>> }
>> $oLayer->open();
>>
>> From debug ( at the end of msPostGISLayerWhichShapes query ):
>>
>> 3922))',0) and ("avd"::text = '250' and "orgid" = 'BSD' and "fv" = 'FV1' and
>> "bev" = '1' and "skifte" = '3235')
>>
>> Notice "avd"::text='250'.
>>
>> php_code for oracle:
>>
>> $oLayer= $oMap->getLayerByName("AVD");
>> if ($bDebug) {
>> fwrite($f,"Layername: ".$oLayer->name."\r\n");
>> }
>>
>> $data = "KARTOBJ_SDO FROM ".$dbowner.".AVD*AVD";
>> $data = str_replace("*","$",$data);
>> $ab = $oLayer->set("data", $data);
>> $filter = "([ORGID]='".$orgid."' AND [FV]='".$fv."' AND [BEV]='".$bev."' AND
>> [SKIFTE]='".$skifte."')";
>> $aa = $oLayer->setFilter($filter);
>>
>> $ac = $oLayer->setConnectionType(MS_ORACLESPATIAL);
>> $oLayer->set("connection", $conn);
>> $res = $oLayer->queryByAttributes("AVD", "'".$avd."'", MS_SINGLE);
>> if ($bDebug) {
>> fwrite($f,"avd-res: ".$res."\r\n");
>> }
>> $oLayer->open();
>>
>> From debug ( same query as above ):
>>
>> wnum, KARTOBJ_SDO FROM c2.AVD$AVD WHERE AVD = AVD = '250' AND ORGID = 'BSD'
>> AND FV = 'FV1' AND BEV = '1' AND SKIFTE = '3235'
>>
>> Notice WHERE AVD = AVD = '250' which of cause gives ora error 933.
>>
>> Postgres and oracle obviously either treat the in-arguments to
>> queryByAttributes differently or there is a bug somewhere. I would very much
>> like to get the thing going in oracle so if there is anything I can do to
>> test further I will be glad to help.
>>
>> That goes with the previous filter problems too.
>>
>> Björn D
>>> -----Original Message-----
>>> From: Michael Smith <michael.smith.erdc at gmail.com>
>>> To: Björn Danielsson <bjorn.danielsson at falubo.se>
>>> Cc: <mapserver-dev at lists.osgeo.org>
>>> Date: Mon, 18 Jan 2016 07:43:31 -0500
>>> Subject: Re: [EXTERNAL] [mapserver-dev] Problem with filter in Oracle
>>>
>>> No, there is no check on the number of records. In both queries, the spatial
>>> filter is passed but in the second one, there is no other filter.
>>>
>>> If both queries are returning rows in sqlplus, then I think you need to look
>>> at the rendering side. Perhaps you have something that is preventing the
>>> image from drawing if ID is set?
>>>
>>> You can also increase the DEBUG value to 5 and see the rows produced by each
>>> query but if both are producing rows in sqlplus, they should in mapserver.
>>>
>>> And the :ordinates bind value does include the sdo_ordinate_array type being
>>> passed.
>>>
>>> First query:
>>>
>>> SELECT ID, rownum, KARTOBJ_SDO FROM c2.KARTA$TMP2 WHERE ID =
>>> '1eokfraqi19niup7402g3iflp7' AND SDO_FILTER( KARTOBJ_SDO,
>>> MDSYS.SDO_GEOMETRY(2003, :srid,
>>> NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates),'querytype=window') =
>>> 'TRUE'
>>>
>>> Second query:
>>> SELECT rownum, KARTOBJ_SDO FROM c2.KARTA$TMP2 WHERE SDO_FILTER( KARTOBJ_SDO,
>>> MDSYS.SDO_GEOMETRY(2003, :srid,
>>> NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates),'querytype=window') =
>>> 'TRUE'
>>>
>>> Mike
>>>
>>>
>>>
>>> From: Björn Danielsson < bjorn.danielsson at falubo.se
>>> <mailto:bjorn.danielsson at falubo.se> >
>>> Date: Monday, January 18, 2016 at 3:33 AM
>>> To: Michael Smith <michael.smith.erdc at gmail.com >
>>> Cc: <mapserver-dev at lists.osgeo.org >
>>> Subject: Re: [EXTERNAL] [mapserver-dev] Problem with filter in Oracle
>>>
>>>> Hi Michael,
>>>> Sorry, I did not notice that I only replied to your private address.
>>>>
>>>> Anyway, I followed your advice and set DEBUG 3 in layer 'X', and made two
>>>> runs: one with filter set and one with filter not set. The debug output for
>>>> both runs is in the included ms_error.txt-file.
>>>>
>>>> The one with filter set produce an empty image. The one with filter not set
>>>> works fine.
>>>>
>>>> I took the statement from 'Using this Sql to retrieve data', replaced :srid
>>>> with -1 and :ordinates with
>>>> SDO_ORDINATE_ARRAY(393339.5,6773950.0,394169.5,6774780.0) and ran the
>>>> statement in sqlplus.
>>>> Both examples worked.
>>>> So I am a bit at loss. The only thing I can see in the debug file is that
>>>> :ordinates is not declared in 'Bind values' but the rectangle's extents
>>>> are. But I don't know what goes on behind the scene so that might be
>>>> correct. It feels strange though that this would affect the filter. Or can
>>>> it be that oracle spatial detects that the table ( when filter not set )
>>>> contains only one record and therefore skip the sdo-filtering altogether?
>>>>
>>>> Björn D
>>>>
>>>>> -----Original Message-----
>>>>> From: Michael Smith < michael.smith.erdc at gmail.com
>>>>> <mailto:michael.smith.erdc at gmail.com> >
>>>>> To: Björn Danielsson < bjorn.danielsson at falubo.se
>>>>> <mailto:bjorn.danielsson at falubo.se> >, < mapserver-users at lists.osgeo.org
>>>>> <mailto:mapserver-users at lists.osgeo.org> >
>>>>> Date: Fri, 15 Jan 2016 07:04:55 -0500
>>>>> Subject: Re: [EXTERNAL] [mapserver-dev] Problem with filter in Oracle
>>>>>
>>>>> Always reply back to the list, others can assist also.
>>>>>
>>>>> You need to set the DEBUG 3 at the layer level in your mapfile and then
>>>>> the actual queries sent to Oracle will be displayed in the log file (as
>>>>> well as any spatial bind values). Then you can run those queries outside
>>>>> of mapserver and verify whether you are getting the rows of data you
>>>>> expect or not.
>>>>>
>>>>> Mike
>>>>>
>>>>> ----
>>>>> Michael Smith
>>>>> US Army Corps
>>>>> Remote Sensing GIS/Center
>>>>> michael.smith at usace.army.mil
>>>>>
>>>>>
>>>>> From: Björn Danielsson < bjorn.danielsson at falubo.se
>>>>> <mailto:bjorn.danielsson at falubo.se> >
>>>>> Date: Friday, January 15, 2016 at 2:40 AM
>>>>> To: Michael Smith <michael.smith.erdc at gmail.com >
>>>>> Subject: Re: [EXTERNAL] [mapserver-dev] Problem with filter in Oracle
>>>>>
>>>>>> Hi Michael and thanks for your quick response.
>>>>>>
>>>>>> I did 3 runs with debuglevel 3 and the outcome from the debugger does not
>>>>>> say me much.
>>>>>>
>>>>>> DEBUG 3
>>>>>> CONFIG "MS_ERRORFILE" "/ms4w/tmp/ms_error.txt"
>>>>>>
>>>>>> I also activated the 'debugger' in my php scripts that writes a lot,
>>>>>> among other things the database calls, to a file.
>>>>>>
>>>>>> The three runs and the resulting debug files are called A,B and C. From
>>>>>> mapserver debug 'ms_error_A.txt', and from php debug 'php_debug_A.log'.
>>>>>> Look for 'filter = ' in the php debug files.
>>>>>>
>>>>>> A, B and C refer to the examples in my original mail.
>>>>>>
>>>>>> Run A shows the example:
>>>>>> $filter = "([ID]='".$sessid."')";
>>>>>> with the layers 'AVD' and 'X' where 'AVD' works fine and 'X' does not.
>>>>>>
>>>>>> Run B and C is from the same php script with different contents in
>>>>>> filter.
>>>>>> Look for layers 'AVD' and 'AVD-L'. Actually the same table and column.
>>>>>> Layer 'AVD' shows polygons and layer 'AVD-L' the polygons' outlines.
>>>>>> Run C works and run B does not.
>>>>>> The column SKIFTE in run B is surrounded by parenthesis because it may be
>>>>>> like (SKIFTE='A' OR SKIFTE='C' OR ...) because mapserver obviously no
>>>>>> longer supports the 'in'-statement. I have tried without parenthesis but
>>>>>> that did not change anything.
>>>>>>
>>>>>> I can't see any pattern in this.
>>>>>>
>>>>>> Björn Danielsson
>>>>>>> -----Original Message-----
>>>>>>> From: Michael Smith < michael.smith.erdc at gmail.com
>>>>>>> <mailto:michael.smith.erdc at gmail.com> >
>>>>>>> To: Björn Danielsson < bjorn.danielsson at falubo.se
>>>>>>> <mailto:bjorn.danielsson at falubo.se> >, "mapserver-dev at lists.osgeo.org "
>>>>>>> < mapserver-dev at lists.osgeo.org <mailto:mapserver-dev at lists.osgeo.org> >
>>>>>>> Date: Thu, 14 Jan 2016 09:35:31 -0500
>>>>>>> Subject: Re: [EXTERNAL] [mapserver-dev] Problem with filter in Oracle
>>>>>>>
>>>>>>> Björn,
>>>>>>>
>>>>>>> Can you turn on DEBUG level 3 and show what queries are being generated?
>>>>>>>
>>>>>>> That will help isolate whats going on with the queries to the backend.
>>>>>>>
>>>>>>> Mike
>>>>>>>
>>>>>>> --
>>>>>>> Michael Smith
>>>>>>> Remote Sensing/GIS Center
>>>>>>> US Army Corps of Engineers
>>>>>>> From: mapserver-dev < mapserver-dev-bounces at lists.osgeo.org
>>>>>>> <mailto:mapserver-dev-bounces at lists.osgeo.org> > on behalf of Björn
>>>>>>> Danielsson < bjorn.danielsson at falubo.se
>>>>>>> <mailto:bjorn.danielsson at falubo.se> >
>>>>>>> Date: Thursday, January 14, 2016 at 4:30 AM
>>>>>>> To: "mapserver-dev at lists.osgeo.org " < mapserver-dev at lists.osgeo.org
>>>>>>> <mailto:mapserver-dev at lists.osgeo.org> >
>>>>>>> Subject: [EXTERNAL] [mapserver-dev] Problem with filter in Oracle
>>>>>>> Resent-From: Michael Smith <michael.smith at usace.army.mil >
>>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> I have been using ms4w for quite some time and have discovered problems
>>>>>>> with Oracle when using php_mapscript. I posted this problem at ms4w's
>>>>>>> support and was adviced to turn to you.
>>>>>>>
>>>>>>> I am supporting a gis-system on the web that is heavily database
>>>>>>> dependent, and can run on either postgres or oracle according to the
>>>>>>> costumer's request. So there are two sets of php-scripts that are
>>>>>>> identical apart from the database calls.
>>>>>>>
>>>>>>> Thera are no problems with postgres.
>>>>>>>
>>>>>>> With Oracle there is a funny problem with filter.
>>>>>>> Look at this:
>>>>>>>
>>>>>>> foreach($aLayersIdx as $layerIndex) {
>>>>>>> $oLayerK = $oMapK->getLayer($layerIndex);
>>>>>>> if ($bDebug) {
>>>>>>> fwrite($f,"layer: ".$oLayerK->name."\r\n");
>>>>>>> }
>>>>>>> if ($oLayerK->name == "AVD" && $avd == "") {
>>>>>>> $oLayerK->set("status",MS_OFF);
>>>>>>> continue;
>>>>>>> }
>>>>>>> $filter = "";
>>>>>>> switch ($oLayerK->name) {
>>>>>>> case "AVD" :
>>>>>>> $filter = "([ORGID]='".$orgid."' AND [FV]='".$fv."' AND
>>>>>>> [BEV]='".$bev."' AND [SKIFTE]='".$skifte."' AND [AVD]='".$avd."')";
>>>>>>> $data = "KARTOBJ_SDO FROM ".$dbowner.".AVD*AVD";
>>>>>>> break;
>>>>>>>
>>>>>>> case "X" :
>>>>>>> $filter = "([ID]='".$sessid."')";
>>>>>>> $data = "KARTOBJ_SDO FROM ".$dbowner.".KARTA*TMP2";
>>>>>>> break;
>>>>>>> }
>>>>>>>
>>>>>>> $data = str_replace("*","$",$data);
>>>>>>>
>>>>>>> $oLayerK->set("data", $data);
>>>>>>> $oLayerK->setFilter($filter);
>>>>>>> $oLayerK->setConnectionType(MS_ORACLESPATIAL);
>>>>>>> $oLayerK->set("connection", $conn);
>>>>>>> $oLayerK->set("status", MS_ON);
>>>>>>> if ($bDebug) {
>>>>>>> fwrite($f,"connection: ".$conn."\r\n");
>>>>>>> fwrite($f,"filter: ".$filter."\r\n");
>>>>>>> fwrite($f,"data: ".$data."\r\n");
>>>>>>> }
>>>>>>> }
>>>>>>>
>>>>>>> A mapfile with two layers, 'AVD' and 'X'.
>>>>>>> Problem is that filter for layer 'X' does not work. Or, rather, it works
>>>>>>> but no record is returned. The mapserver debugger reports no error.
>>>>>>> The filter for layer 'AVD' works fine.
>>>>>>>
>>>>>>> When the line $filter = "[ID]='".$sessid."')" is commented and the table
>>>>>>> KARTA$TMP2 contains only one record it works and a map layer is
>>>>>>> produced. So it seems that when filter is introduced in the select
>>>>>>> something goes wrong.
>>>>>>>
>>>>>>> I thought that ID might be a reserved word but after some testing I
>>>>>>> found that filter fails in calls to other tables as well.
>>>>>>> For example:
>>>>>>> This works
>>>>>>> $filter = "([ORGID]='".$orgid."' AND [FV]='".$fv."' AND
>>>>>>> [BEV]='".$bev."')";
>>>>>>> But this does not work
>>>>>>> $filter = "([ORGID]='".$orgid."' AND [FV]='".$fv."' AND
>>>>>>> [BEV]='".$bev."' AND [SKIFTE]='".$skifte."')";
>>>>>>>
>>>>>>> The problem is consistent in that way that where the problem occurs it
>>>>>>> always occurs, and where it not occurs it never occurs. It is not
>>>>>>> intermittent.
>>>>>>>
>>>>>>> The php script ( where the snippet came from above ) was developed under
>>>>>>> ms4w version 2.2.7 and at that time no problems occured. I have also
>>>>>>> used version 3.0.6 and 3.1.1 and both versions have problems. So
>>>>>>> somewhere between 2.2.7 and 3.0.6 something happened.
>>>>>>>
>>>>>>> I am at loss here.
>>>>>>>
>>>>>>> Björn Danielsson
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-dev/attachments/20160130/4c3db506/attachment-0001.html>
More information about the mapserver-dev
mailing list