SQL Server spatial types and ArcGIS

Geodatabases support storing vector data using Microsoft geometry and geography types. These types are available in SQL Server; they do not require a separate installation to use. You also can use ArcGIS to access database tables that contain geometry or geography columns.

Use the geometry storage type with ArcGIS if either of the following apply:

• The coordinate system you want to use is not a geographic coordinate system or is not defined in the SQL Server data dictionary.
• You want to store z- or m-values with the shape.

Use the geography storage type with ArcGIS if any of the following apply:

• The coordinate system you want to use is defined in the SQL Server data dictionary.
• Your data covers large spatial extents and you need to use SQL area and length calculations.

  The SQL area and length calculations that use Great Elliptic line interpolation can be noticeably different than planar line interpolation over large spatial extents.

• You need to use Great Elliptic line interpolation for SQL spatial queries.

Use configuration keywords to specify spatial types

By default, geodatabases in SQL Server use the geometry type. If you want to use the geography type for storage in geodatabases in SQL Server, you must do one of the following:

• Change the GEOMETRY_STORAGE configuration parameter under the DEFAULTS configuration keyword to GEOGRAPHY. You should only alter the GEOMETRY_STORAGE parameter under the DEFAULTS keyword if the majority of your users will use geography for their data most of the time.
• Specify a configuration keyword that designates a GEOMETRY_STORAGE configuration parameter of GEOGRAPHY when creating feature classes. If only some of your data will be stored in the geography type, designate a separate keyword when the feature class is created. A keyword is provided for you—GEOGRAPHY—or you can create your own custom keyword.

When creating feature classes in a SQL Server database (not a geodatabase), choose either the geometry or geography keyword.

Register an existing spatial table with the geodatabase

If you used a third-party application or SQL to create tables that contain SQL Server spatial type columns in your geodatabase, you can register these tables with the geodatabase to take advantage of geodatabase functionality such as relationship classes, topology, geometric networks, parcel fabrics, or have subtypes, default values, domains, or validation rules. To do so, the tables must meet the following 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 the geodatabase 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 does not, the projection must be defined at the time of registration.
• If the table contains a primary key, it must be clustered.

Note:

Enabling a geodatabase in an existing database does not automatically register existing tables with the geodatabase. Any tables or feature classes that you want to participate in the geodatabase must be registered with the geodatabase separately.

Create a spatial index

SQL Server requires the table to have a primary key to be able to create a spatial index on it.

In ArcGIS

When you use ArcGIS to create a feature class with a geometry or geography column, ArcGIS creates a clustered primary key on the ObjectID column of the business table by default. Next, ArcGIS builds a spatial index using SQL Server defaults. When the feature class is registered as versioned, a clustered primary key is created on the ObjectID and state id columns of the Adds table, and the spatial index is built.

ArcGIS calculates the bounding box of the feature class with a geometry or geography spatial column as the extent of the data to be indexed. Any features falling outside this range are not indexed but will be returned in spatial queries. If the feature class extent is not set, the maximum range of coordinates for the feature class's spatial reference system is used for the bounding box. You can set or recalculate a feature class's extent from the Feature Class Properties dialog box. If the feature class is in a geodatabase, the bounding box is adjusted with the latest extent whenever the spatial index is dropped and re-created.

Outside ArcGIS

For spatial tables created outside 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 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>
 )

The following syntax creates a spatial index on a geography column:

CREATE SPATIAL INDEX <index_name>
 ON <table> (<spatial column>)
 USING GEOGRAPHY_GRID
 WITH (
  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>
 )

ArcGIS adds a column to store extra geometric elements

The geometry and geography types cannot store all types of geometric elements that geodatabases must support. Therefore, when you create or register a feature class that uses the geometry or geography storage type with a geodatabase in SQL Server, ArcGIS adds a column to the business table to store these additional geometric elements. The column is called gdb_geomattr_data. The additional geometric elements include the following:

• Parametric objects such as circular arcs and Bezier curves created with advanced editing tools
• PointIDs
• Multipatch features
• Surface patches from ArcGIS Spatial Analyst extension

When ArcGIS detects the data source has these additional geometric elements, a simple geometric representation is stored in the shape column and the unmodified data (including the additional elements) is stored in the gdb_geomattr_data column.

Known limits of using SQL Server geography with ArcGIS

For the most part, measurements for geography data are in meters. The unit of measure is indicated in sys.spatial_ref_system; check the units used with the EPSG value associated with your data.