Как вычислить расстояние между 2 точками с координатами через Google Maps

Используем Google Maps Directions & Distance Matrix API. 

Это позволит нам измерить расстояние между точками, а также определять маршрут между 2 геоточками. 

Подключение к Google Map API

На аккаунте с ключем API должны быть включены Directions API и Distance Matrix API, а также обязательно подключен активный способ платежа (billing methods/accounts).

Directions API

Directions API принимает на вход две точки на карте (а также дополнительные параметры) и показывает маршрут, по которому можно добраться от одной точки до другой. Показывает время, расстояние, в каком направлении нужно двигаться.

Хранимая процедура Request

CREATE PROCEDURE [dbo].[api_googlemaps_directions_request]
    @parameters ExtendedDictionaryParameter READONLY,  -- входящие параметры для внутренней обработки (используйте Key, Value2)
    @username nvarchar(32)  -- текущий пользователь.
AS
BEGIN
    /*
    параметры
    формат: {тип} название описание
    типы:
    string - строка
    array - несколько строк, соединенные в одну через |
    bool - строка со значением true или false
    date - дата в виде количества секунд с 1970 года (unix timestamp). Можно получить с помощью DATEDIFF(SECOND,'1970-01-01', GETDATE())
    {string} origin *Начало пути. id google maps, адрес или широта/долгота. Последние через запятую, например 41.43206,-81.38992
    {string} destination *Назначение пути, аналогично
    {string} mode Способ передвижения. Варианты: driving, walking, bicycling, transit (публичный транспорт)
    {array} waypoints Массив точек назначения, в которых нужно остановиться по пути
    {bool} alternatives Если true, то может быть предложено несколько вариантов. Несовместимо с waypoints
    {array} avoid Исключает маршруты с определенными особенностями. В массив можно включить значения: tolls (платные проезды), highways (шоссе), ferries (паромы?), indoors (помещения)
    {string} language Язык, например, ru или en
    {string} units Единицы измерения metric для км или imperial для миль
    {string} region Регион. Нужен, если вместо координат передаем названия мест, т.к. бывают одинаковые названия городов в разных странах. Например, ru
    {date} arrival_time Желаемое время отправления. Доступно в зависимости от mode
    {date} departure_time Желаемое время отправления. Доступно в зависимости от mode
    {string} traffic_model Способ оценки загруженности трафика. best_guess - по умолчанию, также есть pessimistic и optimistic
    {array} transit_mode Способ передвижения на публичном транспорте. Можно включить в массив сразу несколько. bus, subway, train, tram, rail
    {array} transit_routing_preference Если передвигается публичным транспортом, то: less_walking - хочу меньше ходить пешком и fewer_transfers - хочу меньше пересадок
    */

    --список нужных параметров
    declare @url_param_keys table (name nvarchar(64))
    insert into @url_param_keys values
    ('origin'), ('destination'),
    ('mode'), ('waypoints'), ('alternatives'), ('avoid'),
    ('language'), ('units'), ('region'),
    ('arrival_time'), ('departure_time'), ('traffic_model'),
    ('transit_mode'), ('transit_routing_preference')

    declare @url_params table (name nvarchar(64), value nvarchar(4000))
    insert into @url_params (name, value) 
    select [Key], Value2 
    from @parameters p
    join @url_param_keys pk on pk.name=p.[Key]
    where Value2 is not null and Value2 <> ''
    union
    select 'key', 'api_key' --здесь нужно передать ключ api

    --тестовые параметры (нужно убрать после проверки)
    insert into @url_params values
    ('language', 'ru'), 
    ('origin', '55.39392641183048,86.0027973945145'), 
    ('destination', '55.34399641272105,86.00968945673426')

    declare @url nvarchar(64) = 'https://maps.googleapis.com/maps/api/directions/json'
    declare @query nvarchar(4000) = replace(
                                        STUFF((Select '^' + name + '=' + value
                                        from @url_params
                                        FOR XML PATH('')),1,1,''), 
                                        '^', '&'
                                    )

    select '' Msg, 1 Result, @url + '?' + @query Url
END

Пример хранимой процедуры Response

CREATE PROCEDURE [dbo].[api_googlemaps_directions_response]
    @response nvarchar(max),
    @parameters ExtendedDictionaryParameter READONLY,  -- входящие параметры для внутренней обработки (используйте Key, Value2 - те же что и на request)
    @username nvarchar(32)
AS
BEGIN
    declare @distance nvarchar(32) = json_value(@response, '$.routes[0].legs[0].distance.text')
    declare @duration nvarchar(32) = json_value(@response, '$.routes[0].legs[0].duration.text')
    declare @start_address nvarchar(1024) = json_value(@response, '$.routes[0].legs[0].start_address')
    declare @end_address nvarchar(1024) = json_value(@response, '$.routes[0].legs[0].end_address')

    --полное описание маршрута, по которому предлагается добираться до места назначения
    declare @steps table (
      distance nvarchar(32), --расстояние до следующей точки
      duration nvarchar(32), --время до следующей точки
      start_lat nvarchar(32), --широта отправной точки
      start_lng nvarchar(32), --долгота отправной точки
      end_lat nvarchar(32), --широта точки назначения
      end_lng nvarchar(32), --долгота точки назначения
      html_instructions nvarchar(1024), --инструкции вида "поверните направо на ул. такой-то" в формате html
      maneuver nvarchar(32), --та же инструкция, но в виде кода. Например, "turn-left" для поворота налево
      travel_mode nvarchar(32) --вид транспортировки. Например, 'DRIVING' для автотранспорта
    )
    insert into @steps (distance, duration, start_lat, start_lng, end_lat, end_lng, html_instructions, maneuver, travel_mode)

    select *
    from openjson(@response, '$.routes[0].legs[0].steps') 
    with (
      distance nvarchar(32) '$.distance',
      duration nvarchar(32) '$.duration',
      start_lat nvarchar(32) '$.start_location.lat',
      start_lng nvarchar(32) '$.start_location.lng',
      end_lat nvarchar(32) '$.end_location.lat',
      end_lng nvarchar(32) '$.end_location.lng',
      html_instructions nvarchar(1024) '$.html_instructions',
      maneuver nvarchar(32) '$.maneuver',
      travel_mode nvarchar(32) '$.travel_mode'
    )

    --для примера собираем html инструкции в одну строку
    declare @steps_html nvarchar(4000) = STUFF((Select '
' + html_instructions
                                        from @steps
                                        FOR XML PATH('')),1,1,'')

    -- SELECT 1
    --select '' Msg, 1 Result, @response Response
    select '' Msg, 1 Result, N'От ' + @start_address + N' до ' + @end_address + ': ' + @distance + ', ' + @duration + N'. Шаги: ' + @steps_html Response

    -- SELECT 2 Внешние действия
END

Distance Matrix API

Distance Matrix API принимает на вход несколько точек отправления и несколько точек назначения, и показывает расстояние и время путешествия от каждой из точек отправления до каждой из точек назначения.

Хранимая процедура Request

CREATE PROCEDURE [dbo].[api_googlemaps_distancematrix_request]
    @parameters ExtendedDictionaryParameter READONLY,  -- входящие параметры для внутренней обработки (используйте Key, Value2)
    @username nvarchar(32)  -- текущий пользователь.
AS
BEGIN
    /*
    параметры
    формат: {тип} название описание
    типы:
    string - строка
    array - несколько строк, соединенные в одну через |
    bool - строка со значением true или false
    date - дата в виде количества секунд с 1970 года (unix timestamp). Можно получить с помощью DATEDIFF(SECOND,'1970-01-01', GETDATE())
    {array} origins *Массив отправных точек. id google maps, адрес или широта/долгота. Последние через запятую, например 41.43206,-81.38992
    {array} destinations *Массив точек назначения, аналогично
    {string} mode Способ передвижения. Варианты: driving, walking, bicycling, transit (публичный транспорт)
    {string} language Язык, например, ru или en,
    {string} region Регион. Нужен, если вместо координат передаем названия мест, т.к. бывают одинаковые названия городов в разных странах. Например, ru
    {array} avoid Исключает маршруты с определенными особенностями. В массив можно включить значения: tolls (платные проезды), highways (шоссе), ferries (паромы?), indoors (помещения)
    {string} units Единицы измерения metric для км или imperial для миль
    {date} arrival_time Желаемое время отправления. Доступно в зависимости от mode
    {date} departure_time Желаемое время отправления. Доступно в зависимости от mode
    {string} traffic_model Способ оценки загруженности трафика. best_guess - по умолчанию, также есть pessimistic и optimistic
    {array} transit_mode Способ передвижения на публичном транспорте. Можно включить в массив сразу несколько. bus, subway, train, tram, rail
    {array} transit_routing_preference Если передвигаемся публичным транспортом, то: less_walking - хочу меньше ходить пешком и fewer_transfers - хочу меньше пересадок
   */

    --список нужных параметров
    declare @url_param_keys table (name nvarchar(64))
    insert into @url_param_keys values
    ('origins'), ('destinations'),
    ('mode'), ('language'), ('region'), ('avoid'), ('units'), 
    ('arrival_time'), ('departure_time'), ('traffic_model'),
    ('transit_mode'), ('transit_routing_preference')

    declare @url_params table (name nvarchar(64), value nvarchar(4000))
    insert into @url_params (name, value) 
    select [Key], Value2 
    from @parameters p
    join @url_param_keys pk on pk.name=p.[Key]
    where Value2 is not null and Value2 <> ''
    union
    select 'key', 'api_key' --здесь нужно передать ключ api

    --тестовые параметры (нужно убрать после проверки)
    insert into @url_params values
    ('language', 'ru'), 
    ('origins', '55.39392641183048,86.0027973945145|55.24778985467413,86.0843738182027'),
    ('destinations', '55.34399641272105,86.00968945673426|55.32665644500734,86.04187177511305')

    declare @url nvarchar(64) = 'https://maps.googleapis.com/maps/api/distancematrix/json'
    declare @query nvarchar(4000) = replace(
                                        STUFF((Select '^' + name + '=' + value
                                        from @url_params
                                        FOR XML PATH('')),1,1,''), 
                                        '^', '&'
                                    )

    select '' Msg, 1 Result, @url + '?' + @query Url
END

Пример хранимой процедуры Response

CREATE PROCEDURE [dbo].[api_googlemaps_distancematrix_response]
    @response nvarchar(max),
    @parameters ExtendedDictionaryParameter READONLY,  -- входящие параметры для внутренней обработки (используйте Key, Value2 - те же что и на request)
    @username nvarchar(32)
AS
BEGIN
    --собираем данные в таблицу
    declare @data table (
      origin nvarchar(1024), --отправление
      destination nvarchar(1024), --назначение
      distance nvarchar(32), --расстояние
      duration nvarchar(32) --время
    );

    --разбираем json
    --точки отправления, назначения, и данные о расстояниях лежат в разных массивах, которые нужно связывать через порядок элементов
    --пример json есть на https://developers.google.com/maps/documentation/distance-matrix/overview
    with origins (address, ord) as
    (
      select value as address, [key]+1 as ord
      from openjson(@response, '$.origin_addresses')
    ),
    destinations (address, ord) as
    (
      select value as address, [key]+1 as ord
      from openjson(@response, '$.destination_addresses')
    ),
    result (distance, duration, ord_origin, ord_destination) as
    (
      select 
        res.distance, res.duration, 
        ceiling(1.0*(row_number() over (order by (select null)))/(select count(*) from origins)) as ord_origin, 
        row_number() over (partition by rows.elements order by (select null)) as ord_destination
      from openjson(@response, '$.rows') with (elements nvarchar(max) '$.elements' as json) as rows
      cross apply openjson(rows.elements) with (
        duration nvarchar(64) '$.duration.text',
        distance nvarchar(64) '$.distance.text'
      ) as res
    )

    insert into @data (origin, destination, distance, duration)
    select origins.address, destinations.address, result.distance, result.duration
    from result
    join origins on origins.ord=result.ord_origin
    join destinations on destinations.ord=result.ord_destination

    --пример: собираем строку с результатом
    declare @res nvarchar(4000) = STUFF((Select '; ' + origin + ', ' + destination + ', ' + distance + ', ' + duration
                                            from @data
                                            FOR XML PATH('')),1,1,'')
 

    -- SELECT 1
    select '' Msg, 1 Result, @res Response

    -- SELECT 2 Внешние действия
END