Jaa

Eye Candy of the Highest Order

  • Artikkeli

Hi Folks,

One of the most common questions about the spatial support in SQL Server is how to visualize results.  To illustrate the problem, consider a query over zip code data:

 select id, shapegeog
from zipcodes
where id like '981%'

The results, while an accurate description of the data, are profoundly unhelpful:

image

We can improve the human readability of this by fetching WKT instead of the original geography data:

image 

While we can now read it, it still doesn't mean much.  Unlike social security numbers or names, visualization is almost required if you want to understand spatial data.  And if you really want to visualize the data, our only avenue has been to make use of an external program, such as Virtual Earth.  It's not super hard to make a mashup with VE, but it is another step.

Thanks to some very hard work of a number of people, a very late feature has been slipped into the server: a map control.  With the map control, if you run the query, you get the following:

image

And the magic reveals itself when you click that mysterious "Spatial result" tab:

image

These maps are interactive: you can zoom; you can pan; you can even label the results.  It isn't aimed at reporting, and this isn't a reusable component that can be used in other applications, but it can be invaluable in giving you a feel for what your results look like.  It goes far beyond eye candy: it actually allows you to understand your data.

Unfortunately, it's a bit of a tease at the moment: the code just went in, which means none of this will be available in our upcoming RC0.  It will be in RTM, however, so you shouldn't have to wait too long.

I can claim precious little credit for this, but if you're at TechEd this week, I'd be happy to demo it for you. So would Goldie Chaudhuri, who actually led the effort.

Cheers,

-Isaac

Comments

  • Anonymous
    June 03, 2008
    Awesome! This is a great addition to the spatial offering for SQL2008... no more relying on dodgy third-party visualisation tools when all I want to do is roughly check whether spatial data looks sensible. Congrats to everyone who managed to get this included at such a late stage - can't wait to start using it.

  • Anonymous
    June 03, 2008
    Well well, I had a feeling this was coming and I see its now going to be made available in the final release. Microsoft SQL Server 2008 is getting a map viewer! Many people have been asking how they can visualize their results from spatial queries they

  • Anonymous
    June 04, 2008
    Looks really good Isaac. Great work.

  • Anonymous
    June 04, 2008
    Issac, are these the Dundas map controls by chance?

  • Anonymous
    June 05, 2008
    But this eye candy is worth waiting for. What I am talking about? I have spend that last weeks nose down

  • Anonymous
    June 07, 2008
    Kent, Yup: Dundas it is. -Isaac

  • Anonymous
    June 07, 2008
    "Eye candy of the highest order?"  That would be Google Earth, not this dumb toy.  Your low standards are an embarrassment to Microsoft.

  • Anonymous
    June 11, 2008
    Sorry Ben. But where's your spatial database Map Viewer. I'm sure we'd all like to see it.

  • Anonymous
    June 15, 2008
    The comment has been removed

  • Anonymous
    June 16, 2008
    Hey - where'd that "Select Projection" drop down come from? The last I heard - projections weren't supported and coordinate tranformations were a no-no.

  • Anonymous
    June 17, 2008
    Hi Hugh, There's no big change here.  We still don't support projections in the engine, but we need to have some projection support to display round earth data on your flat monitor.  (I'm still looking for a spherical monitor to make my boss buy me---let me know if you find one.) I don't show it in the picture, but we only support equirectangular, Mercator, Robinson, and Bonne projections. Cheers, -Isaac

  • Anonymous
    June 17, 2008
    Thanks Isaac.  No worries - just checking.   Projection support would be handy, but its not critical as any goood corporate GIS will only have data in a single projection, which makes projections largely irrelevent in the DB. Cheers, Hugh

  • Anonymous
    July 02, 2008
    Isaac, I noticed that there isn't a sql function similar to Oracle's sdo_nn do you know of anything in the works? -Andy

  • Anonymous
    July 03, 2008
    In an earlier post I mentioned the new visualisation functionality for spatial data.  Michael Rys

  • Anonymous
    July 21, 2008
    Related to visualization of SQL 08 spatial, we just posted a new iteration to our (IDV Solutions) Silverlight 2.0 r&d project where you can draw on the map and export to SQL Server 08 script. See http://silverlight.idvsolutions.com

  • Anonymous
    July 24, 2008
    Thank you! That's really all I can say. I'm catching up on some news and this totally made my day.

  • Anonymous
    August 15, 2008
    In regard to the spherical monitor, current Coldplay tour seem to use something that may work. They're nice sizes too - your boss will have to also buy you an office :-)

  • Anonymous
    August 24, 2008
    One of the things on my list of stuff to check out in the RTM version of SQL Server Management Studio

  • Anonymous
    September 06, 2008
    So, what about this project, how is it going now with the RTM?

  • Anonymous
    September 07, 2008
    Vladimir, This is out and included in the RTM.  You can download and play with it if you haven't already. Cheers, -Isaac

  • Anonymous
    October 03, 2008
    Isaac, I've tried it and its really good, but Ive noticed (at least in my experimentations) that when you have a label column and its text has accents, it isnt presented correctly. Do you have any words on this?

  • Anonymous
    October 03, 2008
    Hi Vladimir, I gave it a quick try, and don't see anything out of the ordinary.  That said, if you have a concrete repro of something odd, we'd love to see it.  I'm sure there are some bugs to be squished. It would be best to file it through Connect (http://connect.microsoft.com). Thanks, -Isaac

  • Anonymous
    October 26, 2008
    This is very cool.  Is it possible to return a snapshot of the "eye candy" with a result set, to the client, say as a PNG?  That would be very, very helpfull!  Right now we are pre-rendering millions of "tiles" to overlay on Virtual Earth, from geometry stored in SQL, and storing the PNG files in SQL.  If we could generate the PNG files on the fly...WOW!

  • Anonymous
    March 14, 2009
    It would be great to be able to get the 'eye candy' into SSRS. Displaying the attributes of a list of shapes close to an initial shape would be a lot sexier if it could be done graphically (or can it be done already and I am just not smart enough?)