SQL Server 2008 Spatial Data

Here are some notes on “SQL Server 2008 Spatial Data” I took while attending an advanced class on SQL Server taught by Greg Low (from http://sqlblog.com/blogs/greg_low/ and http://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Spatial Data

• Most applications can make use of spatial data, not just mapping and CAD
• Types: Vector vs. Raster, 2D vs 3D vs 4D, Flat Earth vs. Round Earth
• Open Geographic Consortium: defines data types, plus a variety of collections. OGC Type Hierarchy
• Units of measurement: sys.spatial_reference_systems. 4326 is most common (GPS system)
• Units of measurement: unit_conversion_factor
• Michael Rys – AD-312-M Building Spatial Application with Microsoft SQL Server
• See http://msdn.microsoft.com/en-us/library/bb933790.aspx

Data Types

• Geometry – Flat Earth type – Doesn’t account for the Earth’s curvature
• See http://msdn.microsoft.com/en-us/library/bb895270.aspx
• Geography – Round Earth type – Cannot span a hemisphere
• See http://msdn.microsoft.com/en-us/library/bb895266.aspx
• Assembly provided – Microsoft.SqlServer.Types (SELECT * FROM sys.assemblies) also available as MSI file
• ST prefix on OGC-defined methods. Others are Microsoft extensions.

System CLR types

• Enabled even if “clr enabled” setting not on server.
• In SQL Server 2008: Geometry, Geography, HierarchyID
• New large CLR type support: up to 2GB of serialized data
• Call via: Instance.Property, Instance.Method(), Type::StaticMethod()
• Static: Instead of “DECLARE @a zVarChar @a.Compress(‘b’)” you can do “zVarchar::Compress(‘y’)”
• Geometry: 2D, X and Y, measure not relevant to type, good OGC coverage
• Geography: 2D, Longitude and Lat, Elevation
• Careful: Long and Lat order changed from beta to RTM. If example does not work, try reversing.
• Careful: Order is important for polygons – Common error saying “cannot span a hemisphere” when order is wrong

Data Format

• Internal binary format not exposed
• Parsing (from text to internal) – WKT (verbose text), WKB (binary), GML (XML-based) – Parse assumes WKT
• Output (from internal to text) – As parse, but can also export Z and M – ToString assumes WKT
• Not supported: GeoRSS, KML – Lots of projects on Codeplex, third party tools
• Downloadable maps, US Census Data – Availability varies depending on country (from free to very expensive)
• See http://blogs.msdn.com/davidlean/archive/2008/11/14/sql-spatial-how-to-get-spatial-data-free-maps-n-demographics.aspx

OGC Methods and Collections

• DECLARE @shape GEOMETRY;
• SET @shape = GEOMETRY::STGeomFromText(‘POLYGON ((10 10, 10 40, 40 40, 40 10, 10 10))’, 4326);
• SELECT @shape AS Square
• “Spatial results” renders it. Third-party extension to render in SSMS also available.
• Trying “SELECT @shape1 AS multishapes UNION ALL SELECT @shape2”
• Trying “SELECT @shape1.STUNION(@shape2)”
• Trying “SELECT @shape1.STINTERSECTION(@shape2)”
• See http://msdn.microsoft.com/en-us/library/bb933960.aspx 
• Looking at the map of Australia defined as a shape.
• Looking at STBuffer, STEnvelope, STConvexHull, STReduce with the map of Australia

Putting it into the database

• Looking at table with cities in Australia. There is column for Location.
• Select Location from Table: Shows all cities plotted in “Spatial results”.
• Select a city, showing the distance from “Perth”  using Location.STDistance(…)
• Select a city, showing the distance from all other cities in Australia.
• “Where is the nearest <x>?” now is much simpler…

Spatial Indexing

• Typical vector-style query – From SQL 2008 Metro materials
• The real problem – Doing it with the entire country could take a long time
• To overcome it, indexing is key. But not your usual kind of index…
• Tesselation process – Breaking into a grid and removing squares that don’t matter.
• Primary filter – Finds all candidates, false positive OKs, no false negatives
• Secondary filter – Removes false positives
• See http://msdn.microsoft.com/en-us/library/bb895265.aspx

Spatial Indexing Demo

• Creates table with a spatial column, then an index
• CREATE SPATIAL INDEX IX_Table1_Column1 ON Table1(Column1)
• WITH (BOUNDING_BOX=(0,0,512,512), GRIDS=(LOW,LOW,LOW,LOW)
• The “BOUNDING_BOX” defines the boundaries for tesselation
• Looking at sys.spatial_index, sys.spatial_index_tesselation, sys.internal_tables
• How to tell if the spatial index is a good one? Difference from primary to secondary filter.
• Compare results with WHERE Shape.STIntersect and with WHERE Shape.Filter

Spatial Indexing Details

• Included: Catalog views, DDL for everything, DBCC checks, file groups, partitioning, can be hinted
• Not included: Online rebuild, parallel creation, database tuning advisor
• Careful – These types are not comparable. You can’t do ORDER BY or WHERE t1.shape=t2.shape
• Table must have primary key (<=15 cols), index key <=895, up to 249 spatial indexes/table
• Can’t change PK with spatial index, or create on indexed views

Geography Builder Class

• Demo in Visual Basic of an app that creates spatial data.
• Dim gb as new SqlGeographyBuilder
• Create with .SetGrid, .BeginGeography, .BeginFigure, .AddLine, .EndFigure, .EndGeography
• Look with.ToString
• See http://blogs.msdn.com/davidlean/archive/2008/11/01/sql-2008-spatial-samples-part-1-of-9-how-to-learn-sql-spatial.aspx

Spatial Tools

• Lots of codeplex projects
• Projections, additional geometry and geography functions
• For instance, aggregate functions for UNION or ENVELOPE
• See http://www.codeplex.com/site/search?TagName=Spatial

Related Blogs

• http://blogs.msdn.com/isaac
• http://blogs.msdn.com/edkatibah
• http://sqlblog.com/blogs/michael_rys