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:


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


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.


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.

CURSOR cs is
SELECT pk_id, shape FROM my_spatial_table;
a_geom mdsys.sdo_geometry;
FOR irec in cs loop
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;



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)

i number;
_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;


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;


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.


Want to (just) display data from Oracle or SQLServer in ArcMap? You have to pay for the privilege!

I was just reading Abe Gillespie’s post about the decision to release zigGIS, the Arcmap plugin to access PostGIS data  as open source (again) and it got me thinking:- If you want to display oracle or sql server spatial data in ArcMap you have to also pay for ArcSDE. And it doesn’t come cheap. Bit cheeky this from ESRI I think. Especially since you did NOT have to do this back in ArcGIS 8.x days. The sde libraries were part of the ArcMap product and you could use “direct connect” to simply display the data. (You still had to create the sde schema and owner in your database which was another major pain, but will leave that for now…)

Compare this with the $279 you have to pay now for ziGIS to access PostGIS data (and soon to be $0) and the $0 you pay to display Oracle or SQLServer data using MapServer or QGIS. And none of them requires you to tinker with your database!

Is it just me who thinks that ESRI (“ezree”) got it wrong somewhere along the line? Why do I have to go for a fully-blown ESRI solution? Why should my customers pay for something they will never use? Still though, since ESRI’s ArcMap IS a good product and it IS used by a lot of people, are there  any other brave souls out there who are working for Oracle or SQLServer plugins?