NEW SPATIAL FEATURES IN The SQL SERVER 2008 FEBRUARY CTP

New SQL Server Spatial blog

New SQL Server Spatial blog — Sat, 01 Mar 2008 05:31:18 GMT

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

Let Ed tell us what's new in the February CTP

Isaac @ MSDN — Sat, 01 Mar 2008 06:06:04 GMT

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

Let Ed tell us what's new in the February CTP

Noticias externas — Sat, 01 Mar 2008 06:27:20 GMT

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

re: New Spatial Features in the SQL Server 2008 February CTP

adamw-manifold — Sat, 01 Mar 2008 15:36:02 GMT

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!

re: New Spatial Features in the SQL Server 2008 February CTP

adamw-manifold — Sat, 01 Mar 2008 15:44:37 GMT

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.

re: New Spatial Features in the SQL Server 2008 February CTP

Steven Hemingray — Sun, 02 Mar 2008 04:48:10 GMT

Hi Adam,

Just wanted to make sure you fully understand the rather limit set of cases in which the change for #2 would apply. If you have two Polygons that intersect at a Line, in almost all cases Intersection would return that line. However, in a very small number of cases, the Intersection would appear to have non-zero Area and we would generate a Polygon. This Polygon might turn out to be so thin at at least one point that it was considered degenerate and would collapse to Empty. This change only affects the outcome of the degenerate cases - the Intersection code itself remains unchanged. Intersection and most of the other operations already had the semantics of returning any type of object, so in no case could you rely on Polygon/Polygon Intersection always returning another Polygon.

The only operation that does conceptually always return a Polygon is Buffer. In that case, it's a question of which conceptual semantics is stronger:

1. That the Buffer output should contain the original input

or 2. That Buffer output should be a Polygon?

I believe that #1 is definitely the right choice here - the original behavior as seen in Morton's post just seems buggy.

While there is no built-in switch to disable this behavior, it is very easy to handle it yourself by switching off the InstanceOf expression of the output:

select case x.shape.InstanceOf('Polygon') when 1 then x.shape when 0 then geometry::STGeomFromText('POLYGON EMPTY', x.shape.STSrid) end from (select geometry::Parse('POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))').Reduce(10) as shape) x

For Reduce, this change lets the user decide whether they would like to display small Islands as Points on their map when zoomed out for example, which was much more difficult to do under the old behavior.

re: New Spatial Features in the SQL Server 2008 February CTP

adamw-manifold — Mon, 03 Mar 2008 10:01:45 GMT

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.

re: New Spatial Features in the SQL Server 2008 February CTP

Steven Hemingray — Tue, 04 Mar 2008 00:33:14 GMT

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.

re: New Spatial Features in the SQL Server 2008 February CTP

adamw-manifold — Tue, 04 Mar 2008 08:35:49 GMT

Will do. Any comments on the other two questions in my first post? Anyone?

re: New Spatial Features in the SQL Server 2008 February CTP

Steven Hemingray — Wed, 05 Mar 2008 21:33:28 GMT

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.

3. 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.

re: New Spatial Features in the SQL Server 2008 February CTP

t_sch — Wed, 12 Mar 2008 20:08:12 GMT

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

re: NEW SPATIAL FEATURES IN The SQL SERVER 2008 FEBRUARY CTP

SQL server 2008 features for database developers — Wed, 06 Aug 2008 22:40:11 GMT

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>