Validating Oracle Spatial layers for ArcSDE

A ‘valid geometry’ is an interesting term. It depends on the program that performs the validation. Take for example Oracle and ArcSDE. ArcSDE has a much more rigid validation regime and hence a feature that is ‘valid’ for Oracle, may be ‘corrupt’ for ArcSDE. On top of that, ArcMap has the incredibly annoying habit of stopping the map rendering as soon as it encounters an invalid geometry. So if you are unlucky enough to have the first geometry in your layer being flagged as invalid, (regardless if the rest of your 999,999 records are valid) you will get a nice, big, empty map.

I get this problem a lot as I often work with Oracle spatial layers which are created outside the warm and fuzzy environment of ArcGIS.

So I put together some tips and tricks to try when either your layer is only half-drawn or not drawn at all. Scripts are mainly focused on measured, linear geometries but could be easily adapted for other geometry types.

The first thing to do is to validate the table using Oracle’s SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT routine. The routine will create a table which hold any invalid geometries and the specific error.

You will need to create the validation table first such as:

create table  validation_tab ( sdo_rowid ROWID, result varchar2(1000));

The run the validation:

BEGIN

sdo_geom.validate_layer_with_context(‘MY_SPATIAL_TABLE’, ‘SHAPE’, ‘VALIDATION_TAB’);

END;

The figure below displays the contents of the validation_tab table. In this case we have 4 records with a ORA-13011 error- value is out of range, which probably means that these shapes are outside the layer’s extent.

validation_tab

In case of ORA-13556 errors (adjacent points in geometry are redundant), you can use this script to fix them (or change the cursor to join to the validation table as below:)

select sdo_rowid, shape from my_spatial_tab a, validation_tab b 
where a.rowid = b.sdo_rowid
and result like ‘13356%’;

The script will remove any duplicate vertices (or within of 0.005m of each other) on the geometries in the table.

DECLARE
CURSOR cs is
SELECT pk_id, shape FROM my_spatial_table;
a_geom mdsys.sdo_geometry;
BEGIN
FOR irec in cs loop
BEGIN
a_geom := SDO_UTIL.REMOVE_DUPLICATE_VERTICES(irec.shape, 0.005);
update my_spatial_table
set shape= a_geom
where pk_id = irec.pk_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;

END LOOP;
END;

 

The following script will flash out and fix any geometries where the coordinate count is less than 6 (less than 2 points basically, since we are talking about 3D shapes-X,Y,M)

DECLARE
i number;
BEGIN
FOR rec IN (SELECT pk
_id, shape FROM my_spatial_table) LOOP
i := 1;
if (rec.shape.sdo_ordinates.count <6) then
DBMS_OUTPUT.PUT_LINE ('Invalid geometry ID: ' || rec.pk_id || ' ' || rec.shape.sdo_ordinates.count);
end if;
END LOOP;
END;

 

Another useful function is the SDO_TUNE_MIX_INFO which will tell you whether all geometries in the layer are of the same type:

SQL> set serverout on;

SQL> EXECUTE SDO_TUNE.MIX_INFO(‘MY_SPATIAL_TABLE’,  ‘SHAPE’);

This will return something like:

Total number of geometries: 24294
Point geometries:        0  (0%)
Curvestring geometries:   24294  (100%)
Polygon geometries:      0  (0%)
Complex geometries:      0  (0%)

PL/SQL procedure successfully completed.

And for more fine tuning on the geometry types, use this select statement to find out whether all geometries are of the same SDO_GTYPE@

select a.shape.sdo_gtype, count(*) from my_spatial_table a group by a.shape.sdo_gtype

After all said and done though, the final check will always be ArcSDE’s own validation command: sdelayer –o feature_info with the –r invalid switch which would give you the definitive answer of whether ArcMap will display the layer. For more information on this command you can refer to ArcGIS Online Help.

2 thoughts on “Validating Oracle Spatial layers for ArcSDE

  1. How can ArcSDE stop a map from rendering? It doesn’t render maps. Clients do – perhaps that’s what you meant? If so, which client? One of the Esri ones or something else?

    1. You are absolutely right. I should have written “…ArcMap has the incredibly annoying habit of stopping the map rendering…” I will change the post accordingly. But as far as validation is concerned, it IS ArcSDE that decides whether a map is valid or not which then ‘sends’ the messages to ArcMap or whichever client

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s