![Page 1: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/1.jpg)
Deep Dive Into Spatial Data
Isaac KunenSenior Program ManagerMicrosoft SQL Server
BB24
![Page 2: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/2.jpg)
Preface
This talk is for anyone who wants a better understanding of SQL Server’s spatial support I’ve culled a few of the most interesting topics from
the message boards, my blog, etc.
Caveat: This is a 400 level talk I will assume some familiarity with SQL Server
spatial, but...
Bonus: Lots of pretty pictures
![Page 3: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/3.jpg)
An Aggressive Agenda
Precision and Robustness The Sphere is not the Plane Extending Spatial Index Usage A Look at Where We’re Headed
![Page 4: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/4.jpg)
Precision and Robustness
![Page 5: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/5.jpg)
Let's Intersect Some Lines
Demo!
![Page 6: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/6.jpg)
What’s Going On?
The actual intersection point is (2/3, 2) How does the computer handle this? Possibility: rational number type
Prohibitively expensive Real answer: limit precision
But, the same value, calculated different ways, may have different results
![Page 7: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/7.jpg)
Spatial Isn’t Special
declare @u float = 1000
declare @v float = -1
declare @w float = 1.0001
print 'equal'
else
print 'not equal'
select (@u * (@v + @w)) -- 0.099999999999989
select ((@u * @v) + (@u * @w)) -- 0.100000000000023
-- this one!
if (@u * (@v + @w)) = ((@u * @v) + (@u * @w)) (@u * (@v + @w)) ((@u * @v) + (@u * @w))
![Page 8: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/8.jpg)
Upshot
We have to learn to deal with this For many operations, we don’t need to worry.
Region queries, computing distances, etc.
If we depend on an exact result...where @line.STIntersects(@point) = 1
...then we should ask a fuzzy query instead:where @line.STDistance(@point) < ε
![Page 9: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/9.jpg)
Brief Interlude
Q: How do we tell what’s in/out of a polygon?A: Cast a ray to infinity and count the number of
times it crosses the boundary
![Page 10: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/10.jpg)
Robustness: Small ErrorsBig Problems
![Page 11: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/11.jpg)
How We Perform Calculations
1. Snap to an integer grid2. Perform calculations using exact integer
arithmetic3. Convert back to floats
This process will cause small errors (within tolerance) ...
...but avoids the big ones
![Page 12: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/12.jpg)
Geometry on the Grid
f(...)
![Page 13: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/13.jpg)
The Sphere is not the Plane
![Page 14: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/14.jpg)
What is an Edge?
This is not just a philosophical question:
In any case, on the plane this is pretty clear Curves like these are called geodesics
Locally path minimizing curve
?
![Page 15: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/15.jpg)
Edges on the Sphere
Great CirclesNice properties
Geodesics Usually unique... ...not always
?
?
?
![Page 16: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/16.jpg)
Edges on an Ellipsoid
Geodesics? Ambiguous Unnatural (?)
Great Elliptic Arcs Usually unique Natural (?) Very nice
computationally Usually close to a
geodesic
![Page 17: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/17.jpg)
Another Brief Interlude
Q: How do we tell what’s in/out of a polygon? We cannot cast a ray to infinity—there isn’t one
A: Have to use ring orientation instead
![Page 18: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/18.jpg)
Let Me Disabuse You of a Few Notions
Nonsense (!) Clockwise/
Counter-Clockwise Interior/Exterior
rings Sense
Left-/Right-Hand side
![Page 19: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/19.jpg)
Extending Spatial
![Page 20: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/20.jpg)
So We Have This Spatial Library…
We now have this wonderful spatial library inside SQL Server But wait, there’s more!
We ship the library separately CLR type + unmanaged DLL Available for separate download/install Can code against it through C#, VB, etc.
We can use it to extend spatial in various interesting ways SQL CLR Sink/Builder
![Page 21: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/21.jpg)
Sink API
IGeometrySink {
void SetSrid(int srid)
void BeginGeometry(OpenGisGeometryType type)
void BeginFigure(double x, double y, double? z, double?
m)
void AddLine(double x, double y, double? z, double? m)
void EndFigure()
void EndGeometry()
}
![Page 22: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/22.jpg)
Using the API
0:
BeginGeometry(Polygon)BeginFigure(0,0,null,null)AddLine(0,10,null,null)AddLine(10,0,null,null)AddLine(0,0,null,null)EndFigure()BeginFigure(2,2,null,null)AddLine(2,8,null,null)AddLine(8,2,null,null)AddLine(2,2,null,null)EndFigure()EndGeometry()
Polygon:
SetSrid(0)
![Page 23: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/23.jpg)
Sink/Builder
Three easy pieces: IGeometrySink interface that allows for the
description of any geometry instance Populate(IGeometrySink) method on
geometry takes a sink and calls methods to describe itself
GeometryBuilder is a special IGeometrySink that constructs whatever instance is described
These repeat for geography These can be pipelined
![Page 24: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/24.jpg)
Creating a SwapXY Function
Demo!
![Page 25: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/25.jpg)
SQL Server Spatial Tools
The sink/builder API is a very powerful way to extend our spatial functionality
We’re slowly adding new functionality in the SQL Server Spatial Tools project on CodePlex Free stuff!
Partial list of functionality: Densification Affine transforms Linear referencing Simple projections
![Page 26: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/26.jpg)
Index Usage
![Page 27: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/27.jpg)
A: Usually because the index isn’t being used.Q: How do I tell?A: SELECT * FROM T WHERE g.STIntersects(@x)
= 1
Q: Why is my Query so Slow?
NO INDEX
INDEX!
![Page 28: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/28.jpg)
Spatial indexes can be forced if needed.
SELECT * FROM T WHERE g.STIntersects(@x) = 1
Hinting the Index
WITH(INDEX(T_g_idx))
![Page 29: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/29.jpg)
Plan choice is cost-based QO uses various information, including cardinality
When can we estimate cardinality? Variables: never Literals: not in this case Parameters: yes, but cached, so first call matters
But Why Isn't My Index Used?
DECLARE @x geometry = 'POINT (0 0)'SELECT *FROM TWHERE T.g.STIntersects(@x) = 1
SELECT *FROM TWHERE T.g.STIntersects('POINT (0 0)') = 1
EXEC sp_executesql N'SELECT * FROM T WHERE T.g.STIntersects(@x) = 1', N'@x geometry', N'POINT (0 0)'
![Page 30: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/30.jpg)
Futures
![Page 31: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/31.jpg)
Where are we going?
2D Map components coming to Reporting Services Eliminating hemisphere limitation Improving indexing, both plan choice and perf Additional methods
3D Looking at model-independent strategies
Raster
![Page 32: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/32.jpg)
More info/Pointers
![Page 33: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/33.jpg)
Where to get More Information
Go see the VE deep dive: BB10, Thursday at 1:45
My main sources of inspiration for the talk: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1629&SiteID=
1 http://blogs.msdn.com/isaac/ http://www.codeplex.com/sqlspatialtools
Other places you should know: http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx http://www.codeplex.com/ProjNET http://www.geoquery2008.com/ http://johanneskebeck.spaces.live.com/ http://blogs.msdn.com/edkatibah/
![Page 34: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/34.jpg)
Evals & Recordings
Please fill
out your
evaluation for
this session at:
This session will be available as a recording at:
www.microsoftpdc.com
![Page 35: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/35.jpg)
Please use the microphones provided
Q&A
![Page 36: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/36.jpg)
© 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market
conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
![Page 37: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/37.jpg)
![Page 38: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/38.jpg)
Encore 1: Calculating on the Globe
![Page 39: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/39.jpg)
Gnomonic Projection
Flattens to a plane Projects from the
center Edges are projected
exactly to lines Now operate in the
plane! Distortion grows
with distance... ...so the useful area
is limited
![Page 40: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/40.jpg)
Extending the Projection
Use more planes Start with half a
tetrahedron Introduce
“fake” vertices on the folds
Flatten the tetrahedron to a single plane
Now, operate on the plane!
![Page 41: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/41.jpg)
Our Workflow
Select a projection If the object is small enough, use a single plane If it’s too big, choose a tetrahedron We use the same bounding cap we’ve
described Operate in the plane Project back to the sphere
![Page 42: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/42.jpg)
But...
What if the object is larger than a hemisphere? We can project to a full tetrahedron We then flatten to two planes
The projection is not particularly difficult Have to work with two planes
Sorting out the interface during the inverse projection is harder... ...but it’s coming!
![Page 43: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/43.jpg)
Encore 2: Envelopes
![Page 44: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/44.jpg)
Are Bounding Boxes Nonsense?
Naive Algorithm:
declare @_p geography = 'POINT (0 45)'
declare @A geography = @_p.STBuffer(1000000)
declare @B geometry =
geometry::Parse(@A.ToString())
declare @C geometry = @B.STEnvelope()
declare @D geography =
geography::Parse(@C.ToString())
![Page 45: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/45.jpg)
Can Bounding Boxes Work?
Naive bounding box clearly doesn’t work We can patch this up a bit What happens over the poles, though?
A bounding box of any sort seems to be rather unnatural—to me, at least If you come up with a natural, simple, general
definition, please let me know If you’re projecting the data, bounding boxes
make sense in the projected space
![Page 46: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/46.jpg)
Bounding Circles
We really want a simple proxy for the object We give bounding circles: center point and angle Guaranteed to cover, but not to be tight
![Page 47: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/47.jpg)
Appendix
![Page 48: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/48.jpg)
/**
* This class implements a geometry sink that will swap the x and y coordinates of the instance.
* Note that this will invert the orientation of all rings in the instance.
* It directs its output to another sink, and can therefore be used in a pipeline if desired.
*/
public class SwapXYSink : IGeometrySink
{
private readonly IGeometrySink _target; // the target sink
public SwapXYSink(IGeometrySink target)
{
_target = target;
}
// Just pass through without change.
public void SetSrid(int srid)
{
_target.SetSrid(srid);
}
// Just pass through without change.
public void BeginGeometry(OpenGisGeometryType type)
{
_target.BeginGeometry(type);
}
// Each BeginFigure call will just swap the x and y coordinates.
public void BeginFigure(double x, double y, double? z, double? m)
{
_target.BeginFigure(y, x, z, m);
}
// Each AddLine call will just swap the x and y coordinates.
public void AddLine(double x, double y, double? z, double? m)
{
_target.AddLine(y, x, z, m);
}
// Just pass through without change.
public void EndFigure()
{
_target.EndFigure();
}
// Just pass through without change.
public void EndGeometry()
{
_target.EndGeometry();
}
}
Full SwapXY Sink Code
![Page 49: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/49.jpg)
Creating a SwapXY Function
Create a pipeline, last sink first:
public static SqlGeometry SwapXY(SqlGeometry source)
{ SqlGeometryBuilder b = new SqlGeometryBuilder(); SwapXYSink swap = new SwapXYSink(b); // Could chain more sinks... source.Populate(swap); return b.ConstructedGeometry;}
This can now be registered and used in SQL Server
![Page 50: Isaac Kunen Senior Program Manager Microsoft SQL Server BB24](https://reader035.vdocuments.us/reader035/viewer/2022062318/551b6fe2550346a6148b4eb8/html5/thumbnails/50.jpg)