How do I use Oracle Locator's mdsys.sdo_geometry with ArcSDE?

Question

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

Answer 1

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

Answer 2

Oracle's precision (or tolerance) value represents the largest possible distance between two points for them to be considered equal by Oracle's spatial operators. While you could try to set it based on the accuracy of a GPS device, that may not be a good practice because your GPS accuracy will vary based on the conditions and devices used when collecting the data, but the tolerance value in your metadata will need to be consistent for the entire data set. It would be better to set the tolerance based on how close you expect your data points to get and still be considered separate locations.

From what I understand, ArcSDE doesn't use Oracle Spatial's spatial operators (it only uses SDO_GEOMETRY for storage), so the tolerance value won't matter to you unless you plan to manipulate the data outside of SDE. As far as I know, ESRI doesn't have any limits on the range of tolerances that can be used with ArcSDE (but be aware that ArcSDE may not use the Oracle precision, as mapoholic mentioned).