Geodatabases support storing vector data using Microsoft geometry and geography types in Microsoft SQL Server. These types are available automatically 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 fields (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.
- The data covers large spatial extents and you need to use SQL area and length calculations.
- You need to use a great elliptic arc interpolation for SQL spatial queries.
The SQL area and length calculations that use a great elliptic arc interpolation can be noticeably different than planar line interpolation over large spatial extents.
Use configuration keywords to specify spatial types
By default, geodatabases in SQL Server use the geometry spatial type. To use the geography type for feature class storage in geodatabases in SQL Server, you must do one of the following:
- If the majority of your users will use geography for their spatial data most of the time, change the GEOMETRY_STORAGE configuration parameter under the DEFAULTS configuration keyword to GEOGRAPHY. All feature classes created in the geodatabase will use the geography type by default. See Alter configuration keywords for instructions.
- If only some of the data will be stored in the geography type, specify a configuration keyword that designates a GEOMETRY_STORAGE configuration parameter of GEOGRAPHY when creating feature classes. A keyword is provided—GEOGRAPHY—or you can create a custom keyword.
Choose either the geometry or geography keyword when you create feature classes in a SQL Server database (not a geodatabase).
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 fields in the database where your geodatabase is stored, you can register these tables with the geodatabase to take advantage of geodatabase functionality such as relationship classes, topology, utility 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 field of either geometry or geography type.
- All shapes in the field must be the same spatial type, either points, lines, polygons, multipoints, multilinestrings, or multipolygons.
Adding shapes of a different type to the field using SQL after registering the table with the geodatabase is not supported and will result in unpredictable behavior of the feature class.
- All shapes in the field 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 need 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 field, ArcGIS creates a clustered primary key on the object ID field of the business table by default. Next, ArcGIS builds a spatial index using SQL Server defaults. When the feature class is registered as for traditional versioning, a clustered primary key is created on the object ID and state id fields 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 field as the extent of the data to be indexed. Any features that are outside this range are not indexed but are 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. Consult the Microsoft SQL Server documentation for instructions and examples.
ArcGIS adds a field 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 field to the business table to store these additional geometric elements. The name of the field is 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 field and the unmodified data (including the additional elements) is stored in the gdb_geomattr_data field.
If you register a table or view that contains a field named gdb_geomattr_data, the data in the field must be the geometric elements that are associated with the object.
SQL Server geography units in ArcGIS
Typically, measurements for geography data are in meters. The unit of measure is indicated in the sys.spatial_ref_system table; confirm the units used with the European Petroleum Survey Group (EPSG) value associated with the data.