|
Implementing a Store Finder |
In this section,we show how to implement a store-finder on your site, or what Microsoft
calls a Proximity Search.
For our first example, we'll use a list of IMAX theaters around the United States to display
the nearest locations within some specified radius to a particular ZIP code or city.
We have two tables. The first is a table of all IMAX locations with addresses
and ZIP codes. The second table is a list of all five-digit ZIP codes in the U.S.
(There are about 42,000 of them.) For each ZIP code we have a pair of earth
coordinates, latitude and longitude in degrees.
Our problem then becomes finding great-circle distances within a specified radius
from the user's ZIP code. Assume
that the earth is a sphere with constant radius over its entire surface. For
a sphere of radius R, and points 1 and 2 with lat-long pairs (lat1, lon1), (lat2,
lon2), the great circle distance d is
(1) d = R arccos ( A + B * C)
where
A = sin (lat1) * sin (lat2)
B = cos (lat1) * cos(lat2)
C = cos (lon2 - lon1)
where latitude and longitude are expressed in radians. Note that the C term
is an even function and depends only the difference in longitudes.
As a check, let's calculate the distance from Zipcode = 93110 and Zipcode 90034,
which are in Santa Barbara, CA and the Cheviot Hills neighborhood in Los Angeles.
The radius of the earth R is 3,951.13 miles. Consulting our ZIP code table
for the earth coordinates for the two ZIP codes, we get
93110: lat = 34.44187 deg N lon = 119.76794 deg W
90034: lat = 34.03112 deg N lon = 118.39047 deg W
A = 0.316517253
B = 0.68345705
C = 0.999712792
and arccos (A + B * C) = 0.02104993 rad which gives d = 83.17 miles
using the above value R for earth radius. The Yahoo! maps give a driving
distance between the same ZIP codes of 96.3 miles which is fairly close agreement.
The road path from Santa Barbara to West Los Angeles deviates markedly from a straight-line
path particularly due to the inclusion of a near right-angle turn from the Ventura
Freeway (US 101) and the San Diego Freeway (I-405).
The Great Circle distance is adequate if we keep in mind that the practical use
of a store finder is to identify business locations within an approximate radius.
Our stored procedure must (a) fetch the latitude and longitude for the user's ZIP
code
and (b) find values of expression (1) above that are less than the maximum distance
constraint.
In addition, we allow the user to specify a city and state. But our method
will use the first ZIP code it finds for this city in the ZIP code table.
For a physically large city like Los Angeles with many ZIP codes, this may not find
locations close to the user's neighborhood. But at least it's a start when
the ZIP code is unknown. And using a small suburb, e.g., Culver City, with
only four ZIP codes is nearly equivalent to specifying Culver City's central ZIP
code, 90230.
We implement our search with a stored procedure.
Click here
to see the procedure.
Input arguments are the user's ZIP code, city and state any of which can be blank.
However, the client software should force either the ZIP or the city and state fields
to have non-blank values. Otherwise the stored procedure will return an empty
set. A maximum number
of locations in the return data and a maximum distance are also specified.
These default to specific values. |
|
|