SQL11UPD03-TSCRIPT-03
This wiki is a transcript of a previously recorded video.
Related content assets:
Hi folks, I’m Greg Low. I’m a SQL Server MVP and part of the Microsoft Regional Director program. In this session I want to take a quick look at the spatial related improvements in SQL Server 2012.
First up, there’s circular arc support. There was good support for a wide variety of objects introduced in SQL Server 2008. In fact, I thought SQL Server Spatial was probably one of the best things included in the product in 2008. I see a lot of people who tend to think that spatial applications are all about perhaps mapping or things like that, and they often don’t think they relate to business applications. It’s really important to realize that almost every business application involves something dealing with addresses or things that could turn into locations. Whenever you have particularly things like business intelligence applications, being able to show where things are and so on simply brings them to life. The objects that were supported in 2008 were a good start, but there were a few things in the OGC standards that we had not yet had added to the product. A good example of this are curves. You can imagine if I wanted to draw a circle, the amount of data involved in just having a center point and radius is hugely different and more accurate than sitting there describing in vector form the entire shape of the curve. It’s painfully obvious we need to have some sort of curve support and that’s been added in 2012. The circular arc object provides this. We have CircularStrings, CompoundCurves, and also CurvedPolygons. A number of other things have been added to enhance the use of these curves as well.
In the example I have down at the bottom, this is now an example of STGeomFromText. CIRCULARSTRING has been added to the well known text in this case, and I can retrieve a circular segment. There is a substantial difference in what I can do by having a CurveString as opposed to a LineString, but lots of other methods have been added that are enhanced by the use of curves. For example, one of my favorite methods in the product has been Buffer. Buffer takes a shape and then gives us the shape surrounding that out by a certain distance. For example if I had the map of a country and then I said take it out by five kilometers or three miles, that would give me a shape that’s larger than that with the surrounding area. Or perhaps it’s a street, and what is the corridor surrounding that street? The shapes that we had of these before were all in the same shape that we could draw using vectors under the covers.
In this example you can see I’ve got a triangular shape, but by using BufferWithCurves I actually have a much more accurate situation where the corners occur. Similarly, we have CurveToLine and CurveToLineWithTolerance that provides a buffering.
Another big issue that came up in 2008 is that when we had a geography object it couldn’t occupy more than a hemisphere. So not a specific hemisphere like the Northern Hemisphere or the Southern Hemisphere but any particular hemisphere, so any more than half the size of the world. If you did that you’d end up with this error message here: The specified input doesn’t represent a valid geography instance because it exceeds a single hemisphere. That restriction is gone in SQL Server 2012, so it now supports objects that are up to the full globe in size.
One of the issues we had before is that for example, if I drew the region of a postal code or zip code, the thing to understand is that I’m actually drawing two regions. I’m drawing what’s in that postal code and I’m also drawing everything that’s not in that postal code. The way that was determined was by the order of the points we laid down in the polygon. The idea was that everything to the left as you drove along the boundary was included in the shape. It was really important before that you had them in the right order because if you took a shape that was able to be represented and then we turned it around the other way, suddenly you were talking about a thing automatically that was bigger than a hemisphere.
In this case what we have had added was EnvelopeAngle, and what this does is return us a value so we can detect objects that are now bigger than the hemisphere’s size. The other one that’s been added is ReorientObject, that takes that shape and then flips it around the other way because that value is now supportable.
Also where we have well known text, we also have FULLGLOBE support. In this example I have down at the bottom, I’ve simply said give me a geography object based on the entire globe in 4326, which is the WSG84, like the GPS system similarly. Then I’ve called the AREA function on that, and then basically saying, what’s the area of the entire world?
Spatial indexes have taken a while to build, and in this version they now build four or five times faster than they did previously, but there is more to that in these indexes as well. Another thing we had to do was tune the index. We had a multi level grid that was used to, they called the process of tessellation, but it was all about taking a bigger area and working out what parts could be relevant. So if I had an area and I said, what streets intersect that? That’s a straightforward problem if it’s a small number of streets, but if I had all of the streets in the country that would be too big of a job, so I need to quickly eliminate as I go down through the levels the parts that are of interest or not. Spatial indexes were used for this. What a spatial index does is get you to a situation of not finding the exact things, but finding things that could be candidates that need to be looked at. Tuning the grids was something that people were fairly unfamiliar with. In this version we now have new auto grid options that make this sort of tuning fairly unnecessary going forward.
Another big difference was data compression. Again I thought this was one of the best things also added in SQL Server 2008. My two favorites were spatial and compression. But we could not do data compression on the spatial indexes in the previous version. Now we can use both row and page compression can be applied to reduce the size of these spatial indexes. We’ve also got options to allow us to sort of fine tune the maximum number of cells per window.
With the methods there were a number of methods provided by people up on Codeplex and by members of the team. In particular there were things like aggregates over the individual shapes. These things have now been added rather as community contributed efforts; a lot of these are now a part of the product itself. For example, I’ve got a UnionAggregate listed here. In this case I’ve got a map of the country of France and the different provinces. Each province would be contained in a row, the shape of that. By creating a UnionAggregate we’ve grabbed the totals area or the total shape by combining all of the individual shapes from all of the rows, like we do with any other aggregate in the product.
Similarly an EnvelopeAggregate says find me the smallest rectangle that would enclose every possible shape contained in the data. ConvexHullAggregate says find me the minimum polygon that would enclose the entire shape without going back convex on itself. These sorts of methods are completely amazing, the level of work they do for you. CollectionAggregate allows us to take a collection of different shapes and combine them together into an aggregate. Another interesting new method is ShortestLineTo. This allows me to take two arbitrary shapes and find the shortest line that would connect them, and importantly it doesn’t have to be between vertex points, it can now find the point on perhaps a curve and find this is the shortest place.
A couple of other general related enhancements here. The precision has been increased, so we had 27-bit precision in earlier version. This has been moved to 48-bit precision now. The sort of example before where I had a map of France and we had the different provinces and then we had created a union over the top of that. When we did that – I saw examples of this in SQL Server 2008 – because of the very fine rounding that would occur, what you would often end up with instead of an aggregate that just showed the entire shape, you’d get this sort of whisker effect all over the place where you’d just have the subtlest little bit of rounding in each case would tend to give you these tiny little lines in amongst the overall object. That really was just about rounding.
Now in this case we can now go to a much, much higher precision than what we could do before. Because there are both new and old geometry and geography types what we’ve got is both higher precision and lower precision versions of the same thing. Both of them are supported by the product, but if you have an object and you’re wondering, which version of SQL Server can I work with it on, then what we’ve also added there is there is a minimum level of DB compatibility level. In this example here I’ve said, give me a CircularString. In this case this is something that’s only supported in SQL Server 2012, and that well know text – if I parse that – what I can retrieve back out of that is the minimum version of SQL Server that would support that, which is 110, which is SQL Server 2012.
There were also a lot of methods on the GEOMETRY object that weren’t available on the GEOGRAPHY object in the previous versions. There has been a substantial increase in the number of methods that were on GEOMETRY that are now supported on GEOGRAPHY as well.
Finally if you wanted to use spatial types in a persisted computed column, that wasn’t a support use in previous versions. That is now a supported use. If you look at this example here, I’ve got a table called CustomerLocations. I’ve set a CustomerID, but then I have a latitude and longitude stored as a FLOAT. I’ve created though a calculated column called location where I’ve said take geography, call the point method on the class, pass in the latitude and longitude and 4326 saying that’s my spatial reference I want to use or GPS in this case. Then I’ve said persisted. The advantage of this is that all I need to do is update the latitude and longitude and the location field will be automatically modified to then support that new value and could then be used in applications that make use of that. But that now is supported in 2012.
Return to SQL Server 2012 Developer Training Kit BOM (en-US)
Richard Mueller edited Revision 2. Comment: Removed (en-US) from title