SQL Server Spatial Support: An Introduction

SQL Server Spatial Support: An Introduction

  • Comments 32

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:

Microsoft's Main Campus

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

  • RTFM on SQL Server 2008 (Spatial)

  • 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?

    SF

  • 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?

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

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

  • So how do you get started incorporating spatial data in SQL Server 2008? Here&#39;s some samples, tutorials

  • With what viewer can see spatial data of sqlserver?

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

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

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

  • 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?

  • 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?

  • 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?

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

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

Page 2 of 3 (32 items) 123