在 ASP.NET Core 2 和 Entity Framework Core 2 中的表中查询附近的位置 [英] Query nearby locations in table in ASP.NET Core 2 and Entity Framework Core 2

问题描述

我正在尝试进行查询，我可以在其中找到我的 SQL 数据库中给定经度和纬度点范围内(比如 50 英里)的所有位置.

I'm trying to make a query where I can find all locations in my SQL database within (lets say 50 miles) of a given Longitude and Latitude points.

我填充了一些帖子，在帖子表中，我有两列在创建帖子时存储经度和纬度.这是表格的外观:

I populated some posts, and in the Post table I have two columns where I store Longitude and Latitude when creating a post. Here is how the table looks:

我浏览了许多关于如何尝试实现这一点的示例，但似乎它们不起作用.我从研究中知道 ASP.NET Core 不支持 DbGeography，其他文章只是过时了.

I have browsed many examples of how to try to implement this, but it seems like they just don't work. I know from researching that ASP.NET Core doesn't support DbGeography, and other articles are just outdated.

发现这个 文章，但它使用 DbGeography，所以这不起作用.

Found this article, but it uses DbGeography, so that doesn't work.

在 GitHub 上找到了这个:这里我只是不知道这是否可行.

Found this on GitHub: here I just don't know if that would work though.

仅供参考，这是我的 Post 模型的外观:

Just for reference, this is how my Post model looks:

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }

    public double Lat { get; set; }
    public double Lng { get; set; }

    public DateTime Created { get; set; }
    public Category Category { get; set; }
    public int CategoryId { get; set; }

    // Other navigation properties...
}

这就是我现在进行查询以在前端的 Google 地图上显示点的方式:

And this is how I do my query as of right now to display points on my Google Maps in the frontend:

public class HomeController : Controller
{
    private readonly ApplicationDbContext _context;

    public HomeController(ApplicationDbContext context)
    {
        _context = context;
    }


    [HttpGet]
    public JsonResult GetGalleryLocations()
    {

        var data = _context.Posts
            .Include(c => c.Category)            
            .ToList();

        return Json(data);
    }       
}

有人知道如何实现这样的查询吗?

Does anyone have any insight on how to implement a query like this?

推荐答案

如果您从数据库中提取适合您所需距离的圆周围的正方形的数据，然后在客户端微调到恰好圈子里的帖子?

How about if you extract the data from the database that fits in the square around your circle of desired distance, and then fine tune on the client side to just the posts in the circle?

给定您的初始数据

var myLat = 25.05;
var myLon = -80.3;
var radiusInMile = 50;

您可以计算包含该圆的正方形的边界

You can compute the bounds of the square containing that circle

var minMilePerLat = 68.703;
var milePerLon = Math.Cos(myLat) * 69.172;
var minLat = myLat - radiusInMile / minMilePerLat;
var maxLat = myLat + radiusInMile / minMilePerLat;
var minLon = myLon - radiusInMile / milePerLon;
var maxLon = myLon + radiusInMile / milePerLon;

然后你可以在数据库中查询广场内的那些帖子，把它们带到客户端，只保留圆圈内的那些

Then you can query the database for those posts inside the square, bring them to the client and keep just the ones in the circle

var data = _context.Posts
                   .Where(p => (minLat <= p.Lat && p.Lat <= maxLat) && (minLon <= p.Lng && p.Lng <= maxLon))
                   .AsEnumerable()
                   .Select(p => new { p, Dist = distanceInMiles(myLon, myLat, p.Lng, p.Lat) })
                   .Where(p => p.Dist <= radiusInMile);

其中distanceInMiles函数定义为

public double ToRadians(double degrees) => degrees * Math.PI / 180.0;
public double distanceInMiles(double lon1d, double lat1d, double lon2d, double lat2d) {
    var lon1 = ToRadians(lon1d);
    var lat1 = ToRadians(lat1d);
    var lon2 = ToRadians(lon2d);
    var lat2 = ToRadians(lat2d);

    var deltaLon = lon2 - lon1;
    var c = Math.Acos(Math.Sin(lat1) * Math.Sin(lat2) + Math.Cos(lat1) * Math.Cos(lat2) * Math.Cos(deltaLon));
    var earthRadius = 3958.76;
    var distInMiles = earthRadius * c;

    return distInMiles;
}

我使用余弦的球面定律来计算距离，我认为这足以解决这个问题.如果您需要更高的准确性，您可以将公式升级为更复杂的公式，例如haversine 或 Vincenty.

I am using the spherical law of cosines to compute the distance, which I assume is enough for this problem. If you need more accuracy, you could upgrade the formula to something more complicated like haversine or Vincenty.

这篇关于在 ASP.NET Core 2 和 Entity Framework Core 2 中的表中查询附近的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！