English

FAQ: How does the Oracle SDO_GEOMETRY example COLA_MARKETS work with ArcSDE?

Question

How does the Oracle SDO_GEOMETRY example COLA_MARKETS work with ArcSDE?

Answer

In the Oracle COLA_MARKETS example, the first feature is an optimized rectangle. ArcSDE versions before version 9.0 will not autoregister a layer that has an optimized rectangle in the first row. If using a version of ArcSDE earlier than version 9.0, remove the first feature from the example. The resulting Oracle SQL is as follows:

Code:
CREATE TABLE cola_markets (
mkt_id NUMBER(38),
name VARCHAR2(32),
shape MDSYS.SDO_GEOMETRY);

INSERT INTO cola_markets VALUES(
2,
'cola_b',
MDSYS.SDO_GEOMETRY(
2003, -- 2-dimensional polygon
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
)
);



INSERT INTO cola_markets VALUES(
3,
'cola_c',
MDSYS.SDO_GEOMETRY(
2003, -- 2-dimensional polygon
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
MDSYS.SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3)
)
);


INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'cola_markets',
'shape',
MDSYS.SDO_DIM_ARRAY( -- 20X20 grid
MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
),
NULL -- SRID
);

CREATE INDEX cola_spatial_idx
ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

commit;


The table must be registered with ArcSDE. Manually register the SDO_GEOMETRY layer with ArcSDE using the 'sdelayer' command.

Code:
sdelayer -o register -l cola_markets,shape -e a -c mkt_id -C user -u . . .

For version 9.3, there are some changes in the sde commands to register the layer, an example is pasted below:

C:\Documents and Settings\bisw0000>sdelayer -o register -l cola_markets,shape -e a -C mkt_id,user -t SDO_GEOMETRY -P BASIC -u sde -p sde -i 6161
ArcSDE 9.3 for Oracle10g Build 508 Thu Apr 17 12:23:18 2008
Layer Administration Utility
-----------------------------------------------------
Successfully Created Layer.

If the data was autoregistered, and it does not display in ArcCatalog, refer to the article in Related Information on how to unregister autoregistered third-party Oracle Spatial tables.

Related Information