English

How To: Create an Oracle View of an Oracle Spatial layer, containing multiple geometry columns, and register it with ArcSDE

Summary

Instructions on how to create an Oracle View of an Oracle Spatial layer, containing multiple geometry columns, and register it with ArcSDE.

Procedure

Create an Oracle View of the layer so that only one MDSYS.SDO_GEOMETRY (spatial) column is specified and then register the view with ArcSDE.

  1. Use SQL to create a view on the Oracle Spatial layer. Include only one MDSYS.SDO_GEOMETRY (spatial) column.

    Code:
    CREATE VIEW <view_name> AS SELECT <column1>, <column2>, <spatial_column> FROM <table_with_multiple_geom_cols>;

  2. Insert metadata for the newly created view.

    Code:
    INSERT INTO USER_SDO_GEOM_METADATA
    VALUES ('<view_name>','<spatial_column>',
    MDSYS.SDO_DIM_ARRAY( -- upper and lower bounds for the view, tolerance
    MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.5),
    MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.5)),
    <SRID> -- SRID from MDSYS.CS_SRS
    );

  3. Register the view with ArcSDE specifying the user-maintained Row ID :

    Code:
    sdelayer -o register -l <view_name>,<spatial_column> -C <id_column>,USER -e <allowed_types> -i <service> -u <user> -p <password>

Related Information