SQL Server Spatial Support: An Introduction
I’ve been pretty quiet around here, and at least part of the reason is that we’ve been pretty tight-lipped about what we’ve been up to. We’ve now gone public—we’re providing support for geospatial data in our next version of SQL Server, codenamed Katmai. The Virtual Earth folks mentioned this in their blog (others, too) but now I can make a little more noise.
What I’d like to do first is try to explain to the folks out there who aren’t experts in the field what this all means and give a very high-level tour of what we’re providing.
First, what is geospatial data? For starters, we mean locations on the Earth. It’s more than location, though: we mean the location and shape of objects on the Earth. Think of the description of roads, states, lakes, etc. For example, think of something like Live Maps:
In addition to the normal objects, I’ve added a polygon that shows Microsoft’s main campus. We want to be able to store all of the data on this map—the roads, the parks, and user-generated polygon—in SQL Server. Being a database, we want to be able to ask questions about the data. For example, “What are the roads that intersect Microsoft’s main campus?” A more complex example would be “What is the area of all parks within 1 kilometer of Microsoft’s main campus?”
How do we do this? First, we need new data types to be able to store this information. Toward this end, we are introducing two new types in Katmai:
· The first type, “geography”, will store points, lines, polygons, and collections of these in latitude/longitude coordinates using a round-Earth model. Most commonly-available data is given in latitude/longitude coordinates, so we expect that most people will want to use this type. Furthermore, this type will give correct computations on a true ellipsoidal model of the planet. What is the area of Indonesia? Will my flight from Seattle to Beijing take me over North Korea? Where can I store my GPS readings? This is the type for you.
· A “geometry” type to support flat-earth data. For those of you who are familiar with such things, this type is our OGC-compliant offering. In some ways, this is a more specialty offering for people who need to work in projected map coordinates either for legacy or legal reasons, but this type can be used for things like interior spaces as well, e.g., “Where in this warehouse is my book?”
If we take our roads data above—perhaps all of the roads for the United States—we could store them in a table Roads:
Roads(name varchar(30), location geography)
I.e., geography is a column type just like any other. We expose a pretty comprehensive set of operations on these type through a method-based interface. For example, if we have a geometry variable @microsoft that represents Microsoft’s main campus, we can find out which roads intersect it with the query:
SELECT name
FROM Roads
WHERE location.STIntersects(@microsoft) = 1
Given a similar Parks table containing all US parks, we can ask our parks question from above:
SELECT SUM(location.STArea())
FROM Parks
WHERE location.STDistance(@microsoft) < 1.0
Of course, beyond answering this query, we need to be able to answer the query quickly. Since my data may be very large, speed is going to mean having a good spatial index. Perhaps that will be the subject of my next post. Watch this space for more.
Cheers,
-Isaac
Comments
Anonymous
May 16, 2007
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.Anonymous
May 17, 2007
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 andAnonymous
May 17, 2007
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.Anonymous
May 17, 2007
Doesn't ESRI already provide GeoSpatial support for SQL Server using their SDE product?Anonymous
May 17, 2007
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, -IsaacAnonymous
May 17, 2007
The comment has been removedAnonymous
May 18, 2007
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, -IsaacAnonymous
May 18, 2007
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.Anonymous
May 18, 2007
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.Anonymous
May 19, 2007
<<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, BillAnonymous
May 21, 2007
The comment has been removedAnonymous
May 29, 2007
This is a little bit less of an introductory post than the last one , but there was quite a bit of discussionAnonymous
May 30, 2007
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...Anonymous
June 26, 2007
I feel so old fashioned not being able to understand the world of SQL going beyond 2D. I found this greatAnonymous
July 02, 2007
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)?Anonymous
July 04, 2007
The comment has been removedAnonymous
July 27, 2007
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? SFAnonymous
August 31, 2007
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?Anonymous
August 31, 2007
I feel so old fashioned not being able to understand the world of SQL going beyond 2D. I found this greatAnonymous
December 13, 2007
So how do you get started incorporating spatial data in SQL Server 2008? Here's some samples, tutorials,Anonymous
December 13, 2007
So how do you get started incorporating spatial data in SQL Server 2008? Here's some samples, tutorialsAnonymous
December 18, 2007
With what viewer can see spatial data of sqlserver?Anonymous
March 19, 2008
Geography & Geometry has much significance in day to day life, within that as a data now you canAnonymous
March 19, 2008
Geography & Geometry has much significance in day to day life, within that as a data now you canAnonymous
March 31, 2008
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.Anonymous
April 15, 2008
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?Anonymous
July 23, 2008
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?Anonymous
October 14, 2008
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?Anonymous
November 03, 2008
In this second to last chapter in the series I’ll discuss leveraging SQL Server 2008’s support for spatialAnonymous
November 04, 2008
How can I load shape files into SQL Server 2008? Is there a free tool available or does Sql Server 2008 have a tool?Anonymous
December 16, 2008
I feel so old fashioned not being able to understand the world of SQL going beyond 2D. I found this greatAnonymous
January 05, 2009
I feel so old fashioned not being able to understand the world of SQL going beyond 2D. I found this great