From NGDC Wiki
Jump to: navigation, search

Validate the geometry and SRIDs in an Oracle spatial table

Validate the geometry in a table POLY0 storing the results in a table RESULTS

Note that SDO_VALIDATE_LAYER has been deprecated in favor of SDO_VALIDATE_LAYER_WITH_CONTEXT The results table created is a slightly different format.

  1. Create a table to hold the results
    create table RESULTS (sdo_rowid ROWID, result VARCHAR2(1000));
  2. Validate the layer
    Note (above) that the results table name should be uppercase, otherwise the following puzzling error may result:
    ORA-01403: no data found
    ORA-06512: at "MDSYS.SDO_GEOM", line 3829
    ORA-06512: at line 1
  3. for convenience, you can also create a view showing more meaningful objectids with their corresponding error
    create or replace view RESULTS2 
    as select a.objectid,b.result 
    from POLY0 a, RESULTS b 
    where a.rowid = b.sdo_rowid

Common Error messages reported in the results

ESRI Feature Validation Tools

As of ArcSDE release 9, ESRI provides the checkfeatures command to check the validity of any registered oracle layer.

checkfeatures -l <table_name>,<shape_column> [-C <rowid_column>] [-T <output_table> <-k keyword>] -u <username> -p <password> 
[-s <server>] [-i <instance>] [-r <rowcount>] [-E <errcount>] [-v <verbosity>] [-w <where_clause>]

This is located in $SDEHOME/tools/generic/checkfeatures

See knowledge base article 28627 for details.

At 9.2, checkfeatures has been replaced with sdelayer -o feature_info

 sdelayer -o feature_info -l <table,column> [-V <version>]
                  [-r {valid | all | invalid}] [-w <"where_clause">] [-c]
                  [-s <server_name>] [-D <database>]
                  [-u <DB_User_name>] [-p <DB_User_password>] [-N] [-q]

See knowledge base article 28367 for details. Error codes can be found in sdeerno.h.

Invalid geometries can prevent all features from displaying in ESRI clients. See knowledge base article 36047 for details.

ESRI Validation Rules

Rules for points:

  • The area and length of points are set to 0.0.
  • A single point’s envelope is equal to the point’s x,y values.
  • The envelope of a multipart point shape is set to the minimum bounding box.

Rules for simple lines, or linestrings:

  • Sequential duplicate points are removed.
  • Each part must have at least two distinct points.
  • Each part may not intersect itself. The start and end points may be the same, but the resulting ring is not treated as an area shape.
  • Parts may touch each other at the end points.
  • The length is the sum of all the parts.
  • Lines can intersect themselves.

Rules for lines, or spaghetti features:

  • Each part must have at least two distinct points.
  • Sequential duplicate points are deleted.
  • The length is the length of all of its parts added together.

Rules and operations on area features:

  • Delete duplicate sequential occurrences of a coordinate point.
  • Verify that the line segments close (z-coordinates at start and end points must also be the same) and don’t cross.
  • Correct rotation to counterclockwise. See the previous section for an explanation of how ArcSDE stores area shapes.
  • For area shapes with holes, make sure holes reside inside the outer boundary. ArcSDE eliminates any holes that are outside the outer boundary.
  • Convert a hole that touches an outer boundary at a single common point into an inversion of the area shape.
  • Combine multiple holes that touch at common points into a single hole.
  • Multipart area shapes may not overlap; however, two parts may touch at a point.
  • If two parts have a common boundary, they are merged into one part.
  • Calculate the total feature perimeter, including the boundaries of all holes in donut polygons, and store the perimeter as the length of the feature.
  • Calculate the area.
  • Calculate the envelope.

Oracle Validation Rules

  1. Polygons have at least four points, which includes the point that closes the polygon. (The last point is the same as the first.)
  2. Polygons are not self-crossing.
  3. No two vertices on a line or polygon are the same.
  4. Polygons are oriented correctly. (Exterior ring boundaries must be oriented counterclockwise, and interior ring boundaries must be oriented clockwise.)
  5. An interior polygon ring touches the exterior polygon ring at no more than one point.
  6. If two or more interior polygon rings are in an exterior polygon ring, the interior polygon rings touch at no more than one point.
  7. Line strings have at least two points.
  8. Geometries are within the specified bounds of the applicable DIMINFO column value (from the USER_SDO_GEOM_METADATA view).

In checking for geometry consistency, the function considers the geometry's tolerance value in determining if lines touch or if points are the same.


Influence of SRID on Validation

When dealing with geodetic data in one of the ESRI tools ArcCatalog, ArcMap, ArcIMS, etc.), it is important that any spatial figure validate both in the SRID to which is it assigned as well as the null SRID, even if the null SRID is nowhere to be found in the data or Oracle metadata. This is the case because ESRI will apparently display the data with no regard for the associated SRID.

In ArcMap (and probably ArcCatalog) the following non-helpful error will appear in a dialog box if any individual geometry (aka a single row in the spatial table) is invalid from a null SRID point of view:

One or more layers failed to draw: <name of layer here>

In one enlightening case of the smoke polygons associated with the firedetects map service, a certain geometry would validate if associated with an Oracle SRID of 8307, but not with a null SRID. This can be shown by the query below:

SELECT objectid,
  SDO_GEOM.VALIDATE_GEOMETRY_with_context(a.shape, 0.005) regular_validate,
                         a.shape.sdo_ordinates), 0.00000000000000000001) null_srid_validate
FROM fires.smoke_tsqa a
WHERE a.objectid between 3940 and 3960;

---------- ------------ ------------
      3940 TRUE         TRUE
      3941 TRUE         TRUE
      3942 TRUE         TRUE
      3943 TRUE         TRUE
      3944 TRUE         TRUE
      3945 TRUE         TRUE
      3946 TRUE         TRUE
      3947 TRUE         TRUE
      3948 TRUE         TRUE
      3949 TRUE         TRUE
      3950 TRUE         13349 [Element <1>] [Ring <1>][Edge <21>][Edge <19>]

      3951 TRUE         TRUE
      3952 TRUE         TRUE
      3953 TRUE         TRUE
      3954 TRUE         TRUE
      3955 TRUE         TRUE
      3956 TRUE         TRUE
      3957 TRUE         TRUE
      3958 TRUE         TRUE
      3959 TRUE         TRUE
      3960 TRUE         TRUE

21 rows selected.

Unfortunately, the use of a very, very small tolerance in the call to the sdo_geom.validate_geometry_with_context is required. Ideally, zero would be the best value, but Oracle returns an error if that is used. If you were to validate an existing table using a similar call to sdo_geom.validate_layer_with_context with a zero tolerance value, the query would succeed. Go figure.

In the cheetah DB, fires.all_smoke_tsqa contains some polygons that validate with SRID=8307 but not with SRID=NULL. These have been identified by objectid although this field varies by table and DB; (sid, begtime, endtime) is the primary key and the corresponding primary key needs to be used when working outside this table. Currently the operational fires.smoke_tsqa has the invalid polygons removed. Here are some plots

This polygon causes ArcCatalog to fail to draw image Here is the troublesome area up close, there is a backtrack between points 19 and 22. image

Here is one that does draw, but still matches the mathematical definition of a backtrack. image Zooming in on the backtrack between 2 and 5: image

The problem objectids in cheetah:fires.all_smoke_tsqa are

alter session set NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

select objectid, sid, begtime, endtime
from all_smoke_tsqa
where objectid in (17248, 17811, 18065, 18129, 21181, 21384, 21391, 21668);
---------- ---------- ------------------- -------------------
     17248        112 2004 03 31 22:15:00 2004 03 31 23:45:00 (backtrack)
     17811         51 2004 04 04 21:45:00 2004 04 04 23:15:00 (backtrack)
     18065         23 2004 04 07 06:45:00 2004 04 07 17:45:00 (backtrack)
     18129         25 2004 04 08 06:45:00 2004 04 08 17:45:00 (backtrack, same poly as above)
     21181         10 2004 06 29 19:30:00 2004 06 29 01:00:00 (antimeridian)
     21384         34 2004 07 02 13:00:00 2004 07 02 18:00:00 (antimeridian)
     21391         41 2004 07 02 23:00:00 2004 07 03 02:00:00 (antimeridian)
     21668          4 2004 07 14 18:15:00 2004 07 14 23:45:00 (crossover)

Corresponding plots are: image image image image image image image image

3-Dimensional Data

Please note that the "regular" Oracle SDO functions and procedures will only take the first two dimensions of any shape object into account. This must be considered when dealing with three dimensional data. For example, if a three dimensional "polygon" has a perfectly vertical line segment, then the ol' ORA-13356 (adjacent points in a geometry are redundant) error will appear in an attempted Oracle spatial validation because the two points in the segment are identical in the first two dimensions.

This limitation in the Oracle spatial functions and procedures is documented here, at the very end of the section.

notes on validate_layer_with_context