Standardize data storage for geography spatial data type in SQL Server

Say I have data in my spatial field which is based on different spatial reference systems and of different shapes like point, polygon, etc. When applications need to plot data on a map using spatial data, all of these data are required to follow a uniform geometry and use the same spatial reference system.

It can be easily understood with an example, that you cannot construct a house using different units of measurements for different rooms. In case, you use different units of measurements like inches and feet, you need to convert one of them to make a comparison. In a spatial data context, this is spatial reference systems.

When When we need to build walls, we generally need bricks of a uniform structure, we can't have a few bricks of rectangular shape and a few the shape of a football. Consider how your wall would look! In a spatial data context, this is the geometry stored within the spatial data in the spatial field.

When you attempt to consume spatial data using the Maps Control Wizard in SSRS, one of the screen where standardization is expected is shown below. The layer type expects a single / uniform type of geometry.

Standardization

Generally applications that consume these data firstly need data that is based on a single spatial reference system and secondly this data is mostly expected to be of a common geometrical type. It really depends on the kind of design that has been implemented, but in general there are two ways to deal with this requirement. If your table stores this spatial data based on multiple spatial reference systems and multiple types of geometry in the same field, then when querying the data, it would be required to validate each value and filter out the data based on the required attribute values. The second option is to add a constraint to the table containing this spatial field of geography data type, so that only standard data can be stored in each field and fields can be separated out based on the kind of reference system and geometrical structure.

Let's take a look at how to implement these constraints.

(i) Geography data type contains one instance level property called "STSrid" which returns the SRID associated with the value. So to add a constraint that allows spatial data that is based on "4326" SRID, in a table named "SpatialTable" on a field named "SpatialCol", the syntax would be as below:

ALTER TABLE SpatialTable
ADD CONSTRAINT SRIDCheck
CHECCHECK (SpatialCol.STSrid = 4326)

(ii) Geography data type contains one instance level method called "STGeometryType" which returns the kind of geometry contained in the value stored in field of geography data type. To add a constraint that allows only "POINT" to be stored, in a table named "SpatialTable" on a field named "SpatialCol", the syntax would be as follows:

ALTER TABLE SpatialTable
ADD CONSTRAINT SpatialGeomCheck
CHECK (SpatialCol.STGeometryType() = 'POINT')

Keep in view that the property or method name are case-sensitive. By placing such standardization and implementing proper design by placing appropriate differentiators for different spatial reference systems, consumption of spatial data by different applications (for example the map control in SSRS 2008 R2) can become very easy.

Next Steps