# Calculate closest distance with database • ### Question

• User-1660670502 posted

Hi

i will have a list of some places for example lets say all starbukcs coordinates in a city. i will store it in a dataase or xml

when user use my app i will get its coordinate with google map api

then finally i want to say the user you are here and the closest starbucks is here(show the points in map)

how should i do this easily?

in my opinion i can get its coordinates and match it with all the coordinates in databse and find the distance in meters then attach all the distances to an array then find the minimum one in that array

is there any easy way to find the closest point for my scenario?

thanks

Saturday, October 1, 2011 4:15 PM

• User-2064262021 posted

Hi,

-- Start of example:
CREATE TABLE CoffeeHouse (HouseName VARCHAR(MAX), HouseLat FLOAT(53), HouseLong FLOAT(53))
go
INSERT CoffeeHouse SELECT 'Starbucks', 34.770094, -92.337348
go
DECLARE @MyLat FLOAT(53) = 34.746665
DECLARE @MyLong FLOAT(53) = -92.400853
SELECT --MIN(
dbo.DistanceBetween(@MyLat, @MyLong, HouseLat, HouseLong) --)
FROM CoffeeHouse
WHERE HouseName =  'Starbucks'
-- End of example.

SQL Server's spatial data (geography type) offers a built-in function that appears to perform almost the same distance calculation (including a consideration of geodesics/great circles).  Here is a an example from http://msdn.microsoft.com/en-us/library/bb933808.aspx:

-- Start of STDistance (geography Data Type) example:
DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.STDistance(@h);
-- End of STDistance (geography Data Type) example.

Thanks,

Cathy Miller

Microsoft Online Community Support

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, October 12, 2011 10:55 AM

### All replies

• Sunday, October 2, 2011 10:36 AM
• User-1660670502 posted

thanks for the link. i also find many links which gives distance finder formula

but the main problem is how can i calculate each row in database

i mean there is 50 row in database

how can i use the function:

```CREATE FUNCTION [dbo].[DistanceBetween] (@Lat1 as real,
@Long1 as real, @Lat2 as real, @Long2 as real)
RETURNS real
AS
BEGIN

SET @dLat1InRad = @Lat1 * (PI()/180.0);
SET @dLong1InRad = @Long1 * (PI()/180.0);
SET @dLat2InRad = @Lat2 * (PI()/180.0);
SET @dLong2InRad = @Long2 * (PI()/180.0);

DECLARE @dLongitude as float(53);
DECLARE @dLatitude as float(53);
/* Intermediate result a. */
DECLARE @a as float(53);
SET @a = SQUARE (SIN (@dLatitude / 2.0)) + COS (@dLat1InRad)
* SQUARE(SIN (@dLongitude / 2.0));
/* Intermediate result c (great circle distance in Radians). */
DECLARE @c as real;
SET @c = 2.0 * ATN2 (SQRT (@a), SQRT (1.0 - @a));
/* SET kEarthRadius = 3956.0 miles */
SET @kEarthRadius = 6376.5;        /* kms */

DECLARE @dDistance as real;
SET @dDistance = @kEarthRadius * @c;
return (@dDistance);
END```

in this function takes 4 parameters. 2 parameters are constant(static,fixed) but the other 2 will come from database table (the rows of lattitude and longitude columns of table)

so should i use cursor in my query or is there any other way to do it?

Tuesday, October 4, 2011 8:12 AM
• User-2064262021 posted

Hi,

-- Start of example:
CREATE TABLE CoffeeHouse (HouseName VARCHAR(MAX), HouseLat FLOAT(53), HouseLong FLOAT(53))
go
INSERT CoffeeHouse SELECT 'Starbucks', 34.770094, -92.337348
go
DECLARE @MyLat FLOAT(53) = 34.746665
DECLARE @MyLong FLOAT(53) = -92.400853
SELECT --MIN(
dbo.DistanceBetween(@MyLat, @MyLong, HouseLat, HouseLong) --)
FROM CoffeeHouse
WHERE HouseName =  'Starbucks'
-- End of example.

SQL Server's spatial data (geography type) offers a built-in function that appears to perform almost the same distance calculation (including a consideration of geodesics/great circles).  Here is a an example from http://msdn.microsoft.com/en-us/library/bb933808.aspx:

-- Start of STDistance (geography Data Type) example:
DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.STDistance(@h);
-- End of STDistance (geography Data Type) example.

Thanks,

Cathy Miller

Microsoft Online Community Support

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, October 12, 2011 10:55 AM