quinta-feira, 28 de outubro de 2010

Função para Calcular a Distancia entre Duas Coordenadas no SQL Server (Transact-SQL)

                Em sistemas de logística temos sempre que calcular a distancia entre pontos e geralmente temos tabelas com endereço dos pontos ou as coordenadas do endereço, sempre que temos que saber a distancia usamos ferramentas de roteirização para calcular o trajeto e informar a distancia. Esse tipo de solução pode ter um custo alto de processamento, velocidade e financeiro já que algumas ferramentas cobram por acesso a sua base.
                Para evitar custos desnecessários podemos fazer este calculo no banco de dados, ao invés de usar a ferramenta de roteirização. A vantagem de usar esta abordagem é que mais rápido, podemos usar o comando order by (sem precisar recorrer a código para ordenar pelo mais próximo ou mais distante) e não tem custo, porem poderemos ter problemas se os pontos estiverem muito próximos já que o calculo será feito em linha reta sem levar em consideração o trajeto.
                Primeiro vamos criar a função para calcular a distancia entre dois pares de coordenadas, estes pares devem estar em graus decimais. Esta função é a mesma que eu criei no artigo Função em Delphi para Calcular a Distância Entre Dois Pares de Coordenadas (Latitude e Longitude) convertida nas funções do Transact-SQL.
                Segue o script para criar a função:
-- =============================================
-- Author:        Wagner Carmo da Silva
-- Create date:   20/10/2010 17:24
-- Description:   Calcula a distância em quilometros
-- =============================================
CREATE FUNCTION [fun_CalcDistancia] 
(
     @latIni float, -- Latitude do ponto inicial
     @lonIni float, -- Longitude do ponto inicial
     @latFim float,-- Latitude do ponto final
     @lonFim float -- Longitude do ponto final
)
RETURNS float
AS
BEGIN
     DECLARE @Result AS FLOAT
     DECLARE @arcoA AS FLOAT
     DECLARE @arcoB AS FLOAT
     DECLARE @arcoC AS FLOAT
     DECLARE @auxPI AS FLOAT

     SET @auxPi = Pi() / 180
     SET @arcoA = (@lonFim - @lonIni) * @auxPi
     SET @arcoB = (90 - @latFim) * @auxPi
     SET @arcoC = (90 - @latIni) * @auxPi
     SET @Result = Cos(@arcoB) * Cos(@arcoC) + Sin(@arcoB) * Sin(@arcoC) * Cos(@arcoA)
     SET @Result = (40030 * ((180 / Pi()) * Acos(@Result))) /360

     RETURN Round(@Result,2)
END
                Esta função retorna o valor em quilômetros arredondando duas casas, se precisar aumentar a precisão do calculo basta alterar este trecho do código RETURN Round(@Result,2) , número 2 é a quantidade de casas decimais depois da virgula basta aumentar este número para aumentar as casas, se quiser deixar com precisão total basta remover a função round (RETURN @Result).
                Agora vou mostrar um select que mostra uso dessa função :

SELECT
     cd_Empresa,
     nr_Latitude,
     nr_Longitude,
     fun_CalcDistancia(-23.479483, -46.749340, nr_Latitude, nr_Longitude) AS nr_Distancia
FROM tbl_Empresas
ORDER BY fun_CalcDistancia(-23.479483, -46.749340, nr_Latitude, nr_Latitude)

                Repare que eu usei a função até mesmo para ordenar o resultado, neste caso ele traz o mais próximo, para inverter isto basta usar o desc depois do order by.
                Importante é não esquecer que o calculo é em linha reta, isso deve ser usado quando os pontos tem uma boa distancia, ou seja, o caminho não vai interferir muito na distancia do ponto.

Referências:

8 comentários :

  1. Ow Wagner

    Valeu pela função. Vai me ajudar bastante.

    Agora só preciso transformá-la em C#.


    Rafael.

    ResponderExcluir
  2. Rafael,


    Acredito que você não terá problemas, mas se precisar de ajuda é só perguntar.

    ResponderExcluir
  3. renatopozzer@terra.com.br24 de janeiro de 2011 às 10:44

    Legal o post, o código com certeza será muito útil para muitos.
    Vou colaborar com uma outra maneira de fazer o mesmo cálculo...

    CREATE FUNCTION [dbo].[CALC_DIST](@LA1 float, @LO1 float, @LA2 Float, @LO2 Float)
    RETURNS float
    AS
    BEGIN
    Return(CAST(SQRT(((@LA1 - @LA2)*(@LA1 - @LA2) + (@LO1 - @LO2)*(@LO1 - @LO2)))*111.18 as numeric(9,3)))
    END

    ResponderExcluir
    Respostas
    1. Utilizado 111,18 que é a largura de um minuto ao outro, porém a Terra por ser redonda só pode ser atribuído na Linha do Equador, quando mais próximo dos trópicos esta diferença é menor, ou seja pode variar de 0 a 111,18.

      Excluir
  4. Renato,

    Muito legal valeu pela colaboração

    ResponderExcluir
  5. Formato C#

    public const double auxPi = 0.0174532925199433;
    public Double DistanciaEntreLatLog(double latIni, double latFim, double lonIni, double lonFim)
    {
    return Math.Round((40030 * ((180 / Math.PI) *
    Math.Acos(Math.Cos((90 - latFim) * auxPi) *
    Math.Cos((90 - latIni) * auxPi) +
    Math.Sin((90 - latFim) * auxPi) *
    Math.Sin((90 - latIni) * auxPi) *
    Math.Cos((lonFim - lonIni) * auxPi)))) / 360, 2);
    }

    ResponderExcluir
  6. Como eu faço para inserir essa funcao no phpmyadmin ? Esta dando alguns erros SQL...

    ResponderExcluir
    Respostas
    1. Seria legal você mencionar os erros (lembrando que essa função foi desenvolvida para o SQL Server)

      Excluir