Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Validating features/geometries for ArcSDE

502625Mar 29 2006 — edited May 10 2007

This is my first attempt at doing real work with Oracle Spatial. I'm trying to validate all geometries within an envolope. So far I've cobbled together an SQL query which I believe is correct.

Will the below select statement return the rows inside a 2 km square window having an invalid geometry?

select 
    l.primary_key as primary_key, 
    sdo_geom.validate_geometry_with_context(l.shape, m.diminfo) as vgeom
from 
    topographic_line l, 
    ALL_SDO_GEOM_METADATA M
where 
    mdsys.sdo_filter(l.shape,
        mdsys.sdo_geometry(
            2003,   --SDO_GTYPE: 2 dimensions, 0 = no linear ref, 03 = polygon
            81989,  --SDO_SRID: British National Grid 
            NULL,   --SDO_POINT: only for optimal single point storage
            mdsys.sdo_elem_info_array(
                1,      --SDO_STARTING_OFFSET, Ordinates begin at first entry
                1003,   --SDO_ETYPE: 1003 = exterior polygon ring (counter clockwise)
                1       --SDO_INTERPRETATION: vertices are straight lines
            ),
            MDSYS.SDO_ORDINATE_ARRAY(       --2Km square over 416000 141000
                :x-1000, :y-1000, --lower left
                :x+1000, :y-1000, --lower right
                :x+1000, :y+1000, --upper right
                :x-1000, :y+1000, --upper left
                :x-1000, :y-1000  --repetition of first
            )
        ),
        'querytype=window'
    ) = 'TRUE'   
    AND m.table_name = 'TOPOGRAPHIC_LINE' 
    AND m.column_name = 'SHAPE'
    AND sdo_geom.validate_geometry_with_context(shape, m.diminfo) <> 'TRUE'
order by primary_key

With thanks

Alex

Background:
The purpose is to track down the cause of several dropped connections in ArcGIS whilst browsing & printing the data. ArcGIS 8.3 is connecting through ArcSDE 8.3 to an Oracle 9.2.0.6 instance.

The connection from ArcSDE to Oracle is dropping after some usage, although the sdemon tool continues to show the user's session. This corresponds to an entry, "load_buffer error -2028", in the sde.errlog. The following ESRI KB article suggests this is caused by features that are invalid by ArcSDE standards.

http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=22896

It suggests running validate_geometry. I do not wish to run the simpler validate_layer(_with_context), as the tables in question each contain tens-of-millions of rows.

Comments

Siva Ravada-Oracle
Your query looks fine to me.

siva
429668
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.
502625
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
91061
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';
429668
Alex - If you still want the script please let me know and i'll send it on.
502625
Yes please David. Either post it to this thread or send it to my email address: alex@moreati.org.uk

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

p.baynham@verizon.net
Simon Greener
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
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 7 2007
Added on Mar 29 2006
#database-key-features, #spatial
8 comments
2,337 views