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.
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 are using SQL Server 2008 or 2008 R2 and want to store z- or m-values with the shape.
You might 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.
- You use SQL Server 2008 or 2008 R2 and don't need to store z- or m-values for features.
- You use SQL Server 2012, for which z- and m-values are supported with ArcGIS.
- 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 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 Register a table with the geodatabase for instructions.
Create 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 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 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 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>
)
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:
- If you are using SQL Server 2008 or 2008 R2, ArcGIS cannot store z- or m-values in the geography data type. 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 to a geodatabase in SQL Server 2008 or 2008 R2. Alternatively, data with 3D or measure attributes can be imported into feature classes that use geometry or the compressed binary storage type, or into geodatabases in SQL Server 2012.
- 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.