Monday, December 29, 2008

First Article at SQLPASS.org

I wrote an article last week for PASS's web-site, and it's currently their "feature article":
time and datetime2 - Exploring SQL Server 2008's New Date/Time Data Types
I may end up doing some volunteering for them regarding technical articles. Right now, I think everyone is on Christmas vacation.

Also, the 22nd installment of my Database Journal column was published Friday:
Performance Testing – SQL Server 2008 versus SQL Server 2005

Thursday, December 04, 2008

Ten Minutes with SQL 2008's Geography Methods

Problem: Figure the distance between two points.

How far is WebMD's office from my home (as the crow flies)? How far will I travel to get to Spokane for New Year's Eve? We just booked a trip to Disney World. How far is that?

Let's use SQL 2008's new Geography methods.

Initially, just using variables, here's how it's done.
DECLARE @MyHome  geography
DECLARE @WebMD geography
DECLARE @Spokane geography
DECLARE @Disney geography

DECLARE @MilesPerMeter decimal(19,12)
DECLARE @SRID smallint

SET @MilesPerMeter = 0.000621371192
SET @SRID = 4326

-- Exercise the Point, STDistance and
-- STAsText methods on variables
-- Address-to-Lat/Lon Converter:
-- http://stevemorse.org/jcal/latlon.php
SET @MyHome =
Geography::Point(45.55823900, -122.84051000, @SRID)
SET @WebMD =
Geography::Point(45.53677100, -122.70774100, @SRID)
SET @Spokane =
Geography::Point(47.65354400, -117.41126300, @SRID)
SET @Disney =
Geography::Point(28.40982500, -81.58848800, @SRID)
Notice how we create variables and then call methods on those variables. The STAsText method returns this:
SELECT @myHome.STAsText() AS Point

POINT
----------------------------
POINT (-122.84051 45.558239)
Next, let's get the distances:
SELECT
'My home to Spokane' AS 'From/To',
(@myHome.STDistance(@Spokane)
* @MilesPerMeter) AS 'Distance in Miles'
UNION ALL
SELECT
'My home to WebMD''s office' AS 'From/To',
(@myHome.STDistance(@WebMD)
* @MilesPerMeter) AS 'Distance in Miles'
UNION ALL
SELECT
'My home to Disney World' AS 'From/To',
(@myHome.STDistance(@Disney)
* @MilesPerMeter) AS 'Distance in Miles'
Results:
From/To                   Distance in Miles
------------------------- -----------------
My home to Spokane 296.125371694122
My home to WebMD's office 6.61091520932017
My home to Disney World 2533.12214098223
Now let's put this into a table.
CREATE TABLE dbo.Points (
PointName varchar(32) NOT NULL,
Lat decimal(19,8) NOT NULL,
Lon decimal(19,8) NOT NULL,
Geog Geography NULL
)

INSERT INTO dbo.Points (PointName, Lat, Lon) VALUES
('Rob''s Home', 45.55823900, -122.84051000),
('WebMD Portland', 45.53677100, -122.70774100),
('Spokane, WA', 47.65354400, -122.70774100),
('Disney World', 28.40982500, -81.58848800)

-- Populate the Geog column based on
-- the Lat and Lon columns
UPDATE dbo.Points
SET Geog = Geography::Point(Lat, Lon, @SRID)
If you SELECT the Geog column, you'll see something like this:
Geog
----------------------------------------------
0xE6100000010C062D246074C74640807D74EACAB55EC0
Here are the points translated from the Geog column:
SELECT
PointName,
Geog.STAsText() AS Point
FROM dbo.Points

PointName Point
-------------- -----------------------------
Rob's Home POINT (-122.84051 45.558239)
WebMD Portland POINT (-122.707741 45.536771)
Spokane, WA POINT (-122.707741 47.653544)
Disney World POINT (-81.588488 28.409825)
Last, let's get the distance between my home and the other three points using this query:
SELECT
TravelPoints.PointName,
(MyHome.Geog.STDistance(TravelPoints.Geog)
* @MilesPerMeter) AS 'Distance from My Home'
FROM dbo.Points AS MyHome
CROSS JOIN dbo.Points AS TravelPoints
WHERE MyHome.PointName = 'Rob''s Home'
AND TravelPoints.PointName <> 'Rob''s Home'

PointName Distance from My Home
-------------- ---------------------
WebMD Portland 6.61091520932017
Spokane, WA 144.868086608637
Disney World 2533.12214098223
There is a lot more functionality available. This just scratches the surface.