Notes on Defining 3D Objects in Oracle Spatial
Setting the Spatial Index Dimension to 3D
To properly query a 3D urban model feature, the Oracle Spatial index must be set to 3D.
For example:
CREATE INDEX Building_SIDX ON BUILDING(OGC_GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('layer_gtype=MULTIPOLYGON, sdo_indx_dims=3');
Setting the Appropriate gtype
To store 3D models in Oracle, the appropriate gtype needs to be set in the index as shown in the script below.
The script below shows how to define a table to be used to store a 3D building feature and domain table in Oracle Spatial:
-- create_BUILDING.sql -- -- creates the spatially enabled BUILDING table. For use with Oracle and Bentley Map. -- Create feature table (BUILDING -- CREATE TABLE BUILDING( ID NUMBER CONSTRAINT BUILDING_pk PRIMARY KEY, Owner VARCHAR2(50), Name VARCHAR2(50), Status VARCHAR2(50), geometry SDO_GEOMETRY); -- Create table for STATUS domain -- CREATE TABLE DOMAIN_BUILDING_STATUS ("STATUS" VARCHAR2(10) NOT NULL, CONSTRAINT "DOMAIN_BUILDING_STATUS_PK" PRIMARY KEY("STATUS")); Insert into DOMAIN_BUILDING_STATUS("STATUS") values ('Proposed'); Insert into DOMAIN_BUILDING_STATUS("STATUS") values ('Review'); Insert into DOMAIN_BUILDING_STATUS("STATUS") values ('Existing'); Insert into DOMAIN_BUILDING_STATUS("STATUS") values ('Abandoned'); Insert into DOMAIN_BUILDING_STATUS("STATUS") values ('Demolished'); -- Create foreign key alter table building add constraint DOMAIN_BUILDING_STATUS_FK foreign key(STATUS) references DOMAIN_BUILDING_STATUS(STATUS) disable; -- -- Add metadata to spatial view USER_SDO_GEOM_METADATA. -- INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('BUILDING', 'GEOMETRY', MDSYS.SDO_DIM_ARRAY (MDSYS.SDO_DIM_ELEMENT('X', 310630.031200000, 314262.608400001, 0.000000050), MDSYS.SDO_DIM_ELEMENT('Y', 4832370.993600001, 4835182.257600000, 0.000000050), MDSYS.SDO_DIM_ELEMENT('Z', -1000, 1000, 0.000000050)),NULL); -- -- Note: to work properly in Bentley Map, the SDO_GTYPE needs to -- be explicitly defined in the index. -- CREATE INDEX BUILDING_idx ON BUILDING(geometry) INDEXTYPE IS mdsys.spatial_index PARAMETERS ('layer_gtype=MULTIPOLYGON sdo_indx_dims=3'); commit; exit; -- -- end of create_BUILDING.sql
Supported Oracle Spatial Index Dimensions
2D data with 2D index | 3D data with 2D index | 3D data with 3D index | |
2D DGN | Yes | No | Yes (see note 1) |
3D DGN (see note 2) | Yes | No | Yes |
Note 1) Z-value not used in query.
Note 2) Queries in non-top views use the view rectangle as the top of the frustum and the bottom is calculated using the Oracle metadata extents for the layers