SDO_GEOM.VALIDATE_LAYER_WITH

Format

SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT(     
  geom_table      IN VARCHAR2,      
  geom_column     IN VARCHAR2,      
  result_table    IN VARCHAR2,      
  commit_interval IN NUMBER DEFAULT -1,       
  conditional     IN VARCHAR2 DEFAULT 'TRUE' ,      
  flag10g         IN VARCHAR2 DEFAULT 'FALSE',      
  geom_schema     IN VARCHAR2 DEFAULT NULL);

Description

Examines a geometry column to determine if the stored geometries follow the defined rules for geometry objects, and returns context information about any invalid geometries.

Parameters

geom_table

Spatial geometry table. Can be specified in schema.table format (for example, scott.shapes), unless the geom_schema parameter is specified.

geom_column

Geometry object column to be examined.

result_table

Result table to hold the validation results. A row is added to result_table for each invalid geometry. If there are no invalid geometries, one or more (depending on the commit_interval value) rows with a result of DONE are added.

commit_interval

Number of geometries to validate before Spatial performs an internal commit operation and writes a row with a result of DONE to result_table (if no rows for invalid geometries have been written since the last commit operation). If commit_interval is not specified, no internal commit operations are performed during the validation.

The commit_interval option is helpful if you want to look at the contents of result_table while the validation is in progress.

conditional

Conditional flag; relevant only for a three-dimensional composite surface or composite solid. A string value of TRUE (the default) causes validation to fail if two outer rings are on the same plane and share an edge; a string value of FALSE does not cause validation to fail if two outer rings are on the same plane and share an edge.

flag10g

Oracle Database 10g compatibility flag. A string value of TRUE causes only validation checks specific to two-dimensional geometries to be performed, and no 3D-specific validation checks to be performed. A string value of FALSE (the default) performs all validation checks that are relevant for the geometries. (See the Usage Notes for the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function for more information about the flag10g parameter.)

geom_schema

Specifies the schema of the geom_table table, if it differs from the current schema. If geom_schema is specified, the geom_table parameter cannot be specified in schema.table format.

Usage Notes

You should validate all geometry data, and fix any validation errors, before performing any spatial operations on the data, as explained in Recommendations for Loading and Validating Spatial Data.

This procedure loads the result table with validation results.

An empty result table (result_table parameter) should be created before calling this procedure. The format of the result table is: (sdo_rowid ROWID, result VARCHAR2(2000)). If result_table is not empty, it is automatically truncated by the procedure before any rows are added.

The result table contains one row for each invalid geometry. A row is not written if a geometry is valid, except as follows:

If commit_interval is not specified (or if the commit_interval value is greater than the number of geometries in the layer) and no invalid geometries are found, a single row with a RESULT value of DONE is written.
If commit_interval is specified and if no invalid geometries are found between an internal commit and the previous internal commit (or start of validation for the first internal commit), a single row with the primary key of the last geometry validated and a RESULT value of DONE is written. (If there have been no invalid geometries since the last internal commit operation, this row replaces the previous row that had a result of DONE.)

In each row for an invalid geometry, the SDO_ROWID column contains the ROWID value of the row containing the invalid geometry, and the RESULT column contains an Oracle error message number and the context of the error (the coordinate, edge, or ring that causes the geometry to be invalid). You can then look up the error message for more information about the cause of the failure.

This procedure performs the following checks on each geometry in the layer (geom_column):

All type consistency and geometry consistency checks that are performed by the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function (see the Usage Notes for that function).
The geometry's SRID value (coordinate system) is the same as the one specified in the applicable DIMINFO column value (from the USER_SDO_GEOM_METADATA view, which is described in Geometry Metadata Views).

Examples

The following example validates the geometry objects stored in the SHAPE column of the COLA_MARKETS table. The example includes the creation of the result table. For this example, a deliberately invalid geometry was inserted into the table before the validation was performed.

-- Is a layer valid? (First, create the result table.)
CREATE TABLE val_results (sdo_rowid ROWID, result varchar2(1000));
-- (Next statement must be on one command line.)
CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('COLA_MARKETS','SHAPE','VAL_RESULTS');

Call completed.

SQL> SELECT * from val_results;

SDO_ROWID                                                                       
------------------                                                              
RESULT                                                                          
--------------------------------------------------------------------------------
                                                                                
Rows Processed <12>                                                             
                                                                                
AAABXNAABAAAK+YAAC                                                              
13349 [Element <1>] [Ring <1>][Edge <1>][Edge <3>]