[mapserver-dev] [EXTERNAL] Problem with filter in Oracle
Michael Smith
michael.smith.erdc at gmail.com
Fri Jan 29 12:27:19 PST 2016
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/20160129/8fd78f12/attachment-0001.html>
More information about the mapserver-dev
mailing list