SQL Server 2008 has spatial types which are very useful for all kinds of applications. I thought it would be nice to produce one of those charts like you get in maps, telling you the distance between cities.
So I start by populating the data. This uses the geography data type, which understands about the spherical nature of the world. The data is populated using POINTS, using data which I found around the internet.
create table dbo.Cities
(id int identity(1,1) primary key,
name char(3) not null,
fullname varchar(100) not null,
location geography);insert into dbo.cities values
(‘ADL’,’Adelaide’, ‘POINT(-34.92 138.58)’),
(‘MEL’,’Melbourne’, ‘POINT(-37.78 144.97)’),
(‘SYD’,’Sydney’, ‘POINT(-34 151)’),
(‘PER’,’Perth’, ‘POINT(-31.95 115.87)’),
(‘BRI’,’Brisbane’, ‘POINT(-27.48 153.13)’),
(‘CAN’,’Canberra’, ‘POINT(-35.3 149.12)’),
(‘HOB’,’Hobart’, ‘POINT(-42.87 147.32)’)
;
Now that this is populated, a simple query will produce the results that can be put into a report. STDistance is a method which applies to one geography value, accepting another as a parameter.
select c1.name city1, c2.name city2, c1.location.STDistance(c2.location) / 1000 distance
from dbo.Cities c1
join dbo.Cities c2
on c2.id <= c1.id
;
Now, I can make a simple matrix in SSRS, using =IIF(Fields!city1.Value = Fields!city2.Value, Fields!city1.Value, Sum(Fields!distance.Value)) as the value in the centre of the matrix, with city1 and city2 on the rows and columns. This formula means that through the main diagonal of the matrix, the city names will be displayed. Hiding the header rows and columns, we quite quickly achieve something that looks like the image here. Of course, if you’re doing this for a proper application instead of just a proof-of-concept, you should take the time to format it more nicely. Colours, borders, that type of thing. But you get the idea.
I’m not sure how useful this is to your own applications – but it’s so easy to query this kind of spatial data, I encourage you to start considering it to be a useful part of your toolset.
This week, a tour of SQL Server User Groups in Australia starts, covering the spatial features in SQL Server 2008. The list of the meetings are at http://www.sqlserver.org.au, and includes all seven groups.
