Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
Making Sdo Geometry Metadata
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 the PL/SQL packages downloadable from the SDBA Shop
I hope the code is useful to someone.
Someone requested a 3D version, so I have created one that handles Z and M.
The new function also creates sdo_geom_metadata if it doesn’t exist for the supplied table.
CREATE OR REPLACE
PROCEDURE UpdateSdoMetadata( p_table_name IN varchar2,
p_column_name IN varchar2,
p_mbr_xy_factor IN NUMBER,
p_tolerance IN NUMBER := 0.005,
p_z_tolerance IN NUMBER := 0.05,
p_m_tolerance IN NUMBER := 0.005,
p_commit IN BOOLEAN := FALSE )
AUTHID CURRENT_USER
/** function UpdateSdoMetadata
* description Updates 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_xy_factor Expansion/Shrinkage amount for MBR of current data eg 1 meter. * Only applied to XY ordinates
* param p_tolerance SDO_GEOM_METADATA sdo_tolerance for new entries * param p_Z_tolerance SDO_GEOM_METADATA sdo_tolerance for Z ordinate for new entries * param p_M_tolerance SDO_GEOM_METADATA sdo_tolerance for M ordinate for new entries * param p_commit Whether to commit the update.
*/
AS
v_mbr_xy_factor NUMBER := CASE WHEN p_mbr_xy_factor IS NULL THEN 0 ELSE p_mbr_xy_factor END; v_tolerance NUMBER := NVL(p_tolerance,0.005);
v_tz NUMBER := NVL(p_z_tolerance,0.005);
v_tm NUMBER := NVL(p_m_tolerance,0.0005);
v_metadata BOOLEAN := FALSE;
v_diminfo mdsys.sdo_dim_array;
v_dims INTEGER;
v_lrs_dim INTEGER;
v_srid INTEGER;
v_sql VARCHAR(4000);
BEGIN
-- Check if something to process
IF ( p_table_name IS NULL OR p_column_name IS NULL ) THEN
RETURN;
END IF;
-- Get sdo_gtype of a geometry in p_table_name/p_column_name
--
BEGIN
EXECUTE IMMEDIATE
' SELECT a.' || p_column_name || '.GET_DIMS() as gdims,' ||
' a.' || p_column_name || '.GET_LRS_DIM() as lrsdim,' ||
' a.' || p_column_name || '.sdo_srid as srid ' ||
' FROM ' || p_table_name || ' a ' ||
' WHERE ROWNUM < 2'
INTO v_dims, v_lrs_dim,v_srid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(20001,'No data in ' || p_table_name,TRUE);
END;
-- Get existing record (checks if one even exists)
--
BEGIN
SELECT diminfo
INTO v_diminfo
FROM user_sdo_geom_metadata
WHERE TABLE_NAME = UPPER(p_table_name)
AND column_name = UPPER(p_column_name);
v_metadata := TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_metadata := FALSE;
dbms_output.put_line('Creating new DIMINFO as no SDO_METADATA record exists for ' || p_table_name || '.' || p_column_name ); v_diminfo := MDSYS.SDO_DIM_ARRAY();
v_diminfo.EXTEND(v_dims);
v_diminfo(1) := MDSYS.SDO_DIM_ELEMENT('X', NULL, NULL, v_tolerance);
v_diminfo(2) := MDSYS.SDO_DIM_ELEMENT('Y', NULL, NULL, v_tolerance);
IF ( v_dims > 2 AND v_lrs_dim = 0 ) THEN
v_diminfo(3) := MDSYS.SDO_DIM_ELEMENT('Z', NULL, NULL, v_tz);
ELSIF ( v_dims > 2 AND v_lrs_dim = 3 ) THEN
v_diminfo(3) := MDSYS.SDO_DIM_ELEMENT('M', NULL, NULL, v_tm);
ELSIF ( v_dims > 2 AND v_lrs_dim = 4 ) THEN
v_diminfo(3) := MDSYS.SDO_DIM_ELEMENT('Z', NULL, NULL, v_tz);
v_diminfo(4) := MDSYS.SDO_DIM_ELEMENT('M', NULL, NULL, v_tm);
END IF;
END;
v_tz := CASE WHEN v_diminfo.COUNT = 3 AND UPPER(v_diminfo(3).SDO_DIMNAME) = 'Z'
THEN NVL(v_diminfo(3).sdo_tolerance,v_tz)
ELSE v_tz
END;
v_tm := CASE WHEN v_diminfo.COUNT = 4 AND UPPER(v_diminfo(3).SDO_DIMNAME) IN ('W','M') THEN NVL(v_diminfo(4).sdo_tolerance,v_tm)
ELSE v_tm
END;
v_sql :=
'SELECT CASE WHEN minz is not null and minm is null
THEN MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT(:X, minx, maxx, :T),
MDSYS.SDO_DIM_ELEMENT(:Y, miny, maxy, :T),
MDSYS.SDO_DIM_ELEMENT(case when :LRS <> 0
then ''M''
else ''Z''
end, minz, maxz, :TZ))
WHEN minz is not null and minm is not null
THEN MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT(:X, minx, maxx, :T),
MDSYS.SDO_DIM_ELEMENT(:Y, miny, maxy, :T),
MDSYS.SDO_DIM_ELEMENT(''Z'', minz, maxz, :TZ),
MDSYS.SDO_DIM_ELEMENT(''M'', minm, maxm, :TM))
ELSE MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT(:X, minx, maxx, :T),
MDSYS.SDO_DIM_ELEMENT(:Y, miny, maxy, :T))
END as diminfo
FROM ( SELECT TRUNC( MIN( v.x ) - :F,0) as minx,
ROUND( MAX( v.x ) + :F,0) as maxx,
TRUNC( MIN( v.y ) - :F,0) as miny,
ROUND( MAX( v.y ) + :F,0) as maxy,
TRUNC( MIN( v.z ),0) as minz,
ROUND( MAX( v.z ),0) as maxz,
TRUNC( MIN( v.w ),0) as minm,
ROUND( MAX( v.w ),0) as maxm
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
)';
-- Create new diminfo structure from existing data
-- Update the diminfo with the MBR of the existing data
EXECUTE IMMEDIATE
v_sql
INTO v_diminfo
USING /*:X*/ v_diminfo(1).sdo_dimname, /*:T*/ v_diminfo(1).sdo_tolerance, /*:Y*/ v_diminfo(2).sdo_dimname, /*:T*/ v_diminfo(2).sdo_tolerance, /*:LRS*/ v_lrs_dim, /*:T*/ CASE WHEN v_lrs_dim <> 0 THEN v_diminfo(v_lrs_dim).sdo_tolerance ELSE v_tz END, /*:X*/ v_diminfo(1).sdo_dimname, /*:T*/ v_diminfo(1).sdo_tolerance, /*:Y*/ v_diminfo(2).sdo_dimname, /*:T*/ v_diminfo(2).sdo_tolerance, /*:TZ*/ v_tz,
/*:TM*/ v_tm,
/*:X*/ v_diminfo(1).sdo_dimname, /*:T*/ v_diminfo(1).sdo_tolerance, /*:Y*/ v_diminfo(2).sdo_dimname, /*:T*/ v_diminfo(2).sdo_tolerance, /*F*/ v_mbr_xy_factor,v_mbr_xy_factor,v_mbr_xy_factor,v_mbr_xy_factor; -- Now update the existing record
--
IF ( v_metadata ) THEN
UPDATE user_sdo_geom_metadata
SET diminfo = v_diminfo
WHERE TABLE_NAME = UPPER(p_table_name)
AND column_name = UPPER(p_column_name);
ELSE
INSERT INTO user_sdo_geom_metadata(TABLE_NAME,COLUMN_NAME,DIMINFO,SRID)
VALUES (p_table_name,p_column_name,v_diminfo,v_srid);
END IF;
-- Commit if requested
IF ( p_commit ) THEN
commit;
END IF;
RETURN;
END UpdateSdoMetadata;
/
SHOW errors
-- Testing...
-- 3D Point with M in 3rd ordinate position
DROP TABLE foo ;
DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM'; commit;
CREATE TABLE foo ( id INTEGER, geom sdo_geometry );
-- Points ...
INSERT INTO foo VALUES(1,sdo_geometry(3301,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(1,2,3))); INSERT INTO foo VALUES(2,sdo_geometry(3301,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(10,20,30))); EXECUTE UpdateSdoMetadata( 'FOO','GEOM',1,0.05,NULL,0.005,TRUE);
SELECT DIMINFO FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM'; DIMINFO
------------------------------------------------------------------------------------------------------------ SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',0,11,0.05),SDO_DIM_ELEMENT('Y',1,21,0.05),SDO_DIM_ELEMENT('M',3,30,0.005)) -- 4D Point M 4th ordinate
DELETE FROM FOO;
DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM'; COMMIT;
INSERT INTO foo VALUES(1,sdo_geometry(4401,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(1,2,3,4))); INSERT INTO foo VALUES(2,sdo_geometry(4401,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(10,20,30,40))); commit;
EXECUTE UpdateSdoMetadata( 'FOO','GEOM',0,0.05,0.005,0.0005,TRUE);
SELECT DIMINFO FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM'; DIMINFO
--------------------------------------------------------------------------------------------------------------------------------------------- SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',1,10,0.05),SDO_DIM_ELEMENT('Y',2,20,0.05),SDO_DIM_ELEMENT('Z',3,30,0.005),SDO_DIM_ELEMENT('M',4,40,0.0005)) -- Lines
DELETE FROM FOO;
DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM'; COMMIT;
INSERT INTO FOO VALUES(1,SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(380326.792,5167089.286, 380326.792,5167889.286, 380826.792,5167889.286, 380126.792,5167489.286))); INSERT INTO FOO VALUES(2,SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(380000,5100000, 380000,5160000.0))); INSERT INTO FOO VALUES(3,SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(380000,5100000, 390000,5110000.0))); INSERT INTO FOO VALUES(4,SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(380000,5100000, 380000.001,5100000, 390000,5110000.0))); EXECUTE UpdateSdoMetadata( 'FOO','GEOM',0.0,0.0005,NULL,NULL,TRUE);
SELECT DIMINFO FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM'; DIMINFO
--------------------------------------------------------------------------------------------------- SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',380000,390000,0.0005),SDO_DIM_ELEMENT('Y',5100000,5167889,0.0005)) -- LRS
DELETE FROM foo;
DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM'; COMMIT;
INSERT INTO FOO VALUES (1,SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2,2,0, 2,4,3.218, 8,4,12.872, 12,4,19.308, 12,10,28.962, 8,10,35.398, 5,14,43.443))); INSERT INTO FOO VALUES (2,SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2,2,0, 2,4,2, 5,4,5.0))); INSERT INTO FOO VALUES (3,SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(5,4,5, 8,4,8, 12,4,12, 12,10,18, 8,10,22, 5,14,27.0))); INSERT INTO FOO VALUES (4,SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2,2,0, 2,4,2, 5,4,5, 8,4,8, 12,4,12, 12,10,18, 8,10,22, 5,14,27.0))); EXECUTE UpdateSdoMetadata( 'FOO','GEOM',0,0.05,0.005,0.0005,TRUE);
SELECT DIMINFO FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM'; DIMINFO
------------------------------------------------------------------------------------------------------------- SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',2,12,0.05),SDO_DIM_ELEMENT('Y',2,14,0.05),SDO_DIM_ELEMENT('M',0,43,0.0005)) -- Create and update
DELETE FROM FOO;
DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM'; COMMIT;
INSERT INTO foo VALUES(1,sdo_geometry(4401,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(1,2,3,4))); INSERT INTO foo VALUES(2,sdo_geometry(4401,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(10,20,30,40))); commit;
EXECUTE UpdateSdoMetadata( 'FOO','GEOM',0,0.05,0.005,0.0005,TRUE);
SELECT DIMINFO FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM'; INSERT INTO foo VALUES(3,sdo_geometry(4401,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(100,200,300,400))); EXECUTE UpdateSdoMetadata( 'FOO','GEOM',0,0.05,0.005,0.0005,TRUE);
SELECT DIMINFO FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM'; DIMINFO
--------------------------------------------------------------------------------------------------------------------------------------------- SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',1,10,0.05),SDO_DIM_ELEMENT('Y',2,20,0.05),SDO_DIM_ELEMENT('Z',3,30,0.005),SDO_DIM_ELEMENT('M',4,40,0.0005)) DIMINFO
------------------------------------------------------------------------------------------------------------------------------------------------- SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',1,100,0.05),SDO_DIM_ELEMENT('Y',2,200,0.05),SDO_DIM_ELEMENT('Z',3,300,0.005),SDO_DIM_ELEMENT('M',4,400,0.0005))
I hope the updated function is of use to someone.