14Jun 2010 by Grant Fritchey 3 Comments

Maps in Reports!

Spatial Data
I've been playing with SQL Server 2008 R2 for quite a while in the CTP's and what not. But, I hadn't made a concerted effort to look at the new version of Reporting Services... HUGE mistake. There are a number of, not insubstantial, updates to Reporting Services that, probably, are the primary selling points of 2008 R2. The big one, for me, was the incorporation of mapping directly into reports. Did I say big? I'm sorry, I meant, enormous, gigantic, galactic, really, really important... Why you ask? I work for an insurance company. We insure factories, warehouses, that sort of thing. Funny bit of information about a factory, when floods, high wind or earthquakes come calling, they just don't seem to be able to get out of the way adequately. Weird, huh?…
Read More
23Jun 2009 by Grant Fritchey No Comments

Learning Spatial Data in SQL Server 2008

PASS, Spatial Data
PASS just published a new Top 10 list of mine over at the their web site. This one is the Top 10 Articles on the web if you're trying to learn about spatial data. I'm not trying to say that I know what I'm doing with spatial data. I'm still feeling my way forward. These articles have proved to be the most useful in the learning I've done so far. I wanted to publish a little more information than we put into our Top 10 lists over at PASS. Having the list is good, but I thought it needed a bit of commentary to be complete. All these articles are good and all the web sites hosting the articles have good stuff on them. A few of the web sites stand out. Paul Randal's…
Read More
11Jun 2009 by Grant Fritchey 2 Comments

More Spatial Headaches

Spatial Data, T-SQL
I keep thinking I've got a handle on spatial data and I keep finding out I'm wrong. I rewrote the cursor that my co-worker used into a query that joined the list of test criteria onto our list of locations. I used an OUTER APPLY to more or less run a check for each of the test criteria since, except for the proximity to the locations, there's no actual relationship between the test criteria and the location data for me to join on. The query looked something like this: SELECT a .CriteriaDesc ,a.CriteriaLoc ,l.[Location].Lat AS LocationLatitude ,l.[Location].Long AS LocationLongitude ,l.LocationDesc FROM dbo.Alert AS a OUTER APPLY (SELECT x.[Location] FROM dbo.MyLocs x WHERE x.OrgID = 42 AND x.[Location].STDistance(a.AlertLocation) < 50000) AS l The cursor was taking almost a full minute to…
Read More
09Jun 2009 by Grant Fritchey 3 Comments

Spatial Indexes and a Lack of Data

Spatial Data, T-SQL
I was feeling quite confident about my new-found abilities with spatial indexes so I did a presentation for my team, to share what I had learned. I had also been sharing with one co-worker as I developed the knowledge of spatial indexes. While I was preparing my presentation, he was preparing his. I had focused on finding a set of data that showed it's proximity to a test location and then showing how retrieving that set of data was faster because of the spatial index. He took a different approach. He took the idea of saying, here's a list of different test locations, let's see which one of our internal locations meet the proximity test. At the same time, he tried three different spatial indexes, one with high granularity, one with medium and a final…
Read More
28May 2009 by Grant Fritchey 3 Comments

Spatial Index & Performance & Brain Pain

Spatial Data, T-SQL
In my previous post, Spatial Data Hurts My Brain, I showed how a query used the spatial index, but hurt performance. Several people jumped in to try to help out. Thanks to Bob Beauchamin, Isaac Kunin, Gail Shaw, and Valerie Yakich (via Twitter). I've definately learned a bit more than I knew previously about spatial data and spatial indexes. I've posted about it before, but it bears repeating, if you're just starting out, I can't recommend Alistair Aitchison's book, Beginning Spatial with SQL Server 2008, enough. Here's where I'm at. I think I was confusing/confused/confounded/something about what to expect from a spatial index. I'm going to present two queries and two indexes and try to explain why each works well, or not together, mainly as a means for enabling my…
Read More
27May 2009 by Grant Fritchey 13 Comments

Spatial Data Hurts My Brain

Spatial Data, T-SQL
I'm still barely scratching the surface working with spatial data in SQL Server 2008. We've ported some of the data into a table where we built a geography spatial data column and we're begginning to work with point data. The requirements from the developers are, so far, very simple. They'll feed me a point and I find all the locations "close" to it. We had to go round & round on what defines "close" but finally settled on, I think, 15km. The query to answer a question like this is ridiculously simple (a few object names have been changed): SELECT ebe.[Location].STDistance(@Location) AS Distance, ebe.[InterestId], ebe.[Location].Lat AS Latitude, ebe.[Location].Long AS Longitude, ebe.[OrgId] FROM dbo.[ebe] AS ebe WHERE ebe[OrgId] = @OrgId AND ebe.[Location].STDistance(@Location) < @CloseDistance I'm not even hard-coding the "close" value…
Read More
30Mar 2009 by Grant Fritchey 4 Comments

Reading to Learn

Spatial Data
I just finished chapter 1 of Alastair Aitchison'snew book on SQL Server spatial data, "Beginning Spatial with SQL Server 2008." If this is the beginners book... oh boy. The advanced book must be insane. Seriously though, Mr. Aitchison seems to have written a fantastic book. I'm going to tear through it as fast as I can because I've got two projects that are looking to start using spatial data and quite frankly, I'm a bit lost. There's a great discussiongoing on over at SSC as to the worth of technical books for DBA's. It's based on this editorialby Tony Davis. I'm surprised by the number of people who say they don't use books. It seems that a lot more people use blogs and articles and discussion groups to learn. Maybe…
Read More
02Jun 2008 by Grant Fritchey 6 Comments

Spatial Data Types in SQL Server 2008

Uncategorized
I'm pretty excited about this new data type and the functionality associated with it. I'm sure our company will have some application for this. However, I saw this interesting post over at SQL Server Central. If anyone can help shed some light on this question, I'm sure a lot of people would be interested in the answers. UPDATE: I made an incorrect link above in the original posting. It was just an oopsie, not an intentional misdirection. My apologies.
Read More