Data Validation with Esri and Oracle Spatial
By jeanihm-Oracle on Feb 05, 2014
By: Daniel Geringer, Senior Development Manager, Oracle Spatial and Graph
One of the things users can run into when using Esri with Oracle Spatial can be problems with data validation when loading data using Esri utilities into Oracle Spatial data types. From my experience, the solution is a relatively straightforward change in the workflow used to load data.
Here is an excerpt from a user problem where it sounds like Esri is calling Oracle's validation on load (not Esri's validation).
I don't believe Esri's validation would be sensitive to the database version.
- It seems there are tighter tolerances (or the tolerances work) in 220.127.116.11 and features that were not reporting as invalid in 18.104.22.168 are now reporting as invalid features (mainly self-intersecting polygons) in the 22.214.171.124. We are assuming that for some reason the SDE tools are crashing when they hit an invalid feature.
This sounds more like a workflow defined by Esri than differences in validation. I think the issue is once Esri encounters an invalid geometry, it stops loading the rest (even if they are valid). I believe Esri chooses this workflow to ensure all geometries in a layer are valid. I agree the outcome is optimal, and what Oracle recommends too. A deficiency in the workflow is:
1. It prevents valid geometries from being loaded after an invalid one is encountered
2. Not loading the invalid geometry does not provide a way to inspect it
From a product perspective, a change in the Esri workflow would have to be implemented by Esri. (Maybe the default behavior can remain the same, but enable a switch that populates invalid geometries into a different table for inspection at a later time. This is just one suggestion. There may be other options.)
Currently, the best way to circumvent this is to do what this customer did, and load data with Oracle or third party tools like FME. Once the data is loaded, Oracle also recommends validation, and removing/isolating invalid geometries until they can be fixed. I discuss a strategy to do this in a best practices presentation.
One thing I keep hearing is the claim that Esri and Oracle validate differently.
- Both ESRI and Oracle validate by OGC rules.
- The only difference I see is when the data is geodetic. Esri does not consider geodesic paths between vertices for linestrings or polygons, but Oracle does.
- There is only one case I have come across that is valid in Esri, but not in Oracle or by OGC standards.
- Imagine a box with a triangle void, where the triangle only touches the box at one point.
- Esri can model this as a single exterior ring with no voids, and considers it valid. This does not comply with OGC standards.
- Oracle and the OGC standard state that this case is valid if it is modeled with two rings, one exterior and one interior, where the interior ring touches the exterior at one point.
- Other than geodetic data validation, I believe both Esri and Oracle offer comprehensive validation, and for all intents and purposes, the validation offered by both solutions is equivalent.