Hi Folks,
Marko Tintor from our Serbia team just dropped some new functionality into the SQL Server Spatial Tools project on CodePlex:
- A method for testing whether a geography instance is valid.
- Better: a method for creating a valid geography from invalid input. How this works deserves a post on of its own, but it basically uses the technique I suggested in this forum thread.
- Methods for creating convex hulls in geography.
Enjoy!
-Isaac
Hi Folks,
We all know that the sphere isn’t the plane, and that an ellipsoid is different as well, but I don’t know if everyone has given so much thought to the practical effects of these differences.
And so I thought it might be a nice idea to catalog some of the interesting things that change when we move from a flat to round model—and it also makes a nice new series for the blog. My planned agenda is:
- Edges on the Ellipsoid aren’t Obvious
- The Sphere is Bounded…
- … So Orientation Matters on the Sphere…
- …And yet, Maybe not so Much
- Some Sensible Concepts Are Nonsense on the Sphere
- Bounding Boxes on the Sphere are Strange
- Funny Things Happen to Convex Polygons
I’ll keep this list up to date with links as they bake.
As you can see, I get off easy on the first post: I wrote it some time ago. I’ve also started to touch on the next few topics, but I think there’s more to say. I’ll be scrounging some material from my PDC talk last year, but there should be some new goodies as well.
Cheers,
-Isaac
Hi Folks,
This post contains no new information; it’s just a rollup of links to spatial indexing posts I’ve made. If you want to know more about spatial indexing, these are the ones to read.
In order:
- Why a Spatial Index?
- A Simple Spatial Indexing Scheme
- Faster Primary Filtering
- Picking up on Indexing: Moving Beyond the Simple Grid
- Basic Multi-Level Grids
- More on the Multi-Level Grid
- Is my spatial index being used?
Hmm… it looks like I never got around to the specific of the geography index. It’s in the queue now.
Cheers,
-Isaac
Simon Sabin asked me to pass along information about a free spatial event that he and Johannes Kebeck are holding in London on the 16th. It looks like they’ll be covering a bunch of hot SQL Server Spatial topics: how to get your data in to SQL, how to get your data out and visualize it, and how to make best use of spatial indexes.
Actually, they’re holding it twice: once in the afternoon, and again in the evening. Follow those links to sign up.
It looks like it should be a good meeting.
Cheers,
-Isaac
Hi Folks,
Jason Follas recently ran across a lingering issue when using our spatial library without the server. I mistakenly thought the fix had been shipped, but for now it remains.
The background is that the spatial library consists of a managed part and a native part. Roughly, the core geometry library resides in the native code, and everything else is in the managed layer. The native code requires the C++ runtime libraries. This is installed with SQL Server, so everything works fine if you install SQL.
The redistributable version we ship as part of the feature pack should redistribute the runtime as well, but it doesn’t. That’s the bug. So, if you install the library on a machine without the runtime, then it will fail.
The workaround is simple: install the runtime manually. Annoying, but effective. You’ll also be fine if you install another package—like the SQL Native Client—that includes the runtime, but unless you also want that component, I’d stick with the stand-alone install.
SP1 for SQL Server 2008 is coming up, but unfortunately, we’ve missed the cutoff to get this in. We’ll try to get this into the next service pack, but that will take some time. In the meantime, you’ll have to use this workaround. I’ll try to get an official KB article published on this as well.
So, apologies for the inconvenience. And thanks to Jason for raising the issue.
Cheers,
-Isaac
Hi Folks,
It occurs to me that I haven’t posted anything about the hemisphere limitation for the SQL Server geography type. This limitation is a little confusing, and could probably use some clarification.
It also occurs to me that I haven’t posted anything in a while—let’s rectify both of these.
We usually express SQL Server’s limitation by saying that no object can exceed a hemisphere. But which hemisphere? Does this mean that objects cannot cross the equator? The international date line? The truth: neither. We can, in fact, generate objects that cross the equator:
POLYGON ((-145 -45, -55 -45, -55 45, -145 45, -145 -45))
Or the date line:
POLYGON ((135 0, -135 0, -135 90, 135 90, 135 0))
Or the poles:
POLYGON ((0 45, 90 45, 180 45, 270 45, 0 45))
This illustrates that it is not objects in certain positions that are disallowed. Rather, large objects are disallowed: if an object spans more than half the globe, SQL Server will complain. So while a polygon that sits slightly inside of a hemisphere is fine:
POLYGON ((0 1, 90 1, 180 1, 270 1, 0 1))
One that is slightly larger causes problems:
POLYGON ((0 -1, 90 -1, 180 -1, 270 -1, 0 -1))
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
Microsoft.SqlServer.Types.GLArgumentException: 24205: 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.
Microsoft.SqlServer.Types.GLArgumentException:
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData g)
at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive()
at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
.
We can dig a little deeper. When SQL Server sees a geography instance, it computes a bounding cap for it. This is very much like a bounding box on the plane, but is defined by a center point and an angle. We can actually find the bounding cap for an object using the EnvelopeCenter() and EnvelopeAngle() methods on geography. Using these, we can plot the cap. For example:
POLYGON ((-50 -10, 50 -10, 50 10, -50 10, -50 -10))
If the cap angle exceeds 90 degrees, then the object exceeds a hemisphere. The cap computed above has an angle of 50.7 degrees.
But there is some subtlety in how SQL Server determines this cap. The angle is relatively simple: it is the maximum angle from the center to any of the points in the figure, yielding a minimal cap with that center. The center, however, is determined by summing the vectors from the center of the globe to each vertex in the figure, essentially averaging the vertices. This means that if the points in our figure are lopsided—e.g., if add more points on the eastern edge—we will compute a cap that no longer looks very minimal:
POLYGON ((-50 -10, 50 -10, 50 -5, 50 0, 50 5, 50 10, -50 10, -50 -10))
Now the cap center is skewed, so the angle has to increase—to 77.4 degrees—to contain the object. If we add more points on the eastern edge we get into trouble:
POLYGON ((-50 -10, 50 -10, 50 -8, 50 -6, 50 -5 50 -4, 50 -2, 50 0, 50 2, 50 4, 50 5, 50 6, 50 8, 50 10, -50 10, -50 -10))
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
Microsoft.SqlServer.Types.GLArgumentException: 24205: 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.
Microsoft.SqlServer.Types.GLArgumentException:
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData g)
at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive()
at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
.
So although our object hasn’t changed—the extra vertices we added were chosen so they don’t change the shape—SQL Server now thinks we’ve exceeded a hemisphere.
Let me answer two more obvious questions. First: why do we compute caps this way instead of computing a true minimal bounding cap? It turns out that finding a minimal cap is difficult computationally, and because this operation happens a lot, we need something cheap.
Second: why does SQL Server have the hemisphere limitation at all? A complete answer will require another post. Suffice to say, it’s an implementation artifact that the SQL Server team absolutely abhors. That SQL Server isn’t the only system with such a limitation doesn’t make it any less annoying, and removing the limit is high on everyone’s priorities.
Cheers,
-Isaac
Hi Folks,
While I continue to procrastinate, here are some excellent spatial resources to peruse.
First, David Lean, a Microsoft evangelist, has very comprehensive list of spatial posts over on his Dave does Data blog. He covers a wide range of spatial methods, the builder/sink API, as well as some pointers on creating spatial applications.
Second, John O’Brien, a Microsoft MVP, has a couple articles (here and here) showing how to do some cool data visualization right in Management Studio.
Finally, Johannes Kebeck continues with a couple recent posts on creating maps in Virtual Earth based on data coming from SQL Server.
Enjoy!
-Isaac
Last time, I posted a question sent to me by Rob Mount. So what solutions exist other than the north pole?
You did well: jnelso99 got part of the way there; tanoshimi finished it off. We can find a set of circles around the south pole with lengths of 1/N miles: 1 mile, 1/2 mile, 1/3 mile, etc. Any point one mile north of any of these circles will work: you travel south one mile to one of the circles, circle the earth N times, and then head back north to where you started.
Where exactly are these circles? Well, let’s be a bit mathematical about it. Let R be the radius of the earth, and let x be the distance of each leg of our path. Working in radians, let p0 = (lat, long) be our starting point. (Radians just make the math so much nicer.)
First we walk south x units. This only changes the latitude, and so we find ourselves at a new point:
p1 = (lat – x/R, long)
Next we walk x units east, which only impacts the longitude. How much this affects the longitude depends, however, on our current latitude:
p2 = (lat – x/R, long + x/cos(lat – x/R))
Finally we walk x units north. This brings the latitude back to where we started:
pf = (lat, long + x/(R * cos(lat – x/R)))
Okay, so what do we make of this? We want p0 to equal pf. The latitude is easy, since the latitudes of p0 and pf are the same. Further, we note that if lat is positive or negative pi/2 then we’re at a pole and the value of long is irrelevant. Positive pi/2 gives us the north pole solution; –pi/2 ends up being nonsense, since you can’t go south from the south pole.
But if lat is not pi/2, then long matters. In particular, our starting longitude has to equal our ending, but it can wrap around, so:
long + 2 * pi * N = long + x / (R * cos(lat – x/R))
2 * pi * N = x / (R * cos(lat – x/R))
Here N is any integer; the 2 * pi * N factor accounts for the fact that we can circle the globe. Solving for lat we find:
cos(lat – x/R) = x / ( 2 * pi * N * R )
lat – x/R = acos( x / ( 2 * pi * N * R) )
lat = acos( x / ( 2 * pi * N * R) ) + x/R
The acos term is undefined if N = 0, and if N < 1 then the value lies outside of the range [–pi/2, pi/2]. When N is greater than 0, then the results of the acos term are in our range, but they could be positive or negative; it turns out that the values we want are negative.
So what does this mean? We have derived a set of latitude values that look like solutions, and the longitudes associated with these solutions are unconstrained. In other words, we have a set of circles around the south pole corresponding to each N in {1, 2, …}. These are exactly the circles we’ve already described, but now we can compute exactly where they are. For R = 3963 miles and x = 1:
N=1: lat = -acos( 1 / (24900.263) ) + 0.000252 = -1.5705041 = -89.983260 degrees
N=2: lat = -acos( 1 / (49800.527) ) + 0.000252 = -1.5705242 = -89.984410 degrees
N=3: lat = -acos( 1 / (74700.790) ) + 0.000252 = -1.5705309 = -89.984794 degrees
…
And when N=1, this circle is indeed about 1.16 miles north of the south pole, just as jnelso99 noted.
Cheers,
-Isaac
Hi Folks,
At PDC I gave a talk largely inspired by topics raised here and in the spatial forums. But “inspired by” doesn’t equate to “a duplicate of”, and to turn things around, I’ve been meaning to write a few posts here inspired by my PDC talk. Stay tuned.
In the meantime, Rob Mount from Intergraph sent me a note that started:
As I review your PDC presentation I’m reminded of a puzzle I think you’ll enjoy.
I did indeed, and I hope a lot of you will enjoy it as well. Rob started out with a puzzle I expect most of us have heard before:
When I was growing up one of my uncles was fond of challenging the children in the family with puzzles. One of my favorites, as a very young child, was this one: A hunter walks a mile south, a mile east and a mile north and finds himself back at the starting point. A bear walks by. What color is the bear?
This is well-known enough that I don’t think the answer will be much of a spoiler:
He, of course, thought the hunter started at the North Pole. The bear was a polar bear and hence white.
A perfectly valid answer, of course. But Rob continues:
Years later I finally got my revenge by stumping him with this problem: I reminded him of the hunter and the polar bear and pointed out that there are actually several other points on the earth that meet the geographic constraint he stated – if you walk a mile south, a mile east and a mile north you find yourself back where you started.
Assuming a spherical earth, how many such points exist and where are they?
I won’t spoil this one so quickly. Go ahead and post your answers in the comments; I’ll post the solution in a few days.
Cheers,
-Isaac
This has come up a few times now. Multiple questions deserve at least one answer, right?
First, Microsoft has not produced a shapefile loader (or exporter). If this is important to you, here’s the Connect item to hit. (And yes: we do listen to Connect!)
So, what are you to do? There are a few options:
- If you are a user of one of our partner GIS vendors---Autodesk, ESRI, Intergraph, Manifold, or Pitney Bowes (MapInfo)---they probably have support for loading shapefiles already. You’ll have to ask them, though: I’m not an expert in their products.
- If not, and if you aren’t interested in a real GIS, then you should check out Safe Software: they build spatial ETL software that will take shapefiles (as well as about ~200 other formats) perform transformations on the data, and load into SQL Server.
- If you’re looking for a free solution, then there are a few popping up in the wild. Morten Nielsen has had his tool available for some time now, which has both a command line and GUI interfaces. Scott Ellington has created a command line tool as well, but I haven’t had a chance to play with it yet.
- You could build your own. I suggest using the sink/builder API---you can find plenty of examples in our Codeplex project.
Hopefully one of these solutions will work for you.
Cheers,
-Isaac
Despite my relative youth, I can be a bit of a fuddy-duddy. Fighting my natural impulses, I’ve decided to give Twitter a try---at least while I’m at PDC. So, if you’re interested in tracking me down, surf the tubes over to my very own Truman Show.
Or drop me an old-fashioned email.
See you at the show!
-Isaac
Hi Folks,
Spatial users often want to find the object nearest a given point. This operation, usually referred to as nearest neighbor search, is remarkably common in many areas of computer science. In general, we may wish to find not only the nearest, but the k-nearest neighbors.
How can we accomplish this with SQL Server? Here we’ll look at finding the single nearest neighbor; the extension to k-nearest neighbors is relatively straight forward.
First, let's examine the naive method for accomplishing this: simply order the table by distance and restrict the results. For all of these examples, we’ll assume a table T with a spatial column g, as well as a parameter @x containing the search point:
SELECT TOP(1) *
FROM T
ORDER BY g.STDistance(@x) ASC
This solution certainly has simplicity on its side, but consider the work that needs to be done. The entire table must be scanned, and the distance of each to the search point must be calculated. Ouch.
We could conceivably improve on this by restricting our search space to to the immediate region around the target point:
DECLARE @region geography = @x.STBuffer(10000)
SELECT TOP(1) *
FROM T
WHERE g.Filter(@region) = 1
ORDER BY g.STDistance(@x) ASC
But this solution requires that we know our data very well: if there are no rows in the region, then we will fail to find the nearest neighbor; if there are too many, then we will again be left with a rather inefficient query.
How do we escape this morass? We can do so by starting with a very small region---so small that we can be certain not to encounter too many results---and then keep enlarging it until we find something. Doing this with a loop is not hard, but Steven Hemingray showed me how to do this with entirely declarative syntax:
DECLARE @start FLOAT = 1000;
WITH NearestPoints AS
(
SELECT TOP(1) WITH TIES *, T.g.STDistance(@x) AS dist
FROM Numbers JOIN T WITH(INDEX(spatial_index))
ON T.g.STDistance(@x) < @start*POWER(2,Numbers.n)
ORDER BY n
)
SELECT TOP(1) * FROM NearestPoints
ORDER BY n, dist
This requires some explanation. First, the @start parameter gives the initial region to search. I’ve chosen one kilometer, but this can be adjusted downward if your data is very dense. Second, you’ll notice that we make use of a Numbers table, which just contains the numbers 1 through n. This just contains a long list of integers, which is is useful in many situations.
The inner query examines a set of exponentially-expanding regions. The ORDER BY clause along with the TOP(1) allows the query to stop as soon as it finds the smallest non-empty region. The WITH TIES statement makes sure that all of the objects in that region will be in the result set.
Once the inner query returns a list of potential results, the outer query examines them to find which is actually nearest. With this approach, we can select a start area small enough to keep the cost low in dense data, but also be guaranteed to find a distant nearest neighbor.
Incidentally, if you don’t already have a numbers table, you can create one quite quickly with some mildly-black magic like this:
SELECT TOP 100000 IDENTITY(int,1,1) AS n
INTO numbers
FROM MASTER..spt_values a, MASTER..spt_values b
CREATE UNIQUE CLUSTERED INDEX idx_1 ON numbers(n)
This isn’t a particularly pretty solution, but to proactively answer a question, we didn’t add a method for this primarily because we ran out of time. Look for something more built-in the next go around.
Cheers,
-Isaac
Hi Folks,
A post on the spatial forum last week caught my attention for entirely non-spatial reasons: it made use of two functions---RADIANS() and DEGREES()---that I never knew existed.
It seems rather strange that out of the innumerable (well, technically enumerable) list of functions that we could implement, someone felt these were worth the effort. For example, we save exactly 0 characters by using these functions:
Becomes:
Granted, using the functions is clearer and less error prone; but I’m not arguing that they are bad functions, just surprising.
What is surprising and bad, if you ask me, is that both RADIANS() and DEGREES() have int—>int overloads. In other words, if you call these functions with an integer, you get an integer result:
SELECT RADIANS(180) -- 3, not 3.141592653589793100
SELECT DEGREES(3) -- 171, not 171.887338539246970000
This behavior is very strange. I cannot think of a case in which it is useful, yet plenty where it could cause harm. The behavior is noted in BOL:
Arithmetic functions, such as ABS, CEILING, DEGREES, FLOOR, POWER, RADIANS, and SIGN, return a value having the same data type as the input value.
Of these, only RADIANS() and DEGREES() have natural floating-point return values: all others are naturally integers, and while promoting them to floats may not be necessary, it doesn’t seem problematic.
Let me know if you can see a benefit of this behavior; I’d love to see these overloads on our deprecation list.
Cheers,
-Isaac
Hi Folks,
Unfortunately, I need to correct an earlier post of mine. I will not be speaking at PASS this year---it looks like there were some mixed signals. If you’re looking for your spatial fix, Michael Rys will be talking about types and indexing.
Still, PASS is on my home turf, so I’ll be around: come see me at the Ask the Experts session, or drop me a line and we can meet up.
PDC and Autodesk University are still on until they tell me otherwise.
Cheers,
-Isaac
Hi Folks,
I'd like to let everyone know about a few conferences that I'll be at in the near future. First up is PDC in Los Angeles at the end of October. After that is the PASS Community Summit in beautiful Seattle middle of November. (At a full 5 miles door-to-door, I can't beat the travel for this one. I also don't get frequent flier miles...)
At both of these conferences, I'll be giving a 400-level talk about spatial---I'm definitely going to assume some familiarity with the subject. I'll look at some of the more interesting issues that have been discussed here and on the spatial forum, as well as some of those that haven't quite made it there yet. I guarantee that I'll tell you something you didn't know before. (Terms and conditions may apply.)
Finally, I'll be giving a joint presentation with Orest Halustchak at Autodesk University in Las Vegas at the beginning of December. I'll give an introduction to the spatial support we've introduced, and Orest will talk about how Autodesk is making use of it.
If you're at any of these events, please feel free to look me up. (And please do come to my talks!)
Cheers,
-Isaac
