SQL Saturday #21 Orlando – Spatial Data in SQL 2008

October 17, 2009

This session is

a quick tour of the spatial features of SQL Server 2008slide1

PowerPoint slides

PowerPoint as PDF

Tyler Chessman’s article in SQL Server Magazine’s December 2008 issue is my primary source of information to implement my spatial solutions. Visit http://www.sqlmag.com and search for instant doc ID 100528.
Here is a link to the article

What is Spatial Data

  • Spatial data represents the shape and physical location of an object.
  • The object can be a house, business, sub-division, or a county.
  • SQL Server 2008 has two new data types GEOMETRY and GEOGRAPHY.
  • GEOMETRY works with flat objects.
  • GEOGRAPHY considers the shape of the earth.

CLR Types

  • GEOGRAPHY and GEOMETRY are CLR data types.
  • You do not need to have CLR enabled on the SQL Server instance.
  • Microsoft has provided a long list of OGC methods.
  • These are methods that are part of the independent Open Geospatial Consortium list of specifications

STDistance Function explained

  • The STDistance function requires an argument of the SQLgeometrytype
  • Convert the latitude and longitude to SQLgeometrytype.
  • Declare a variable of the geography type.
  • Set the variable to geography::STGeomFromText(‘point(lon lat)’,4326).
  • Notice that the point is a string.
  • Notice there is no comma between lat and lon
  • 4326 is an SRID and is used to tell the function what method to use to calculate distance on a not-so-round planet.

STDistance in action

Declare @CodeGEOG geography =
geography::STGeomFromText(‘Point(-95.3410 29.7070)’,4326);
Select top(5) addr.AddressID as id ,
addr.SpatialLocation, — Will show the raw data
addr.SpatialLocation.AsGml() as SpationalGML,
addr.SpatialLocation.STAsText() as SpatialText,
addr.SpatialLocation.STGeometryType() as GeoType,
addr.SpatialLocation.STNumPoints() as Points,
addr.SpatialLocation.STDistance(@CodeGEOG) as distance_in_meters,
addr.SpatialLocation.STDistance(@CodeGEOG)/1609.344 as distance_in_miles
from dbo.Address addr
addr.SpatialLocation.STDistance(@CodeGEOG)/1609.344 < 50
order by distance_in_miles

Wire the SQL up to Virtual Earth

The wire up will get it’s own blog entry