ArcGIS Server Banner

Using the Microsoft spatial types

Using the Microsoft spatial types

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
Note:This topic was updated for 9.3.1.

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.

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's spatial types, consult the SQL Server 2008 documentation.

How do ArcSDE geodatabases use Microsoft's 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 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

Using existing geography or geometry tables

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:

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:

  1. Start ArcCatalog.
  2. Right-click the feature class you want to register with the geodatabase.
  3. Click Register with Geodatabase.

Creating a spatial index

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.

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: