通过

Playing with SQL 2008, long gestation features - Spatial Support

  • 项目

There are a bunch of features in SQL 2008 that have been on the ask list for a VERY long time. I suspect DATE and TIME types will win the prize as I remember asking for these as a customer of 6.5 10 years ago. However one that's been around for a long time (I remember hearing it asked for as part of 7.0) is the spatial data type support. Given the prevalence of GPS data online, geo services (https://maps.live.com etc) and location based services the time seems right to move this capability much more into the mainstream. Its been a long journey I first remember hearing about GIS back in 92 when a colleague was involved in GIS startup project in the local government org that we worked at. I was especially jealous at the time as they had 2 color SPARC stations (the only SPARCS in the org period) and we had to make do with our 80286s :-)

In the intervening years companies like ESRI have made great strides in moving the adoption of these technologies forward, it will be interesting to see how putting them into the core of SQL Server changes this landscape as there have been some very impressive solutions built using SQL Server 2000/2005 and partners over the last few years.

All right, before I started playing with this myself I decided to do a little research, here is what I came up with;

 

Web Cast by 2 PMs working on the project.

Blog of Isaac, one of the 2 PMs from the Web Cast

MSDN Forum for Spatial Support

VERY high level data sheet

 

However the best source of info I found was good ol Roger Doherty (a man I have almost never forgiven me for huge amounts of pain and late nights he caused me when was the ISV Evangelist for BI and I was a Dev Mgr for BI start up...) who is starting the SQL2008 ISV evangelism train as we speak. In the time I have known him roger has spent most of his time as an evangelist but he also worked as a dev on a feature we shipped in Server 2003, so he has a good technical background. His demo app uses the new Geo types along with FILESTREAM in what I would imagine is going to be a very typical app type.

This blog entry has a WEALTH of information that I have so far found invaluable.

The only disappointing thing I have found so far (and I am still researching ) is that it appears that SQL2008 uses flat-earth geometry and not curved earth. The app I am trying to build uses Great Circle Lines, which means I might be busted for now.

 

UPDATE: Isaac says there is support for round earth in Geography, looks like I have a fun weekend ahead.

Comments

  • Anonymous
    November 23, 2007
    PingBack from http://msdnrss.thecoderblogs.com/2007/11/24/playing-with-sql-2008-long-gestation-features-spatial-support/

  • Anonymous
    November 23, 2007
    Euan, We have pretty complete support for a round-earth, it just has it's own type: geography. Cheers, -Isaac

  • Anonymous
    November 26, 2007
    I got back from vacation ( more here if you want to read about where we go and what we do every other

  • Anonymous
    November 27, 2007
    Please add a TSQL function to create a DATETIME value based on input parameters.  There are functions to extract parts of a date, such as DATEPART, but not one to create a date.   DATECREATE( int day, int month, int year, int hour, int minute, int second, int millisecond) We do lots of date arithmetic in our application and building strings of dates for date creation is problematic for performance and language reasons.

  • Anonymous
    December 18, 2007
    Re DATECREATE() Paste this code and give it whirl it is just too simple! Turns out that I needed it anyway. I don't know which systems do not use 1900-01-01 00:00:00 as the base date but I beleive it is universal to TSql so that shouldn't be a problem CREATE FUNCTION fn_CreateDate ( @pYear Integer,    @pMonth Integer,    @pDay Integer,    @pHour Integer,    @pMinute Integer,    @pSecond Integer,    @pMilliSecond Integer ) RETURNS DateTime AS BEGIN DECLARE @dtReturn DateTime    -- TSQL considers 1900-01-01 as the base date    -- The next command will create a date of the proper year as of    -- January 1 at a time of 00:00:00 set @dtReturn = DateAdd(Year, @pYear -1900, 0)    set @dtReturn = DateAdd(Month, @pMonth -1, @dtReturn)    set @dtReturn = DateAdd(Day, @pDay -1, @dtReturn)    set @dtReturn = DateAdd(Hour, @pHour, @dtReturn)    set @dtReturn = DateAdd(Minute, @pMinute, @dtReturn)    set @dtReturn = DateAdd(Second, @pSecond, @dtReturn)    set @dtReturn = DateAdd(MilliSecond, @pMilliSecond, @dtReturn) -- Return the result of the function RETURN @dtReturn END -- ======================================================================= -- Following code tests this function -- ======================================================================= /* Select dbo.fn_CreateDate (1776, 7, 4, 12, 13, 55,123) -- Noon Independence day GO */ -- ======================================================================= -- ======================================================================= -- End of fn_CreateDate  function. -- ======================================================================= GO