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

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.