SpatialCookbook

From NGDCWiki

Jump to: navigation, search

Contents

Oracle Spatial and ArcSDE Cookbook

Resources

Create a table, insert point values, register with Oracle and ArcSDE

create a table including columns for X, Y values

create table point_tst (
  OBJECTID NUMBER(38) NOT NULL UNIQUE,
  NAME VARCHAR2(32),
  LON NUMBER,
  LAT NUMBER,
  SHAPE MDSYS.SDO_GEOMETRY
);

The "OBJECTID" column may be named anything, but must be defined as "NUMBER(38)" and constrained to "NOT NULL, UNIQUE" in order to be used by SDE. The "SHAPE" column may be named anything, but must be of type "MDSYS.SDO_GEOMETRY"

insert two records into the table

insert into point_tst values (
  1,
  'Boulder',
  -105.2700,
  40.0150,
  MDSYS.SDO_GEOMETRY(
     2001,
     8307,
     MDSYS.SDO_POINT_TYPE(-105.2700, 40.0150, NULL),
     NULL,
     NULL
  )
);
insert into point_tst values (
  2,
  'Denver',
  -104.9842,
  39.7392,
  MDSYS.SDO_GEOMETRY(
     2001,
     8307,
     MDSYS.SDO_POINT_TYPE(-104.9842, 39.7392, NULL),
     NULL,
     NULL
  )
);

The SHAPE field is populated with a geometry object that is constructed as part of the insert statement. The constructor for this geometry object takes five arguments, only the first three of which are of interest here. The '2001' is a SDO_GTYPE value indicating a 2-D single point. The SDO_GEOMETRY field is commonly named "SHAPE", but this is a only a naming convention.

The second argument indicates the Spatial Reference Identifier (SRID) for this object. "8307" in this example indicates geodetic data on a WGS84 datum (A complete list of SRID codes and descriptions can be found in the MDSYS.CS_SRS table). By setting this value to NULL, Oracle will treat the data as Cartesian.

The third argument is a constructor for a SDO_POINT_TYPE object. The three arguments here are X, Y, and Z coordinates respectively.

The final two arguments are SDO_ELEM_INFO and SDO_ORDINATES objects and are set to NULL when using SDO_POINT as the geometry type.

Alternatively, you can also use OGC well-known-text to construct a geometry.

insert into point_tst values (
  2,
  'Denver',
  -104.9842,
  39.7392,
  MDSYS.SDO_GEOMETRY('POINT (-104.9842 39.7392)', 8307)
);


populate the Oracle Spatial metadata for this table

Insert a record into USER_SDO_GEOM_METADATA.

insert into user_sdo_geom_metadata values (
  'POINT_TST',
  'SHAPE',
  MDSYS.SDO_DIM_ARRAY(
     MDSYS.SDO_DIM_ELEMENT('LONGITUDE',-180,180,0.05), 
     MDSYS.SDO_DIM_ELEMENT('LATITUDE',-90,90,0.05)
  ),
  8307);

Note that NOSA tables currently use NAD83 (Oracle SRID=8265) - this will be migrated to WGS84 in the future.

The first two fields in this record are the table name and the column holding the SDO_GEOMTETRY value. Recommended to always use uppercase for table and column names here.

The third field is an array object containing two dimension elements for the x and y dimensions respectively. The constructors for each of these contains the dimension name, minimum and maximum values, and the tolerance. The dimensions can be named anything, but by convention are "LONGITUDE", "LATITUDE" for geodetic data and "X", "Y" for projected data. In the case of geodetic data, the ranges must be -180 to +180 for longitude and -90 to +90 for latitude. The units for the tolerance are specified in meters (despite the longitude and latitude values having units of decimal degrees). The tolerance value for geodetic data should not be smaller than 0.05 (5 centimeters), and in most cases it should be larger. Oracle spatial uses 0.05 as the tolerance value for geodetic data if you specify a smaller value. For non-geodetic data, the tolerance units are the same as the x,y units. So, if you specify NULL SRID and still use decimal degrees for the x,y coordinates, the tolerance will be in units of decimal degrees.

The last value is the SRID, and this needs to match the value specified in the MDSYS.SDO_GEOMETRY constructor in the previous step. Again, a NULL value here causes Oracle to treat these data as cartesian and not geodetic.

create a spatial index on the geometry column

create index point_tst_spatial_idx on point_tst(SHAPE) 
indextype is MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point');

This builds a RTREE index on the geometry column. The index name is arbitrary, but limited to 32 characters in length. The parameters 'LAYER_GTYPE=point') clause is optional and insures that only point features may be inserted into this table. It also may increase the performance of some queries. Although Oracle allows different types of geometries in a single column, ArcSDE does not. This constraint enforces that restriction.
With earlier versions of ArcSDE, ESRI did NOT recommend using this index constraint. See Base article 23179, however it seems to work fine in ArcSDE 9.x

At this point the table is spatially enabled and ready for use with SQL spatial queries.

(optional) validate the geometry in the table

register the table with SDE, creating a "layer" accessible to ESRI clients (ArcMap, ArcIMS, etc.)

Substitute the sdehostname (e.g. bobcat, cheetah, cougar, or kodkod) and the user name (schema name, e.g. mgg, nosa) and schema password in the command.

Syntax for ArcSDE 9.3. Note the use of the "-t", "-P" parameters to specify the geometry type and precision respectively

sdelayer -o register  -u geodas -i port:5151 -s sleet.ngdc.noaa.gov -l HIRES_VSQL,SHAPE -e sl+ -C OBJECTID,USER -t  SDO_GEOMETRY 
-S "high resolution geodas tracklines" -k SDO_WGS84 -P HIGH

Syntax for ArcSDE 9.2 and earlier did not include the "-t" parameter but used the dbtune keyword to indirectly specify geometry type, e.g.

sdelayer -o register -l POINT_TST,SHAPE -e p -c OBJECTID -C USER -s <sdehostname> ^
-S "layer description" -k SDO_WGS84 -u <username> -p <password>

marks the layer as using SDO_GEOMETRY for geometry storage and 8307 for the SRID because they are specified in the DBTUNE file under the SDO_WGS84 keyword

This registers the table with ArcSDE, but not with the Geodatabase. This however is all that is need to use the spatially-enabled table with ESRI clients. page for sdelayer.

Registering a table using ArcCatalog or allowing AutoRegistration to take place will regsiter the spatial table as a featureclass in the geodatabase. If this happens, the layer can no longer be deleted using sdelayer as it will leave a remnant in the ArcSDE repository.

When a layer is registered, the spatial reference system is read from the oracle spatial metadata (USER_SDO_GEOM_METADATA view). Oracle and ArcSDE use different SRID codes, so the mapping between SRIDs is done via the text description of the spatial reference. In earlier ArcSDE versions this presented a problem, but it works pretty well now. After a layer is registered, ArcSDE does not look at the information in USER_SDO_GEOM_METADATA, so it is possible to alter the ArcSDE registration and have a different SRID associated with the table in ArcSDE than in Oracle - this is not recommended!

AutoRegistration is the process by which ArcSDE looks for new Oracle Spatial tables that have the correct structure and automatically registers them. This happens when ArcCatalog or ArcMap connect. It's recommended NOT to depend on auto-registration, but to explicitly register using the process listed above. AutoRegistration was on by default in ArcSDE8.x, but is off by default in ArcSDE9. Note that servers that were migrated from 8.x retain their old settings. See KnowledgeBase article 20274 for more information.

Specifying USER means that the OBJECTID column, known to ArcSDE as the rowid column, will be managed outside of ArcSDE. It is crucial that whatever column is specified as the rowid column contain a unique value for each row (feature) in the table. User-maintained rowid columns are not expected to be editied via ESRI tools.

If one specifies a SDE-maintained rowid column, and it does not already exist, ArcSDE will create it for you.

^If you encounter the following error message:

   Error: Entry for SDE instance not found in services file (-102).
   Error: Cannot get configuration information

It means that you don't have an entry like esri_sde 5150/tcp in your services file. You can work around this by adding -i port:5150 as part of the command.^

Registering as a SDE Layer vs. registering with the Geodatabase (i.e. creating a Feature Class)

There is a distinction between an SDE Layer and a Feature Class even though they are both commonly referred to as "layers" and appear the same in ArcCatalog. A SDE Layer is simpler and can be managed through the use of the sdelayer command line tools. Creating a Feature Class or "registering with the geodatabase", can only be done using ArcCatalog. Reminder: Do not use sdelayer to delete a Feature Class - it will leave artifacts in the SDE repository!

We generally use SDE layers but there are a few cases where only a Feature Class will work. One such example is when the layer needs to participate in a ArcGIS Server WFS service. There are some outstanding ESRI bugs with regard to registering an SDO_Geometry table or materialized view as a Feature Class, but the following procedure should work, assuming that you don't plan to modify the contents of the table via ESRI tools, but only via SQL.

  • insure the spatial table has an user_sdo_geom_metadata entry, spatial index, OBJECTID column populated w/ unique values
  • register the spatial table as an SDE Layer using sdelayer. You must specify the row_id_column as SDE-maintained, e.g. "sdelayer -o register -C OBJECTID,SDE" even though it's not.
  • register the SDE Layer with the geodatabase using ArcCatalog

In the case of materialized view, first drop the view from Oracle, then delete the Feature Class from ArcCatalog. You'll get an error the underlying datasource, but proceed anyway.

Note that if one attempts to register a SDE Layer with the geodatabase which has been created with a USER-maintained row_id_column, a second column will be added to the materialized view and an error will be thrown as SDE attempts to populate the new column. If one attempts to directly register a SDO_GEOMETRY table without first creating an SDE Layer, a error will be thrown about the inability to read Abstract data types.

If a null SRID was used in Oracle, "correct" the SRID in the SDE metadata

sdelayer -o alter -l POINT_TST,SHAPE -G 4326 -s sdehostname -u username -p

If the Oracle metadata has a value of NULL at the time the table is registered with SDE, SDE uses "UNKNOWN" for the spatial reference.

This command allows the user to specify the "correct" spatial reference. In this example, it is being set to WGS 1984.

ESRI and Oracle use different codes and slightly different text descriptions for the various spatial references.

Now the same spatially-enabled table is accessible to Oracle spatial queries and ESRI clients. ESRI sees the data as geodetic, while Oracle sees them as cartesian. This should not present any problems from ESRI's perspective since they only use the Oracle's SDO_FILTER procedure. Consideration must be given however when using Oracle Spatial directly since the procedures will be assuming a "flat earth" and not measuring distances along a sphere.

grant SELECT privileges to the SDE_VIEWER role so other users can see the table

sdelayer -o grant -l POINT_TST,SHAPE -A select -U SDE_VIEWER ^
-s sdehostname -u username -p

This step can also be performed from ArcCatalog by right-clicking the layername and choosing "Privileges...". Grant select privileges to the SDE_VIEWER role (user).

Be aware that simply granting privileges to the table in Oracle will not allow it to work correctly via ESRI tools.


Spatially-enable an existing table containing columns "LONGITUDE" AND "LATITUDE"

  1. add the OBJECTID,geometry columns to the table
    alter table point_tst add (OBJECTID NUMBER(38) unique, SHAPE MDSYS.SDO_GEOMETRY);
          
  2. populate the newly-created columns
    update point_tst set OBJECTID = rownum;
    alter table point_tst modify (OBJECTID not null);
    update point_tst set SHAPE = 
       MDSYS.SDO_GEOMETRY(
          2001,
          8307,
          MDSYS.SDO_POINT_TYPE(LONGITUDE, LATITUDE, NULL),
          NULL,
          NULL
       );  

    The OBJECTID column needs to contain a unique integer value and the rowid pseudo-column is used to get those values. After being populated, the NOT NULL constraint is applied to the column to satisfy SDE's requirements.

    The geometry object constructor is used just as above. Note that here the SRID is specified as "8307" indicating geodetic data on the WGS84 datum.

  3. populate the Oracle Spatial metadata for this table. Insert a record into USER_SDO_GEOM_METADATA.
    insert into user_sdo_geom_metadata values (
       'point_tst',
       'SHAPE', 
       MDSYS.SDO_DIM_ARRAY(
          MDSYS.SDO_DIM_ELEMENT('Longitude',-180,180,0.005), 
          MDSYS.SDO_DIM_ELEMENT('Latitude',-90,90,0.005)
       ), 
       8307
    );
          

    The first two fields in this record are the table name and the column holding the SDO_GEOMETRY value.

    The third field is an array object containing two dimension elements for the x and y dimensions respectively.

    The constructors for each of these contains the dimension name, minimum and maximum values, and the tolerance.

    Because the Geometry objects in the table are geodetic data, the ranges must be -180 to +180 for longitude and -90 to +90 for latitude. Also in the case of geodetic data the tolerance is specified in meters, not decimal degrees.

    The last value in the insert statement is the SRID, and this needs to match the value specified in the MDSYS.SDO_GEOMETRY constructor in the previous step.

  4. create a spatial index on the geometry column
    create index point_tst_spatial_idx on point_tst(SHAPE) indextype is MDSYS.SPATIAL_INDEX;
          

    This builds a RTREE index on the geometry column. The index name is arbitrary. You can find a list of spatial indexes and their types in the user_sdo_index_info view.

                                                                                                
    select INDEX_NAME,TABLE_NAME,SDO_INDEX_TYPE from user_sdo_index_info;
          
  5. (optional) validate the geometry in the table At this point the table is spatially enabled and ready for use with SQL spatial queries.
  6. register the table with SDE, creating a "layer" accessible to ESRI clients (ArcMap, ArcIMS, etc.)
    
    sdelayer -o register -l POINT_TST,SHAPE -e p -C OBJECTID,USER -G 4326 ^
    -k SDO_WGS84 -s sdehostname -S "layer description" -u username -p 
          

    This registers the table with SDE, but not with the Geodatabase. This however is all that is need to use the spatially-enabled table with ESRI clients.

    If the Oracle metadata had a value of NULL at the time the table was registered with SDE, SDE uses "UNKNOWN" for the spatial reference. The -G option in the 'sdelayer -o register' command allows the user to specify the correct spatial reference. In this example, it is being set to WGS 1984 to match the Oracle geometries and spatial metadata. The -k option in the 'sdelayer -o register' command allows the user to specify the sdo_geometry keyword. In this example, it is being set to SDO_WGS84.

    A table of Oracle and ESRI spatial reference identifier values (SRIDs) shows commonly-used values for geodetic data. Note that the ESRI and Oracle use different codes and slightly different text descriptions for the various spatial references.

    Note that the ESRI and Oracle use different codes and slightly different text descriptions for the various spatial references.

    Now the same spatially-enabled table is accessible to Oracle spatial queries and ESRI clients.

    If the data were spatially enabled in Oracle with a null SRID, ESRI sees the data as geodetic, while Oracle sees them as cartesian. This should not present any problems from ESRI's perspective since they only use the Oracle's SDO_FILTER procedure.

    Consideration must be given however when using Oracle Spatial directly since the procedures will be assuming a "flat earth" and not measuring distances along a sphere.

  7. grant SELECT privileges to the SDE_VIEWER role so other users can see the table
    sdelayer -o grant -l POINT_TST,SHAPE -A select -U SDE_VIEWER ^
    -s sdehostname -u username -p 
          

    This step can also be performed from ArcCatalog by right-clicking the layername and choosing "Privileges...". Be aware that simply granting privileges to the table in Oracle will not allow it to work correctly via ESRI tools.


Split large geodetic polygons at the prime-meridian

Oracle will not allow geodetic polygons to be larger than 1/2 the surface of the earth (reference). The error returned in these cases is "ORA-13367: polygon coords in wrong order". One way to accommodate this limitation is to break large simple polygons into multipart polgons. Note that Oracle will also not allow two parts of a multipart polygon to intersect (ORA-13351) so we introduce a very small gap between the parts.

Assuming a table POLY with columns MINX,MINY,MAXX,MAXY defining the corner points of rectangles, one can update the polygons with:

update POLY set shape =
   MDSYS.SDO_GEOMETRY(
      2007, -- 2D polygon
      8307, -- SRID
      NULL,
      MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1, 11,1003,1),
      MDSYS.SDO_ORDINATE_ARRAY(
         MINX, MINY,
         -0.0001, MINY,
         -0.0001, maxY,
         MINX, MAXY,
         MINX, MINY,
         0.0001, MINY,
         MAXX, MINY,
         MAXY, MAXY,
         0.0001, MAXY,
         0.0001, MINY
      )
   ) where  MAXX - MINX > = 180

Create a table, insert polygon values, register with Oracle and SDE

  1. create and populate the table
    create table POLY0 (OBJECTID number(38), SHAPE MDSYS.SDO_GEOMETRY);
          
  2. insert a record into the table
    insert into POLY0 values (
      1,
      MDSYS.SDO_GEOMETRY(
        2003, -- 2D polygon
        8265, -- SRID
        NULL,
        MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior)
        MDSYS.SDO_ORDINATE_ARRAY(
          0,-10,
          10,0,
          0,10,
          -10,0,
          0,-10
        )
      )
    );
          

    The "2003" is a SDO_GTYPE value indicating a 2D polygon.

    The "1003" code in the SDO_ELEM_INFO_ARRAY indicates a simple exterior polygon.

    For exterior polygons, coordinate pairs should be listed in counter-clockwise order.

    Points are listed as (X1,Y1,X2,Y2,X3,Y3...Xn,Yn) where X is longitude and Y is latitude.

    The first and last points should be identical to close the polygon.

  3. populate the Oracle Spatial metadata for this table. Insert a record into USER_SDO_GEOM_METADATA .
    
    insert into user_sdo_geom_metadata values (
       'POLY0',
       'SHAPE',
       MDSYS.SDO_DIM_ARRAY(
          MDSYS.SDO_DIM_ELEMENT('Longitude',-180,180,0.5),
          MDSYS.SDO_DIM_ELEMENT('Latitude',-90,90,0.5)
       ),
       8265
    );
       

    "8265" represents the SRID value

  4. create a spatial index on the geometry column
    create index poly0_spatial_idx on poly0(SHAPE) indextype is MDSYS.SPATIAL_INDEX;
       
  5. (optional) validate the geometry and SRIDs in the table
  6. register with SDE. See steps 6-8 in Case 1.

Spatially-enable an existing table containing columns holding corner points

  1. add the OBJECTID,geometry columns to the table
    alter table poly_tst add (OBJECTID NUMBER(38), SHAPE MDSYS.SDO_GEOMETRY);
          
  2. populate the newly-created columns
    update poly_tst set OBJECTID = rownum;
    alter table poly_tst modify (OBJECTID not null);
    update poly_tst set SHAPE =
       MDSYS.SDO_GEOMETRY(
          2003,
          8307,
          NULL,
          MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
          MDSYS.SDO_ORDINATE_ARRAY(minx,miny, maxx,miny, maxx,maxy, minx,maxy, minx,miny)
       );
          

    The OBJECTID column needs to contain a unique integer value and the rowid pseudo-column is used to get those values. After being populated, the NOT NULL constraint is applied to the column to satisfy SDE's requirements.

    The geometry object constructor is used just as above. Note that here the SRID is specified as "8307" indicating geodetic data on the WGS84 datum.

  3. populate the Oracle Spatial metadata for this table. Insert a record into USER_SDO_GEOM_METADATA
  4. create a spatial index on the geometry column
  5. (optional) validate the geometry in the table
  6. register the table with SDE, creating a "layer" accessible to ESRI clients (ArcMap, ArcIMS, etc.)
  7. grant SELECT privileges to the SDE_VIEWER role so other users can see the table

Create a table, insert line values, register with Oracle and SDE

  1. create a table
    create table line_test (
       OBJECTID number(38), 
       FROM_X number, FROM_Y number,
       TO_X number, TO_Y number,
       SHAPE MDSYS.SDO_GEOMETRY)
    
  2. insert a row
    insert into line_test values (
       1,-105,39,-103,40,sdo_geometry(
        2002,   -- linestring
        8307, --WGS84 SRID 
        null,
        sdo_elem_info_array(1,2,1),   --one line string, straight segment
        sdo_ordinate_array(-105,39,-103,40)
       ))
    
  3. insert metadata
    insert into user_sdo_geom_metadata values ( 
       'LINE_TEST',
       'SHAPE',
       SDO_DIM_ARRAY (
          SDO_DIM_ELEMENT ('Longitude',-180,180,0.5),
          SDO_DIM_ELEMENT('Latitude',-90,90,0.5)
       ),
       8307)
    
  4. build index
    create index line_test_spidx on line_test(shape) indextype is MDSYS.SPATIAL_INDEX;
    
  5. validate
    select objectid,sdo_geom.validate_geometry_with_context(l.shape,0.5) from line_test l;
    

Create lines from a point table

Using a table of points with an "lineid" attribute, construct a series of lines, one per unique lineid. This could probably be done all in SQL using Oracle's lag functions, but the python script below offers a simple alternative.

Point table VERTICES_TST:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECTID                                  NOT NULL NUMBER(38)
 LINEID                                             NUMBER
 SHAPE                                              MDSYS.SDO_GEOMETRY


Line table LINE_TST

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OBJECTID                                  NOT NULL NUMBER(38)
SHAPE                                              MDSYS.SDO_GEOMETRY


Python script

import cx_Oracle

def buildInsertStatement(lineid,ordinates):
  coordString = ", ".join(map(str,ordinates))
  return  """
insert into line_tst (objectid,shape) values (%s, sdo_geometry (
  2002,
  null,
  null,
  sdo_elem_info_array (1,2,1),
  sdo_ordinate_array (%s))
)""" % (lineid, coordString)

connection = cx_Oracle.connect("myuser", "mypasswd", "mydb")

cursor = connection.cursor()
cursor.arraysize = 50
#assume the order of the vertices in each line is defined by OBJECTID 
cursor.execute("""
       select
          p.LINEID, p.SHAPE.SDO_POINT.X, p.SHAPE.SDO_POINT.Y
       from
          VERTICES_TST p
       order by p.OBJECTID
       """
       )

#initialize w/ first row of resultset
row = cursor.fetchone()
lineid = row[0]
ordinates = list()
ordinates.append(row[1]);
ordinates.append(row[2]);

for row in cursor.fetchall():
  if (row[0] == lineid):
     #append to existing line
     ordinates.append(row[1]);
     ordinates.append(row[2]);
  else:
     print buildInsertStatement(lineid, ordinates)
     #create a  new line
     lineid = row[0]
     ordinates = list()
     ordinates.append(row[1]);
     ordinates.append(row[2]);

#finish by inserting last line segment
print buildInsertStatement(lineid, ordinates)

Register a table containing multiple spatial columns

ArcSDE cannot register a table containing more than one spatial column. To work around this, create one or more views based on the table each of which contains a single spatial column and the relevant attribute columns. Register each view individually. See knowledge base article 31709 for details.

Register a table whose geometry column contains multiple geometry types

ArcSDE cannot register a table if the geometry column contains more than one geometry type, e.g. points and lines. To work around this, create one or more views based on the table each of which contains a single geometry type and the relevant attribute columns, e.g.

CREATE or REPLACE VIEW POINT_VIEW 
as 
select * from EVENTS s where s.location.get_gtype() = 1

Then register each view individually. See knowledge base article 31709 for details.

Create and register a view

In previous ArcSDE versions registering Oracle views could be problematic and the use of sdetable to create the view was encouraged. Now Oracle views are better supported, and the general recommendation is to create the view in Oracle and then register w/ sdelayer.

Using this approach:

  • insure that at least one of the underlying tables contains a SDO_GEOMETRY column, entry in user_sdo_geom_metadata, and spatial index
  • create the view in Oracle in the standard way, and add entry into user_sdo_geom_metadata
  • register the view w/ ArcSDE using "sdelayer -o register" just like you would w/ any oracle Spatial table

Insure that the join columns are primary keys or you may see an error like this:

SQL> exec sdo_geom.validate_layer_with_context('AERO_HIRES_VSQL','SHAPE','HIRES_VAL');
BEGIN sdo_geom.validate_layer_with_context('AERO_HIRES_VSQL','SHAPE','HIRES_VAL'); END;


ERROR at line 1:
ORA-29400: data cartridge error
ORA-01009: missing mandatory parameter
ORA-29400: data cartridge error
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
ORA-29400: data cartridge error
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
ORA-06512: at "MDSYS.SDO_3GL", line 1380
ORA-06512: at "MDSYS.SDO_GEOM", line 3797
ORA-06512: at line 1

Adding a primary_key constraint should resolve the issue.


Alternatively, use sdetable which creates an oracle view, populates the user_sdo_geom_metadata table, and registers with ArcSDE all in one command. The assumption is that the spatial table used in the view definition has already been registered w/ ArcSDE.

sdetable -o create_view -T view1 -t cities -c objectid,city_name,cntry_name,shape ^ 
-s cheetah.ngdc.noaa.gov -u username -p password

-T the view name -t comma-separated list of tables to be used in view -c comma-separated list of columns man page


A potential performance increase can be realized by using a materialized view in place of a standard view. Materialized views are treated as tables by ArcSDE and registered with sdelayer. Note that materialized views should only be used with "simple" view definitions to avoid problems filling up the oracle logs. Specifying "fast refresh on commit" when defining the materialized view will warn if a complex view is attempted.

see also ((OracleViewsAndArcSDE|notes)) on testing of views.


The following notes are only applicable to older ArcSDE versions and should no longer be necessary nor recommended

For at least some complex views, there is no way to define the view using sdetable. Two approaches are possible here ( Note that creating an Oracle view and subsequently registering it does not work):

Preferred approach: 1) create the new view as a table in Oracle (Use create table) 2) add the spatial metadata and spatial index in Oracle 3) register the table in SDE 4) drop the table in Oracle 5) create the view in Oracle (with the same definition as in step 1, but use create view)

Also see more explanation ((CreateSpatialView|here)), including examples. Another example shows the creation of a spatial view for ((WfabbaSDEViewCreate|Fires WFABBA)) data.

--- A second alternative is the "trivial view" approach: 1) use sdetable to create a simple view containing the row_id_column (by convention named "OBJECTID"), and the SDO_GEOMETRY column (by convention named "SHAPE").

this should produce a ArcSDE layer visible in ArcCatalog, etc. Make sure to grant the appropriate permissions.

2) via SQL, alter the view definition for the view created in step 1. No change in the ArcSDE registration should be necessary.


Here is an old article that describes altering the view registration to define a row_id_column. This does not seem to be necessary any longer and is not recommended. KnowledgeBase article 20867

Modify the SRID of a table that is already registered with SDE

Create a spatial table using shp2sde

original tiki page

shp2sde -o create -l POINTS_TSQP,SHAPE -f shape_file -S "layer_description" -e p -k SDO_GEOMETRY -a all -s database -u user_name -p

Also see: http://edndoc.esri.com/arcsde/9.1/admin_cmd_refs/shp2sde.htm

shp2sde -o create -l -f <shape_file> -I Spatial_Index [Spatial_Ref_Opts}] -S <layer_description_str> -v OFF} 64} -e <entity_mask> -k <config_keyword> -M <minimum_ID> all -r <reject_shpfile> -V <version_name> [-C <row_id_column> USER},<min_ID>] -c <commit_interval> -i <service> -s <server_name> -D <database> -u <DB_User_name> -p <DB_User_password> -k configuration keywords (NGDC specific): SDO_GEOMETRY SDO_WGS84 SDO_NAD83

Restricting invalid geometries from table

Oracle SDO_GTYPE / ESRI Entity Type Comparison

OracleESRIInformix
Point2001pST_Point
Line2002lST_LineString
Spaghetti Line (self-crossing)s
Polygon2003aST_Polygon
Multipart Point2005p+ST_MultiPoint
Multipart Line2006l+ST_MultiLineString
Multipart Spaghetti Line (self-crossing)s+
Multipart Polygon2007a+ST_MultiPolygon

Commonly-used Oracle and ESRI spatial reference identifier values (SRIDs)

from the oracle manual: Geodetic coordinates (sometimes called geographic coordinates) are angular coordinates (longitude and latitude), closely related to spherical polar coordinates, and are defined relative to a particular Earth geodetic datum. (A geodetic datum is a means of representing the figure of the Earth and is the reference for the system of geodetic coordinates.)^ The complete list of ESRI codes can be found [1] The complete list of Oracle SRIDs can be found in the MDSYS.CS_SRS table

select CS_NAME,SRID from  MDSYS.CS_SRS
OracleESRI
Geodetic WGS8483074326
Geodetic NAD8382654269
Geodetic NAD2782604267

NGDC Spatial Table and View Naming Conventions

Spatial tables should be named with a base name of 20 characters or less and an informative 5-character suffix (including a leading underscore) that gives some details about the contents of the spatial table. Adhering to this convention ensures that there is a bit of additional space for suffixes to create index, trigger, and sequence names that refer to the table.

basename_1234

  1. Table or View: "t" for Table, "v" for View, or "s" for Snapshot
    • 'snapshot tables must have the exact same name as the source view with the exception of s in the extension instead of v
  2. Name of spatial column: "s" for "shape" (almost all our tables use "shape"), "g" for "geometry", "o" for other
  3. Type of the spatial column: "b" for Binary, "n" for Normalized, "w" for Well-known binary, "q" for Oracle SDO_GEOMETRY (this is the most common), "i" for Informix spatial, "h" for Informix Geodetic ))DataBlade(( (Hipparchus based), "r" for Informix Grid ))DataBlade(( (Raster)
  4. Geometry described by the spatial column: "p" for Point, "l" for Line, "a" for Area (polygon), and "m" for Multiple (multiple spatial types exist within the table)

System Units (affecting feature validity)

TODO need to verify

  • registering w/ ArcCatalog automatically generates extents and system units. We are unable to intervene.
  • the system units, extents do not affect the actual storage of the data in Oracle, but do affect the data as seen by ESRI clients.
  • the system units affect the precision with which ESRI clients see these coordinates and thus can affect the validity of features.
Personal tools