This content has been marked as final. Show 9 replies
Check [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11827/geor_ref.htm#CHEFEFCG]SDO_GEOR.getRasterSubset:
Creates a single BLOB object containing all cells of a specified pyramid level that are inside or on the boundary of either a specified rectangular window or polygon geometry object.HTH,
Thanks for the info Stefan.
I found that function and managed to run it on my raster.
How do I get the list of cell values from the resulting BLOB?
Oh, true, it returns a BLOB, on which cellValues does no do much.
try [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11827/geor_ref.htm#CHEGFEDF]SDO_GEOR.subset, that returns a GEORASTER object.
Sorry, my mistake. On the whole, these kinds of questions you could also find the answers for yourself in the [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11827/toc.htm]documentation.
Hi, what's your purpose of doing this (i.e., what kind app, geom window size, how frequent to query, etc.)? are you programming in PL/SQL or Java or OCI?
JRaster.getRasterSubset in the GeoRaster Java API can return an in-memory byte array directly (http://docs.oracle.com/cd/E14072_01/appdev.112/e11829/toc.htm). Otherwise, you can call getCellValue or if you call getRasterSubset and as you asked, reading the BLOB needs to be done. For example, in PL/SQL, you need to call dbms_lob.read and utl_raw.cast_to_binary_integer etc.
I am provided with 3 tables:
1 - The GeoRaster
2 - The geoRasterData table
3 - A VAT table who's PK is the cell value from the above tables
Currently the user can select a point in our application and by using the getCellValue we get the PK on the 3rd table and this gives us the details to return to the user.
We now want to give the worst scenario within a given geometry or distance. So if I get back all the cell values within a given geometry/distance I can then call my other functions against the 3rd table to get the worst scores.
One thing I saw in both getSubSet and getRasterSubSet was that they return the MBR, not the contents or ANYINTERACT cells.
I found a good example where these were used, but they got the highest cell value of the MBR. I can post this code if you want?
If I am looking to get a list of cell values that interact with my geometry/distance and then loop through them, is there a better way?
Thanks in advance!
Sorry I didn't answer your question properly:
I am querying a raster covering the uk in 5M2 cells, but only looking at a 10 meter/ 100 meter radius of the point.
So far I am running this in PL/SQL, but I am ultimately passing the values back to my Java app.
This query will run a couple of times a second. Currently to do it for a point takes about 150 milliseconds. Even if this took several times that it would be great. (But anything is better then not being able to do it at all! :) )
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.
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
flt number := 0;
maxv number := null;
-- first figure out the celldepth from the metadata
cdp := gr.metadata.extract('/georasterMetadata/rasterInfo/cellDepth/text()',
if cdp = '32BIT_REAL' then
flt := 1;
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';
parm := parm || ' compression=none';
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);
val := utl_raw.cast_to_binary_float(r4);
elsif cdl = 8 then
r8 := utl_raw.substr(buf, (i-1)*cdl+1, cdl);
val := utl_raw.cast_to_binary_double(r8);
if maxv is null or maxv < val then
maxv := val;
off := off+amt;
amt := amt0;
from georaster_table where georid=8;
from georaster_table where georid=9;
That's brilliant, thanks a million Jeffrey, its a pretty good base to work off!
I'm going to try and get back to that project over the weekend.
I'll let you know how I get on with it!
I think I got that working.
I'll do a bit more testing with it tomorrow and I'll make it a bit more production ready (remove DBMS_OUTPUT from everywhere :) ) and then I'll post the code I used.
Thanks a million for all your help I never would have got it working!