4.1 Querying and Searching GeoRaster Objects

GeoRaster tables are regular relational tables that can have various columns, such as an ID number, a name, a timestamp, and a unique description in the form of a string. These columns can be indexed, and GeoRaster objects can be queried using the standard database indexing and query statements, as shown in many examples in this manual.

After the GeoRaster tables are spatially indexed (see Indexing GeoRaster Objects), you can quickly query or search GeoRaster objects using a geometry as well. For example, you may want to find all images (maybe hundreds or more) inside a specific region and then generate full pyramids for each image, as in the following example.

Example 4-1 Searching GeoRaster Objects and Generating Pyramids for Them

DECLARE
  type curtype is ref cursor;
  my_cursor curtype;
  stmt varchar2(1000);
  tid     number;
  gr    sdo_georaster;
  gm  sdo_geometry;
BEGIN
  -- 1. Define the query area in EPSG 4326 (WGS84) coordinate system
  gm := sdo_geometry(2003, 4326, null,
              sdo_elem_info_array(1,1003,3),
              sdo_ordinate_array(5,6,30,30));
 
  -- 2. Define the query statement on the GeoRaster table (city_images)using the given geometry
  stmt := 'select id from city_images t ' ||
    'where sdo_inside(t.image.spatialextent, :1)=''TRUE''';
 
  -- 3. Spatially query all images INSIDE the query area 
  --    and generate full pyramids for each of the images
  open my_cursor for stmt using gm;
  loop
    fetch my_cursor into tid;
    exit when my_cursor%NOTFOUND;
    -- retrieve the image to generate the pyramids
    select image into gr from city_images where id = tid for update;
    sdo_geor.generatePyramid(gr, 'resampling=bilinear', null, ‘parallel=4’);
    update city_images set image=gr
       where id = tid;
    commit; 
  end loop;
  close my_cursor;
END;

You can also wrap up such blocks into a PL/SQL procedure and store it in the database, then call the stored procedure directly. These features enable you to organize complex processes and automate database administration tasks.