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.
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.
This Post Has 4 Comments
Its a case of SQL2008 be accurate compared with much of what we see on maps that assume the earth is flat.
If you were to convert the data to a geometry type this could provide a solution. Or else get a more accurate polygon with weighpoints every 400 KM.
John.
Yes, exactly. I bet there are many people who consider that the many state borders are straight, just because that’s how they’re shown on a map.
Do you think 400km is accurate enough? I suppose it depends on how accurate you need your points to be (like, how close things are to the border).
Rob
Rob,
I do not get the concept of straight in map context since the Earth is spherical shape.
Even the Equator is not straight line, it is a circle.
If I fly from Boston to New York, I can really keep the plane flying straight with modern navigation, yet it is flying a curved path.
Do you mean the shadow of a straight line on the surface of the Earth as “straight”?
Kalman,
Yeah – pilots don’t have a problem with this stuff. They know that the map shows a straight line as curved. But when people see a line that’s straight on the map (like the US border), they often have trouble understanding that it’s actually a curved line.
Rob