1 2 Previous Next 23 Replies Latest reply: Nov 26, 2013 9:40 AM by 996581 RSS

    Trying to get multiple cell values within a geometry

    996581

      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;

      /

        1 2 Previous Next