5.2 Cell Value-Based Conditional Queries

Using cell-based conditional queries, you can generate a new GeoRaster object based on a specified condition.

In addition to their use in space-based queries on rasters (such as with SDO_GEOR.getRasterSubset), cell value-based queries are particularly useful in analytics and application modeling. To perform cell value based conditional queries and generate a new GeoRaster object based on the specified condition, you can use the SDO_GEOR_RA.findCells procedure and specify an appropriate condition parameter.

The condition parameter must specify a valid booleanExpr value (explained in Raster Algebra Language). The procedure computes the booleanExpr against each cell in the source GeoRaster object. If the result is TRUE, the original cell values are kept in the output GeoRaster object; if the result is FALSE, the bgValues are used to fill cell values in the output GeoRaster object. This can also be considered as a masking operation.

Example 5-4 Conditional Query

Example 5-4 calls the SDO_GEOR_RA.findCells procedure to find all pixels where the value of the second band is greater than 200. Because the bgValues parameter is not specified, the value 0 is used as the background value to fill all pixels that make the condition false. The example assumes that the source GeoRaster object is an image with more than two bands.

DECLARE
  geor   SDO_GEORASTER;
  geor1  SDO_GEORASTER;
BEGIN
  SELECT georaster INTO geor FROM georaster_table WHERE georid = 1;
  INSERT into georaster_table values (5, sdo_geor.init('rdt_1', 5)) returning georaster into geor1;
  sdo_geor_ra.findcells(geor, '{ 1 }>200', 'blocking=optimalpadding, blocksize=(512,512,3)', geor1);
  UPDATE georaster_table set georaster = geor1 WHERE georid = 5;
  COMMIT;
END;
/
 
-- This pixel is set to (0,0,0) because the cell value of the
-- second band is 136, which is not greater than 200.
SELECT sdo_geor.getcellvalue(georaster,0,30,30,'') FROM georaster_table WHERE georid =1 OR georid=5 ORDER BY georid;
SDO_GEOR.GETCELLVALUE(GEORASTER,0,30,30,'')
--------------------------------------------------------------------------------
SDO_NUMBER_ARRAY(88, 136, 35)
SDO_NUMBER_ARRAY(0, 0, 0)
 
2 rows selected.
 
-- This pixel keeps the original values because the cell value
-- of the second band is greater than 200.
SELECT sdo_geor.getcellvalue(georaster,0,132,116,'') FROM georaster_table WHERE georid =1 OR georid=5 ORDER BY georid;
 
SDO_GEOR.GETCELLVALUE(GEORASTER,0,132,116,'')
--------------------------------------------------------------------------------
SDO_NUMBER_ARRAY(242, 225, 233)
SDO_NUMBER_ARRAY(242, 225, 233)
 
2 rows selected.
 
-- This pixel keeps the original values because the cell value
-- of the second band is greater than 200.
SELECT sdo_geor.getcellvalue(georaster,0,261,185,'') FROM georaster_table WHERE georid =1 OR georid=5 ORDER BY georid;
 
SDO_GEOR.GETCELLVALUE(GEORASTER,0,261,185,'')
--------------------------------------------------------------------------------
SDO_NUMBER_ARRAY(255, 214, 2)
SDO_NUMBER_ARRAY(255, 214, 2)

Example 5-5 Conditional Query with nodata Parameter

Example 5-5 is basically the same as Example 5-4, except that the nodata parameter value is set to 'TRUE', so that all NODATA pixels keep their original values from the input GeoRaster object in the output GeoRaster object.

DECLARE
  geor   SDO_GEORASTER;
  geor1  SDO_GEORASTER;
BEGIN
  SELECT georaster INTO geor FROM georaster_table WHERE georid = 1;
  INSERT into georaster_table values (5, sdo_geor.init('rdt_1', 5)) returning georaster into geor1;
  sdo_geor_ra.findcells(geor, '{ 1 }>200', null, geor1, null, 'TRUE');
  UPDATE georaster_table set georaster = geor1 WHERE georid = 5;
  COMMIT;
END;
/
 
SELECT sdo_geor.getcellvalue(georaster,0,30,30,'') FROM georaster_table WHERE georid =1;
SDO_GEOR.GETCELLVALUE(GEORASTER,0,30,30,'')
--------------------------------------------------------------------------------
SDO_NUMBER_ARRAY(88, 136, 35)
 
1 row selected.
 
-- This pixel keeps its original cell values because it is nodata, even though
-- the cell value of the second band is not greater than 200.
SELECT sdo_geor.getcellvalue(georaster,0,30,30,'') FROM georaster_table WHERE georid=5;
 
SDO_GEOR.GETCELLVALUE(GEORASTER,0,30,30,'')
--------------------------------------------------------------------------------
SDO_NUMBER_ARRAY(88, 136, 35)
 
1 row selected.
 

Example 5-6 Conditional Query with parallelParam

Example 5-6 finds all pixels that meet all of the following conditions:

• The cell value of the first band is between (100,200).

• The cell value of the second band is between [50,250].

• The cell value of the third band is greater than 100.

In addition, because parallelParam is specified as 'parallel=4', the procedure in Example 5-6 will run in parallel with four processes.

DECLARE 
  geor   SDO_GEORASTER;
  geor1  SDO_GEORASTER;
BEGIN
   SELECT georaster INTO geor FROM georaster_table WHERE georid = 2;
   INSERT into georaster_table values (10, sdo_geor.init('rdt_1', 10)) returning georaster into geor1;
   sdo_geor_ra.findcells(geor,'({1}>=50)&({1}<=250)&({0}>100)&({0}<200)&{2}>100) ',null,geor1,null,'false','parallel=4');
   UPDATE georaster_table SET georaster = geor1 WHERE georid = 10;
   COMMIT;
END;
/