Standardize data storage for geography spatial data type in SQL Server
Geography is one of the spatial data types introduced in SQL Server 2008. One of the characteristic of the geography data type is that it can accommodate any supported geometry based on any supported spatial reference system in the same field. So if the data that is stored in a column of geography data type is not standardized, it can be a very tedious and resource intensive task to figure out the geometry and spatial reference system associated with each value. To deal with this issue, we need to standardize the data stored in a field of that has the geography data type and in this tip we will look at how to deal with this issue.
Before we start with the solution, it would be helpful to understand these two points below.
(1) What is a spatial reference system?
A Spatial Reference System, in simple terms, is a reference system based on a point that can be uniquely plotted on a world map. In table "sys.spatial_reference_systems" you can list all the spatial reference systems supported by SQL Server. One of the most commonly used spatial reference system is "WGS 84, Datum" which is represented by spatial reference id (SRID) "4326".
(2) What are the geometries that can be accommodated in a geography data type?
Geometries that can be accommodated by the geography data type in SQL Server include Point, Multipoint, LineString, MultiLineString, Polygon, Multipolygon, and GeometryCollection. Each of these geometrical shapes require a different number of points to complete the shape.
Why Standardize Data
Before we look at how to standardize spatial data storage, it would be advantageous to understand why we need standardization. If you are now aware of how to consume spatial data, I suggest that you read my BI-Satellite article series which explains consumption of spatial data from various aspects.
As an example, I take it that the reader is aware of how to consume spatial data in SSRS using the Bing Maps control. Suppose my requirement is to plot points on a map and the result should look something like the below screenshot.
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.
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.
- Add these constraints on your existing tables as per the nature of your data
- Try adding conflicting data and study the error message that pop-up from the .NET CLR
- Read these related tips
About the author
View all my tips