Keeping Oracle and ESRI spatial metadata (MBR) in sync

This is a little script which comes very useful when the extents of your Oracle spatial layer are changed. It calculates the MBR and  uses these values to update the ArcSDE metadata (SDE.LAYERS and SDE.SPATIAL_REFERENCES):

UPDATE: Amend script to update the USER_SDO_GEOM_METADATA and use a cursor.

DECLARE
 mbr mdsys.sdo_geometry;
 cursor cs1 is
  select table_name, column_name
  from user_sdo_geom_metadata
  where table_name like '%COLA%'; 
 -- Number of dimensions
 idim integer;
BEGIN
   FOR each_rec in cs1
   LOOP 
      -- Get the extent
      mbr:=sdo_tune.extent_of(tabName,sColName);
      SELECT count(*) into idim
       FROM user_sdo_geom_metadata usgm,
              TABLE(usgm.diminfo) dim
        WHERE table_name = each_rec.table_name;
       IF idim = 2 THEN
         -- Update diminfo
         UPDATE user_sdo_geom_metadata
         SET diminfo= SDO_DIM_ARRAY(   -- 20X20 grid
              SDO_DIM_ELEMENT('X', mbr.sdo_ordinates(1), mbr.sdo_ordinates(3), 0.005),
              SDO_DIM_ELEMENT('Y', mbr.sdo_ordinates(2), mbr.sdo_ordinates(4), 0.005))
              WHERE table_name= each_rec.table_name;
        ELSE -- assume 3 dims, 3rd is the M. Default to 0, 999999999, 0.005
          UPDATE user_sdo_geom_metadata
          SET diminfo= SDO_DIM_ARRAY(   -- 20X20 grid
               SDO_DIM_ELEMENT('X', mbr.sdo_ordinates(1), mbr.sdo_ordinates(3), 0.005),
               SDO_DIM_ELEMENT('Y', mbr.sdo_ordinates(2), mbr.sdo_ordinates(4), 0.005),
               SDO_DIM_ELEMENT('M', 0, 999999999, 0.005))
           WHERE table_name= each_rec.table_name;
         END IF;
       -- Update SDE.LAYERS
         update sde.layers
         set minx=mbr.sdo_ordinates(1),
             miny=mbr.sdo_ordinates(2),
             maxx=mbr.sdo_ordinates(3),
             maxy=mbr.sdo_ordinates(4)
         where table_name=tabName;
       -- Update SDE.Spatial References
         update sde.spatial_references
         set falsex=mbr.sdo_ordinates(1),
             falsey=mbr.sdo_ordinates(2)
          where srid in (
           (select srid from sde.layers
            where table_name=tabName));
   END LOOP;
END;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s