SQL Server Spatial Support: An Introduction

re: SQL Server Spatial Support: An Introduction

SharpGIS — Thu, 17 May 2007 04:46:03 GMT

Why this distinction between geography and geometry? To me, everything is geometry, and a geometry has a spacial reference. The spatial reference specifies whether this is in spherical (geographic), cartesian (projected) or geocentric space, what datum is used etc.

Are the any subtypes like point, polyline, polygon etc, or will a table always be this generic type where you can't enforce the specific geometry type?

Furthermore I don't see a unit on those distance and area queries, so how would that work on a geographic type? Is the distance in degrees which rarely makes much sense (and even less on an area). Furthermore the datum used will also change this. I know this is not part of the OGC stuff, but then again, you introduced this new (weird?) geographic type. Maybe you can elabotate a bit more on why we now have these two types.

SQL Server Spatial Blog

All Points Blog — Thu, 17 May 2007 15:43:59 GMT

Isaac @ MSDN is a new blog from "Isaac Kunen is a Program Manager on the SQL Server team working on Spatial support in SQL Server 2008." In a first post he introduces the basics of the implementation including the difference between the round earth and

re: SQL Server Spatial Support: An Introduction

Kirk — Thu, 17 May 2007 17:29:49 GMT

I look forward to learning more details about the spatial features of Katmai.

One thing that I hope gets addressed early on is a "translation" feature to take data from existing GIS formats and convert them into the microsoft format (and vice versa). We use lots of boundary files from ESRI or MapInfo for things like county boundaries, zipcode boundaries, etc that would be useful for "point in polygon" type operations.

The Spatial sample for SQL2005 showed some initial promise for us, but there did not appear to be an easy way to convert complex boundaries from another format into something that could be used by it. If I am wrong about that, someone please post how this can be done.

re: SQL Server Spatial Support: An Introduction

GISMasterBlaster — Thu, 17 May 2007 20:25:43 GMT

Doesn't ESRI already provide GeoSpatial support for SQL Server using their SDE product?

re: SQL Server Spatial Support: An Introduction

isaac — Thu, 17 May 2007 20:28:44 GMT

SharpGIS,

You're right: distances in degrees---or areas in square degrees---doesn't make much sense. For geography, the results of operations will depend on which ellipsoid you use, so your SRID will matter. We'll return results in the unit of measure for the system, typically meters (or square meters in the area case).

I'll try to tackle the geography/geometry separation in a future post.

Cheers,

-Isaac

re: SQL Server Spatial Support: An Introduction

Morten — Fri, 18 May 2007 08:12:00 GMT

OK thanks, I'll look forward to that. I really don't see the reason for having the geographic datatype. From my point of view, it will just cause more confusion. Only reason I can see is for some weird bordering case at the date-line and poles where you would want a special type of indexing to handle these cases, but fact is that this is not in any way specific to Geographic - Projected data has the same issue.

To me, saying that "most people would want to use geographic" is clearly a statement indicating that You don't know the geospatial industry very well. Frankly, when I was working for a data vendor, I can't recall a single time where our clients requested data from us in geographic.

re: SQL Server Spatial Support: An Introduction

isaac — Fri, 18 May 2007 18:10:51 GMT

Hi Morten,

I think it depends on which “most people” you’re looking at. It’s true that most people in the GIS community use planar coordinates, but we’re looking to support people beyond the GIS establishment.

It’s true that the planar model is dominant in the established GIS market, but this is more for legacy reasons than anything else. Why would you work on such a distorted version of the Earth except for practicality? It’s easier to work with a flat piece of paper than a globe, but it distorts the data and introduces complexities that are now unnecessary now that most work is done on the computer. That said, we recognize that the GIS community is very heavily invested in planar mapping, so we’re supporting it.

For people without that legacy, however, there is little reason to work in anything other than a round-Earth model. GPS units output in geodetic coordinates, most data is available in geodetic coordinates, and visualization software can project to the plane on the fly for display. Why would someone fresh to the field deliberately distort things (and make the problem more complex) by projecting everything to the plane?

Cheers,

-Isaac

re: SQL Server Spatial Support: An Introduction

SharpGIS — Fri, 18 May 2007 20:00:34 GMT

I'm not saying that geographic coordinates are bad. They have their place. I'm arguing that I don't see the need for two geometry models. It's just a spatial reference on the geometry, and the spatial reference specifies whether the units are degees, meters, feet or what-ever.

re: SQL Server Spatial Support: An Introduction

guyn2gis — Sat, 19 May 2007 08:49:47 GMT

I think this is fantastic. It is about time, and makes complete sense, since GIS data has moved beyond the "GIS Professional" level to the masses. More people will understand geography than geometry.

re: SQL Server Spatial Support: An Introduction

Bill — Sat, 19 May 2007 20:16:09 GMT

<<It’s true that the planar model is dominant in the established GIS market, but this is more for legacy reasons than anything else>>

I wish the above statement was true. The fact is that even if we were starting from scratch, with no legacy to support, we would still probably utilize planar mapping and common map projections. The reason for this, of course is that the geodetic coordinate systems are based on a mathematical 'approximation' of the earth (the ellipsoid), not the true shape of the earth (the geoid). Of course, none of this matters if you are simply plotting points of interest, but makes a world of difference when it comes to subdivision boundaries, and other locations requiring pinpoint accuracy.

It is great to finally see spatial support coming. I hope it will follow Oracle's lead in providing a complete spatial model that will eliminate the need for middleware like ESRI's SDE.

Cheers,

Bill

re: SQL Server Spatial Support: An Introduction

Alden — Mon, 21 May 2007 23:03:12 GMT

How are you handling topology? Oracle uses a persistent (explicit) topology data model while ESRI uses on-the-fly topology discovery. What path are you going down for topology?

One Type, Two Types...

Isaac @ MSDN — Tue, 29 May 2007 17:24:56 GMT

This is a little bit less of an introductory post than the last one , but there was quite a bit of discussion

re: SQL Server Spatial Support: An Introduction

Sanjeev — Wed, 30 May 2007 19:29:03 GMT

Great post.

Do you have plans to eventually come out with client tools to work with spatial data stored in SQL2K8, integrating those 70+ spatial functions in some Microsoft Suite, such as SS Management Studio or BI packages (SSIS)?

I see this is a great move to help larger enterprises integrate GIS with Business data. Just saw that there are already spatial ETL tools to help migrate data from hundreds of other spatial formats into SSIS (http://www.safe.com/microsoft).

Looking forward to the next post on Spatial...

SQL Serve 2008: Spatial Data Types

Technoeuphoria! — Wed, 27 Jun 2007 06:18:00 GMT

I feel so old fashioned not being able to understand the world of SQL going beyond 2D. I found this great

re: SQL Server Spatial Support: An Introduction

Ian — Mon, 02 Jul 2007 23:43:25 GMT

I'm looking forward to seeing this. Currently I use MapInfo SpatialWare, which allows me to store my geometries as a blob in the SQL server table. From what I understand, this new spatial support will allow me to do the same thing (no like ArcSDE requiring middleware).

I can see some benefit in having the two data types (the maths is one), and I hope that as part of this development there will be functions within the database to convert between geographic and geometry datatypes (co-ordinate conversion). If this functionality is not available, then the product will have very limited value, as you would have to use a GIS to perform this conversion.

I am also interested in the precision of the stored geometries. I often work with survey accurate data, and some software stores this as a series of floating point values (which degrades the accuracy of the data), is this the case with these new data types?

Do these datatypes store 2D, 2.5D or 3D geometries (even a GPS will give co-ordinates in 3 dimensions)?

RTFM on SQL Server 2008 (Spatial)

Stuff that's in my head — Wed, 04 Jul 2007 23:39:16 GMT

RTFM on SQL Server 2008 (Spatial)

re: SQL Server Spatial Support: An Introduction

Sandflea — Fri, 27 Jul 2007 16:16:04 GMT

I believe the power of ESRI's solutions is how well they document their api (ArcObjects), and how they've tended to embrace XML web services (ArcXML, ArcGIS Server, ArcIMS).

If MS can keep their technologies relatively well documented, and blog important development milestones (in forums like this BLOG) - then we'll see some more excellent opportunities for systems integrators, programmers, geeks, etc.

Glad to hear things are moving forward. How about an update?

re: SQL Server Spatial Support: An Introduction

Jay Douglas — Fri, 31 Aug 2007 18:06:12 GMT

I’m very excited about MS finally supporting geometry types in SQL, it was a long time coming. I don’t even know the last time PostGIS came out with a new version, which has its own issues.

SharpMap and MsSqlSpatial have done a GREAT job filling the void for MS developers working geometry types. Good job.

Since SQL 08 is going to support geometry types, does C# 3.5 have Geometry types and supporting classes much like SharpMap and JTS?

SQL Serve 2008: Spatial Data Types

Jocelyn World — Sat, 01 Sep 2007 08:31:26 GMT

I feel so old fashioned not being able to understand the world of SQL going beyond 2D. I found this great

GIS & Spacial How-tos for SQL Server 2008

US ISV Developer Evangelism Team — Thu, 13 Dec 2007 19:37:28 GMT

So how do you get started incorporating spatial data in SQL Server 2008? Here's some samples, tutorials,

GIS & Spatial How-tos for SQL Server 2008

Noticias externas — Thu, 13 Dec 2007 20:07:48 GMT

So how do you get started incorporating spatial data in SQL Server 2008? Here's some samples, tutorials

re: SQL Server Spatial Support: An Introduction

Juan B. — Tue, 18 Dec 2007 19:39:21 GMT

With what viewer can see spatial data of sqlserver?

SQL Server 2008 Spatial data - geography & geometry, all in one

SQL Server 2008 - CTP (SSQA.net) — Thu, 20 Mar 2008 03:47:42 GMT

Geography & Geometry has much significance in day to day life, within that as a data now you can

SQL Server 2008 Spatial data - geography & geometry, all in one

SQL Server 2008 - CTP (SSQA.net) — Thu, 20 Mar 2008 14:24:21 GMT

Geography & Geometry has much significance in day to day life, within that as a data now you can

re: SQL Server Spatial Support: An Introduction

Forrest — Tue, 01 Apr 2008 01:41:08 GMT

This is exciting news ... and long awaited. Now the real problem comes up: how to make use of the spatial data once it's stored and tagged to the objects it points to? That's always been an abstract question up 'till now.

re: SQL Server Spatial Support: An Introduction

Nick C — Tue, 15 Apr 2008 21:54:31 GMT

I agree with Morton. No need at all for Geography data type. Geodetic coordinates are almost never useful in the real world. Flat is the only option for visualization. Ever see a blueprint in DMS?

Looking for way to self-identify the unit of measure in float values

John Huff — Thu, 24 Jul 2008 01:47:55 GMT

Common to many data endeavors is the need to distinguish between measures quoted in various measures (e.g. years vs months, decimal vs percent vs "basis points" where 1000 bp = 1 percent).

Presently, at our firm we distinguish by convention, which means the implicit knowledge of which of the above units has been chosen for a field needs to live throughout the organization.

It would be great if there was an efficient way to associate the units with the values IN THE DATABASE, so that association would have to be performed only once at the initial population in the table. (In 2008, data vendors typically indicate units by implicit convention--e.g. the documentation states a certain measure is quoted in percent--not by self identification.)

I realize I could substitute an xml column containing the float & unit pair, but that approach is condemned not to scale.

Any suggestions?

re: SQL Server Spatial Support: An Introduction

Nighthawk — Wed, 15 Oct 2008 02:29:05 GMT

What's the graphic representation of a spatial query's result? Can we render it as a map? Do we need a third-party software to view the map over the web?

Part VII - WiE Goes Spatial: Using SQL Server 2008 as a Spatial Rules Engine for Location-based Services.

Discussions on SQL Server Manageability and Development. — Tue, 04 Nov 2008 02:08:39 GMT

In this second to last chapter in the series I’ll discuss leveraging SQL Server 2008’s support for spatial

re: SQL Server Spatial Support: An Introduction

Eva — Tue, 04 Nov 2008 20:02:39 GMT

How can I load shape files into SQL Server 2008? Is there a free tool available or does Sql Server 2008 have a tool?

SQL Serve 2008: Spatial Data Types

Jocelyn — Wed, 17 Dec 2008 13:25:50 GMT

I feel so old fashioned not being able to understand the world of SQL going beyond 2D. I found this great

SQL Serve 2008: Spatial Data Types

jocelyn — Tue, 06 Jan 2009 14:55:24 GMT

I feel so old fashioned not being able to understand the world of SQL going beyond 2D. I found this great