9 Replies Latest reply on Mar 13, 2013 8:41 PM by 996581

    Trying to get multiple cell values within a geometry

      Hi all,

      I'm trying to get a list of cell values from a raster within a given geometry or distance of a geometry.

      For a single point I call sdo_geor.getCellValue, but is there a way to get a list cell values within a 2003 polygon geometry.

      What is the best way to go about this.

      Thanks in advance
        • 1. Re: Trying to get multiple cell values within a geometry
          Stefan Jager
          Hi Unknown,

          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.
          • 2. Re: Trying to get multiple cell values within a geometry

            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?
            • 3. Re: Trying to get multiple cell values within a geometry
              Stefan Jager
              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.

              • 4. Re: Trying to get multiple cell values within a geometry
                Jeffrey Xie-Oracle
                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.
                • 5. Re: Trying to get multiple cell values within a geometry

                  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!
                  • 6. Re: Trying to get multiple cell values within a geometry
                    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! :) )
                    • 7. Re: Trying to get multiple cell values within a geometry
                      Jeffrey Xie-Oracle
                      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
                      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;
                      -- first figure out the celldepth from the metadata
                      cdp := gr.metadata.extract('/georasterMetadata/rasterInfo/cellDepth/text()',
                      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);
                      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);
                      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;

                      return maxv;
                      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;
                      • 8. Re: Trying to get multiple cell values within a geometry
                        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!
                        • 9. Re: Trying to get multiple cell values within a geometry

                          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!