If you do this you need to create two user_sdo_geom_metadata entries. One as you have already done, and the other for the projected data:
create table INVENTORYGEOMETRY ( fid integer, INVGEO sdo_geometry, /* For the geodetic data */ INVPROJ sdo_geometry /* for the projected data */ );
Where you replace 0,1000 ranges with your actual data range and XXXXX with your actual projected SRID (eg 28355 as in the example below).
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('INVENTORYGEOMETRY','INVPROJ', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',0,1000, .05),MDSYS.SDO_DIM_ELEMENT('Y',0,1000, .05)),XXXXX);
create trigger inventorygeometry_proj_bi before insert on inventorygeometry FOR EACH ROW begin if (:new.invgeo is not null) then :new.invproj := mdsys.sdo_cs.transform(:new.invgeo,28355); end if; end; /
Also, I don't know the relationship between the SDO_ELLIPSOIDS table and the SDO_GEOMETRY table. Will you please explain it to me?The SDO_ELLIPSOIDS table is part of the implementation of projections within the Oracle Spatial package. When you use a SRID in an SDO_GEOMETRY, Oracle uses that SRID to query the underlying tables (of which SDO_ELLIPSOIDS is only one member) to get the properties of the projection eg the definition of the ellipsoid being one.