Multipart polygons (or polygons with holes) in Oracle Spatial do not appear in ArcMap

[MichaelDamrath]

Hi all.

In our Oracle (11g) schema, we have several tables with SDO_GEOMETRY columns.  The tables are used by our application through simple CRUD operations.  It is a requirement, however, that the data be registerable with ArcSDE and viewable with ArcMap, so I'm in the processing of verifying that it can be done.  We are having a problem, however, in that any shapes inserted with multiple parts (or with holes) will not appear in ArcMap and are not selectable in the attribute table for the layer. 



As an example of what we're doing, here is the definition for one of the tables:

  CREATE TABLE "USSF_MICHAELD"."F_HEALTH_PLAN" 
   ( "FHP_ID" NUMBER(38,0) NOT NULL ENABLE, 
 "FHP_PLAN_POLYGON" "MDSYS"."SDO_GEOMETRY" , 
 "LEGAL_DESC" VARCHAR2(1000 CHAR), 
 "PROPERTY_DESC" VARCHAR2(1000 CHAR), 
 "CALCED_ACRES" NUMBER(38,8), 
 "RECORDED_ACRES" NUMBER(38,8), 
  CONSTRAINT "F_HEALTH_PLAN_PK" PRIMARY KEY ("FHP_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USSF_TABLESPACE"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USSF_TABLESPACE" ;

I've inserted metadata for each table into the requisite Oracle tables, and created spatial indices:

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO)
   VALUES ('F_HEALTH_PLAN', 'FHP_PLAN_POLYGON',
   SDO_DIM_ARRAY
     (SDO_DIM_ELEMENT('X', -20037700, 20037700, 0.1),
     SDO_DIM_ELEMENT('Y', -20037700, 20037700, 0.1))
   )
;

CREATE INDEX SPIX_F_HEALTH_PLAN ON F_HEALTH_PLAN(FHP_PLAN_POLYGON)
  INDEXTYPE IS mdsys.spatial_index
;

And I've registered the table as a feature class with ArcSDE with the following command:

sdelayer -o register -l F_HEALTH_PLAN,FHP_PLAN_POLYGON -e na+ -C FHP_ID -i sde:oracle11g -s fhbXen -u USSF_MICHAELD -p 84867AC24TD0u72@orcl -t SDO_GEOMETRY -P High -G file="C:\Projections\WGS 1984 Web Mercator (Auxiliary Sphere).prj"

The feature class appears under the appropriate database connection in ArcMap, and I can add it as a layer to a new map.  When I add data to it through our application, I can see that data in ArcMap.  I can select it.  I can interact with it.  I can see the attribute data.  However, as stated earlier, if that data has multiple parts or has any holes, the record becomes unselectable and is does not appear.  Any ideas on what the problem may be?

(If it's of any use, when inserting data, we are using the SDO_GEOMETRY constructor which takes a WKT)

[VinceAngelo]

Have you used Oracle's tools for validting the geometry topology? Internal rings
in WKT must be wrapped clockwise (SDO_GEOMETRY is left-hand rule).

Check the ArcSDE logs to see if there are geometry errors...

- V

BTW: Please edit your post to remove password information -- no one here needs that.

[MichaelDamrath]

Have you used Oracle's tools for validting the geometry topology? Internal rings
in WKT must be wrapped clockwise (SDO_GEOMETRY is left-hand rule).

Check the ArcSDE logs to see if there are geometry errors...

- V

BTW: Please edit your post to remove password information -- no one here needs that.

I'll give that a go.  Not terribly familiar with those tools, so it hadn't occurred to me.  I think it's worth noting that we haven't had any problems retrieving the data (GET_WKT() function mostly) and displaying it in Openlayers, as well as using it in queries with SDO_RELATE.


All the passwords, usernames, tables names, and column names above are fake, BTW.  Just there to give complete looking examples.  I can remove if it makes you more comfortable.

[VinceAngelo]

If you're loading geometry on your own, then you have to be able to validate the
construction on your own.  Oracle doesn't enforce topology rules on insert because
the detection tools need loaded data, but ArcSDE does enforce ring orientation.
(errors should be logged in $SDEHOME/etc). 

I haven't had to use Oracle's tools much because I usually have access to the API
functions, but all you really need is a parser and a function to calculate area via the
trapezoid rule across the array of segment vertex pairs.

The least confusing way to make up connection parameters is to just use "server",
"instance", "database", "user" and "password" for the respective values (something
like "notThePassword" is a close second).

- V

[MichaelDamrath]

There weren't any errors that I could spot in the SDE logs, so I tried Oracle's SDO_GEOM.VALIDATE_GEOMETRY function.  It failed with an error like "Unable to construct geometry object".

Fortunately, there is another Oracle function, SDO_UTIL.RECTIFY_GEOMETRY, that seems to have done the trick.  Since two of the three things that function fixes were unlikely to be the issue, I assume it had something to do with the ring orientation for the multipart shapes.  Once I ran that function on our data, everything worked fine in ArcMap.

Thanks for the guidance!