[mapserver-dev] [EXTERNAL] Problem with filter in Oracle
Michael Smith
michael.smith.erdc at gmail.com
Wed Feb 24 00:41:14 PST 2016
Björn,
I've looked at this a bit at the Paris Code Sprint and its an issue with the
old style qitem/qvalue filtering. In MapScript now there is a queryByFilter
that is really more what you want, as the queryByAttributes only allows a
Item = Value type syntax and the way it works is that it sets the Item =
(Value + existing Filter), which is why you get the AVD = AVD = 250. The
queryByFilter properly allows you to query with more complex filters that
are handled fine with existing filters. The queryByAttributes doesn't. I'll
look into fixing this but the better solution that resolves this (and will
provide better performance) is to use queryByFilter.
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>
Date: Friday, January 29, 2016 at 9:22 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 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/20160224/1218ef77/attachment-0001.html>
More information about the mapserver-dev
mailing list