Where’s My Data? Spatial queries in SQL Server 2008 SQL Bits III – 13th September 2008
New data types geometry and geography Spatial references Spatial operations Spatial indexes Case study What’s covered in this talk
80-90% of all data has a spatial element • Where are your customers? • Where are your assets? • Where are potential customers? • Where are the flood risks? • Where are your complaints coming from? • Where are the accident black-spots? • Where are crimes happening? Why Should I Care About Spatial Data?
Could Fudge the Queries • Postcodes in Glasgow • Zoned • Historical reasons • G5 adjacent to G42 • G40 in an island • Postcodes designed for delivering letters
The data analysis engine • No useful rendering engine • Virtual Earth • Map Point • Other GIS systems • OGC Standards compliance • Plus some “extension” methods of their own What Does SQL Server 2008 Provide?
Geometry • X/Y coordinate on a planar grid • British National Grid • Works well to ~750,000km2 • Different projections
Geography • Geodetic coordinates • Covers larger areas • International datasets • Approximation • Earth actually flattened sphere (oblate spheroid) • Different models • Airy 1830 (used by OS) • WGS84 (used by GPS)
Spatial Reference Identifier • All spatial data has an SRID • SRIDs must match for spatial operations • Null returned if SRIDs don’t match • Geometry can have an SRID of 0 • Not Geography. SRID
Types of spatial Data • Point • LineString • Polygon • GeomCollection • MultiPolygon • MultiLineString • MultiPoint From BOL
Create POINT Data
SELECT a.Name AS StartVenue, b.Name AS EndVenue, a.Location.STDistance(b.Location) / 1000.0 As Distance FROM Venue AS a INNER JOIN Venue AS b ON a.Id < b.Id ORDER BY a.Id, b.Id Finding Distances
A linestring is a series of coordinates • 1 dimension • Defines a linear object • Road • Railway line • River LINESTRING
Can use • STGeomFromText • STGeomFromWKB • STLineFromText • STLineFromWKB • Parse Creating Linestrings
Create LINESTRING Data
Geography uses SI Units Geometry uses the units of the planar system Geometry Lengths & Distances A ? units The square of the hypotenuse is equal to the sum of the square of the other two sides Distance from A to B: √(32+42) = 5 3 units B 4 units Not to scale
Intersecting Lines
A series of coordinates in a closed ring • First and last coordinate are the same • 2 dimensions • Defines an area POLYGON
Geography Polygon Orientation • Interior is everything inside an anti-clockwise ring • Everything on the left-hand side of the perimeter line.
Geography Polygon Orientation The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.
Can use • STGeomFromText • STGeomFromWKB • STPolygonFromText • STPolygonFromWKB • Parse Creating Polygons
Creating polygons
Estate Agent • Filter by price, # bedrooms, type – EASY! • Filter by location? • Until now very vague Fictional Case Study
Common Spatial Questions Near a motorway junction Near my work Near a railway station Near a good school Inside the city Outside the city
The Data • Railway data • Stations • Routes
Simple Spatial Queries
Doesn’t Always work • Edinburgh - Glenrothes (via Kirkcaldy) Name DistKM -------------------------- ---------------- Edinburgh Waverley Station 0 Haymarket 1.89298770395887 South Gyle 6.95446540329848 Burntisland 12.086984317701 Dalmeny 12.49585147351 Kinghorn 13.1439998546632 Aberdour 13.3392361220632 North Queensferry 14.3833962761344 Dalgety Bay 15.0082794626365 Inverkeithing 15.7316327831032 Kirkcaldy 17.9484646860063 Glenrothes With Thornton 23.7022936117453
Looking for a home
Display the properties
Spatial Indexing • Decomposes space into 4 levels of grid • Level 1 is the top • Cells are uniform in a level • A level can be a 4x4, 8x8 or 16x16 grid • 8x8 by default
Table must have a primary key Primary key cannot subsequently be changed. Not on views Maximum of 249 Spatial indexes per column Restrictions
Why have multiple indexes on one column • Where geometry/ geography sizes vary • e.g. Rail routes • Small: Suburban lines • Large: Intercity lines
Supports • STIntersects • STEquals • STDistance • One Geography must be a point • Both sides of the spatial operation must have the same SRID Spatial Indexing
Must specify boundary of spatial area • Additional methods supported • STContains • STOverlaps • STTouches • STWithin Spatial Indexing on a Geometry
CodePlex project • More spatial methods • Aggregations • Scripts http://www.codeplex.com/sqlspatialtools And There’s More!
What I’ve not mentioned • GML • Import and export • M and Z • Can store • Cannot operate. • Other spatial Operations • Geometry has more! • Visualisation • .NET application integration • Data Importing
Slide Deck on my website • http://www.colinmackay.net • Blog posts on Spatial Data • http://blog.colinmackay.net Get More Information
Questions ?
