Блог

Я новичок в функциях с табличным значением, и у меня возникают проблемы с запуском этой функции с нуля. Я пытаюсь создать функцию, которая принимает 3 параметра (@Lat decimal, @Long decimal, @RangeInMiles int). Что я хочу сделать, так это вернуть идентификаторы местоположений из таблицы местоположений, которые находятся в радиусе действия переданного параметра: @RangeInMiles.

Таблица местоположений включает следующие столбцы: loc_ID ИДЕНТИФИКАТОР PK (типичный адрес) loc_longitude loc_latity

Итак, идея состоит в том, чтобы заставить эту функцию использовать широту и длину, которые были переданы для сравнения с широтами и длинами в таблице Locations, и возвращать те идентификаторы, которые находятся в пределах переданного радиуса: @RangeInMiles.

Весь код, который у меня есть на данный момент, приведен ниже, но я зашел в тупик в предложении WHERE. Спасибо за помощь!

 CREATE FUNCTION dbo.GetAvailableSalesByDistance(@Lat decimal, @Long decimal, @RangeInMiles int)
RETURNS @LocationIds Table
(loc_ID int)
AS
BEGIN INSERT @LocationIds
SELECT Locations.loc_ID
FROM Locations
WHERE 
RETURN
END
  

Вы могли бы попробовать это, следуя Пифагору:

 CREATE FUNCTION dbo.GetAvailableSalesByDistance(@Lat decimal, @Long decimal, @RangeInMiles int)
RETURNS @LocationIds Table
(loc_ID int)
AS
BEGIN INSERT INTO@LocationIds
SELECT Locations.loc_ID
FROM Locations as l
WHERE (SQRT(SQUARE(l.loc_latitude-@Lat) SQUARE(l.loc_longitude-@Long))<=@RangeInMiles)
RETURN
END
  

Если вы хотите найти ложные данные, вы можете попробовать это утверждение:

 SELECT * FROM Locations
WHERE TRY_CONVERT(numeric, loc_latitude) IS NULL
OR TRY_CONVERT(numeric, loc_longitude) IS NULL
  

Редактировать:
Чтобы сделать это правильно, вы могли бы использовать haversine:

 CREATE FUNCTION dbo.GetAvailableSalesByDistance(@Lat decimal, @Long decimal, @RangeInMiles int)
    RETURNS @LocationIds Table
    (loc_ID int)
    AS
    BEGIN INSERT INTO@LocationIds
    SELECT Locations.loc_ID
    FROM Locations as l
    WHERE 
(2 * 3961 * asin(sqrt((sin(radians((l.loc_latitude - @Lat) / 2))) ^ 2   cos(radians(@Lat)) * cos(radians(l.loc_latitude)) * (sin(radians((l.loc_longitude - @Long) / 2))) ^ 2))) <=@RangeInMiles)
    RETURN
    END
  

Он не тестировался и получен из этого источника

Ваш оператор insert с предложением updated может быть:

 INSERT @LocationIds
        SELECT
            Locations.loc_ID 
        FROM
            Locations 
        WHERE
            SQRT(SQUARE(loc_longitude - @Long)   SQUARE(loc_latitude - @Lat)) <= @RangeInMiles
  

Это намного сложнее, чем то, что опубликовал @Nikolaus, широта и долгота находятся на сфере (Земля) и не выражаются в милях, поэтому тройная система Пифагора здесь неприменима. Следуя формуле, описанной здесь, мы получим следующую функцию в SQL Server для вычисления расстояния:

 CREATE FUNCTION dbo.spatialDistance(@lat1 DECIMAL, @lon1 DECIMAL, @lat2 DECIMAL, @lon2 DECIMAL)
RETURNS TABLE
AS
RETURN (
    SELECT 6371 * Calc2.c AS DistanceKm
         , 3958.8 * Calc2.c AS DistanceMi
      FROM (SELECT RADIANS(@lat1) AS Lat1
                 , RADIANS(@lat2) AS Lat2
                 , RADIANS(@lat2 - @lat1) AS dLat
                 , RADIANS(@lon2 - @lon1) AS dLon) conv
     CROSS
     APPLY (SELECT a = SQUARE(SIN(dLat/2.0))   SQUARE(SIN(dLon/2.0)) * COS(Lat1) * COS(Lat2)) Calc1
     CROSS
     APPLY (SELECT c = 2 * ATN2(SQRT(Calc1.a), SQRT(1 - Calc1.a))) AS Calc2
)
  

Затем вы могли бы написать желаемую функцию следующим образом:

 CREATE FUNCTION dbo.GetAvailableSalesByDistance(@Lat decimal, @Long decimal, @RangeInMiles int)
RETURNS Table
AS
RETURN (
    SELECT l.loc_id
      FROM Locations AS l
     CROSS
     APPLY dbo.spatialDistance(l.loc_latitude, l.loc_longitude, @Lat, @Long) dist
     WHERE dist.DistanceMi <= @RangeInMiles
)  
  

Обратите внимание, что я использую встроенные табличные функции. Скалярные функции и TVF с несколькими операторами, подобные тем, которые вы использовали в своем вопросе, ужасны для производительности и не должны использоваться. Смотрите здесь объяснение, если вам интересно.