ArcGIS and Microsoft spatial types

Geodatabases support storing vector data using Microsoft's 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.

Geometry vs. Geography

Both the Microsoft geometry and geography types provide SQL access to your spatial data. The following sections compare other characteristics of the two spatial types. For additional information on Microsoft spatial types, consult the SQL Server documentation.

Geometry

  • The geometry type supports any X/Y coordinate system.
  • Planar (flat-Earth, Euclidean) calculations and straight-line interpolation between vertices are used for rendering and spatial comparisons.
  • Conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.9, and compliant with SQL MM, an ISO standard.

You might 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.

Geography

  • The geography type supports many standard geographic coordinate systems, such as GPS latitude and longitude.

    Microsoft requires that you use SRIDs and the geographic coordinate systems defined in the SQL Server data dictionary.

  • An ellipsoid (round Earth) model and Great Elliptic interpolation of line segments between vertices are used for calculations and spatial comparisons.
  • Uses a global (spheroidal) layer extent.

    Coordinates of the data cannot exceed global extent.

  • In SQL Server 2008 and 2008 R2, features must be smaller than a single hemisphere.
  • When used with ArcGIS, features cannot have z- or m-coordinates.

You might use the geography storage type with ArcGIS if

  • The coordinate system you want to use is defined in the SQL Server data dictionary.
  • You don't need to store z- or m-values for features.
  • 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 that planar line interpolation over large spatial extents.

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

Using Microsoft spatial types with ArcGIS

Use configuration keywords to specify Microsoft spatial types

By default, geodatabases in SQL Server use the Microsoft geometry type. If you want to use the Microsoft geography type for storage in geodatabases in on-premises SQL Server databases, you must either (1) change the GEOMETRY_STORAGE parameter under the DEFAULTS configuration keyword in the SDE_dbtune table to GEOGRAPHY, or (2) specify a configuration keyword that designates a GEOMETRY_STORAGE parameter of 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 geography for their data most of the time. 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.

Use the sdedbtune administration command to alter the values in the SDE_dbtune table. This command can be installed using the ArcSDE Application Server for SQL Server installation.

When creating feature classes in SQL Server databases, choose either the geometry or geography keyword.

Registering an existing spatial table with the geodatabase

If you used a third-party application or SQL to create tables that contain Microsoft 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, cadastral fabrics, terrains, or schemas, or have subtypes, default values, domains, or validation rules. 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 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.

See Registering a table with the geodatabase for instructions on how to register the table with the geodatabase from ArcGIS for Desktop.

NoteNote:

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.

Creating a spatial index

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

In ArcGIS

When you create a feature class with a geometry or geography column in a geodatabase using ArcGIS, by default, ArcGIS creates a clustered primary key on the row id (ObjectID) 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 for information on setting values in the SDE_dbtune table.

When creating a feature class with a geometry or geography spatial column in a database or geodatabase through ArcGIS, the bounding box of the feature class is calculated as the extent of the data to be indexed. Any features falling outside this range will not be indexed but will still 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 will be used for the bounding box. You can set or recalculate a feature class's extent from the Feature Extent tab of 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 layer is switched from load-only I/O mode to normal I/O mode.

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>
 )

Known limits of using SQL Server geography with ArcGIS

The following is a list of things to keep in mind when storing SQL Server geography data in your enterprise geodatabase:

  • ArcGIS 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 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.
  • Zooming to a global extent on a geography feature class in SQL Server 2008 can generate a spatial filter that violates the hemisphere rule or which has coordinates outside valid geography extents, in which case filter shape creation will fail and the query will return no result.
8/21/2013