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 … Continue reading The problem with America – spatial data

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 … Continue reading Map of the world in the Spatial Results tab (and converting from Geometry to Geography)

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’, … Continue reading Querying distances using Spatial data types in SQL 2008