To Define Nested Sub-Features
-
Create the table with one geometry column, one ID, one angle field, and one foreign key.
CREATE TABLE DEVICE (ID NUMBER PRIMARY KEY, LinkedID NUMBER, Name VARCHAR2(50), Geometry MDSYS.SDO_GEOMETRY);
-
Insert the table metadata in the Oracle Spatial metadata.
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES (‘DEVICE', 'Geometry', MDSYS.SDO_DIM_ARRAY (MDSYS.SDO_DIM_ELEMENT('X', 714099.913898663, 715312.343907550, 0.000000050), MDSYS.SDO_DIM_ELEMENT('Y', 4908438.586694972, 4910715.465519385, 0.000000050)), 82247);
-
Create the spatial index of type point to use rotation.
CREATE INDEX DEVICE_IDX ON DEVICE(Geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('layer_gtype=point');
-
Create the foreign key constraint defining the sub-feature.
ALTER TABLE DEVICE ADD CONSTRAINT DEVICE_CABLE_FK FOREIGN KEY(LinkedID) REFERENCES CABLE(ID) ON DELETE CASCADE;
-
Create the table with no geometry column, one ID, and one foreign key.
CREATE TABLE NO_GRAPHIC (ID NUMBER PRIMARY KEY, LinkedID NUMBER, Name VARCHAR2(50));
-
Create the foreign key constraint defining the non-graphical sub-feature.
ALTER TABLE NO_GRAPHIC ADD CONSTRAINT NO_GRAPHIC_DEVICE_FK FOREIGN KEY(LinkedID) REFERENCES DEVICE(ID) ON DELETE CASCADE;