Working with Spatial Data in SQL Server 2008

Blog Archive

Home > Blog > Working with Spatial Data in SQL Server 2008

Working with Spatial Data in SQL Server 2008

Tags: SQL Server

With the release of Microsoft SQL Server 2008 comes the ability to work with spatial data. This allows us to store, manipulate and visualise spatial data whilst getting all the benefits of persisting data to a SQL Server database. In this post I will be looking at the different types that are used to store this data as well as how to work with the methods associated with these types.

Spatial Reference Systems

To represent a geographical entity, a coordinate system is needed. There are many implementations available. They are created for local (to map out a campus), regional (mapping a country) or global use. To use data captured in Great Britain for example, you would have to use the British National Grid Reference System.

If you want to plot locations globally, you would typically use the World Geodetic System. It is a standard coordinate system and the latest revision in use is WGS 84 (SRID 4326 – see next paragraph). This is the default system used by GPS devices to represent geodetic data. To get a listing of all SQL supported reference systems and their units of measure, run this query :

SELECT * FROM sys.spatial_reference_systems

When working with spatial data and methods in SQL Server 2008, you need to specify the Spatial Reference ID (SRID). This defines the reference system used by the data objects. The objects obviously have to have the same SRID, as it doesn’t make sense to use objects from one reference system with another in any calculation.

Data Types

Since spatial data is quite complex, it can’t be represented by simple data types. Therefore SQL Server uses new System-Defined Types to store these complex data objects. System-Defined Types are automatically installed with SQL Server 2008 and are basically .NET types. These new types are the Geography and Geometry data types.

Geography : This type is used to represent geodetic data i.e. data that defines any point on a spherical body, in this case our home planet. Any calculations used with the Geography type, account for the curvature of the Earth. Geographical point locations are represented with Latitude and Longitude properties. Using these properties, you can accurately locate this point on a 3D surface. Geography operations in SQL Server typically use the WGS 84 reference system and therefore has a SRID of 4326.

(Picture: Microsoft Virtual Earth)

Geometry : This type is used to represent planar data i.e. data that defines a point on a flat 2D surface. Geometry uses X and Y coordinates to represent point locations. The scale across these axes is uniform. It is therefore commonly used in conjunction with a planar reference system that has already accounted for the curvature of the Earth in order to map to a flat surface. The relevant SRID for the reference system used would have to be specified. It is also used when the scale is small enough that the curvature of the Earth would have a negligible effect on any results. For example, mapping buildings and measuring distances in a manufacturing plant compound. In cases like the latter, the SRID would be 0.

(Picture: Microsoft Virtual Earth)

Both Geography and Geometry are actually abstract types, meaning that you can’t create an instance of these classes by themselves, but rather their derived types. The instantiable types are shown in blue. This diagram is the same for Geography, with Geography being the base class.

(Picture: MSDN)

These geometries represent the following:

Point : An exact location, without length or area. Defined with X and Y (or Lat and Long) values.

LineString : A path between multiple, sequential Points. It has a length and is therefore one-dimensional. A LineString can be closed (a ring) if the start and end points are the same.

Polygon : A two-dimensional surface defined by a bounding ring (closed LineString). It can also contain multiple interior rings (holes). The interiors of these rings are not considered part of the Polygon. A Polygon has area and length values.

GeomCollection : Can contain a collection of all of the above. The derived types MultiPoint, MultiLineString and MultiPolygon can only contain a collection of the type specified by the name.

Data Formats

All the data types can be represented in three formats. They are defined by the Open Geospatial Consortium (OGC).

Well-Known Text (WKT): Presents geometric objects in a human-readable form. E.g.

POINT(10 10)

LINESTRING(10 10,20 20)

Well-Known Binary (WKB) : Presents geometric objects in a binary encoded format.

Geography Markup Language (GML) : XML-based representation of spatial data. E.g.

<Point xmlns="http://www.opengis.net/gml">

  <pos>57.15 -2.15</pos>

</Point>

Importing Spatial Data into SQL Server 2008

Before we look at how to work with the data types, we need some sample data. There are many sources of map data on the internet in various formats. Some of the most popular are Shape and KML. You can find some here:

http://www.collinssoftware.com/freegis_by_region.htm

http://www.cipotato.org/diva/data/MoreData.htm

http://biogeo.berkeley.edu/bgm/gdata.php

http://planet.openstreetmap.org/

We will be using data that is provided in the Shape file format. Unfortunately, SQL Server does not provide any easy way to import spatial data. Luckily we have an easy to use tool (Shape2SQL) created by Morten Nielsen. You can find it here:

http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx

After downloading and extracting some shape files, you can reference them from Shape2SQL and import them into your database. If you are importing geodetic data, be sure to select the Geography type and also set the correct SRID for the reference system that your data uses. This will usually be 4326. It is also a good idea to create a spatial index as this will benefit the execution time of your calculations later.

After the data is imported you can query it the same way as you would normally:

You might have noticed the nice new Spatial results tab. The previous query looks like this in the new tab:

You can select the Spatial data column you want to display if there are more than one in your query. If there is a textual description column in the table you can also select that as a label.

The projection dropdown deserves some further explanation. A projection is basically a mapping of a point in 3D space onto a 2D surface. Projections enable us to project a model of the Earth onto a map for example. There are various projections available, including Mercator above, which is a cylindrical map projection. You can read more about projections here.

Now that we have data in the database, we can look at how to work with the methods that allow us to do more intricate calculations.

Working with Data Type Methods

Note: All methods and properties prefixed with “ST” are OGC standards. All other methods and properties are Microsoft’s extensions to this standard.

Note: All CLR spatial methods and properties are case sensitive

Creating an instance

Before we can work with the methods, we need an instance of a data type. Instances can be created by either calling static methods on the Geography and Geometry abstract classes or querying spatial columns in a table. Static methods act at the class level and therefore do not need an existing instance. The notation for calling a static method in SQL is to separate the data type and static method names with double colons (::).

DECLARE @geo GEOGRAPHY

SET @geo = GEOGRAPHY::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)'

                                                                          , 4326);

The OGC static methods expect either a WKT or WKB representation of the instance type you require, depending on the method you use. For example, the WKB equivalent of the above method is called STGeomFromWKB. The second parameter is the required SRID as discussed previously. This static method returns an instance of type LineString. A FormatException would have been thrown if the provided string was incorrect. More specific methods are available e.g. STPointFromText, STLineFromText, STPolyFromText that only support one type. There are multiple ways of creating an instance of a type as far as syntax is concerned.

DECLARE @geo GEOGRAPHY

-- Expects a Point tagged string

SET @geo = GEOGRAPHY::STPointFromText('POINT(-122.34900 47.65100)'

                                        , 4326);

-- CLR types require a default method, which is STGeomFromText.

-- This is functionally equivalent to calling that method.

-- The default SRID is used for the relevant data type

-- i.e. Geography SRID = 4326; Geometry SRID = 0

SET @geo = 'LINESTRING(-122.360 47.656, -122.343 47.656 )'

-- CLR types need to provide implementations of the ToString

-- and Parse methods for serialisation purposes.These can

-- be called explicitly. The SRID defaults apply.

SET @geo = GEOGRAPHY::Parse('POLYGON((-122.358 47.653, -122.348 47.649, 
                            -122.348 47.658, -122.358 47.653))'

-- Create an instance from GML.

SET    @geo = GEOGRAPHY::GeomFromGml(

'<Point xmlns="http://www.opengis.net/gml">
  <pos>13.75 100.5</pos>
</Point>'

, 4326)

There are also methods to create instances of the collection types : STGeomCollFromText, STMPointFromText, STMLineFromText, STMPolyFromText. All of the above methods are also available in their WKB equivalents.

Working with Instances

To use instance methods, a class instance must first be created. The static methods discussed above can provide us with these instances. Querying a spatial data column also allows us access to their instance methods. Instance properties and methods can be accessed by using dot notation. The sample data I am using has a SRID of 4326, which uses metres as a unit of measurement. I will be using data defining the administrative boundaries for the United Kingdom.

STArea : Calculates the total surface area of a polygon.

SELECT [Geo].STArea() AS Area

  FROM [GeoTest].[dbo].[UnitedKingdom]

  WHERE Name = 'Berkshire'

Result: 1224279111.12743 (m²)

STIntersects : Verifies whether one instance intersects with another. In this query, STIntersect is used to retrieve all the cities within a county. Therefore, the points that intersect with the polygon.

DECLARE @County GEOGRAPHY  = (SELECT [Geo]

                              FROM [GeoTest].[dbo].[UnitedKingdom]

                              WHERE Name = 'Manchester')

SELECT @County AS Location

UNION ALL

SELECT [Geo]

FROM [GeoTest].[dbo].[City]

WHERE @County.STIntersects(Geo) = 1

Result:

STBuffer : Calculates a buffer region around all the points of an instance at the distance provided. Specifying a negative distance provides a diminished area within the instance. In this example, a buffer region is created around a point (city) to determine whether another point is within range.

DECLARE @City GEOGRAPHY  = (SELECT [Geo]

                            FROM [GeoTest].[dbo].[City]

                            WHERE Name = 'Birmingham')

SELECT [Name]

FROM [GeoTest].[dbo].[City]

WHERE @City.STBuffer(120000).STIntersects(Geo) = 1

Result: Bristol

STDistance : Returns the shortest distance between two points. We will use the STDistance method to prove the results of the previous STBuffer example.

DECLARE @City1 GEOGRAPHY  = (SELECT [Geo]

                            FROM [GeoTest].[dbo].[City]

                            WHERE Name = 'Birmingham')

DECLARE @City2 GEOGRAPHY  = (SELECT [Geo]

                            FROM [GeoTest].[dbo].[City]

                            WHERE Name = 'Bristol')

SELECT @City1.STDistance(@City2) AS Distance

Result: 115215.269518136 (m)

STDifference : Removes a given instance from its containing instance and returns the remainder as a new object.

DECLARE @County GEOGRAPHY  = (SELECT [Geo]

                              FROM [GeoTest].[dbo].[UnitedKingdom]

                              WHERE Name = 'Devon')

SELECT [Geo].STDifference(@County)

FROM [GeoTest].[dbo].[UnitedKingdom]

Result:

Representations of geometries can also be retrieved in the three well known formats already mentioned. To achieve this you would use the following instance methods : STAsText, STAsBinary, AsTextZM, ToString and AsGml.

STAsText : Returns the OGC Well-Known Text representation of a geography instance.

SELECT [Geo].STAsText()

FROM [GeoTest].[dbo].[City]

WHERE Name = 'London'

Result: POINT (-0.0833333333333333 51.5333333333333)

These are just a few examples of the instance methods available. You can find a full Method reference for Geography here:

http://msdn.microsoft.com/en-us/library/bb933802.aspx

and for Geometry here:

http://msdn.microsoft.com/en-us/library/bb933973.aspx

Developing in .NET using Spatial Data

Since the Spatial data types are .NET types, you can leverage all the functionality in your normal application development. The .NET type equivalents of Geography and Geometry are SqlGeography and SqlGeometry respectively. You can use SqlGeographyBuilder and SqlGeometryBuilder to easily build up your own objects manually. These types can be found in the Microsoft.SqlServer.Types.dll assembly. This topic is outside the scope of this post.

Conclusion

As you can see, the application possibilities for spatial data is virtually limitless. I am sure that with the inclusion of these new data types within SQL Server 2008, it will enable more developers to produce excellent products that help us visualise our world better.