[GIS] How to use Oracle Locator’s mdsys.sdo_geometry with ArcSDE

enterprise-geodatabaseoracle-dbms

I'm using the Oracle Locator MDSYS.SDO_Geometry type to store my spatial information and then accessing it through ArcSDE. I'm confused about how the precision component of the metadata is used and more specifically how it relates to GPS accuracy.

What is the maximum precision that I can use with ArcSDE?

This is a sample of my code:

Create sequence for object id column (oid)

CREATE SEQUENCE seq_myTable_oid
 MINVALUE 0
 MAXVALUE 99999999
 START WITH 1
 INCREMENT BY 1
 CACHE 20;

Create table

CREATE TABLE myTable
(
 id INT NOT NULL,
 geom MDSYS.SDO_GEOMETRY NOT NULL,
 oid INT NOT NULL
);
ALTER TABLE myTable
ADD CONSTRAINT pk_myTable PRIMARY KEY (id);

Create insert trigger on myTable for OID

CREATE OR REPLACE TRIGGER myTable_i
  BEFORE INSERT ON 
  FOR EACH ROW
DECLARE
BEGIN
  SELECT seq_myTable_oid.NEXTVAL INTO :new.oid FROM DUAL;
END;
/

Insert geom metadata for myTable

INSERT INTO USER_SDO_GEOM_METADATA
VALUES(
'MYTABLE','GEOM',
    MDSYS.SDO_DIM_ARRAY(
    MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.00005),
    MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.00005),
    MDSYS.SDO_DIM_ELEMENT('Z', -10000, 100000, 1)
     ),
   4326
);

Create spatial index on table

CREATE INDEX sp_idx_myTable_geom ON myTable(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Register table with SDE

sdelayer -o register -l myTable,geom -C oid,USER -t SDO_GEOMETRY -P HIGH -e l3 -s serverName -i instance -u userName -p password

Best Answer

I am not sure about the available maximum precision in ArcSDE but this has been a sore point for me for years. As I understand it the oracle and arcsde precisions are two completetly different things. ArcSDE clients (e.g. Arcmap) will only read the precision that is defined in the ArcSDE metadata - and in particular in the SDE.SPATIAL_REFERENCES table, in the fields XYUNITS, ZUNITS and MUNITS.

This for example means that you may store you data in oracle in 5 or more decimal places but if your XYUNITS is set to 1, Arcmap will only DISPLAY (say in the sketch properties window) an integer! Problem is the XYUnits are set automatically when you register the Oracle table or view using some calculation I dont' know about. If someone else does, I would be very interested to know! Ages ago I did find a doco that was explaining the how ArcSDE calulates this number but never found it since...

In the past when I wanted to change the precision that ArcSDE reports, I just ended up changing the XYUnits (or M/Z units) to just 'a large value' completely arbitrary but it seemed to work

Related Question