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

…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.