Currently Being Moderated
Thank you very much for the details. It's an interesting business application.
There are two optimal approaches, assuming you use PL/SQL. (on the side, how many cells on average you need to access in each query? guess your georaster object has only one layer)
1. If you call getCellValue on too many cells in serial, the speed might be slow. However, you can speed up it dramatically by leveraging parallel processing. getCellValue can be parallelized using parallel pipelined table function. On Exadata database machine, one experiment achieved 8951 cells per seconds on a higher cell cepth DEM layer. for your reference, see the slides 27 - 28 on this presentation (http://download.oracle.com/otndocs/products/spatial/pdf/osuc2012_presentations/osuc12_performance_geringer.pdf).
2. depending on the total number of cells you need to query, the best approach is direct access of the BLOB. First, you generate the query geometry (using sdo_buffer etc if necessary). Then call getRasterSubset using the geometry as the query window and apply "polygonClip". it returns a rectangular block of cells (dimensions described by outWindow) based on the MBR of the geometry but all cells outside of the query geometry boundary will be filled with the "bgValue" you specified. Finally you read the cells from the BLOB using dbms_lob read API. This is very fast because the output BLOB is typically a temporary blob (very fast) plus you can read it by chunks.
In either case, we can help you with detailed scripting/programming. Please let us know if you have any specific questions.
thanks
Jeffrey
PS, a pl/sql example for finding the max value in a query window. your script can be simpler because you have fixed cell depth and interleaving and no need to deal with pyramid levels.
-- gr: the georaster object
-- plevel: pyramid level
-- bno: band number
-- win: window of interest in the cell space
create or replace function getMaxCellValue
(gr sdo_georaster, plevel number, bno number, win sdo_number_array)
return number as
cdp varchar2(80);
flt number := 0;
cdl number;
parm varchar(200);
lb blob;
buf raw(32767);
r1 raw(1);
r2 raw(2);
r4 raw(4);
r8 raw(8);
amt0 integer;
amt integer;
off integer;
len integer;
maxv number := null;
val number;
begin
-- first figure out the celldepth from the metadata
cdp := gr.metadata.extract('/georasterMetadata/rasterInfo/cellDepth/text()',
'xmlns=http://xmlns.oracle.com/spatial/georaster').getStringVal();
if cdp = '32BIT_REAL' then
flt := 1;
end if;
cdl := sdo_geor.getCellDepth(gr);
if cdl < 8 then
-- if celldepth<8bit, get the cell values as 8bit integers
cdl := 8;
parm := 'celldepth=8bit_u';
end if;
parm := parm || ' compression=none';
dbms_lob.createTemporary(lb, true);
sdo_geor.getRasterSubset(gr,plevel,win,to_char(bno),lb,parm);
len := dbms_lob.getlength(lb);
--dbms_output.put_line('lob length: ' || len);
cdl := cdl / 8;
-- make sure to read all the bytes of a cell value at one run
amt := floor(32767 / cdl) * cdl;
amt0 := amt;
off := 1;
while off <= len loop
dbms_lob.read(lb, amt, off, buf);
for i in 1..amt/cdl loop
if cdl = 1 then
r1 := utl_raw.substr(buf, (i-1)*cdl+1, cdl);
val := utl_raw.cast_to_binary_integer(r1);
elsif cdl = 2 then
r2 := utl_raw.substr(buf, (i-1)*cdl+1, cdl);
val := utl_raw.cast_to_binary_integer(r2);
elsif cdl = 4 then
r4 := utl_raw.substr(buf, (i-1)*cdl+1, cdl);
if flt = 0 then
val := utl_raw.cast_to_binary_integer(r4);
else
val := utl_raw.cast_to_binary_float(r4);
end if;
elsif cdl = 8 then
r8 := utl_raw.substr(buf, (i-1)*cdl+1, cdl);
val := utl_raw.cast_to_binary_double(r8);
end if;
if maxv is null or maxv < val then
maxv := val;
end if;
end loop;
off := off+amt;
amt := amt0;
end loop;
dbms_lob.freeTemporary(lb);
return maxv;
end;
/
show errors;
select getMaxCellValue(georaster,0,0,sdo_number_array(0,0,511,511))
from georaster_table where georid=8;
select getMaxCellValue(georaster,0,0,null)
from georaster_table where georid=9;