SQL & PL/SQL

Your query looks fine to me.

siva

I've seen similar errors while using Oracle 9.2.0.5 and ArcSDE8.3. It is usually down to polygons not being entirely closed. In SDE the first node and last node need to be exactly the same whereas Oracle, rightly so, applies a tolerance.

You above query will not highlight this unless you change the tolerance of the validate function to something like 0.000000001 metres.

I have a script which will fix these if you're intestered.

David,

Thanks for your answer. After finding errors within a window, I ran validate_layer_with_context and found many instances of ora-13356 (redundant vertices) ora-13349-51 (self intersecting polygons). I believe these can be fixed by applying sdo_union & sdo_util.remove_duplicate_vertices, following this thread:

924033

I also need to check for unclosed polygons and possibly fix them. If it's still available, could you post or send me your correction script.

With thanks, Alex

If performance was important, I'd try to avoid running the validate twice. This is an example, cut your query in as the inner select (how did you keep the formatting of your query?):

select a.primary_key, a.vgeom
from (your_select_statement_here) a
where a.vgeom <> 'TRUE';

Alex - If you still want the script please let me know and i'll send it on.

Yes please David. Either post it to this thread or send it to my email address: alex@moreati.org.uk

Thanks, Alex

Could you send me the script as well David, if you still have it after a year! Thanks

p.baynham@verizon.net

For 8 years I was GIS Manager at a company in Tasmania running ArcSDE on top of Oracle Spatial.

The result of managing this environment led me to create a ShapeCheck routine that I put in the Oracle Job queue that could track transactional edits as they were made each day to ensure data quality was maintained (ie NO errors from VALIDATE_GEOMETRY or VALIDATE_GEOMETRY_WITH_CONTEXT). This ShapeCheck routine was passed a WHERE clause that could be something like "CREATION_DATE LIKE SYSDATE" (ie check all edits for today). This was run each night after 6pm when all editors were no longer at work.

Now that I work for myself I have packaged up all my experience in managing this environment in a bunch of packages that can be downloaded from:

http://www.spatialdbadvisor.com/source_code (click on the "Oracle Spatial Packages and Types" link on the left side of the page).

In particular, have a look inside the TOOLS package for a procedure called GeometryCheck whose signature is:

   Procedure GeometryCheck( p_schema        IN VarChar2, 
                            p_tableName     IN VarChar2,
                            p_ColumnName    IN VarChar2,
                            p_whereClause   IN VarChar2);

Now the original ShapeCheck used to send emails to the person who made the edit with a CC to person who worked for me who was charged with quality assurance. The new version, GeometryCheck, writes its activity (including automated correction) to special tables that can be reported on in SQL Developer.

Wrt SQL Developer I am creating some reports for SQL Developer that will also be available from my website.

regards
S