Go to content Go to navigation and search


Current Oracle Spatial Blog Articles

    Using Oracle's ST_GEOMETRY type hierarchy with SDO_GEOMETRY: ST_PointN and ST_NumPoints
    Making Sdo Geometry Metadata
    SC4O Test log
    The Frontage Problem: Creating references from land parcel street frontage boundary to point in street
    Scheduling the Export of Spatial Data in Oracle to a Shapefile each night
    Oracle CENTROID package update
    Book: "Applying And Extending Oracle Spatial"
    Create Polygons and Linestrings From Bearings And Distances (COGO)
    Simple Implementation Of Travelling Salesman Problem
    ST_Densify for Object PLSQL
    PLSQL ST_AsEWKT and ST_FromWKT Functions For Oracle (any version)
    Useful Package of Wrapper Functions for Sdo_Util.AffineTransforms
    GeoRaptor 4.x Update 2 (Project has Stalled)
    Tip: Drop that Spatial Index!
    Convert Single Geometry to Multi-part Geometry in Oracle Spatial
    Optimized Rectangle to 5 Point Polygon
    Centroid Package now supports Y ordinate seeding
    Convert GeoJSON document to Sdo_Geometry objects
    Function That Returns a Compass Point From a Whole Circle Bearing
    Playing around with Centroids by using different seed values
    Simple Oracle C Sprintf or Java String.format
    Some Oriented Point Functions
    Extracting Inner Rings Changed Ordinate Ordering: A Trap For Players Who Don't Read Documentation!
    PLS-00306: wrong number or types of arguments in call to 'SDO_GEOMETRY'
    Converting Google Earth Formatted Longitude/Latitude points to decimal degrees
    Oracle Business Intelligence Warehousing and Analytics - Spatial Summit
    How far inside, is inside? Measuring actual distance.
    Noding and building a polygon from single, overlapping linestrings
    Analyzing Spatial Query Performance Improvements in Oracle Spatial and Graph 12c Through Cross-Vendor Comparison
    ST_VertexN / ST_PointN - Extracting a specific point from any geometry
    Convert Single Point stored in SDO_ORDINATES to SDO_POINT_TYPE
    Aggregate APPEND Islands and XOR polygons
    Circular Arcs in Geodetic Polygons
    Some SDO_GEOMETRY/DIMINFO handling functions
    Changing all DIMINFO sdo_tolerance values for all metadata records in one go.
    Building Polygons from Incomplete Linestrings using ST_PolygonBuilder
    Computing Cardinal Directions to nearby geometries
    Intersecting two aggregated polygon layers with SC4O
    Spatial and Oracle 12c
    Update Triggers and SDO_GEOMETRY Equality
    Duplicate Geometry data and Data Models
    CENTROID package update
    How to calculate cumulative length of a linestring
    Compute Location from known Lat/Long point using delta easting and northing in miles
    Sorting SDO_GEOMETRY data using the ORDER BY clause of a SELECT statement
    Creating linestrings from points
    Rounding Coordinates or Ordinates in SDO_GEOMETRY
    Effects of Sdo_Geometry Ordinate Precision on Performance
    Effects of Sdo_Geometry Ordinate Precision on Storage
    The Spatial filtering of geometries: The effect of tolerances on relationships
    Application of Delaunay Triangulation and Inverse Distance Weighting (IDW) in Oracle for Soils Interpolation
    Selecting all SDO_GTYPE values for all tables/sdo_geometry columns in a schema
    CENTROID package - Tips for Use
    Announcing the Spatial Companion For Oracle (SC4O)
    Filtering Rings (Oracle Spatial)
    Splitting a polygon using one or more linestrings
    isValid, isSimple, Dimension and CoordDim methods for SDO_Geometry
    Line Merging or Collecting lines together: ST_LineMerger
    ST_DeleteVertex for Oracle SDO_Geometry based on Jaspa/JTS
    3D/4D and SRID aware Conversion functions for SDO_Geometry: WKT and EWKT
    Topological vs Non-Topological Simplification/Generalization of Aggregated Area Geometies in Oracle
    Filtering very short linestrings via bitmap function index
    CENTROID For Oracle
    Gridding a sdo_geometry line/polygon object (Oracle)
    Finding centre and radius of a circular geometry
    Constraining geometry type for sdo_geometry column in a table.
    CASE Statements and SDO_GEOMETRY
    The Power of Constraints and Indexes for Spatial Constraints: stopping duplicate points
    Replacement for SDO_GEOM.RELATE - ST_Relate based on JTS
    Changing Oracle Spatial Index Parameters on existing index
    Writing Excel Spreadsheets files from within the Oracle database using Java and PL/SQL
    Writing xSV (eg csv) files from within the Oracle database using Java and PL/SQL
    A simple spike finder for Spatial/Locator
    JTS Java class compilation for 11g and above
    Random Spatial Search Procedure
    Geometry Snapping using JTS in Oracle
    Exposing JTS's MinimumBoundingCircle functionality
    Exposing JTS's Densifier functionality
    Using JTS's Comparison Functions - HausdorffSimilarityMeasure & AreaSimilarityMeasure with SDO_GEOMETRY
    Free JTS-based Area/Length Functions
    Handy way of systematically fixing polygon geometries with 13349 and other errors
    Standalone CENTROID package now available for download
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 4 Processing Geodetic data
    Configurable Buffer: JTS and Oracle
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 3
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 2
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 1
    Building Lines into Polygons in Oracle Locator / Spatial
    Finding Intersection Points between Line and Polygon
    Free version of sdo_length
    Alternative to my SQL based GetNumRings function
    External Tables and SDO_Geometry data.
    layer_gtype keyword issue when indexing linear data on 11g
    String Tokenizer for Oracle
    Free Aggregate Method for Concatenating 2D Lines in Oracle Locator 10g
    Reducing 5 Vertex Polygon to Optimized Rectangle
    Square Buffer
    Converting decimal seconds to string
    Valid conversion unit values for Oracle sdo_geom.sdo_length()
    Removing Steps in Gridded Vector Data - SmoothGrid for Oracle
    Oracle Spatial DISJOINT search/filtering
    Creating SDO_Geometry from geometric data recorded in the columns of a table
    Concave Hull Geometries in Oracle 11gR2
    Projecting SDO_GEOM_METADATA DIMINFO XY ordinates
    Instantiating MDSYS.VERTEX_TYPE
    New PL/SQL Packages - Rotate oriented point
    GeoRaptor Development Team
    Fast Refreshing Materialized View Containing SDO_GEOMETRY and SDO_GEOM.SDO_AREA function
    Performance of PL/SQL Functions using SQL vs Pure Code
    Implementing the BEST VicGrid Projection in Oracle 10gR2
    ORA-13011 errors when using SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT()
    Extract Polygons from Compound Polygon
    Detecting sdo_geometries with compound (3-point Arcs) segments
    GEOMETRY_COLUMNS for Oracle Spatial
    Convert GML to SDO_Geometry in Oracle 10gR2
    Spatial Sorting of Data via Morton Key
    Swapping Ordinates in an SDO_GEOMETRY object
    New To_3D Function
    Extend (Reduce/Contract/Skrink) Function for Oracle
    Loading and Processing GPX 1.1 files using Oracle XMLDB
    Loading Spatial Data from an external CSV file in Oracle
    Calling the Oracle Spatial shapefile loader from within the Oracle database itself
    Implementing SDO_VertexUpdate/ST_VertexUpdate for Oracle
    Implementing SDO_RemovePoint/ST_RemovePoint for Oracle
    Implementing SDO_AddPoint/ST_AddPoint for Oracle
    ESRI ArcSDE Exverted and Inverted Polygons and Oracle Spatial
    Funky Fix Ordinates By Formula
    Implementing a SetPoint/ST_SetPoint function in Oracle
    Implementing an ST_SnapToGrid (PostGIS) function for Oracle Spatial
    Generating random point data
    Implementing an Affine/ST_Affine function for Oracle Spatial
    Implementing a Scale/ST_Scale function for Oracle Spatial
    Implementing a Parallel/ST_Parallel function for linestring data for Oracle Spatial
    Implementing a Rotate/ST_Rotate function for Oracle Spatial
    Limiting table list returned when connecting to Oracle Database using ODBC
    ST_Azimuth for Oracle: AKA Cogo.Bearing
    Implementing a Translate/ST_Translate/Move function for Oracle Spatial
    Elem_Info_Array Processing: An alternative to SDO_UTIL.GetNumRings and querying SDO_ELEM_INFO itself
    Minumum Bounding Rectangle (MBR) Object Type for Oracle
    How to extract elements from the result of an sdo_intersection of two polygons.
    How to restart a database after failed parameter change
    Fixing failed spatial indexes after import using data pump
    generate_series: an Oracle implementation in light of SQL Design Patterns
    Multi-Centroid Shootout
    Oracle Spatial Centroid Shootout
    On the use of ROLLUP in Oracle SELECT statements
    Surrounding Parcels
    Spatial Pipelining
    Using Oracle's SDO_NN Operator - Some examples
    Converting distances and units of measure in Oracle Locator
    Split Sdo_Geometry Linestring at a known point
    Forcing an Sdo_Geometry object to contain only points, lines or areas
    Unpacking USER_SDO_GEOM_METADATA's DIMINFO structure using SQL
    Generating multi-points from single point records in Oracle Spatial
    Object Tables of Sdo_Geometry
    Oracle Locator vs Oracle Spatial: A Reflection on Oracle Licensing of the SDO_GEOM Package
    FAST REFRESHing of Oracle Materialized Views containing Sdo_Geometry columns
    Australian MGA/AMG Zone Calculation from geographic (longitude/latitude) data
    Loading Shapefiles (SHP) into Oracle Spatial
    Oracle Spatial Mapping and Map Rendering Performance Tips
    The significance of sdo_lb/sdo_ub in USER_SDO_GEOM_METDATA: Do I need it?
    Oracle Spatial Forum - Melbourne April 2007
    Layer_GTypes for spatial indexes
    Oracle's SQL/MM Compliant Types
    Tips and Tricks



RSS / Atom

Email me


Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Making Sdo Geometry Metadata

Thursday April 04 2019 at 21:43

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.

  1. UPDATE user_sdo_geom_metadata
  2.    SET diminfo = ( SELECT MDSYS.SDO_DIM_ARRAY(
  3.                                 MDSYS.SDO_DIM_ELEMENT('X', minx, maxx, 0.05),
  4.                                 MDSYS.SDO_DIM_ELEMENT('Y', miny, maxy, 0.05)) AS diminfo
  5.                      FROM ( SELECT TRUNC( MIN( v.x ) - 1,0) AS minx,
  6.                                    ROUND( MAX( v.x ) + 1,0) AS maxx,
  7.                                    TRUNC( MIN( v.y ) - 1,0) AS miny,
  8.                                    ROUND( MAX( v.y ) + 1,0) AS maxy
  9.                               FROM (SELECT SDO_AGGR_MBR(a.geometry) AS mbr
  10.                                       FROM rectangles a) b,
  11.                                            TABLE(mdsys.sdo_util.getvertices(b.mbr)) v
  12.                            )
  13.                  )
  15.    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.

  1.    /** @function    UpdateSdoMetadata
  2.    *   @description Updates 2D spatial extent of DIMINFO associated with table/column in all_sdo_geom_metadata
  3.    *   @param       p_table_name  The object containing the spatal data.
  4.    *   @param       p_column_name The sdo_geometry column to be analyzed.
  5.    *   @param       p_mbr_factor  Expansion/Shrinkage amount for MBR of current data.
  6.    *   @param       p_commit      Whether to commit the update.
  7.    */
  9.    PROCEDURE UpdateSdoMetadata( p_table_name  IN varchar2,
  10.                                 p_column_name IN varchar2,
  11.                                 p_mbr_factor  IN NUMBER,
  12.                                 p_commit      IN BOOLEAN := FALSE )
  13.    AS
  14.      v_mbr_factor NUMBER := CASE WHEN p_mbr_factor IS NULL THEN 0 ELSE p_mbr_factor END;
  15.      v_diminfo    mdsys.sdo_dim_array;
  16.    BEGIN
  17.      -- Check if something to process
  18.      IF ( p_table_name IS NULL OR p_column_name IS NULL ) THEN
  19.        RETURN;
  20.      END IF;
  21.      -- Get existing record (checks if one even exists)
  22.      --
  23.      SELECT diminfo
  24.        INTO v_diminfo
  25.        FROM user_sdo_geom_metadata
  26.       WHERE TABLE_NAME  = UPPER(p_table_name)
  27.         AND column_name = UPPER(p_column_name);
  28.       -- Update the diminfo with the MBR of the existing data
  30.                                MDSYS.SDO_DIM_ELEMENT(''X'', minx, maxx, :1),
  31.                                MDSYS.SDO_DIM_ELEMENT(''Y'', miny, maxy, :2)) as diminfo
  32.                     FROM ( SELECT TRUNC( MIN( v.x ) - :3,0) as minx,
  33.                                   ROUND( MAX( v.x ) + :4,0) as maxx,
  34.                                   TRUNC( MIN( v.y ) - :5,0) as miny,
  35.                                   ROUND( MAX( v.y ) + :6,0) as maxy
  36.                              FROM (SELECT SDO_AGGR_MBR(a.' || p_column_name || ') as mbr
  37.                                      FROM ' || p_table_name || ' a) b,
  38.                                           TABLE(mdsys.sdo_util.getvertices(b.mbr)) v
  39.                           )'
  40.                  INTO v_diminfo
  41.                 USING v_diminfo(1).sdo_tolerance,
  42.                       v_diminfo(2).sdo_tolerance,
  43.                       v_mbr_factor,v_mbr_factor,v_mbr_factor,v_mbr_factor;
  44.      -- Now update the existing record
  45.      --
  46.      UPDATE user_sdo_geom_metadata
  47.         SET diminfo     = v_diminfo
  48.       WHERE TABLE_NAME  = UPPER(p_table_name)
  49.         AND column_name = UPPER(p_column_name);
  50.     -- Commit if requested
  51.     IF ( p_commit ) THEN
  52.       commit;
  53.     END IF;
  54.     RETURN;
  55.     EXCEPTION
  57.          raise_application_error(-20000, 'No SDO_METADATA record exists for ' || p_table_name || '.' || p_column_name || '. Run MetadataAnalayzer');
  58.    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.

  2. PROCEDURE UpdateSdoMetadata( p_table_name    IN varchar2,
  3.                           p_column_name   IN varchar2,
  4.                           p_mbr_xy_factor IN NUMBER,
  5.                           p_tolerance     IN NUMBER  := 0.005,
  6.                           p_z_tolerance   IN NUMBER  := 0.05,
  7.                           p_m_tolerance   IN NUMBER  := 0.005,
  8.                           p_commit        IN BOOLEAN := FALSE )
  10. /** function    UpdateSdoMetadata
  11. *   description Updates spatial extent of DIMINFO associated with table/column in all_sdo_geom_metadata
  12. *   param       p_table_name     The object containing the spatal data.
  13. *   param       p_column_name    The sdo_geometry column to be analyzed.
  14. *   param       p_mbr_xy_factor  Expansion/Shrinkage amount for MBR of current data eg 1 meter.
  15. *                                Only applied to XY ordinates
  16. *   param       p_tolerance      SDO_GEOM_METADATA sdo_tolerance for new entries
  17. *   param       p_Z_tolerance    SDO_GEOM_METADATA sdo_tolerance for Z ordinate for new entries
  18. *   param       p_M_tolerance    SDO_GEOM_METADATA sdo_tolerance for M ordinate for new entries
  19. *   param       p_commit         Whether to commit the update.
  20. */
  21. AS
  22.   v_mbr_xy_factor NUMBER  := CASE WHEN p_mbr_xy_factor IS NULL THEN 0 ELSE p_mbr_xy_factor END;
  23.   v_tolerance     NUMBER  := NVL(p_tolerance,0.005);
  24.   v_tz            NUMBER  := NVL(p_z_tolerance,0.005);
  25.   v_tm            NUMBER  := NVL(p_m_tolerance,0.0005);
  26.   v_metadata      BOOLEAN := FALSE;
  27.   v_diminfo       mdsys.sdo_dim_array;
  28.   v_dims          INTEGER;
  29.   v_lrs_dim       INTEGER;
  30.   v_srid          INTEGER;
  31.   v_sql           VARCHAR(4000);
  32. BEGIN
  33.   -- Check if something to process
  34.   IF ( p_table_name IS NULL OR p_column_name IS NULL ) THEN
  35.     RETURN;
  36.   END IF;
  37.   -- Get sdo_gtype of a geometry in p_table_name/p_column_name
  38.   --
  39.   BEGIN
  41.       ' SELECT a.' || p_column_name || '.GET_DIMS()    as gdims,' ||
  42.              ' a.' || p_column_name || '.GET_LRS_DIM() as lrsdim,' ||
  43.              ' a.' || p_column_name || '.sdo_srid      as srid ' ||
  44.         ' FROM ' || p_table_name || ' a ' ||
  45.        ' WHERE ROWNUM < 2'
  46.       INTO v_dims, v_lrs_dim,v_srid;
  47.     EXCEPTION
  49.        raise_application_error(20001,'No data in ' || p_table_name,TRUE);
  50.   END;
  51.   -- Get existing record (checks if one even exists)
  52.   --
  53.   BEGIN
  54.     SELECT diminfo
  55.       INTO v_diminfo
  56.       FROM user_sdo_geom_metadata
  57.      WHERE TABLE_NAME  = UPPER(p_table_name)
  58.        AND column_name = UPPER(p_column_name);
  59.     v_metadata := TRUE;
  60.     EXCEPTION
  62.         v_metadata := FALSE;
  63.         dbms_output.put_line('Creating new DIMINFO as no SDO_METADATA record exists for ' || p_table_name || '.' || p_column_name );
  64.         v_diminfo    := MDSYS.SDO_DIM_ARRAY();
  65.         v_diminfo.EXTEND(v_dims);
  66.         v_diminfo(1) := MDSYS.SDO_DIM_ELEMENT('X', NULL, NULL, v_tolerance);
  67.         v_diminfo(2) := MDSYS.SDO_DIM_ELEMENT('Y', NULL, NULL, v_tolerance);
  68.         IF ( v_dims > 2 AND v_lrs_dim = 0 ) THEN
  69.           v_diminfo(3) := MDSYS.SDO_DIM_ELEMENT('Z', NULL, NULL, v_tz);
  70.         ELSIF ( v_dims > 2 AND v_lrs_dim = 3 ) THEN
  71.           v_diminfo(3) := MDSYS.SDO_DIM_ELEMENT('M', NULL, NULL, v_tm);
  72.         ELSIF ( v_dims > 2 AND v_lrs_dim = 4 ) THEN
  73.           v_diminfo(3) := MDSYS.SDO_DIM_ELEMENT('Z', NULL, NULL, v_tz);
  74.           v_diminfo(4) := MDSYS.SDO_DIM_ELEMENT('M', NULL, NULL, v_tm);
  75.         END IF;
  76.   END;
  77.   v_tz := CASE WHEN v_diminfo.COUNT = 3 AND UPPER(v_diminfo(3).SDO_DIMNAME) = 'Z'
  78.                THEN NVL(v_diminfo(3).sdo_tolerance,v_tz)
  79.                ELSE v_tz
  80.            END;
  81.   v_tm := CASE WHEN v_diminfo.COUNT = 4 AND UPPER(v_diminfo(3).SDO_DIMNAME) IN ('W','M')
  82.                THEN NVL(v_diminfo(4).sdo_tolerance,v_tm)
  83.                ELSE v_tm
  84.            END;
  85.   v_sql :=
  86.      'SELECT CASE WHEN minz is not null and minm is null
  87.                  THEN MDSYS.SDO_DIM_ARRAY(
  88.                         MDSYS.SDO_DIM_ELEMENT(:X, minx, maxx, :T),
  89.                         MDSYS.SDO_DIM_ELEMENT(:Y, miny, maxy, :T),
  90.                         MDSYS.SDO_DIM_ELEMENT(case when :LRS <> 0
  91.                                                    then ''M''
  92.                                                    else ''Z''
  93.                                                end, minz, maxz, :TZ))
  94.                  WHEN minz is not null and minm is not null
  95.                  THEN MDSYS.SDO_DIM_ARRAY(
  96.                         MDSYS.SDO_DIM_ELEMENT(:X,    minx, maxx, :T),
  97.                         MDSYS.SDO_DIM_ELEMENT(:Y,    miny, maxy, :T),
  98.                         MDSYS.SDO_DIM_ELEMENT(''Z'', minz, maxz, :TZ),
  99.                         MDSYS.SDO_DIM_ELEMENT(''M'', minm, maxm, :TM))
  100.                  ELSE MDSYS.SDO_DIM_ARRAY(
  101.                         MDSYS.SDO_DIM_ELEMENT(:X, minx, maxx, :T),
  102.                         MDSYS.SDO_DIM_ELEMENT(:Y, miny, maxy, :T))
  103.              END as diminfo
  104.        FROM ( SELECT TRUNC( MIN( v.x ) - :F,0) as minx,
  105.                      ROUND( MAX( v.x ) + :F,0) as maxx,
  106.                      TRUNC( MIN( v.y ) - :F,0) as miny,
  107.                      ROUND( MAX( v.y ) + :F,0) as maxy,
  108.                      TRUNC( MIN( v.z ),0) as minz,
  109.                      ROUND( MAX( v.z ),0) as maxz,
  110.                      TRUNC( MIN( v.w ),0) as minm,
  111.                      ROUND( MAX( v.w ),0) as maxm
  112.                 FROM (SELECT SDO_AGGR_MBR(a.' || p_column_name || ') as mbr
  113.                         FROM ' || p_table_name || ' a) b,
  114.                              TABLE(mdsys.sdo_util.getvertices(b.mbr)) v
  115.              )';
  116.    -- Create new diminfo structure from existing data
  117.    -- Update the diminfo with the MBR of the existing data
  119.            v_sql
  120.       INTO v_diminfo
  121.      USING /*:X*/  v_diminfo(1).sdo_dimname, /*:T*/ v_diminfo(1).sdo_tolerance,
  122.            /*:Y*/  v_diminfo(2).sdo_dimname, /*:T*/ v_diminfo(2).sdo_tolerance,
  123.            /*:LRS*/ v_lrs_dim,               /*:T*/ CASE WHEN v_lrs_dim <> 0 THEN v_diminfo(v_lrs_dim).sdo_tolerance ELSE v_tz END,
  124.            /*:X*/  v_diminfo(1).sdo_dimname, /*:T*/ v_diminfo(1).sdo_tolerance,
  125.            /*:Y*/  v_diminfo(2).sdo_dimname, /*:T*/ v_diminfo(2).sdo_tolerance,
  126.            /*:TZ*/ v_tz,
  127.            /*:TM*/ v_tm,
  128.            /*:X*/  v_diminfo(1).sdo_dimname, /*:T*/ v_diminfo(1).sdo_tolerance,
  129.            /*:Y*/  v_diminfo(2).sdo_dimname, /*:T*/ v_diminfo(2).sdo_tolerance,
  130.            /*F*/   v_mbr_xy_factor,v_mbr_xy_factor,v_mbr_xy_factor,v_mbr_xy_factor;
  131.   -- Now update the existing record
  132.   --
  133.   IF ( v_metadata ) THEN
  134.     UPDATE user_sdo_geom_metadata
  135.        SET diminfo     = v_diminfo
  136.      WHERE TABLE_NAME  = UPPER(p_table_name)
  137.        AND column_name = UPPER(p_column_name);
  138.   ELSE
  139.     INSERT INTO user_sdo_geom_metadata(TABLE_NAME,COLUMN_NAME,DIMINFO,SRID)
  140.          VALUES (p_table_name,p_column_name,v_diminfo,v_srid);
  141.   END IF;
  142.   -- Commit if requested
  143.   IF ( p_commit ) THEN
  144.     commit;
  145.   END IF;
  146.   RETURN;
  147. END UpdateSdoMetadata;
  148. /
  149. SHOW errors
  150. -- Testing...
  151. -- 3D Point with M in 3rd ordinate position
  152. DROP TABLE foo ;
  153. DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM';
  154. commit;
  155. CREATE TABLE foo ( id INTEGER, geom sdo_geometry );
  156. -- Points ...
  157. INSERT INTO foo VALUES(1,sdo_geometry(3301,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(1,2,3)));
  158. INSERT INTO foo VALUES(2,sdo_geometry(3301,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(10,20,30)));
  159. EXECUTE UpdateSdoMetadata( 'FOO','GEOM',1,0.05,NULL,0.005,TRUE);
  160. SELECT DIMINFO FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM';
  161. DIMINFO
  162. ------------------------------------------------------------------------------------------------------------
  163. 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))
  164. -- 4D Point M 4th ordinate
  166. DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM';
  167. COMMIT;
  168. INSERT INTO foo VALUES(1,sdo_geometry(4401,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(1,2,3,4)));
  169. INSERT INTO foo VALUES(2,sdo_geometry(4401,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(10,20,30,40)));
  170. commit;
  171. EXECUTE UpdateSdoMetadata( 'FOO','GEOM',0,0.05,0.005,0.0005,TRUE);
  172. SELECT DIMINFO FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM';
  173. DIMINFO
  174. ---------------------------------------------------------------------------------------------------------------------------------------------
  175. 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))
  176. -- Lines
  178. DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM';
  179. COMMIT;
  180. 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)));
  181. 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)));
  182. 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)));
  183. 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)));
  184. EXECUTE UpdateSdoMetadata( 'FOO','GEOM',0.0,0.0005,NULL,NULL,TRUE);
  185. SELECT DIMINFO FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM';
  186. DIMINFO
  187. ---------------------------------------------------------------------------------------------------
  188. SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',380000,390000,0.0005),SDO_DIM_ELEMENT('Y',5100000,5167889,0.0005))
  189. -- LRS
  190. DELETE FROM foo;
  191. DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM';
  192. COMMIT;
  193. 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)));
  195. 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)));
  196. 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)));
  197. EXECUTE UpdateSdoMetadata( 'FOO','GEOM',0,0.05,0.005,0.0005,TRUE);
  198. SELECT DIMINFO FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM';
  199. DIMINFO
  200. -------------------------------------------------------------------------------------------------------------
  201. 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))
  202. -- Create and update
  204. DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM';
  205. COMMIT;
  206. INSERT INTO foo VALUES(1,sdo_geometry(4401,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(1,2,3,4)));
  207. INSERT INTO foo VALUES(2,sdo_geometry(4401,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(10,20,30,40)));
  208. commit;
  209. EXECUTE UpdateSdoMetadata( 'FOO','GEOM',0,0.05,0.005,0.0005,TRUE);
  210. SELECT DIMINFO FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM';
  211. INSERT INTO foo VALUES(3,sdo_geometry(4401,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(100,200,300,400)));
  212. EXECUTE UpdateSdoMetadata( 'FOO','GEOM',0,0.05,0.005,0.0005,TRUE);
  213. SELECT DIMINFO FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'FOO' AND column_name = 'GEOM';
  214. DIMINFO
  215. ---------------------------------------------------------------------------------------------------------------------------------------------
  216. 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))
  217. DIMINFO
  218. -------------------------------------------------------------------------------------------------------------------------------------------------
  219. 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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [5]

Thank you !

Pat 26 March 2010, 13:58 #

Hello Simon,

The proc above takes forever for large tables.

Ronald 7 June 2017, 01:46 #

Indeed, you could use EXTENT_OF, and where you want the extent of all the data, and the column is not indexed, you probably should use it.

I guess I tend to treat the SDO_TUNE package as being from those versions of Oracle before SDO_AGGR_MBR and etc. I like the flexibility of getting MBRs based on selections of rows in a table. For example, you could use the SAMPLE keyword against a table in a FROM clause to get the MBR of a 10% sample (for example).

Note that getting the values right in the DIMINFO array is no longer necessary because of the wholesale use of RTrees. The DIMINFO sdo_lb/ub is mainly used by GIS applications to get what it thinks is the MBR of all the data (if may not be the case of course). Oracle Spatial really only uses the sdo_tolerance in the DIMINFO structure for comparing ordinates.

If you are using Quadtrees then you have to set the values so that the MBR they define cover ALL the data.

If the SRID of the sdo_geometry column is projected, and the column is indexed, I would always extract the MBR for the layer from the root MBR of the spatial index.

If it is geodetic, you have to use either my method or the EXTENT_OF function.


Simon Greener 10 June 2017, 18:16 #

Hello Simon, I need this CODE but make 3D spatial Index , SRID=4326

ALDO 4 April 2019, 00:58 #

3D support could be added to the above code fairly easily.

Simon Greener 4 April 2019, 12:07 #