|
Making Sdo Geometry Metadata Update Generic Code
Wednesday December 09 2009 at 20:42
As a part of my last article on tracking down and correcting ORA-13011 errors when using SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT() , I showed how to update the diminfo structure associated with a table’s geometry column in its USER_SDO_GEOM_METADATA entry.
Here was the code in that article.
UPDATE user_sdo_geom_metadata
SET diminfo = ( SELECT MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', minx, maxx, 0.05),
MDSYS.SDO_DIM_ELEMENT('Y', miny, maxy, 0.05)) as diminfo
FROM ( SELECT TRUNC( MIN( v.x ) - 1,0) as minx,
ROUND( MAX( v.x ) + 1,0) as maxx,
TRUNC( MIN( v.y ) - 1,0) as miny,
ROUND( MAX( v.y ) + 1,0) as maxy
FROM (SELECT SDO_AGGR_MBR(a.geometry) as mbr
FROM rectangles a) b,
TABLE(mdsys.sdo_util.getvertices(b.mbr)) v
)
)
WHERE table_name = 'RECTANGLES'
AND column_name = 'GEOMETRY';
Now, that code is pretty neat, but it has in it a few things that need changing every time you want to use it for another table’s geometry column, or you want to change the MBR shrinkage/expansion factor (ie – 1/+ 1), or you can’t remember the sdo_tolerance values (in the above, 0.05).
What we need to do is encapsulate this in a PL/SQL Procedure that you can call anytime you need to. Here is one such implementation that is, I believe, generic enough to do all that is required.
/** function UpdateSdoMetadata
* description Updates 2D spatial extent of DIMINFO associated with table/column in all_sdo_geom_metadata
* param p_table_name The object containing the spatal data.
* param p_column_name The sdo_geometry column to be analyzed.
* param p_mbr_factor Expansion/Shrinkage amount for MBR of current data.
* param p_commit Whether to commit the update.
*/
Create Or Replace
Procedure UpdateSdoMetadata( p_table_name in varchar2,
p_column_name in varchar2,
p_mbr_factor in number,
p_commit in boolean := false )
As
v_mbr_factor number := case when p_mbr_factor is null then 0 else p_mbr_factor end;
v_diminfo mdsys.sdo_dim_array;
Begin
-- Check if something to process
If ( p_table_name is null or p_column_name is null ) Then
Return;
End If;
-- Get existing record (checks if one even exists)
--
SELECT diminfo
INTO v_diminfo
FROM user_sdo_geom_metadata
WHERE table_name = UPPER(p_table_name)
AND column_name = UPPER(p_column_name);
-- Update the diminfo with the MBR of the existing data
EXECUTE IMMEDIATE 'SELECT MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT(''X'', minx, maxx, :1),
MDSYS.SDO_DIM_ELEMENT(''Y'', miny, maxy, :2)) as diminfo
FROM ( SELECT TRUNC( MIN( v.x ) - :3,0) as minx,
ROUND( MAX( v.x ) + :4,0) as maxx,
TRUNC( MIN( v.y ) - :5,0) as miny,
ROUND( MAX( v.y ) + :6,0) as maxy
FROM (SELECT SDO_AGGR_MBR(a.' || p_column_name || ') as mbr
FROM ' || p_table_name || ' a) b,
TABLE(mdsys.sdo_util.getvertices(b.mbr)) v
)'
INTO v_diminfo
USING v_diminfo(1).sdo_tolerance,
v_diminfo(2).sdo_tolerance,
v_mbr_factor,v_mbr_factor,v_mbr_factor,v_mbr_factor;
-- Now update the existing record
--
UPDATE user_sdo_geom_metadata
SET diminfo = v_diminfo
WHERE table_name = UPPER(p_table_name)
AND column_name = UPPER(p_column_name);
-- Commit if requested
If ( p_commit ) Then
commit;
End If;
Return;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20000, 'No SDO_METADATA record exists for ' || p_table_name || '.' || p_column_name || '. Run MetadataAnalayzer');
End UpdateSdoMetadata;
I’ve placed this in the TOOLS package in my free PL/SQL packages.
I hope the code is useful to someone.
        
|
SpatialDB Advisor
Home
Comment [1]
Thank you !
— Pat · 27 March 2010, 00:58 · #