Category Archives: 6528

The problem with America – spatial data

Ok, it’s not really a problem with America, just a problem with the perception of straight lines like the USA-Canada border. It’s roughly the latitude line 49 degrees north. A line that goes East-West, on a straight line.

Except that latitude lines aren’t straight – except the equator. Just look on a globe and you’ll see what I mean.The curvature of the earth just gets in the way a bit. If you’re ten miles from the North Pole and travel east until you come back on yourself, you’ve just gone around in a circle that’s 20 miles across. The smaller the circle, the more obvious the effect. The bigger the circle (like when you’re near the equator), the less obvious the effect. But it’s still there.

Pilots understand the principle. When I flew from London to Seattle earlier this year, we went across the top of Greenland, because that’s the quickest way of getting there. Pick up a globe of the world, and you’ll see exactly why we should fly that way.

When someone wants to fly from one point on the 49th parallel to another, or to simply draw a line, the shortest route actually goes a little further north. The 49th parallel is like that 20-mile circle – it bends to match the curvature of the Earth.

So if you’re mapping the world using spatial data for SQL Server 2008, you may want to consider this when looking at the American border. It’s not a straight line along the 49th parallel, because the 49th parallel isn’t a straight line.

image

To demonstrate this, my friend Greg Larsen (who runs the Olympic Area SQL Server User Group) has sent me a query. You’ll notice that there are some locations north of the 49th parallel that are with the polygon which seems to be bound by the 49th parallel.

USE AdventureWorks2008;
GO
DECLARE @region geography;
SET @region = geography::STGeomFromText(‘POLYGON((-67 49, -125 49, -125 24, -67 24, -67 49))’, 4326);

SELECT  [AddressLine1]
       ,[City]
       ,[SpatialLocation].Long AS Long
       ,[SpatialLocation].Lat AS Lat
FROM [AdventureWorks2008].[Person].[Address]
WHERE [SpatialLocation].STIntersects(@region) = 1
and [SpatialLocation].Lat > 49;

This query actually gives us 632 rows. That’s 632 addresses in the sample database which are more than 49 degrees North, and yet are within a polygon which has corners at -67 49 and -125 49. Hopefully you can look at the picture of the US border and see where those 632 rows much be.

SQL Server spatial stuff is really clever. But perhaps we need a way of telling it to draw lines east-west, instead of as the crow flies.

Map of the world in the Spatial Results tab (and converting from Geometry to Geography)

One of the things on my list of stuff to check out in the RTM version of SQL Server Management Studio in 2008 is the Spatial Results tab that Isaac Kunen talked about a while back. He called it Eye Candy, and it really is.

I started by grabbing a set of data from Virtual Earth expert (and good friend) John O’Brien, containing geometry figures for the world. Geometry for things that are on the world isn’t exactly the best, but it was better than nothing. So then there was the matter of converting it to the Geography type. Geometry is on a flat plane, Geography is on the world – understanding the spherical nature of it.

But either way, it was still very nice to query.

Converting the data into the Geography type was the next challenge. I started by adding a column which was the right type, and tried the simple:

update dbo.WorldBorders set geog = geography::STGeomFromText([geom].ToString(),4326)

It failed. Seems that there were problems with some of them. So I tweaked my script a little to try them all individually, setting the ones that couldn’t be done to POINT(0 0). For this, I used GO n to run it a bunch of times (but I could’ve checked @@ROWCOUNT in a WHILE loop to be a little more precise). And I used the system of updating a derived table to make sure that I could update the first record each time.

begin try
    update w
    set geog = geography::STGeomFromText([geom].ToString(),4326)
    from (select top (1) * from WorldBorders where geog is null order by iso2) w
end try
begin catch
    update w
    set geog = ‘Point(0 0)’
    from (select top (1) * from WorldBorders where geog is null order by iso2) w
end catch
go 250

This converted the ones that were possible, and this script:

select * from WorldBorders
where geog.STAsText() = ‘POINT (0 0)’

world …showed me that the countries that didn’t convert were Canada, Fiji, Italy, Cote d’Ivoire, Antarctica, Russia, United Kingdom and Svalbard. And you can see that from the Spatial Results tab too. Fiji and Svalbard are clearly missing, right? I love the Spatial Results tab – when you have a query which involves Spatial data, it just appears, containing a graphical representation of the data. You can even change the projection if you’re not happy with the one that it shows by default.

As for why they’re missing? Well, I haven’t got around to looking yet. Probably, one of the shapes involved is listed in the wrong order (which is important for geography, but not for geometry). Also could be that Antartica, Russia and Canada are just too big.

When I tried to count how many polygons there were for each one, using:

select *, geom.STNumGeometries()
from WorldBorders
where geog.STAsText() = ‘POINT (0 0)’

I found that some of the geometry fields weren’t valid. So I tried:

select *, geom.MakeValid().STNumGeometries()
from WorldBorders
where geog.STAsText() = ‘POINT (0 0)’

…instead, which worked (telling me I have 954 polyons, including 478 from Canada alone). I may have wrecked some of my shapes using MakeValid(), but hopefully it will be okay. Svalbard converted okay by just putting MakeValid() back in the original conversion script.

Enter my table of numbers so that I can easily handle each polygon separately, I dumped the polygons to a separate table:

select
    n.num, w.iso2
    ,w.geom.STAsText() as geomtext
    ,w.geom.MakeValid().STNumGeometries() as numpolygons
    ,w.geom.MakeValid().STGeometryN(n.num).STAsText() as polygon
into dbo.worldpolygons
from WorldBorders w
   join
   master.dbo.nums n
        on n.num <= w.geom.MakeValid().STNumGeometries()
where w.geog.STAsText() = ‘POINT (0 0)’

Converting these polygons to geography separately still gives a few errors, but I’m feeling a lot closer. More on this in another post, when I have the time to be able to look at it some more.

Querying distances using Spatial data types in SQL 2008

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
;

distancesNow, 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.