Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Standardize data storage for geography spatial data type in SQL Server


By:   |   Last Updated: 2010-09-24   |   Comments   |   Related Tips: More > Spatial Data Storage

Problem

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.

Solution

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".

 a spatial reference system

(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.

how to consume spatial data in SSRS using the Bing Maps control

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.

 using the Maps Control Wizard in SSRS


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


Last Updated: 2010-09-24


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools