在ASP.NET Core 2和Entity Framework Core 2中查询表中附近的位置 [英] Query nearby locations in table in ASP.NET Core 2 and Entity Framework Core 2
问题描述
我正在尝试进行查询，以便可以在给定的经度和纬度点(例如50英里)内找到SQL数据库中的所有位置.
我填充了一些帖子，在帖子"表中有两列，用于在创建帖子时存储经度和纬度.表格外观如下:
我浏览了许多有关如何尝试实现此示例的示例，但似乎它们根本行不通.通过研究，我知道ASP.NET Core不支持DbGeography，而其他文章已经过时了.
找到了这个文章，但它使用DbGeography，因此无法正常工作.
在GitHub上找到了它:在这里我只是不知道那是否行得通./p>
仅供参考，这是我的Post模型的外观:
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地图上显示点:
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);
}
}
有人对如何实现这样的查询有任何见识吗?
如果从数据库中提取所需距离圆的正方形内的数据，然后在客户端微调到在圈子中发帖了吗?
提供您的初始数据
var myLat = 25.05;
var myLon = -80.3;
var radiusInMile = 50;
您可以计算包含该圆的正方形的边界
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;
然后，您可以在数据库中查询广场内的那些帖子，将其带给客户并仅将其保留在圆圈中
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'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:
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.
Found this article, but it uses DbGeography, so that doesn't work.
Found this on GitHub: here I just don't know if that would work though.
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...
}
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?
Given your initial data
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);
Where the distanceInMiles function is defined as
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;
}
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屋！
