By default, ArcSDE geodatabases in SQL Server use the ArcSDE compressed binary type, so if you want to use the Microsoft geometry or geography types for storage, you must either change the GEOMETRY_STORAGE parameter under the DEFAULTS configuration keyword in the SDE_dbtune table to GEOMETRY or GEOGRAPHY, or specify a configuration keyword that designates a GEOMETRY_STORAGE of GEOMETRY or GEOGRAPHY when creating feature classes. You should only alter the GEOMETRY_STORAGE parameter under the DEFAULTS keyword if the majority of your users will use geometry or geography for their data the majority of the time. If only some of your data will be stored in the geometry or geography type, designate a separate keyword when the feature class is created. Two keywords are provided for you, GEOMETRY and GEOGRAPHY, or you can create your own custom keywords.
Be aware that if you create a topology, network, or terrain that includes feature classes that are using geometry or geography storage, you must designate a configuration keyword when creating the topology, network, or terrain that uses the same storage type. Configuration keywords are included in the SDE_dbutne table for you to use. They are as follows:
TOPOLOGY_GEOMETRY
TOPOLOGY_GEOGRAPHY
NETWORK_GEOMETRY
NETWORK_GEOGRAPHY
TERRAIN_GEOMETRY
TERRAIN_GEOGRAPHY
If you have created tables containing Microsoft geometry or geography columns using a third-party application or SQL, you can register these tables with ArcSDE and the geodatabase to take advantage of geodatabase functionality. To do so, the tables must meet certain prerequisites:
- It must be owned by the user registering the table.
- It must have a single spatial column of either geometry or geography.
- All shapes in the column must have the same type of geometry (points, lines, or polygons), although the geometry can be multipart (for example, multipoint, multistring, or multipolygon).
NOTE: Adding shapes of a different type to the column using SQL after registering the layer with ArcSDE is also not supported and will result in unpredictable behavior of the feature class.
- All shapes in the column must use the same spatial reference ID (SRID).
- The SRID specified at the time of registration must exist in the SDE_spatial_references system table. If it is not, either the projection must be defined at the time of registration or an SRID of 0 can be used. Specifying 0 assigns a spatial reference of geographic coordinate system WGS 1984.
- If the table contains a primary key, it must be clustered.
To register the table with ArcSDE, use the sdelayer command with the register operation.
The following is an example of registering a table called spdata containing polygon geometries (–e a) in a geometry column called features. The table has an integer column called fid that will be used as a unique feature identifier column, the values for which will be maintained by ArcSDE (–C fid,SDE). The spatial reference ID (SRID) used to register the layer is 5 (–R 5). This is the SRID in the SDE_spatial_references table.
sdelayer –o register –l spdata,features –e a –C fid,SDE –t GEOMETRY
–R 5 –i sde:sqlserver:tornado –D agency1 –u vijay –p open.septagenarian
If the spatial reference you need does not exist in the SDE_spatial_references table, you can create a template feature class in ArcCatalog that uses the spatial reference you want, then query the SDE_spatial_references table to see what SRID was assigned to it.
For an sde schema geodatabase
SELECT * from sde.SDE_spatial_references
For a dbo schema geodatabase
SELECT * from dbo.SDE_spatial_references
Find the spatial reference you added with the template feature class and identify the SRID number. You could then use that number with the –R option when registering other tables that have geometry columns and are in the same coordinate reference system.
You could also specify a projection ID with the –G option instead of using the –R option. When registering tables with a geography column, the projection ID used must match one of the SRIDs stored in the SQL Server sys.spatial_references_systems table.
sdelayer –o register –l spgeogdata,features –e a –C fid,SDE –t GEOGRAPHY
–G 4326 –i sde:sqlserver:tornado –D agency1 –u vijay –p open.septagenarian
For tables with either type of spatial data storage that contain a large number of records, registration may take less time if you register the row ID column as user maintained.
sdelayer –o register –l polsamples,shape –e p –C fid,USER –t GEOMETRY
–R 300 –i 6543 –D agency1 –u guadalupe –p overnout
Be aware that if you register the feature identifier column as user maintained and subsequently register the feature class with the geodatabase, ArcGIS will add an additional feature identifier column, object_ID. ArcGIS will maintain the values of this column. If the table contains a large number of records, adding this additional object_ID column may take some time.
Executing sdelayer –o register registers your table with ArcSDE, making it a feature class. This adds a record to the SDE_layers, SDE_geometry_columns, SDE_column_registry, and SDE_table_registry system tables in the geodatabase. It does not create a spatial index. You can use SQL to create spatial indexes on tables created outside ArcGIS, then register the tables with ArcSDE. Alternatively, you can create the spatial index after registering the table with ArcSDE using the sdelayer –o load_only_io and normal_io operations. Or if you subsequently register the layer with the geodatabase, you can create a spatial index using ArcCatalog. When you use sdelayer or ArcCatalog to create the spatial index, the spatial index will be created using the spatial index parameters specified in the SDE_dbtune table and the current extent of the data in the layer.
For further information on using the sdelayer command, consult the ArcSDE Administration Command Reference provided with the ArcSDE component of ArcGIS Server Enterprise.
If you need the feature class to participate in geodatabase functionality (such as relationship classes, topology, geometric networks, cadastral fabrics, terrains, schemas) or have subtypes, default values, domains, or validation rules, it must also be registered with the geodatabase.
Registering the feature class with the geodatabase adds a record to the GDB_OBJECTCLASSES and GDB_FEATURECLASSES system tables.
You can register the datasets in ArcCatalog as follows:
- Start ArcCatalog.
- Right-click the feature class you want to register with the geodatabase.
- Click Register with Geodatabase.
SQL Server's prerequisite for creating a spatial index is that the table must have a clustered primary key on it.
For spatial tables created outside ArcGIS (for example, those created using SQL), you must create a clustered primary key on the table and create a spatial index using SQL. The following is the SQL syntax for creating a spatial index on a table that contains a geometry or geography type column:
CREATE SPATIAL INDEX <index_name>
ON <table> (<spatial column>)
USING GEOMETRY_GRID
WITH (
BOUNDING_BOX = minx,miny,maxx,maxy),
GRIDS = (low|medium|high, low|medium|high, low|medium|high, low|medium|high),
CELLS_PER_OBJECT = n,
<other regular btree index options like filegroups, fill factors, etc>
)
When creating a layer with a geometry or geography spatial column through ArcGIS, the bounding box of the feature class is calculated as the extent of the data that is to be indexed. Any features falling outside this range will not be indexed but will still be returned in spatial queries. If the layer extent is not set, the maximum range of coordinates for the layer's spatial reference system will be used for the bounding box. Whenever the layer is switched from load only I/O mode to normal I/O mode, the bounding box is adjusted with the latest layer extent.
Additionally, when you create a feature class with a geometry or geography column using ArcGIS, ArcSDE creates a clustered primary key on the row id column of the business table, then builds a spatial index based on the values set for the B_MS_SPINDEX parameter of the configuration keyword in the SDE_dbtune table used when the feature class was created. When the feature class is registered as versioned, a clustered primary key is created on the row id and state id columns of the Adds table, and the spatial index is built based on the values set for the A_MS_SPINDEX in the configuration keyword used when the feature class was created. The default value for both of these parameters is as follows:
GRIDS = (MEDIUM, MEDIUM, MEDIUM, MEDIUM), CELLS_PER_OBJECT = 16
See
DBTUNE configuration keywords,
DBTUNE configuration parameter name-configuration string pairs, and
The dbtune file and the DBTUNE table for information on setting values in the SDE_dbtune table.
The following is a list of things to keep in mind when storing SQL Server geography data in your ArcSDE geodatabase:
- ArcSDE cannot store z- or m-values in the geography data type at this release. Therefore, when you bring existing data into the geodatabase, such as a shapefile or a feature class from another geodatabase, and it must be stored using the geography data type, the incoming dataset cannot have 3D (z) or measure (m) attributes.
The z and m attributes must be disabled before the data can be imported. Alternatively, data with 3D or measure attributes can be imported into feature classes that use geometry or the default compressed binary storage type.
- For the most part, measurements for geography data are in meters. The unit of measure is indicated in the sys.spatial_ref_system; check the units used with the EPSG value associated with your data.
- Individual features and all parts of a multipart feature must fit into a single hemisphere.
