Go to content Go to navigation and search

Home

Free Software Download

Download Code, Packages and Installers

Object Documentation

Package Documentation

CENTROID
COGO
EXPORTER
GEOM
KML
NETWORK
SDO_ERROR
Spatial Companion For Oracle (SC4O)
TOOLS

Articles

    New versions of LINEAR, CENTROID and GEOM etc packages
    TESSELATE and CENTROID package updates
    Applying and Extending Oracle Spatial: Source Code Problem
    CENTROID package use with ESRI's sde.st_geometry
    Changes to CENTROID and TESSELATE packages
    CENTROID Package now returns centroid of multi-linestring
    New versions of PL/SQL packages uploaded
    New Release of Free PL/SQL Packages
    New version of PL/SQL packages released
    New Version PL/SQL Spatial packages released
    New PL/SQL Package Upload
    PL/SQL Spatial Types and Packages
    Update to PL/SQL Packages
    New PL/SQL Packages
    VB6 Oracle Spatial or Locator OO4O code
    Spatial PL/SQL Packages by Example
    Catalog Registry Tool
    Image Catalog Tool
    Oracle Spatial PL/SQL and VB6 Source Code


Search

Browse

RSS / Atom

Email me

textpattern

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.

TOOLS

Monday August 15 2011 at 06:30

This package mainly contains utility programs that can do things like:

  • Create SDO_GEOM_METADATA records from scratch;
  • Discover SRID;
  • Discover Dimensions in sdo_geometry column;
  • Discover Geometry/Spatial Type (for use in spatial indexing);
  • Discover minimum tolerance of an sdo_geometry column;
  • Analyse vertices;
  • Run a battery of sdo_geometry tests to check sdo_geometry data integrity (including fixing); This can be used with the Scheduler to create a job that checks geometries that were edited during the day; Results are stored in a set of metadata tables;
  • Carry out performance testing using randomly generated search windows.

DEFINE defaultSchema = '&1'
 
create or replace Package TOOLS
AUTHID CURRENT_USER
Is
 
   Function isCompound( p_sdo_elem_info in mdsys.sdo_elem_info_array )
     return integer deterministic;
 
   /*** @function    Execute_Statement
   **   @description Executes a SQL statement capturing errors.
   **   @param       p_sql     The SQL statement to be executed.
   **   @param       p_display Whether to write any errors to dbms_output.
   **/
   Procedure Execute_Statement( p_sql     IN VarChar2,
                                p_display IN Boolean := FALSE);
 
   /*** @function    GeometryCheck
   **   @description Procedure that processes the supplied object looking
   **                for errors and correcting where possible.
   **                Writes activity to FEATURE_ERRORS table.
   **   @param       p_schema      The owner of the table/geometry column data.
   **   @param       p_tableName   The table holding the geometry data to be checked.
   **   @param       p_ColumnName  The sdo_geometry column in the table to be checked.
   **   @param       p_whereClause A predicate to limit the activity to specific rows.
   **/
   Procedure GeometryCheck( p_schema        IN VarChar2,
                            p_tableName     IN VarChar2,
                            p_ColumnName    IN VarChar2,
                            p_whereClause   IN VarChar2);
 
   /** @function    VertexAnalyzer
   *   @description Function that computes basic statistics about the geometries in a table.
   *   The stats computed are:
   *     - Max,
   *     - Min
   *     - Avg number of vertices.
   * @param p_owner       Schema that owns the table.
   * @param p_table_regex Regular expression of the tables to be processed.
   **/
   Procedure VertexAnalyzer( p_owner       In VarChar2 := NULL,
                             p_table_regex IN VarChar2 := '*',
                             p_activity    In Out NoCopy &&defaultSchema..VarChar2_table );
 
   /** @function    hasData
   *   @description Simply checks if the table has any data in it.
   *   @param       p_table_name : varchar2 : Object name.
   *   @param       p_owner      : varchar2 : The schema that holds the table.
   */
   Function hasData( p_table_name In VarChar2,
                     p_owner      In VarChar2 := NULL)
     Return Boolean Deterministic;
 
   /** @function    Generate_Object_Name
   *   @description Generates index/constraint name following a fixed pattern.
   *   @param       p_object_name   : varchar2 : Object name normally a table.
   *   @param       p_column_name   : varchar2 : Normally the name of a column.
   *   @param       p_obj_shortname : varchar2 : An abbreviation of the object
   *   @param       p_col_shortname : varchar2 : An abbreviation of the column
   *   @param       p_prefix        : varchar2 : A prefix normally of a few chars in length.
   *   @param       p_suffix        : varchar2 : A suffix normally of a few chars in length.
   *   @param       p_name_length   : varchar2 : Max length of the desired name.
   **/
   Function Generate_Object_Name ( p_object_name   IN VARCHAR2,
                                   p_column_name   IN VARCHAR2,
                                   p_obj_shortname IN VARCHAR2,
                                   p_col_shortname IN VARCHAR2,
                                   p_prefix        IN VARCHAR2,
                                   p_suffix        IN VARCHAR2,
                                   p_name_length   IN PLS_INTEGER := 30 )
     Return VarChar2 Deterministic;
 
   /** @function    NonLeaf_Spatial_IndexName
   *   @description Gets name of Spatal Index NonLeaf component as well as its size.
   *   @param       p_spindex_name : varchar2 : The user's name of the spatial index.
   *   @param       p_nl_size      : NUMBER : The size of the non-leaf index.
   *   @param       p_owner        : varchar2 : The schema that owns the object.
   *   @param       p_pin          : boolean : Flag saying whether to pin the index in memory.
   */
   Function NonLeaf_Spatial_IndexName( p_spindex_name IN VARCHAR2,
                                       p_nl_size      In Out NoCopy Number,
                                       p_owner        In VarChar2 := NULL,
                                       p_pin          In BOOLEAN  := FALSE )
     Return VarChar2 Deterministic;
 
   /** @function    Discover_SpatialType
   *   @description Processes table/sdo_geometry column to discover type of spatial data it contains.
   *   @param       p_table_name  : varchar2 : The object containing the spatal data.
   *   @param       p_column_name : varchar2 : The sdo_geometry column to be analyzed.
   *   @param       p_owner       : varchar2 : Schema that owns the table.
   *   @param       p_activity    : VarChar2_Table : Array of debug activities that can be used to discover how the procedure processed its data.
   */
   Function Discover_SpatialType( p_table_name  In VarChar2,
                                  p_column_name In VarChar2,
                                  p_owner       In VarChar2 := NULL,
                                  p_activity    In Out NoCopy &&defaultSchema..VarChar2_table )
     Return VarChar2 Deterministic;
 
   /** @function    Discover_Dimensions
   *   @description Processes table/sdo_geometry column to discover dimensionality of the spatial data
   *   @param       p_table_name  The object containing the spatal data.
   *   @param       p_column_name The sdo_geometry column to be analyzed.
   *   @param       p_owner       Schema that owns the table.
   *   @param       p_default_dim Default to return if no data.
   *   @param       p_activity    Array of debug activities that can be used to discover how the procedure processed its data.
   */
   Function Discover_Dimensions( p_table_name  IN VarChar2,
                                 p_column_name IN VarChar2,
                                 p_owner       IN VarChar2 := NULL,
                                 p_default_dim IN Number   := 2,
                                 p_activity    In Out NoCopy &&defaultSchema..VarChar2_table )
     Return Number Deterministic;
 
   /** @function    Discover_SRID
   *   @description Processes table/sdo_geometry column to discover SRID of the spatial data
   *   @param       p_table_name  : varchar2 : The object containing the spatal data.
   *   @param       p_column_name : varchar2 : The sdo_geometry column to be analyzed.
   *   @param       p_owner       : varchar2 : Schema that owns the table.
   *   @param       p_activity    : VarChar2_Table : Array of debug activities that can be used to discover how the procedure processed its data.
   */
   Function Discover_SRID( p_table_name  In VarChar2,
                           p_column_name In VarChar2,
                           p_owner       In VarChar2 := NULL,
                           p_activity    In Out NoCopy &&defaultSchema..VarChar2_table )
     Return Number Deterministic;
 
   /** @function    UpdateSdoMetadata
   *   @description Updates 2D spatial extent of DIMINFO associated with table/column in all_sdo_geom_metadata
   *   @param       p_table_name  : varchar2 : The object containing the spatal data.
   *   @param       p_column_name : varchar2 : The sdo_geometry column to be analyzed.
   *   @param       p_mbr_factor  : number   : Expansion/Shrinkage amount for MBR of current data.
   *   @param       p_commit      : boolean  : Whether to commit the update.
   */
   Procedure UpdateSdoMetadata( p_table_name  in varchar2,
                                p_column_name in varchar2,
                                p_mbr_factor  in number,
                                p_commit      in boolean := false );
 
   /** @function    GetSpatialIndexName
   *   @description Gets name of the spatial index associated with a table/column
   *   @param       p_table_name  : varchar2 : The object containing the spatal data.
   *   @param       p_column_name : varchar2 : The sdo_geometry column to be analyzed.
   *   @param       p_owner       : varchar2 : Schema that owns the table.
   */
   Function GetSpatialIndexName( p_table_name  In VarChar2,
                                 p_column_name In VarChar2,
                                 p_owner       In VarChar2 := NULL )
     Return VarChar2 Deterministic;
 
   /** @function    DropSpatialIndex
   *   @description Finds and drops spatial index associated with a table/column.
   *   @param       p_table_name  : varchar2 : The object containing the spatal data.
   *   @param       p_column_name : varchar2 : The sdo_geometry column whose index we want to drop.
   *   @param       p_owner       : varchar2 : Schema that owns the table.
   */
   Procedure DropSpatialIndex( p_table_name  In VarChar2,
                               p_column_Name In VarChar2,
                               p_owner       In VarChar2 := NULL);
 
   /** @function    SpatialIndexer
   *   @description Procedure that can be used to spatially index a
   *                single table/sdo_geometry column.
   *                Will also analyze the index.
   *   @param       p_table_name      : varchar2 : The object containing the spatal data.
   *   @param       p_column_name     : varchar2 : The sdo_geometry column to be analyzed.
   *   @param       p_owner           : varchar2 : Schema that owns the table.
   *   @param       p_spatial_type    : varchar2 : layer_gtype parameter string value. If NULL Discover_SpatialType is called.
   *   @param       p_check           : boolean  : Check table has metadata and has data before indexing.
   *   @param       p_dimensions      : number   : Dimensionality of data in p_column_name (see Discover_Dimensions)
   *   @param       p_tablespace      : varchar2 : For 10g and above, tablespace to hold index data.
   *   @param       p_work_tablespace : varchar2 : For 10g and above, work tablespace as index is built.
   *   @param       p_pin_non_leaf    : boolean  : If set non leaf index is created and pinned into memory.
   *   @param       p_stats_percent   : number   : If > 0 causes index to be analyzed.
   *   @param       p_activity        : VarChar2_Table : Array of debug activities that can be used to discover how the procedure processed its data.
   */
   Procedure SpatialIndexer( p_table_name      In VarChar2,
                             p_column_name     In VarChar2,
                             p_owner           In VarChar2    := NULL,
                             p_spatial_type    In VarChar2    := NULL,
                             p_check           In Boolean     := FALSE,
                             p_dimensions      In Number      := 2,
                             p_tablespace      In VarChar2    := NULL,
                             p_work_tablespace In VarChar2    := NULL,
                             p_pin_non_leaf    In Boolean     := FALSE,
                             p_stats_percent   In PLS_INTEGER := 0,
                             p_activity        In Out NoCopy &&defaultSchema..VarChar2_table );
 
   /** @function    SpatialIndexUnindexed
   *   @description Procedure that can be used to spatially index those objects with no existing index.
   *   @param       p_owner           : varchar2 : Schema that owns the objects to be indexed. If NULL the sys_context(...,CurrentUser)
   *   @param       p_check           : varchar2 : Check table has metadata and has data before indexing.
   *   @param       p_tablespace      : varchar2 : For 10g and above, tablespace to hold index data.
   *   @param       p_work_tablespace : varchar2 : For 10g and above, work tablespace as index is built.
   *   @param       p_pin_non_leaf    : booelan  : If set non leaf index is created and pinned into memory.
   *   @param       p_stats_percent   : number   : If > 0 causes index to be analyzed.
   **/
   Procedure SpatialIndexUnindexed( p_owner           In VarChar2    := NULL,
                                    p_check           In Boolean     := FALSE,
                                    p_tablespace      In VarChar2    := NULL,
                                    p_work_tablespace In VarChar2    := NULL,
                                    p_pin_non_leaf    In Boolean     := FALSE,
                                    p_stats_percent   In PLS_INTEGER := 0 );
 
   /** @function    MeadataAnalyzer
   *   @description Procedure that can be used to discover sdo_geom_metadata including sdo_tolerance, generates spatial indexes etc.
   *   @param       p_owner           : varchar2 : Schema that owns the objects to be indexed. If NULL the sys_context(...,CurrentUser)
   *   @param       p_table_regex     : varchar2 : Regular expression used to select tables for processing (10g and above)
   *   @param       p_fixed_srid      : varchar2 : If data is from one SRID, user can set it.
   *   @param       p_fixed_diminfo   : SDO_DIM_ARRAY : If user wants to apply a single diminfo structure to processed tables.
   *   @param       p_tablespace      : varchar2 : For 10g and above, tablespace to hold index data.
   *   @param       p_work_tablespace : varchar2 : For 10g and above, work tablespace as index is built.
   *   @param       p_pin_non_leaf    : boolean  : If set non leaf index is created and pinned into memory.
   *   @param       p_stats_percent   : PLS_Integer : If > 0 causes index to be analyzed.
   *   @param       p_min_projected_tolerance : boolean : The smallest tolerance after which tolerance discovery stops.
   *   @param       p_rectify_geometry : boolean  : Attempt to correct invalid geometries
   **/
   Procedure MetadataAnalyzer( p_owner                   IN VARCHAR2            := NULL,
                               p_table_regex             IN VARCHAR2            := '*',
                               p_column_regex            IN VARCHAR2            := '*',
                               p_fixed_srid              IN NUMBER              := -9999,
                               p_fixed_diminfo           IN MDSYS.SDO_DIM_ARRAY := NULL,
                               p_tablespace              IN VARCHAR2            := NULL,
                               p_work_tablespace         IN VARCHAR2            := NULL,
                               p_pin_non_leaf            IN BOOLEAN             := FALSE,
                               p_stats_percent           IN PLS_INTEGER         := 100,
                               p_min_projected_tolerance IN NUMBER              := 0.00005,
                               p_rectify_geometry        IN BOOLEAN             := FALSE );
 
   /** @function    RandomSearchByExtent
   *   @description Procedure that can help for independent testing of the performance of a table/geometry column 
   *                perhaps when spatially indexing, reorganising data, rounding ordinates etc.
   *   @param       p_schema          : varchar2 : Schema that owns the object to be searched.
   *   @param       p_table_name      : varchar2 : The object containing the spatal data for which we want to gather stats.
   *   @param       p_column_name     : varchar2 : The sdo_geometry column to be searched.
   *   @param       p_number_searches : number : Number of times to execute each search.
   *   @param       p_window_set      : WindowSetType : Set of search "windows"
   *   @param       p_no_zeros        : boolean : TRUE => zero features searches ignored
   *   @param       p_sdo_anyinteract : boolean : Use Sdo_AnyInteract rather than SDO_FILTER
   *   @param       p_count_vertices  : boolean : Force code to actually process geometry data.
   *   @param       p_debug_detail    : boolean : Don't bother displaying individual search stats
   *   @param       p_min_pixel_size  : number  : Include min_resolution=p_min_pixel_size in search
   **/
   Procedure RandomSearchByExtent(p_schema          In VarChar2,
                                  p_table_name      In VarChar2,
                                  p_column_name     In VarChar2,
                                  p_number_searches In Number  := 100,
                                  p_window_set      In &&defaultSchema..WindowSetType := &&defaultSchema..WindowSetType(500,1000,2000,3000,4000,5000,10000,20000,50000),
                                  p_no_zeros        In Boolean := TRUE,
                                  p_sdo_anyinteract In Boolean := FALSE,
                                  p_count_vertices  in Boolean := FALSE,
                                  p_debug_detail    In Boolean := FALSE,
                                  p_min_pixel_size  In Number  := NULL );
 
END TOOLS;

The metadata tables that are required for this package are:

DROP TABLE COLUMN_ANALYSES;
DROP TABLE COLUMN_analysis_summaries;
DROP TABLE FEATURE_ERRORS;
DROP TABLE FEATURE_ERROR_SUMMARIES;
DROP TABLE MANAGED_COLUMNS;
DROP SEQUENCE MANAGED_COLUMNS_ID;
CREATE SEQUENCE MANAGED_COLUMNS_ID START WITH 1 INCREMENT BY 1 NOCACHE;
GRANT SELECT ON MANAGED_COLUMNS_ID TO PUBLIC;
CREATE TABLE MANAGED_COLUMNS (
  ID                      INTEGER,
  owner                   VARCHAR2(32),
  table_name              VARCHAR2(32),
  column_name             VARCHAR2(32),
  min_vertices            NUMBER,
  avg_vertices            NUMBER,
  max_vertices            NUMBER,
  vertex_date             DATE
);
GRANT INSERT,UPDATE,DELETE,SELECT 
   ON MANAGED_COLUMNS 
   TO PUBLIC;
ALTER TABLE MANAGED_COLUMNS 
  ADD CONSTRAINT MANAGED_COLUMNS_PK
      PRIMARY KEY (ID);
ALTER TABLE MANAGED_COLUMNS 
  ADD CONSTRAINT MANAGED_COLUMNS_MUNQ
      UNIQUE (owner, table_name, column_name);
ALTER TABLE MANAGED_COLUMNS 
  ADD CONSTRAINT MANAGED_COLUMNS_FK
      FOREIGN KEY (owner, table_name, column_name) 
      REFERENCES ALL_SDO_GEOM_METADATA(owner, table_name, column_name) ;
-- Need ON CASCADE DELETE?
 
Prompt Table for reporting result of the running the Tool.MetadataAnalysis procedure
Prompt against a particular table.
DROP SEQUENCE COLUMN_ANALYSES_ID;
CREATE SEQUENCE COLUMN_ANALYSES_ID START WITH 1 INCREMENT BY 1 NOCACHE;
GRANT SELECT ON COLUMN_ANALYSES_ID TO PUBLIC;
CREATE TABLE COLUMN_ANALYSES (
  ID                INTEGER,
  Managed_Column_ID INTEGER,
  analysis_date     TIMESTAMP,
  result            VARCHAR2(4000)
);
GRANT INSERT,UPDATE,DELETE,SELECT 
   ON COLUMN_ANALYSES 
   TO PUBLIC;
ALTER TABLE COLUMN_ANALYSES 
  ADD CONSTRAINT COLUMN_ANALYSES_PK
      PRIMARY KEY (ID);
ALTER TABLE COLUMN_ANALYSES 
  ADD CONSTRAINT COLUMN_ANALYSES_FK
      FOREIGN KEY (Managed_Column_ID)
      REFERENCES MANAGED_COLUMNS(ID) 
      ON DELETE CASCADE;
 
DROP SEQUENCE COLUMN_ANALYSIS_SUMMARIES_ID;
CREATE SEQUENCE COLUMN_ANALYSIS_SUMMARIES_ID START WITH 1 INCREMENT BY 1 NOCACHE;
GRANT SELECT ON COLUMN_ANALYSIS_SUMMARIES_ID TO PUBLIC;
CREATE TABLE COLUMN_ANALYSIS_SUMMARIES (
  ID                      INTEGER,
  Managed_Column_ID       INTEGER,
  analysis_process_start  DATE,
  analysis_process_end    DATE
);
GRANT INSERT,UPDATE,DELETE,SELECT 
   ON COLUMN_analysis_summaries
   TO PUBLIC;
ALTER TABLE COLUMN_analysis_summaries
  ADD CONSTRAINT COLUMN_analysis_summaries_PK
      PRIMARY KEY (ID);
ALTER TABLE COLUMN_analysis_summaries
  ADD CONSTRAINT COLUMN_analysis_summaries_FK
      FOREIGN KEY (Managed_Column_ID)
      REFERENCES MANAGED_COLUMNS(ID) 
      ON DELETE CASCADE;
 
DROP SEQUENCE FEATURE_ERRORS_ID;
CREATE SEQUENCE FEATURE_ERRORS_ID START WITH 1 INCREMENT BY 1 NOCACHE;
GRANT SELECT ON FEATURE_ERRORS_ID TO PUBLIC;
CREATE TABLE FEATURE_ERRORS (
  ID                 INTEGER,
  Managed_Column_ID  INTEGER,
  feature_rowid      UROWID,
  error_code         VARCHAR2(10),
  error_status       CHAR(1),
  error_context      VARCHAR2(2000),
  error_date         DATE
);
GRANT INSERT,UPDATE,DELETE,SELECT 
   ON FEATURE_ERRORS 
   TO PUBLIC;
ALTER TABLE FEATURE_ERRORS
  ADD CONSTRAINT FEATURE_ERRORS_PK
      PRIMARY KEY (ID);
ALTER TABLE FEATURE_ERRORS 
  ADD CONSTRAINT FEATURE_ERRORS_FK
      FOREIGN KEY (Managed_Column_ID)
      REFERENCES MANAGED_COLUMNS(ID) 
      ON DELETE CASCADE;
 
DROP SEQUENCE FEATURE_ERRORS_SUMMARIES_ID;
CREATE SEQUENCE FEATURE_ERRORS_SUMMARIES_ID START WITH 1 INCREMENT BY 1 NOCACHE;
GRANT SELECT ON FEATURE_ERRORS_SUMMARIES_ID TO PUBLIC;
CREATE TABLE FEATURE_ERROR_SUMMARIES (
  ID                 INTEGER,
  Managed_Column_ID  INTEGER,
  predicate          VARCHAR2(4000),
  process_start      Date,
  process_end        Date,
  process_count      Integer,
  error_total        Integer,
  error_fixed        Integer
);
GRANT INSERT,UPDATE,DELETE,SELECT 
   ON FEATURE_ERROR_SUMMARIES 
   TO PUBLIC;
ALTER TABLE FEATURE_ERROR_SUMMARIES
  ADD CONSTRAINT FEATURE_ERROR_SUMAMRIES_PK
      PRIMARY KEY (ID);
ALTER TABLE FEATURE_ERROR_SUMMARIES 
  ADD CONSTRAINT FEATURE_ERROR_SUMMARIES_FK
      FOREIGN KEY (Managed_Column_Id)
      REFERENCES MANAGED_COLUMNS (ID)
      ON DELETE CASCADE;

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

META HTTP-EQUIV=