Jaylen Jaylen - 1 month ago 14
SQL Question

How can I query SQL Server database for the nearest store in a visitor's neighborhood?

I have a list of stores each store has data like street_address, longitude, latitude.

Now, when a user visits my site, I want to show him/her all available stores in their neighborhood (within 10 miles radios "closest to furthest")

Here is what I have done. I create a new column called

StPoint
with the type
geography
. I used the
geography::Point(latitude, longitude, 4326)
function to convert the longitude/latitude of each store to a point like so

UPDATE stores
SET StPoint = CASE WHEN latitude IS NOT NULL AND longitude IS NOT NULL
THEN geography::Point(latitude, longitude, 4326)
ELSE NULL END


I tried to find the distance between the visitor's current point and all the stores like so

SELECT
CASE WHEN StPoint IS NOT NULL THEN StPoint.STIntersection( geography::Point(42.2974416, -71.4478897, 4326) ).STAsText() ELSE NULL END AS distance
FROM stores


But this is giving me something like


GEOMETRYCOLLECTION EMPTY


or something like this


POINT (-99.4478897 56.2974416)


Finally, I tried using
STDistance
function which is returning a value (I am not sure what unit is used here)

SELECT StPoint.STDistance(geography::Point(42.2974416, -71.4478897, 4326))
FROM stores


Here are sample of what the above query shows

27376.5070395886
3973.49722397033
27620.9636451663
50346.8940144233
7425.98548351235
34193.3513625182


As you can see, the results is not in miles. How can I correctly get the closest store along with how far form the visitor each store is?

Answer

It looks like STDistance returns values in Geog unit. to convert it to Miles you'll need to divide vy 1609.344 (1 mile = 1609.344 meters)

SQL Server 2008 GEOGRAPHY STDistance() value

Try the following

SELECT StPoint.STDistance(geography::Point(42.2974416, -71.4478897, 4326))/1609.344
FROM stores
Comments