时间戳

首页 > 解决方案 > EF 表达式获取距离内的位置

c# - EF 表达式获取距离内的位置

问题描述

我正在尝试查询我的数据库以查找某个位置内的所有事件。我希望能够通过数据库查询这个，所以我不必提取所有事件，所以我尝试将一些代码转换为表达式。原始代码在这里找到：

public static Expression<Func<Location, bool>> GetWithinDistanceExpression(this Location current, double withinDistance, DistanceUnits units)
{
    //in the EQ converts something to meters
    double toMeters = 6376500;
    double toRadiants = 0.0174532925199433;
    double currentLat = current.Latitude * toRadiants;
    double currentLong = current.Longitude * toRadiants;
   
    return loc => 

        (2 * Math.Atan2(
                Math.Sqrt(
                    //TODO: Merge Expressions instead of copy pasta.
                    Math.Pow(Math.Sin((loc.Latitude - currentLat) / 2), 2) + Math.Cos(currentLat) * Math.Cos(loc.Latitude * toRadiants)
                        * Math.Pow(Math.Sin((loc.Longitude - currentLong) / 2), 2)
                ),
                Math.Sqrt(1 -
                    //TODO: Merge Expressions instead of copy pasta.
                    Math.Pow(Math.Sin((loc.Latitude - currentLat) / 2), 2) + Math.Cos(currentLat)
                        * Math.Cos(loc.Latitude * toRadiants) * Math.Pow(Math.Sin((loc.Longitude - currentLong) / 2), 2)
                )
            )
        )    
            * toMeters
            < withinDistance;
}

但是，当我在 Db 中查询位置内的东西时，这不会返回任何内容。我的猜测是这与铸造过程中的精度有关。

如何获取 2 个坐标的特定距离内的位置？

标签： c#sqlsql-servercoordinatesgeospatial

解决方案

    public static IQueryable<Location> GetLocationsWithinMeter(this DbSet<Location> locations, double lat, double longitude, double withinMeters)
    {
        //Use Interpolated to create parameterized Query
        return locations.FromSqlInterpolated(
            @$"
        SELECT * FROM(
            SELECT l.*, (
              6371392.896 * acos (
              cos ( radians({lat}) )
              * cos( radians( l.[Latitude] ) )
              * cos( radians( l.[Longitude] ) - radians({longitude}) )
              + sin ( radians({lat}) )
              * sin( radians( [Latitude] ) )
            )
        ) AS distance
        FROM Locations l) locInfo
        WHERE distance < {withinMeters}"
        );
    }

推荐阅读