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.
- 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
- 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
- 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…
- 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
- 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