locked
Calculate closest distance with database RRS feed

  • 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

Answers

  • 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 Anonymous 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
    
    DECLARE @dLat1InRad as float(53);
    SET @dLat1InRad = @Lat1 * (PI()/180.0);
    DECLARE @dLong1InRad as float(53);
    SET @dLong1InRad = @Long1 * (PI()/180.0);
    DECLARE @dLat2InRad as float(53);
    SET @dLat2InRad = @Lat2 * (PI()/180.0);
    DECLARE @dLong2InRad as float(53);
    SET @dLong2InRad = @Long2 * (PI()/180.0);
    
    DECLARE @dLongitude as float(53);
    SET @dLongitude = @dLong2InRad - @dLong1InRad;
    DECLARE @dLatitude as float(53);
    SET @dLatitude = @dLat2InRad - @dLat1InRad;
    /* Intermediate result a. */
    DECLARE @a as float(53);
    SET @a = SQUARE (SIN (@dLatitude / 2.0)) + COS (@dLat1InRad) 
                     * COS (@dLat2InRad) 
                     * 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));
    DECLARE @kEarthRadius as real;
    /* 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 Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 12, 2011 10:55 AM