Today it should be a matter of geometries that are not Simple Feature are compliant to correct where possible.
for such correction is available in 11gR1 the function RECTIFY_GEOMETRY SDO_UTIL in the package, which is the corrected (Rectified) geometry returns.
A look at the Oracle Spatial on-line documentation reveals the process of the mistakes are correctable:
- double points (based on the tolerance)
- self-intersecting polygons
- wrong orientation of inner and / or outer ring (s) of a polygon
finds there is also the format described.
SDO_UTIL.RECTIFY_GEOMETRY (geom IN SDO_GEOMETRY tolerance IN NUMBER) RETURN SDO_GEOMETRY;
how can we bring this function in application? Let us simply more accurate.
First, a table is created in which all faulty geometries are to be registered.
drop table geometry_errors purge, - create create error table table geometry_errors (table_name varchar2 (30), column_name varchar2 (30), obj_rowid rowid, geometry SDO_GEOMETRY, tolerance number, error_code char (5), error_message varchar2 (256) error_context varchar2 (256) is_fixed number (1) default 0);
The actual procedure for checking the geometry, would look as follows. The problematic geometries below that are included in the error table.
- procedure to check for invalid geometries in the whole scheme - Warning: Can some time BENEFIT! declare DEFAULT_TOLERANCE number: = 0.0000005; COMMIT_FREQUENCY number: = 100; geom_cursor sys_refcursor; v_diminfo sdo_dim_array; v_srid number; v_tolerance number; v_rowid rowid; v_geometry SDO_GEOMETRY; v_num_rows number; v_num_errors number; v_error_code char (5); v_error_message varchar2 (256); v_error_context varchar2 (256); v_status varchar2 (256) begin - All tables with SDO_GEOMETRY columns litigate for t in (select table_name, column_name from user_tab_columns where data_type = 'SDO_GEOMETRY' order by table_name, column_name) loop - begin Lies tolerance from the metadata select diminfo, srid into v_diminfo, v_srid USER_SDO_GEOM_METADATA from table_name where column_name = = t.table_name and t.column_name; exception when NO_DATA_FOUND then v_diminfo: = null; v_srid: = null end; - In the absence of metadata, default tolerance is accept if v_diminfo v_tolerance then zero: = DEFAULT_TOLERANCE; else v_tolerance = v_diminfo (1) sdo_tolerance; end if; - geometries litigate v_num_rows. = 0; v_num_errors: = 0 ; geom_cursor open for 'select rowid,' : = Sqlerrm (v_error_code); v_error_context: = substr (v_status, 7); else v_error_code: = v_status; v_error_message: = null; v_error_context: = null; end IF; - Fehler wegschreiben geometry_errors INSERT INTO (table_name, column_name, obj_rowid , geometry, tolerance, ERROR_CODE, error_message, error_context) Values (t.table_name, t.column_name, v_rowid, v_geometry, v_tolerance, v_error_code, v_error_message, v_error_context ) End if; - commit commit according COMMIT_FREQUENCY if mod (v_num_rows, COMMIT_FREQUENCY) = 0 then; end if; end loop; end loop; - Final commit commit end; / <>should be noted that the For construction can lead to the procedure for a while claim, depending on the number of geometries to be tested;.
For the correction is now the function SDO_UTIL.RECTIFY_GEOMETRY used.
- Error in Table, Status, and Error Correction Code select table_name, is_fixed, error_code, error_message, count (*) from table_name group by geometry_errors, is_fixed, error_code, error_message order by table_name, is_fixed, error_code, - error after correction and error status CodeSelect is_fixed, error_code, error_message, count (*) from geometry_errors group by is_fixed, error_code, error_message order by is_fixed, error_code, - Details select table_name, obj_rowid, is_fixed, error_message, error_context from geometry_errors order by is_fixed, table_name, obj_rowid;
may not have corrected all erroneous geometries in this way are but a first step towards quality improvement is done. Who
not the fault table after correction needed should, by the way do not forget to remove it again. drop table purge geometry_errors;
in the database. I had one. Gpx file that looks something like this ... 
is now on the view not a spatial index, but this is rightly so, to a view index can not be set. The reason is the Spatial Metadata View 
