Joshua Arnold

The latest direction in our Social Networking Platform has focused my attention on the latest fad in web technology: location-based services. I’ve stumbled upon a couple of functions and stored procedures for SQL Server 2005 that can be extremely useful in accomplishing this that I’d like to share.

1. Find items within n miles of the current location

• Solution Overview:

Given some GeoCoordinate c,
calculate some GeoCoordinate min and some GeoCoordinate max
such that min and max represent points along a rectangle with a center coordinate of c.

• SQL Server Implementation:

1. Calculate the distance of some item and the current location

• Solution Overview:

Given some GeoCoordinate c1 and some GeoCoordinate c2,
calculate the distance d, in miles, between c1 and c2.

• SQL Server Implementation:

3. Example Use

Consider the following sample population in some table GeoItem:

1    Yaupon Creek    30.363530    -97.968620
2    Windy Point    30.415200    -97.900840
3    Winns Heliport    30.401310    -97.960290
4    West Ridge Middle School    30.303820    -97.891400
5    White Rock Cemetery    30.309930    -97.962510
6    Wild Basin Wilderness Park    30.309930    -97.821670
7    Renaissance Austin Hotel    30.393300    -97.749600
8    Zachary T and Sallie Lee Scott Senior House    30.302710    -97.779450
9    West Bull Creek    30.359370    -97.787790
10    West Bull Creek Preserve    30.381310    -97.802510

The following query can be used to retrieve nearby items:

DECLARE @CurrentLatitude NUMERIC(18, 6), @CurrentLongitude NUMERIC(18, 6),
@MinLat NUMERIC(18, 6), @MinLon NUMERIC(18, 6), @MaxLat NUMERIC(18, 6),
@MaxLon NUMERIC(18, 6), @Distance NUMERIC(18, 6);
SET @CurrentLatitude = 30.372110
SET @CurrentLongitude = -97.878540
SET @Distance = 15.0; -- 15 miles

EXEC dbo.CalculateGeoExtremas@MinLat = @MinLat OUTPUT, @MinLon = @MinLon OUTPUT,
@MaxLat = @MaxLat OUTPUT, @MaxLon = @MaxLon OUTPUT, @Latitude = @CurrentLatitude,
@Longitude = @CurrentLongitude, @Distance = @Distance;

SELECT @MinLat, @MinLon, @MaxLat, @MaxLon

SELECT g.geoItemId, g.itemName, dbo.CalculateDistance(@CurrentLatitude, @CurrentLongitude,
g.latitude, g.longitude) AS distance
FROM   GeoItem AS g
WHERE  (g.latitude BETWEEN @MinLat AND @MaxLat) AND g.longitude BETWEEN @MinLon AND @MaxLon
ORDER BY distance DESC

which yields:

14    William Braxton Barr House    13.837094
15    Winn Elementary School    13.276661
22    Wheless Lane Church of Christ    12.589629
13    Woodcliff Baptist Church    12.517584
29    Springhill Suites By Marriott Austi)n North    12.317296
21    Westview Middle School    11.683721
27    Esd Austin-North Central    11.041094
20    Young Chapel African Methodist Episcopal Chur)ch    10.991294
19    Hampton Inn Austin North    10.880793
18    Embassy Suites Hotel Austin-North    10.865407
17    La Quinta Inn Austin Highland Mall    10.827897
16    North Austin Plaza Hotel and Suites    10.742710
36    Best Western Atrium North    10.642601
25    Habitat Suites    10.460957
46    Seton Medical Center H-4 Heliport    9.172898
42    The Specialty Hospital of Austin    9.072573
45    Seiders Spring    9.067145
30    Fairfield Inn And Suites By Marriott Austin Northwest    9.013800
41    The Brown School    8.732896
32    Casa Lago Resort    8.709709
24    Northcross Suites Austin Nw    8.612063
26    Esa Austin-Arboretum    8.548290
37    Towneplace Suites By Marriott Austin Northwest)    8.450223
40    Texas Military Forces Museum    8.250134
35    Courtyard by Marriott Austin Arboretum    8.200893
12    Westminster Presbyterian Church    8.161520
7    Renaissance Austin Hotel    7.818169
43    Spicewood Spring    7.798504
8    Zachary T and Sallie Lee Scott Senior House    7.604458
44    Steck Valley Park    7.229184
11    West Lake Hills    6.857748
23    Red Bud    6.610425
5    White Rock Cemetery    6.593043
9    West Bull Creek    5.477373
6    Wild Basin Wilderness Park    5.469529
1    Yaupon Creek    5.398852
3    Winns Heliport    5.269975
4    West Ridge Middle School    4.776967
33    Courtyard by Marriott Downtown Conven)tion Center    4.752998
34    Courtyard by Marriott Austin Airport    4.752998
10    West Bull Creek Preserve    4.573183
39    KUT-FM    4.051770
28    Vintage Villas    3.828173
38    Ossiport Heliport    3.392164
2    Windy Point    3.258134

---

It’s fairly straight forward and very easy to do. Check back for updates as I continue working on this project. I’ll be posting code examples of WCF services and AJAX implementations for dealing with this data and plotting it using the Google Maps API.

If you’d like to play with the data and the code mentioned in this post, you can Download the Database Script Here.