Bringing In Features from SQL Server Spatial
Concept Procedure Quick Reference

AutoCAD Map 3D supports both SQL Server and SQL Server Spatial.

When you view and edit features from a SQL Server Spatial data source, you can do the following:

NoteIf you close a drawing that uses a SQLServerSpatial connection and then reopen that drawing, you will need to first reconnect to the SQLServerSpatial connection and provide your user name and password. This information is not remembered once the drawing is closed.
NoteYou can customize the SQL Server Spatial FDO provider. The API has custom commands to support schema read/write and geospatial and non-geospatial data read/write. For more information, refer to the FDO API Reference and the FDO Provider for SQL Server API Reference.

Dealing With Invalid Geometry

SQL Server Spatial validates geometry objects that are added to the database. It validates the values of geometry columns after they are added. As a result, some geometries are flagged as invalid, even though they are saved. This usually affects polygons (for example, polygons that are not closed) and spikes in geometry. Validation is based on OGC rules.

Querying invalid geometries using a spatial filter (for example, using an expression) generates an error and the query fails. This affects SQL Server geometry (but not SQL Server geography).

NoteThe FDO Provider for SQL Server Spatial uses the geography data type for latitude/longitude coordinate systems and the geometry data type for all others. The geography data type does not retain invalid geometry values and rejects them right away. It does not support the STIsValid() or IsValid(geometry) functions described below.

How Invalid Geometry is Handled

The FDO Provider for SQL Server Spatial recognizes that invalid geometries may exist in a table that is being processed. The provider includes code to avoid having queries fail, using the following strategies:

  • A query with no spatial filter returns all data.
  • A query with a spatial filter skips invalid geometries so that the query does not fail altogether. Valid geometries matching the spatial filter are returned.
  • A query using the FDO EnvelopeIntersects filter does not skip invalid geometries if a spatial index is included on the geometry column. In this special-case combination, SQL Server Spatial does not fail on the query because it doesn’t process the details of the geometry. MapGuide uses this type of filter as its default selection filter.

AutoCAD Map 3D and MapGuide can draw these invalid geometries without a problem.

Using Expressions to Find Invalid Geometry

SQL Server provides a function called STIsValid() that returns 1 for valid geometries and 0 for invalid geometries. FDO exposes access to that function using a function called IsValid(geometry) at the FDO level. You can use this function in AutoCAD Map 3D and MapGuide when you create an expression to filter or select data. If you select data using no filter or using just the EnvelopeIntersects filter, you can create a calculated property in the Data Table using the IsValid function to see which geometries are valid or invalid.

Correcting Invalid Geometry

SQL Server Spatial also provides a function you can use to correct invalid geometries on the server. You cannot use this function from within AutoCAD Map 3D and MapGuide, but you can use it directly against SQL Server, for instance, using Management Studio. Here is an example of this method:

update dbo.road set geom = geom.MakeValid() where geom.STIsValid() = 0;

This operation makes the geometry valid by modifying its invalid parts. You may prefer to edit the geometry yourself, rather than use a default correction with uncertain results.

For more information on the STIsValid() and MakeValid() functions, as well as other aspects of SQL Server Spatial, consult the SQL Server Spatial online documentation.

See Also