Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (20)
- All (400)
- Biography (1)
- Blog (45)
- Business Requirements (1)
- Commentary (1)
- 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) (4)
- Open Source (18)
- Oracle Spatial and Locator (193)
- Partitioning (1)
- PostGIS (34)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (35)
- Space Curves (9)
- Spatial Database Functions (108)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (15)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
TOOLS
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;