ArcSDE and Microsoft spatial types

ArcSDE geodatabases support storing vector data using Microsoft's geometry and geography types. These types are available in SQL Server 2008 and do not require a separate installation to use. You would use these data types if you want to access your spatial data using SQL.

The Microsoft geometry type is similar to existing GIS spatial data types: it uses coordinates in an arbitrary plane, such as defined projections.

The Microsoft geography type is used for data on a spheroid that is stored with latitude and longitude coordinates.

The two types differ in the way that they make spatial calculations. For more information on Microsoft spatial types, consult the SQL Server 2008 documentation.

Use configuration keywords to specify Microsoft spatial types

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 (1) change the GEOMETRY_STORAGE parameter under the DEFAULTS configuration keyword in the SDE_dbtune table to GEOMETRY or GEOGRAPHY or (2) specify a configuration keyword that designates a GEOMETRY_STORAGE parameter 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 most 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.

Prerequisites for using Microsoft geometry or geography in a geodatabase

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:

• The table must be owned by the user who is registering it.
• The table must have a single spatial column of either geometry or geography type.
• All shapes in the column must be the same spatial type, either points, lines, polygons, multipoints, multistrings, or multipolygons.

  Adding shapes of a different type to the column using SQL after registering the table 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 ArcSDE SRID specified at the time of registration must exist in the SDE_spatial_references system table. If it does not, the projection must be defined at the time of registration.
  Note:

  The ArcSDE SRID and Microsoft geometry or geography SRID are not the same things. The ArcSDE SRID is assigned by ArcSDE when the coordinate system is added to the SDE_spatial_references system table.

• If the table contains a primary key, it must be clustered.

Register an existing geometry or geography table with ArcSDE and the geodatabase

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 project 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. However, ESRI does not recommend you use user-maintained row IDs.

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. 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 of ArcGIS then register the tables with ArcSDE. Alternatively, you can create the spatial index after registering the table with ArcSDE using the sdelayer command with the alter operation to define the layer extent, then use the load_only_io operation to put the table in load-only I/O mode and the normal_io operation to place it back in normal I/O mode, which will automatically build a spatial index. 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 more information on using the sdelayer command, consult the ArcSDE Administration Command Reference.

If you need the feature class to participate in geodatabase functionality, such as relationship classes, topology, geometric networks, cadastral fabrics, terrains, or schemas, or have subtypes, default values, domains, or validation rules, it must also be registered with the geodatabase.

You can register the datasets in ArcCatalog as follows:

1. Start ArcCatalog.
2. Connect to the geodatabase that contains the layer to be registered. Be sure to connect as the layer owner.
3. Right-click the layer you want to register with the geodatabase.
4. Click Register with Geodatabase.

Create a spatial index

SQL Server's prerequisite for creating a spatial index is that the table must have a primary key on it.

For spatial tables created outside of ArcGIS—for example, those created using SQL—you must create a 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, by default, 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 Altering the contents of the DBTUNE table after it is created for information on setting values in the SDE_dbtune table.

Known limits of using SQL Server geography with an ArcSDE geodatabase

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.