Oracle Spatial DBTUNE storage parameters
The DBTUNE table contains some storage parameters that are specific to Oracle Spatial. These are described in the following sections.
Creating Oracle Spatial feature classes
To create a feature class that uses the SDO_GEOMETRY storage type, specify a configuration keyword that contains the GEOMETRY_STORAGE parameter set to SDO_GEOMETRY. Some samples are provided in the next section. See How does ArcSDE use Oracle Spatial? for more information.
Two other parameters affect the storage of SDO_GEOMETRY features: SDO_ORDINATES_VARRAY_STORAGE and SDO_ELEM_INFO_VARRAY_STORAGE. Both append a varray storage clause to the CREATE TABLE statement. SDO_ORDINATES_VARRAY_STORAGE defines storage for the SDO_ORDINATES_VARRAY portion of the SDO_GEOMETRY object. SDO_ELEM_INFO_VARRAY_STORAGE defines storage for the SDO_ELEM_INFO_VARRAY portion of the SDO_GEOMETRY object.
If the Oracle Spatial feature class you create stores CAD or ArcSDE annotation data, a column—SE_ANNO_CAD_DATA—is appended to the base table of the feature class to store this information. You can set the SE_ANNOCAD_LOB_STORAGE parameter to define the storage for this column. The value defined for this parameter is added as a LOB clause in the CREATE TABLE statement that is submitted to Oracle to create the feature class.
Sample DBTUNE parameter groups for Oracle Spatial feature classes
This section presents DBTUNE parameter groups that apply to several common scenarios. (Remember, parameters are grouped by configuration keywords.) These samples emphasize the storage parameters for Oracle Spatial feature classes.
Oracle and Esri recommend using R-tree spatial indexes with SDO_GEOMETRY storage. In some of the following examples, you will see the parameter sdo_indx_dims=2, which specifies how many dimensions should be indexed with an R-tree spatial index.
If you are not using Oracle Spatial by default, you can use the existing SDO_GEOMETRY keyword or create your own simple parameter group to create Oracle Spatial feature classes with mostly default settings. The tables and indexes are created in the user's default tablespace using default physical storage parameters, unless specified otherwise in the DEFAULTS parameter group. The spatial index is a two-dimensional R-tree.
##SDO_GEOMETRY GEOMETRY_STORAGE "SDO_GEOMETRY" SDO_INDEX_SHAPE "sdo_indx_dims=2" UI_TEXT "Oracle Spatial: default settings" END
With Oracle Spatial, if data is often loaded using a specific spatial reference identifier (SRID), such as the geodetic SRID 8307 (latitude-longitude WGS84), you can create an expanded version of the previous parameter group. You don't have to specify the upper and lower bounds and tolerance, but you can if you want all your feature classes to have the same metadata for the x and y dimensions.
Note:For geodetic data, the extents are specified in decimal degrees, and the tolerances are specified in meters.
##SDO_GEOMETRY_8307 GEOMETRY_STORAGE "SDO_GEOMETRY" SDO_INDEX_SHAPE "sdo_indx_dims=2" SDO_SRID 8307 SDO_DIMNAME_1 "Lon" SDO_LB_1 -180.000000 SDO_UB_1 180.000000 SDO_TOLERANCE_1 0.05 SDO_DIMNAME_2 "Lat" SDO_LB_2 -90.000000 SDO_UB_2 90.000000 SDO_TOLERANCE_2 0.05 UI_TEXT "Oracle Spatial: WGS84" END
The following example can be used to load a feature class with an R-tree spatial index into the tablespace ORSPBIZ. The R-tree spatial index will be created in the tablespace ORSPIDX. The ArcSDE client that is loading the data decides the values for the metadata.
##SDO_GEOMETRY_ORSPBIZ GEOMETRY_STORAGE "SDO_GEOMETRY" B_STORAGE "TABLESPACE ORSPBIZ" SDO_INDEX_SHAPE "tablespace=ORSPIDX sdo_indx_dims=2" UI_TEXT "Tablespace ORSPBIZ / ORSPIDX" END
When designing your own parameter groups, you might need to add parameters to support other geodatabase constructs such as geometric networks, terrains, or topologies. You could also satisfy these requirements by setting parameters in the DEFAULTS parameter group.
For example, if the GEOMETRY_STORAGE parameter of the DEFAULTS keyword is set to SDO_GEOMETRY when you create topologies, networks, or terrains, the default composite keywords for these are used. Since the default composite keywords don't specify GEOMETRY_STORAGE, the DEFAULTS GEOMETRY_STORAGE will be used; in this case, that is SDO_GEOMETRY.
If instead your DEFAULTS GEOMETRY_STORAGE keyword is set to something other than SDO_GEOMETRY but you want to create, for example, a terrain that uses SDO_GEOMETRY storage, you need to create a new set of terrain keywords designed to store terrains with SDO_GEOMETRY storage. The following is an example of this:
##TERRAIN_SDO
UI_TERRAIN_TEXT "The terrain default configuration"
GEOMETRY_STORAGE "SDO_GEOMETRY"
B_STORAGE "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc"
B_INDEX_ROWID "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc NOLOGGING"
B_INDEX_SHAPE "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc NOLOGGING"
B_INDEX_USER "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc NOLOGGING"
END
##TERRAIN_SDO::EMBEDDED
GEOMETRY_STORAGE "SDO_GEOMETRY"
B_STORAGE "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc"
B_INDEX_ROWID "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc NOLOGGING"
B_INDEX_SHAPE "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc NOLOGGING"
B_INDEX_USER "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc NOLOGGING"
END
See Composite keywords and geometry storage for more information.
Creating Oracle Spatial metadata for new feature classes
The Oracle Spatial database view USER_SDO_GEOM_METADATA contains metadata about SDO_GEOMETRY columns in existing tables. Each user has his or her own USER_SDO_GEOM_METADATA view. To be indexed and queried, the owner of the table must record metadata for each SDO_GEOMETRY column in USER_SDO_GEOM_METADATA. When creating a feature class in an ArcGIS client application, choose a configuration keyword that contains the following parameters, which specify the metadata for new SDO_GEOMETRY feature classes:
SDO_DIMNAME_<n> SDO_LB_<n> SDO_UB_<n> SDO_TOLERANCE_<n> SDO_SRID
The <n> in the first four parameters indicates the dimension to which you want the parameter setting to apply. Oracle Spatial permits feature geometries in the combinations x,y; x,y,z; x,y,m (measure); or x,y,z,m. Therefore, replace the <n> in the parameter names with one of the numbers (1, 2, 3, or 4) as follows:
1 | 2 | 3 | 4 |
|---|---|---|---|
x | y | ||
x | y | z | |
x | y | m | |
x | y | z | m |
If you do not supply these storage parameters, the ArcGIS client application that creates the feature class calculates the upper and lower bound (extent) and tolerance of each dimension.
Note:In addition to the aforementioned combinations, Oracle Spatial also allows feature geometries of x,y,m,z. However, do not use these with SDO_GEOMETRY feature classes in geodatabases.
If the configuration keyword specified during feature class creation contains the SDO_SRID parameter set to a valid coordinate reference system, the SDO_SRID value is used and written to the USER_SDO_GEOM_METADATA view even if a different coordinate reference system is provided by the client.
Creating a spatial index
The DBTUNE parameter SDO_INDEX_SHAPE determines how Oracle Spatial creates the spatial index. ArcSDE appends the contents of this parameter (the configuration string) to the CREATE INDEX statement before submitting the statement to Oracle. The configuration string is inserted into the SQL statement after the PARAMETERS keyword. For example:
CREATE INDEX MY_SP_INDEX ON MY_SP_TABLE(SHAPE)
INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ( <configuration string is inserted here> );
The configuration string is a quoted string containing a list of parameter = value elements. There are many parameters that you can specify in the configuration string. To understand the Oracle Spatial index parameters and how they interact, read the applicable sections of the Oracle Spatial User's Guide and Reference.
Notice the differences between the physical storage parameters in the spatial index configuration string and in a business table configuration string (as specified with the B_STORAGE parameter). One difference is due to the way Oracle expects these parameters to appear in SQL statements. The Oracle statements are formatted differently, so the configuration strings are formatted differently. Also, not every physical storage parameter used for creating tables is available for creating spatial indexes.
B_STORAGE "TABLESPACE ORSPBIZ PCTFREE 10 INITRANS 4 STORAGE(INITIAL 512000)" SDO_INDEX_SHAPE "tablespace=ORSPIDX initial=512000"
Creating raster datasets or catalogs that use SDO_GEORASTER storage
Oracle Spatial has a raster storage type: SDO_GEORASTER. There are three parameters that affect SDO_GEORASTER storage in a geodatabase:
- RASTER_STORAGE, which you can set to specify that rasters be created using the SDO_GEORASTER storage type.
- RDT_STORAGE, which specifies storage information for the SDO_GEORASTER blocks table, SDE_RDT_<N>. N is the rastercolumn_id value for the raster column.
- RDT_INDEX_COMPOSITE, which specifies the storage information for the composite index that is created on the SDO_GEORASTER blocks table, SDE_RDT_<N>_PK. N is the rastercolumn_id value of the raster column.
See ArcSDE and the Oracle Spatial raster type for more information on using the SDO_GEORASTER type.