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

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

OGC Methods and Collections

  • 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)
  • 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

Spatial Tools

Related Blogs

Comments (1)