SQL Server 2008 Spatial Data

SQL Server 2008 Spatial Data

  • Comments 1
  • Likes

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment