Eye Candy of the Highest Order


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 (24)

  1. tanoshimi says:

    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.

  2. mandown says:

    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

  3. SoulSolutions says:

    Looks really good Isaac. Great work.

  4. ktegels says:

    Issac, are these the Dundas map controls by chance?

  5. But this eye candy is worth waiting for. What I am talking about? I have spend that last weeks nose down

  6. Isaac Kunen says:

    Kent,

    Yup: Dundas it is.

    -Isaac

  7. ben swift says:

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

  8. Andrew Williams says:

    Sorry Ben. But where’s your spatial database Map Viewer. I’m sure we’d all like to see it.

  9. Craig Dunn says:

    well, here’s _my_ spatial database Map Viewer:

    http://conceptdevelopment.net/Database/Geoquery/

    although competing with Microsoft isn’t my idea of fun, so it’s hard to tell whether to spend any more time on it… will wait for the RTM to see what’s in the box.

  10. Hugh@minus34 says:

    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.

  11. Isaac Kunen says:

    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

  12. Hugh@minus34 says:

    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

  13. Andy says:

    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

  14. In an earlier post I mentioned the new visualisation functionality for spatial data.  Michael Rys

  15. Ian Clemens says:

    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

  16. Ryan says:

    Thank you! That’s really all I can say. I’m catching up on some news and this totally made my day.

  17. G.S. says:

    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 🙂

  18. Rob Farley says:

    One of the things on my list of stuff to check out in the RTM version of SQL Server Management Studio

  19. Vladimir says:

    So, what about this project, how is it going now with the RTM?

  20. Isaac Kunen says:

    Vladimir,

    This is out and included in the RTM.  You can download and play with it if you haven’t already.

    Cheers,

    -Isaac

  21. 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?

  22. Isaac Kunen says:

    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

  23. stewart says:

    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!

  24. JC in Australia says:

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