This content has been marked as final. Show 4 replies
I take it you want both geodetic and projected geometries in the same table (correct me if I am wrong). You can't put them both in the same column, but you certainly (at least from the database perspective) add two or more sdo_geometry data type columns to a single table - one of each in your case.
Now - certain software does not like this at all, but for some you can get around it by using updateable views.
Can you provide details on what this is to work with?
You can have geodetic and projected data in a single column of a table BUT you can't spatially index them as the index can't work on both projections.
You have to, as Bryan says, create two columns:
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);
Now, assuming this table gets the INVGEO column populated first (via some external dynamic feed) you could synchronise the projected column via a trigger such as the following:
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.
I hope this all helps.
Don't forget to award points as you see fit if our answers are deemed correct or helpful.