SQL Server SIG at the UC

My blogging has been slower this time around because I’ve been doing a lot of booth duty. This is my thrid conference this year and some of our other staff are getting to go to the sessions. I did, however, go to the SQL Server SIG a little while ago.

Ed Katibah had a few tricks up his sleeve. First off, he dropped the news that, as of 11:00am, SQL Server 2008 is now in RTM. Very soon, the final version will be in our hands. That’s great news to everyone who has been working with the CTPs and RCs.

Ed had few other nuggets. He described the stress-testing that SQL 2008 has undergone. In short, there has been about 375,000 hours of stress testing run against SQL 2008 so we should be able to expect it to be solid. Additionally, Microsoft has already been running on SQL 2008 for “a few months.” To me, this is all great news that makes me feel even more confident about the product (which already had my confidence). When I first installed the November CTP, I felt that much of the platform was already production-ready but more testing and use can’t have hurt.

Ed went on to describe the two spatial types and reationale for doing that. That discussion is well documented and I fall in the “it’s a good thing category” mainly because of previous work with applications that had over-the-horizon requirements.

Ed gave props to Paul Ramsey during his talk when explaining the XY switching debate. It was great to see that kind of respect on display.

Some nuts and bolts: SQL 2008 limits spatial objects to about 250 million vertices. This greatly exceeds Ed’s largest test case, which is a high-res vector data set of the world’s oceans. Polygons are limited to about 65,000 holes. I think those limits leave a lot of wiggle room. He also discussed the significance of coordinate ordering for the geography data type. Basically, it’s counter-clockwise for outer rings and clockwise for inner rings. This is consistent with implementations in Informix, DB2 and Oracle (those are what he mentioned). He did mention that this was an industry consensus but there was no official specification on this matter. Ed also mentioned it may be a topic of future discussion for OGC.

Ed also demonstrated the spatial results window in the SQL management Studio. This is a really nice tool that lets you visualize the results of your spatial SQL inside the management studio. As a developer, that will be a huge help because I won’t have to do a test run to see if I got the query right. Ed just saved me a bunch of time!

Ed went on to announce a companion CodePlex site for SQL Server 2008 spatial tools: www.codeplex.com/SqlSpatialTools. There are already some tools that Isaac has posted but Ed said that other tools will include shapefile and KML converters. The site will also be open to contribs from the community. All of the tools currently there were developed using the Builder API and full source code is available so they serve has great examples of working with the Builder API.

All in all, it was a great talk. Ed’s one of the nicest guys ever and he is having a lot of fun with ArcSDE on SQL Server 2008.

  • Marty

    As an accountant, I will soon be receiving GPS signals from truck drivers giving me their current lat-long locations as they drive across the U.S. What I need is the ability to freeform my own territories defining specific areas provided by the IRS and give each of these polygons their own unique name. I anticipate the number of territories I need to define is 1500+. I need to create my own territories because some are based on city boundaries, some are based on counties, still others are based on metro areas like Washington DC which will includes parts of Maryland, Virginia, and Pennsylvania. Then I need a territory that includes the entire state of North Dakota. Then I need to draw out still other polygons that stretch out along certain highways.

    Therefore, my programmer and I need a better understanding how to either import these polygons or just hard code the vertexes for each of these polygons. My programmer can then look up these lat-long locations and evaluate if a given truck is currently inside ANY of these 1500+ territories. If the answer is YES, my programmer can then specifically identify which of the 1500+ territories they are currently inside. Any suggestions??

    Will your software provide me with a map of the U.S. so I can draw out these territories?

    Will your software permit me to define a territory INSIDE another territory? For example, Santa Monica California needs to be segregated out from the rest of Los Angeles County. My programmer and I can already handle those cases where a lat-long is inside one territory that happens to reside inside another territory.

  • Marty,

    I will begin by clarifying that I don’t have any software myself so the term “your software” doesn’t apply to me. If any vendors out there read this post and the comments, feel free to chime in.

    That said, yes the software exists to allow you to define your territories, check to see if your truck is in one of them and identify specifically which truck is in which territory. You can carve territories out of others (such as your Santa Monica example).

    I would typically do the data creation part in a desktop GIS package but the analysis could be done via web application (or you could stay on the desktop if you wish).

    The software I am most comfortable with to perform these tasks is ArcGIS by ESRI (http://www.esri.com). There are also redistricting tools to help with territories (http://www.esri.com/industries/elections/business/redistrict.html). That said, there are other software systems out there that can perform these tasks as well. They include Manifold (http://www.manifold.net), MapInfo (http://www.mapinfo.com), GeoMedia (http://www.intergraph.com/sgi/products/default.aspx#), uDig (http://udig.refractions.net/), Quantum GIS (http://www.qgis.org) and others. I am less familiar with how to perform your specific tasks in those systems but I am sure they are quite capable.

    Generally, most of these applications have programming APIs to enable the location queries you describe. Additionally, web application envronments like ArcGIS Server and SharpMap can also do such things. If you use a spatially enabled database like SQL Server 2008, PostGIS or Oracle Spatial, you can perform those queries using spatial SQL fairly easily. That would be my preferred route, leaning toward SQL Server 2008 or PostGIS.

    Hopefully this points you in a direction or two to solve your problem. I have implemented similar systems (tracking many multiple objects). If you wish to discuss this more, please feel free to contact me directly (see the About page). Blog comments can get cumbersome for this sort of thing.

    If anyone else has any other advice for Marty on this, feel free to contribute.