x

SQL Spatial Types - Distance between two locations in my database

I am creating a stored procedure that returns the distance between two locations.I am getting the latitude and longitude from the user, as well as the distance(5,10,15,or 20 miles. I cannot figure out how to compare the users entries to the data in my table, where I have a geography column that stores the latitude & longitude of my locations. I had it working but I found out that my distance formula is wrong, and I need to alter my stored proc.I tried to get help elsewhere but was told to try here, since it is a SQL question. Here is my stored proc.

CREATE PROCEDURE [dbo].[prcLocationListByDistance]
    (
        @latitude decimal(10, 7),
        @longitude decimal(10, 7), 
        @intRadiusMi int

    )
    As

SET NOCOUNT ON

DECLARE @startingPoint geography;
SET @startingPoint = geography::STPointFromText ('POINT(' + CAST(@latitude AS VARCHAR(20)) + ' ' + CAST(@longitude AS VARCHAR(20)) + ')', 4326)
DECLARE @endingPoint geography;
SET @endingPoint = geography::Point([dbo].HealthCenterAddresses.SpatialLocation.Lat, [dbo].HealthCenterAddresses.SpatialLocation.Long, 4326)

DECLARE @range as float
SET @range = @intRadiusMi * 1609.344

 SELECT
 l.LocationID,
 l.CenterName,
 l.TypeofCenter,
 l.Address,
 l.City,
 l.State,
 l.ZipCode,
 l.Website,
 l.PatientServices,
 l.SpatialLocation.Lat AS Lat,
 l.SpatialLocation.Long AS Long,
 l.SpatialLocation.STSrid AS SLID,
 l.SpatialLocation.STDistance(@startingPoint)
FROM 
 HealthCenterAddresses l
WHERE
 l.SpatialLocation.STDistance(@startingPoint) < (@intRadiusMi * 1609.344)



GO
more ▼

asked Mar 28, 2013 at 06:14 PM in Default

markfisher67 gravatar image

markfisher67
30 1 1 1

From what I can tell by looking over the procedure, it should work correctly. I am assuming the @endingPoint variable was declared to test, as the procedure isn't using it.

Can you post some test user input as well as values that should be pulling in, but aren't?
Mar 28, 2013 at 07:55 PM Beandon10
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I modified your query a little and used it with data from AdventureWorks and it seemed to work fine. I also changed it to use a buffer around a point and look for intersections of that and it seemed to work well too. Again, this is modified slightly from your code, but it might be useful to you:

DECLARE @latitude DECIMAL(10, 7),
 @longitude DECIMAL(10, 7),
 @intRadiusMi INT,
 @startingpoint GEOGRAPHY,
 @searcharea GEOGRAPHY

SET @latitude = -122.335726442416
SET @longitude = 47.7201372000862
SET @intRadiusMi = 2


SET @startingPoint = GEOGRAPHY::STPointFromText('POINT('
 + CAST(@latitude AS VARCHAR(20))
 + ' '
 + CAST(@longitude AS VARCHAR(20))
 + ')', 4326)
SET @searcharea = @startingpoint.STBuffer(@intRadiusMi * 1609.344)


SELECT l.AddressLine1,
 l.City,
 l.StateProvinceID,
 l.PostalCode,
 l.SpatialLocation.STDistance(@startingPoint)
FROM Person.Address AS l
WHERE l.SpatialLocation.STIntersects(@searcharea) = 1;
more ▼

answered Mar 29, 2013 at 10:17 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

Hey Grant, I looked over your query, and it looks fine. I'm going to run it and see what I get. After i made my post I actually searched around some more online and I found some other material. Thank you very much for the effort to help.
Mar 30, 2013 at 11:54 PM markfisher67
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x407

asked: Mar 28, 2013 at 06:14 PM

Seen: 888 times

Last Updated: Mar 31, 2013 at 03:26 AM