计算经纬度的两点之间的距离

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {

            List<LocationData> data = new List<LocationData>();
            using (IDbConnection connection = new SqlConnection("Data Source=...;Initial Catalog=...;;User Id=sa;Password=...;"))
            {
                connection.Open();
                string sqltext = @" SELECT [ID]
                                          ,[LONGITUDE]
                                          ,[LATITUDE]  
                                          ,[X]
                                          ,[Y]
                                      FROM [WAP_CONFIG2].[dbo].[MOBILE_LOCATION]
                                      Where TIME Between @starttime and @endtime  and  USER_ID=2  Order by TIME asc";
                DateTime dastart = Convert.ToDateTime("2019-03-01 14:08:37.613");
                DateTime daend = Convert.ToDateTime("2019-03-01 14:10:37.200");
                var result = connection.Query<LocationData>(sqltext, new { starttime = dastart, endtime = daend });

                data = (result == null || result.Count() == 0) ? new List<LocationData>() : result.ToList();
            }

            double getinstance = 0.00;


            for (int i = 0; i < data.Count(); i++)
            {
                if (i < data.Count() - 1)
                {
                    double lng1 = Convert.ToDouble(data[i].LONGITUDE);
                    double lat1 = Convert.ToDouble(data[i].LATITUDE);
                    double lng2 = Convert.ToDouble(data[i + 1].LONGITUDE);
                    double lat2 = Convert.ToDouble(data[i + 1].LATITUDE);
                    getinstance += DistanceCount(lat1, lng1, lat2, lng2);
                }
            }
            Console.WriteLine(getinstance);

            Console.ReadKey();
        }
        public const double EARTH_RADIUS = 6378.137;
        public static double rad(double d)
        {
            return d * Math.PI / 180.00;
        }
        public static double DistanceCount(double lat1, double lng1, double lat2, double lng2)
        {

            double radLat1 = rad(lat1);
            double radLat2 = rad(lat2);
            double a = radLat1 - radLat2;
            double b = rad(lng1) - rad(lng2);

            double s = 2 * Math.Asin(Math.Sqrt(Math.Pow(Math.Sin(a / 2), 2))) + Math.Cos(radLat1) * Math.Cos(radLat2) * Math.Pow(Math.Sin(b / 2), 2);
            s = s * EARTH_RADIUS;
            s = Math.Round(s * 10000) / 10000;
            return s;

        }
    }

    internal class LocationData
    {
        public int ID { get; set; }
        public double LONGITUDE { get; set; }
        public double LATITUDE { get; set; }
        public double X { get; set; }
        public double Y { get; set; }
    }





}

 

 

 

/****** Object:  UserDefinedFunction [dbo].[fnQueryDistance]    Script Date: 2019/3/2 15:01:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnQueryDistance] 
    (
      @userId INT ,
      @startTime DATETIME ,
      @endTime DATETIME
    )
       RETURNS FLOAT
       AS
BEGIN

        /*
        [fnQueryDistance]和[fnQueryUserOdo],同样为计算里程的函数
        【差别，以米为单位】
        【fnQueryDistance计算值略大于fnQueryUserOdo
        【差异：两者结果在小数点后第二位开始数值不同，不影响整体的统计
        
        */
            --地图坐标范围

        DECLARE @maxLon DATETIME;
        DECLARE @maxLat DATETIME;
        DECLARE @minLon DATETIME;
        DECLARE @minLat DATETIME;

        SET @maxLon = 113.713401;        
        SET @minLon = 114.67351;

        SET @maxLat = 22.80835;
        SET @minLat = 22.833933;
         --如果任务没有结束获取当前时间为结束时间，时间范围不大于1天
        DECLARE @dayTimeEnd DATETIME;
        DECLARE @nowDate DATETIME;

        IF ( ISNULL(@endTime, 0) = 0 )
            BEGIN
                SET @nowDate = GETDATE();
                SET @dayTimeEnd = CONVERT(VARCHAR(10), @startTime, 120)
                    + ' 23:59:59';                
                IF ( CAST(@dayTimeEnd AS DATETIME) <= CAST(@nowDate AS DATETIME) )
                    SET @endTime = @dayTimeEnd;
                ELSE
                    SET @endTime = @nowDate;    
            END; 

       --游标循环的参数
        DECLARE @LngBegin REAL;
        DECLARE @LatBegin REAL;
        DECLARE @LngEnd REAL;
        DECLARE @LatEnd REAL;
       --距离(千米)
       DECLARE @Distance REAL;
       declare @DistanceResult real=0.0;
       declare @DistanceCount Real;
       DECLARE @EARTH_RADIUS  REAL;
       SET @EARTH_RADIUS = 6378.137 ;
       DECLARE @RadLatBegin  REAL,
               @RadLatEnd    REAL,
               @RadLatDiff   REAL,
               @RadLngDiff   REAL;

        DECLARE @gisdata_lon DECIMAL(18, 6);
        DECLARE @gisdata_lat DECIMAL(18, 6);
        DECLARE gisdata CURSOR
        FOR
            SELECT  LONGITUDE ,
                    LATITUDE
            FROM    WAP_CONFIG2.dbo.MOBILE_LOCATION
            WHERE   USER_ID = @userId
                    --AND LONGITUDE BETWEEN @minLon AND @maxLon
                    --AND LATITUDE BETWEEN @minLat AND @maxLat
                    AND TIME BETWEEN @startTime AND @endTime
            ORDER BY TIME ASC;
        OPEN gisdata; 
        FETCH NEXT FROM gisdata INTO @gisdata_lon, @gisdata_lat;
        WHILE ( @@FETCH_STATUS = 0 )
            BEGIN 
               set @LngBegin=@gisdata_lon;
               set @LatBegin=@gisdata_lat;
               FETCH NEXT FROM gisdata INTO @gisdata_lon, @gisdata_lat;
               set @LngEnd=@gisdata_lon;
               set @LatEnd=@gisdata_lat;
               SET @RadLatBegin = @LatBegin *PI()/ 180.0 ;
               SET @RadLatEnd = @LatEnd *PI()/ 180.0 ;
               SET @RadLatDiff = @RadLatBegin - @RadLatEnd ;
               SET @RadLngDiff = @LngBegin *PI()/ 180.0 - @LngEnd *PI()/ 180.0 ;
               SET @Distance = 2 *ASIN(SQRT(POWER(SIN(@RadLatDiff / 2), 2)+COS(@RadLatBegin)*COS(@RadLatEnd)*POWER(SIN(@RadLngDiff / 2), 2)));
               set @DistanceResult=@DistanceResult+(@Distance * @EARTH_RADIUS);                
            END;
        CLOSE gisdata;
        DEALLOCATE gisdata;
       RETURN cast((@DistanceResult*1000) as decimal(18,5));
END
GO