MSDN Blog Postings » Demystifying Spatial Support in SQL Server 2008

MSDN Blog Postings » Demystifying Spatial Support in SQL Server 2008 — Mon, 29 Oct 2007 06:41:01 GMT

PingBack from http://msdnrss.thecoderblogs.com/2007/10/28/demystifying-spatial-support-in-sql-server-2008/

In or Out?

Isaac @ MSDN — Mon, 29 Oct 2007 23:59:45 GMT

I've been stalling, trying not to say too much about spatial until it's actually available. The code

re: Demystifying Spatial Support in SQL Server 2008

Dimitri.Rotow — Fri, 02 Nov 2007 01:59:05 GMT

[Fair warning... this is a long post.] Great blog! I see you are using FME. FME is good stuff, but FME is not a GIS and so is very limited compared to using a full featured GIS for work with either GIS data or with SQL Server 2008 spatial.

I'll use Manifold as the GIS example in this post, since I'm a product manager for Manifold and most familiar with that. As far as I know, Manifold is the only production GIS that is shipping today with built-in support for SQL Server 2008 spatial capabilities (since August, 2007 - See the announcement at http://www.manifold.net/info/news.shtml ).

Manifold has a lot of spatial DBMS experience supporting other vendors, including Oracle Spatial, IBM DB2 with IBM's Spatial Extender, PostgreSQL/PostGIS, SQL Server 2005 using the Manifold Spatial Extender, and "generic" spatial DBMS capability for virtually any DBMS. I hope these comments, drawn from our experience with many tens of thousands of spatial DBMS applications, will help novice spatial DBMS users avoid re-inventing the wheel when putting the mighty power of SQL Server 2008 spatial to use.

GIS data often requires significant work besides simple format translations to be useful within spatial DBMS, and such work is usually best done in the visual / analytic / editing context provided by a real GIS. That speeds up workflow, and allows analytic processes that can transform data into the most useful form, which is what you want if you are going to be storing it within your SQL Server 2008 spatial data warehouse. It is rarely the case that data from legacy formats should be uploaded into a spatial DBMS like SQL Server 2008 without substantial alteration and editing.

A simple example: Let's say you want to grab data from a variety of different formats and different spatial DBMS servers for upload into SQL Server 2008 spatial storage. If you bring the data as layers into a real GIS that can do reprojection on the fly, and combine it with existing layers within SQL Server, you can see to what degree they overlay. Errors in projections will be immediately evident, especially if your GIS has the capability of combining layers from radically different sources, such as images, terrain elevation surfaces and many different formats of vector data, as Manifold automatically does.

What often causes trouble with data not seen in context is not something that can be solved by a simple act of reprojection, it is missing small nuances in matters such as datums or specific coordinate system parameters so that the data to be uploaded is slightly off even though the coordinate system (projection) information appears OK. Such problems are easiest to catch in a visual setting but cause no end of chaos once they get embedded into your corporate geospatial data warehouse.

An example where you might need to do more serious work before uploading: Suppose you have a data set which consists of boundary lines created with AutoCAD and stored in a DXF without any geographic context. That's a classic situation that afflicts about 300,000 jurisdictions in the US with civic spatial data as well as endless commercial users such as utilities with corporate CAD data.

Before uploading into SQL Server 2008 you'll need to get that data set georeferenced, you'll want to get it cleaned up to be free of classic CAD errors (dangles, overshoots, unclosed boundary lines, etc.), you'll most likely want to create area objects instead of boundary lines, assign data attributes automatically to the resultant area parcels and only then export into SQL Server 2008 spatial storage. Manifold can do all that in a highly visual, point-and-click environment using a variety of layers, such as automatically streamed-in Virtual Earth satellite or road map backgrounds, to assist the process. You can use hundreds of editing and analytic tools to assist as well. If a problem is not caught before upload, you can easily fix it afterwards because all those tools still work even after the data is in SQL Server 2008.

When a GIS like Manifold has strong DBMS capabilities, you can also slice and dice your data into desired form using data attributes as well as visual or spatial characteristics to control the process. For example, you might want to use spatial SQL to dynamically create new objects based upon spatial relationships such as intersections between buffer zones and existing objects that also take into account the heights of surfaces in the region or the value of data attributes within nearby polygons. In fact, with Manifold you can do that with data before it uploads into SQL Server 2008 or you can do it later, editing data stored in SQL Server 2008 as if it were a local layer.

A GIS like Manifold that can connect directly to a spatial DBMS and visually, seamlessly work with data using a full power, visual GUI brings a lot of additional value to the data stored in the DBMS. For every user whose choice of interface is SQL, there are many more who prefer a visual GUI using something like Manifold. Having both spatial SQL as well as a visual interface makes it a lot easier to manage data in the DBMS and to develop spatial applications because you can choose when you want to write code for server-side, DBMS processing and when you would prefer a more visual, point-and-click process to do the job.

For example, if you want to grab photos within a given area of interest it is a lot easier for most users to simply "lasso" such points with a mouse on a visual map than it is to write SQL to select them. A GIS like Manifold will give you such visual capabilities and will allow you to dynamically edit what's in your SQL Server 2008 spatial storage and to manage that storage using visual means. If you are developing applications, you can use the GIS as a visual workbench while you try out SQL or .NET scripts within Manifold to help you create a spatial application much faster, even if that application ultimately will be implemented exclusively within server-side code.

Keep in mind that getting data into a spatial DBMS is just the beginning. What comes after is use within applications. While there are plenty of big applications that merit server-side coding to create each application individually, there are far many more applications that become instantly available when people use existing, modern GIS capabilities to manipulate data stored within spatial DBMS in an ad hoc, interactive manner. In most cases, it is much easier and faster to simply point-and-click to accomplish the desired task than to write an application that does it.

For an example of working with layers in a visual context, you can do things like the tutorial at

http://www.manifold.net/doc/exporting_kml_to_google_earth.htm

The above tutorial shows an example where favorite restaurants are marked as points of interest using Virtual Earth background images and maps. That tutorial and any of the other hundreds of visual examples in the user manual can be done as layers within SQL Server 2008.

A general intro to Manifold is at

http://www.manifold.net/doc/introduction.htm

See the whole user manual at

http://www.manifold.net/doc/manifold.htm

Manifold has supported spatial capabilities in SQL Server 2008 since August (the spatial pre-release of that date), and that support is bult into in our standard, production release. See, for example,

http://www.manifold.net/doc/spatial_dbms.htm

Everything works: full multiuser editing of drawings in SQL Server, direct connect to IMS web applications, interaction with images and surfaces, visual copy and paste from Oracle Spatial or other spatial DBMS products to Katmai, zillions of editing and other tools, reprojection on the fly, etc.

CUrrent support in the shipping Manifold product is for Katmai pre-releases that are not public. When Microsoft issues a public SQL Server 2008 CTP that includes spatial capabilities, Manifold will immediately issue a free update that supports the public CTP as well. That update will also include detailed, visual examples featuring SQL Server 2008 to help users new to GIS get started.

As mentioned earlier, Manifold supports essentially supports all other spatial DBMS products as well as SQL Server 2008 spatial.

Having the ability to simultaneously connect to just about any other spatial DBMS is extremely useful when moving data into SQL Server 2008. For example, you could have four different windows simultaneously open showing a drawing each in Oracle, DB2, PostgreSQL and SQL Server 2008 and simply copy and paste between windows to move, say, parcel data, from DB2 into SQL Server 2008. Manifold will automatically match projections, do reprojection on the fly if need be, change geometry types into SQL Server 2008 native types and adjust attribute data so that the spatial data is correctly stored within SQL Server 2008.

Prior experience has also helped Manifold assure that support for SQL Server 2008 spatial includes mature support right from the beginning for other key technologies like native 64-bit Windows code, total support for Vista, automatic usage of Virtual Earth, automatic multicore and multiprocessor support, use of NVIDIA CUDA, integrated .NET scripting (including IronPython), etc.

The result is that right now, today, you can combine SQL Server 2008 spatial and Virtual Earth using seamless Manifold GIS running 64-bit in Windows Vista x64 on a quad-core processor and you will have a GIS / spatial DBMS tool of unprecendented power that works with data from just about any spatial data source known and scales from individual desktop use to thousands of simultaneous users within the largest enterprise. That's a real credit to the synergies of the Microsoft ecosystem.

We're really excited about SQL Server 2008 spatial capability. We are so excited about this that Manifold support for SQL Server 2008 spatial is built in at no additional cost into every Manifold edition from Enterprise Edition ($395 a seat) on up. We want our customers to consider SQL Server 2008 spatial a fundamental, standard building block for both server-side spatial applications and also for everyday desktop GIS.

Dimitri Rotow

dar@manifold.net

Microsoft Announces Spatial Partners at TechEd EMEA

Zach Skyles Owens — Tue, 13 Nov 2007 05:11:30 GMT

Today at TechEd EMEA Microsoft announced 11 partners who will support SQL Server 2008 Spatial . 

Microsoft Announces Spatial Partners at TechEd EMEA

Noticias externas — Tue, 13 Nov 2007 05:17:26 GMT

Today at TechEd EMEA Microsoft announced 11 partners who will support SQL Server 2008 Spatial . 

Playing with SQL 2008, long gestation features - Spatial Support

Euan Garden's BLOG — Sat, 24 Nov 2007 07:08:17 GMT

There are a bunch of features in SQL 2008 that have been on the ask list for a VERY long time. I suspect

Playing with SQL 2008, long gestation features - Spatial Support

Noticias externas — Sat, 24 Nov 2007 07:22:51 GMT

There are a bunch of features in SQL 2008 that have been on the ask list for a VERY long time. I suspect

rdoherty's WebLog : Demystifying Spatial Support in SQL Server 2008

Contagious Curiosity — Sat, 24 Nov 2007 10:00:17 GMT

Interested in using the newly-available GEOGRAPHY data type? This well-written article provides a detail

rdoherty's WebLog : Demystifying Spatial Support in SQL Server 2008

Noticias externas — Sat, 24 Nov 2007 10:25:44 GMT

Interested in using the newly-available GEOGRAPHY data type? This well-written article provides a detail

Tessellation?

rdoherty's WebLog — Fri, 14 Mar 2008 21:35:32 GMT

Okay, it's time to come clean. As a technical evangelist, particularly one that focuses on driving platform

MCTS 70-561: Consultando data

SergioTarrillo's RichWeblog — Mon, 24 Mar 2008 09:41:03 GMT

Construyendo comandos para consultas a base de datos, es un tema que siempre despierta interés y siempre