Partager via

NEW SPATIAL FEATURES IN The SQL SERVER 2008 FEBRUARY CTP

  • Article

Now that the February CTP (CTP-6) for SQL Server 2008 is available, it's time to let folks know what new spatial features have been added or updated since the November CTP (CTP-5).

Before I get into the new and updated features, I want to make sure that it is clear that the latitude-longitude coordinate ordering switch, which we will be making for the Geography type, is not in CTP-6. Please see https://blogs.msdn.com/isaac/archive/2007/12/27/latitude-longitude-ordering.aspx for more information

Here are the new and updated features:

Degenerate Polygons are now collapsed to LineStrings or Points instead of to Empty. Previously, if you had a Polygon with a very thin spike and ran an operation on it, there was a good chance that the spike would disappear entirely. Additionally, in some cases, STIntersects between two Polygons would return 1, however the STIntersection would return GeometryCollection Empty because the resultant intersection was considered to be a degenerate Polygon. Another example of this behavior is seen in the following post: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2709103&SiteID=1 where a small buffer on a large LineString produced a MultiPolygon which looked like a dashed-line as portions of the resultant very thin Polygon were considered degenerate and collapsed to empty. In CTP-6, this will return a GeometryCollection with LineString segments connecting all of the original MultiPolygon segments together.

Reduce (Douglas-Peucker -based Generalization). Because of the above change, we were able to remove the exception that would occur when you ran Reduce on a Geometry that would produce an invalid output. Instead, we call MakeValid on the result, which, although it may alter the type or structure of the original Geometry, will not collapse it to Empty. For example, in CTP-5 the call:

SELECT geometry::Parse('POLYGON ((0 0, 0 10, 10 10, 10 0, 0 0))').Reduce(10).ToString()

would throw an exception saying that the method could not be completed because the result would be Invalid. In CTP-6, it will return:

LINESTRING(10 10, 0 0)

In addition, the Reduce method was added to the Geography type. Given a tolerance in the units of the SRID defining the Geography, Reduce will apply the Douglas-Peucker algorithm to return a simplified version of the Geography data. Since the Reduce method is intended for scenarios where speed and simplicity is more important than accuracy, determining if a vertex is within tolerance to the line will not be computed by using the precise geodetic distance. Instead, the distance will be computed as the great circle distance on the sphere defined by the average of the ellipsoid's semi-major and semi-minor radii. While this is less accurate, it is significantly faster to compute.

Filter. Filter is a new method added to both types that provides a fast index-only intersects method that may produce false-positives (a.k.a. a primary filter). It does this by returning all objects in the cells of the index which contain the parameter object, without running the full spatial STIntersects test to determine whether they actually intersect. If no index is defined on the table or the index is not used in the query or the method is used on the client directly, then the method behavior maps exactly to that of STIntersects. This method is useful for scenarios where it is important to quickly determine whether there are any candidate intersections, or to quickly return the candidate objects to a specialized client, such as a graphics canvas, which does not care if there is a true intersection at the edges of the display.

EnvelopeCenter and EnvelopeAngle (Geography data type). The methods EnvelopeCenter and EnvelopeAngle were added to the Geography type. These two methods allow users to fetch a simple description for the bounds of a geodetic object. The corresponding operation (STEnvelope) on Geometry does not make sense on the earth, as it returns a bounding box with straight lines that cannot be represented on the ellipsoid. We instead return a bounding circle, which is not guaranteed to be the minimal possible bounding circle. This is the same bounding circle we use to determine if a Geography fits inside a hemisphere.

EnvelopeCenter() : Returns the point described by the vector sum of the points in the Geography. For closed loops, either in a Polygon or a LineString, the duplicate first/last point is used only once. This point will not correspond to the true Centroid of the Geography.

EnvelopeAngle() : Returns the maximum angle between the point returned by EnvelopeCenter and a point in the Geography in degrees. The maximum value for this method is 90, as otherwise the Geography would be considered to exceed a hemisphere.

STDistance (GEOGRAPHY data type). In CTP-5, STDistance could only be calculated on Geography instances if one of the inputs was a Point object. This restriction has now been removed, and STDistance can be calculated on any pair of Geography objects.

We will have a few more changes to Spatial coming in the next CTP (including the aforementioned latitude-longitude coordinate order switch). These changes will be discussed when we are closer to release.

Comments

  • Anonymous
    February 29, 2008
    PingBack from http://www.iter.dk/post/2008/02/29/New-SQL-Server-Spatial-blog.aspx

  • Anonymous
    February 29, 2008
    Hi Folks, Well, it looks like Ed finally started blogging. (I'll try not to let him take too much pressure

  • Anonymous
    February 29, 2008
    Hi Folks, Well, it looks like Ed finally started blogging. (I'll try not to let him take too much

  • Anonymous
    February 29, 2008
    Several things:

  1. Latitude / longitude: "I want to make sure that it is clear that the latitude-longitude coordinate ordering switch, which we will be making for the Geography type, is not in CTP-6" Could you elaborate on what exactly the change is going to be? I have followed the link to Isaac's blog and to the relevant thread on MSDN forum, but came up empty. Is it that you will switch the WKB and WKT representations for the GEOGRAPHY type to use XY ordering or is it something else?
  2. Degenerate polygons collapsed to lines and points: "Additionally, in some cases, STIntersects between two Polygons would return 1, however the STIntersection would return GeometryCollection Empty because the resultant intersection was considered to be a degenerate Polygon." Is there an option to turn this off (if that's in the docs, just say so and I will find it)? For many scenarios, it would be undesirable for an operation that should conceptually produce a polygon to produce something else because of the limitations of floating-point arithmetic. In fact, I am not sure I can come up with a single scenario where this collapse to lines and points would be desired. Why was that done? In the thread you linked, why not just return an empty geometry value? Was it the desire to make geometry::Parse('<polygon>').Reduce(10).ToString() (mentioned in your next point) work regardless of whether or not the parsed polygon is degenerate? If so, why would one want that?
  3. Distance between GEOGRAPHY objects "STDistance can be calculated on any pair of Geography objects" What is the computational complexity of that? Thanks a lot for Filter!

  • Anonymous
    February 29, 2008
    Just noted that the entry to my question 2 is misleading. What I would like to know is this: If I read that correctly, in the new CTP the intersection operation and other operations that were previously returning Empty due to the resulting polygon being degenerate will apparently start returning sets of lines and points. Is there a way to avoid that and force the operation at hand to use the previous behavior and return Empty? Thanks.

  • Anonymous
    March 01, 2008
    The comment has been removed

  • Anonymous
    March 02, 2008
    Thanks for the clarification. First, on the semantics of operations on polygons. I forgot that you are aiming to be compliant with OGC SFS. That answers my "why" question in that the reason intersecting two polygons might return something else than a polygon or a semantic null is that this is what the OGC spec states this operation should do. Same for other operations. I would argue that returning a polygon or a null would be much better than returning a mish-mash of lines and points, but I understand that it is too late to do anything about this. On to your proposed workaround: Of course, one can use "case ... end" like you show but that makes the text of a query much more complex than it should be. If the original query was already complex, it gets completely unreadable. What we and other people will likely end up doing is create a wrapper function that would maintain the "returns polygon" invariant. There would have to be a wrapper function for each spatial operation. It would of course be simpler if we did not have to use these wrappers, since creating them requires having write access to the database (for CREATE FUNCTION). Or am I overlooking a simpler way to both enforce a "return polygon" thing without adding too much to the text (and complexity) of the subject query? Thanks for your answer.

  • Anonymous
    March 03, 2008
    I don't think there is currently a better workaround to meet your requirements.  Though I'm not sure I understand your complete requirements in the general case.  What would you expect for the Intersection of a Polygon and a LineString, for example? Or would this be disallowed? The only product I'm aware of that works this way is DB2, where the inputs to ST_Union must be of the same Dimension and the output of ST_Intersection is defined to be in the minimum dimension of the inputs.  My impression was that this was an implementation restriction rather than the desired behavior, however. If you have a clear, valuable use-case for such behavior and can codify the semantics for different dimensionality in different methods, I'd suggest you file it as a suggestion on connect.microsoft.com.

  • Anonymous
    March 03, 2008
    Will do. Any comments on the other two questions in my first post? Anyone?

  • Anonymous
    March 05, 2008

  1. As you said, the Lat/Long change coming in CTP6 only swaps the coordinate order in WKT and WKB.  GML, the static Point constructor, and the on-disk binary format are not affected.
  2.  Geodetic distance complexity is still O(n*m), just like geometry distance.  The constant factor is just a little higher to account for doing operations on the ellipsoid vs the plane.

  • Anonymous
    March 12, 2008
    Hi Ed, great to see you here, too! I observed that Reduce() acts the same on LINESTRINGs and that does not conform to the docs that say the start and end points of LINESTRINGs stay the same. select geometry::Parse('LINESTRING(0 0, 0 10, 10 10, 10 0, 0 0)').Reduce(10).ToString() returns LINESTRING (10 10, 0 0) I already filed a bug in Connect. Reason is we always work on polylines as the base objects and concat polygons on the fly. So we are able to display borders of different hierarchical levels in different signatures and don't waste space on redundant coords. Some polylines represent small islands and those have the same start and end point. Cheers, Thomas

  • Anonymous
    August 06, 2008
    Thanks for nice post. Here are some more features that SQL server 2008 provide for database developers <a href="http://www.techbaba.com/q/2051-new+features+sql+server+2008+database+developers.aspx">SQL server 2008 features for database developers</a>