# Trying to get multiple cell values within a geometry

**996581**Nov 20, 2013 6:26 AM

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 cell value which is 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.

I had a conversation open for this before where JeffreyXie had some brilliant input, but it got archived while I was waiting on Oracle to resolve a bug (about 7 months)

See:

Trying to get multiple cell values within a geometry

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?

BTW, if anybody wants to play with this functionality, it only seems to work in 11.2.0.4.

Below is the code I was using last, I think it is trying to get the cell values but the numbers coming back are not correct, I think I am converting the binary to integer wrong.

Any ideas?

CREATE OR REPLACE FUNCTION GEOSUK.getCellValuesInGeom_FNC RETURN VARCHAR2 AS

gr sdo_georaster;

lb blob;

win1 sdo_geometry;

win2 sdo_number_array;

status VARCHAR2(1000) := NULL;

CDP varchar2(80);

FLT number := 0;

cdl number;

vals varchar2(32000) := null;

VAL number;

amt0 integer;

amt integer;

off integer;

len integer;

buf raw(32767);

MAXV number := null;

r1 raw(1);

r2 raw(2);

r4 raw(200);

r8 raw(8);

MATCH varchar2(10) := '';

ROW_COUNT integer := 0;

COL_COUNT integer := 0;

ROW_CUR integer := 0;

COL_CUR integer := 0;

CUR_XOFFSET integer := 0;

CUR_YOFFSET integer := 0;

ORIGINY integer := 0;

ORIGINX integer := 0;

XOFF number(38,0) := 0;

YOFF number(38,0) := 0;

BEGIN

status := '1';

SELECT a.georaster INTO gr FROM JBA_MEGARASTER_1012 a WHERE id=1;

-- 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;

end if;

dbms_lob.createTemporary(lb, TRUE);

status := '2';

-- querying/clipping polygon

win1 := SDO_GEOM.SDO_BUFFER(SDO_GEOMETRY(2001,27700,MDSYS.SDO_POINT_TYPE(473517,173650.3, NULL),NULL,NULL), 10, .005);

status := '1.2';

sdo_geor.getRasterSubset(gr, 0, win1, '1',

lb, win2, NULL, NULL, 'TRUE');

-- Then work on the resulting subset stored in lb.

status := '2.3';

DBMS_OUTPUT.PUT_LINE ( 'cdl: '||cdl );

len := dbms_lob.getlength(lb);

cdl := cdl / 8;

-- make sure to read all the bytes of a cell value at one run

amt := floor(32767 / cdl) * cdl;

amt0 := amt;

status := '3';

ROW_COUNT := (WIN2(3) - WIN2(1))+1;

COL_COUNT := (WIN2(4) - WIN2(2))+1;

--NEED TO FETCH FROM RASTER

ORIGINY := 979405;

ORIGINX := 91685;

--CALCUALATE BLOB AREA

YOFF := ORIGINY - (WIN2(1) * 5); --177005;

XOFF := ORIGINX + (WIN2(2) * 5); --530505;

status := '4';

--LOOP CELLS

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

IF (((i-1)*cdl+1) + cdl) > len THEN

r4 := utl_raw.substr(buf, (i-1)*cdl+1, (len - ((i-1)*cdl+1)));

ELSE

r4 := utl_raw.substr(buf, (i-1)*cdl+1, cdl+1);

END IF;

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;

IF i = 1 THEN

VALS := VALS || VAL;

ELSE

VALS := VALS ||'|'|| VAL;

END IF;

end loop;

off := off+amt;

amt := amt0;

end loop;

dbms_lob.freeTemporary(lb);

status := '5';

RETURN VALS;

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20001, 'GENERAL ERROR IN MY PROC, Status: '||status||', SQL ERROR: '||SQLERRM);

END;

/