October 17, 2009
This session is …
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.
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.
- 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 =
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