使用LINQ从数据库中查找附近的地方 [英] Using LINQ find nearby places from database

问题描述

我们希望使用ASP.NET 2012中的LINQ从数据库中接收附近地点的列表，并希望对我们的策略有所反馈.

We want to receive list of nearby places from database using LINQ in ASP.NET 2012 and would like some feedback on our strategy.

我的表格和假数据:

     PlaceId    Name       Latitude   Longitude 
       1          A          18.1        20.1
       2          B          18.2        20.2
       3          C          18.3        20.3

1)在我们的项目中，将客户当前位置(经度和纬度)作为输入

1) In our project the client current location (latitude and longitude) is taken as input

2)在服务器端，根据客户端当前位置，我们需要使用LINQ从数据库中找到附近的地方

2) At server side ,depending upon the client current location, we need to find nearby places from the database using LINQ

这是我先前使用的SQL代码，但是现在我们要使用LINQ.

Here's the code for SQL which I earlier used , but now we want to use LINQ.

SELECT  name, Latitude, Longitude , 
  ( 3959 * acos( cos( radians(?) )* cos( radians( Latitude) ) * cos( radians( Longitude ) - radians(?) ) 
 + sin( radians(?) ) * sin( radians( Latitude) ) ) ) AS distance 
FROM TABLE_NAME 
HAVING distance < ? 
ORDER BY distance LIMIT 0 , 20

[但是问题是如何在LINQ中编写这样的查询.]

我在这方面的工作

在寻找解决方案时，我遇到了这段代码

While searching for the solution, I came across this code

        var Value1 = 57.2957795130823D;
        var Value2 = 3958.75586574D;

        var searchWithin = 20;

    double latitude = ConversionHelper.SafeConvertToDoubleCultureInd(Latitude, 0),
          longitude = ConversionHelper.SafeConvertToDoubleCultureInd(Longitude, 0);

    var location = (from l in sdbml.Places
                    let temp = Math.Sin(Convert.ToDouble(l.Latitude) / Value1) *  Math.Sin(Convert.ToDouble(latitude) / Value1) +
                             Math.Cos(Convert.ToDouble(l.Latitude) / Value1) *
                             Math.Cos(Convert.ToDouble(latitude) / Value1) *
                             Math.Cos((Convert.ToDouble(longitude) / Value1) - (Convert.ToDouble(l.Longitude) / Value1))
                         let calMiles = (Value2 * Math.Acos(temp > 1 ? 1 : (temp < -1 ? -1 : temp)))
                         where (l.Latitude > 0 && l.Longitude > 0)
                         orderby calMiles
                        select new location
                             {
                                    Name = l.name
                                });
                        return location .ToList();

但是问题是，如何引用ConversionHelper或它位于哪个命名空间下.

But the problem is ,how to reference ConversionHelper or under which namespace it comes.

感谢所有建议.

推荐答案

以下是我最终必须解决的代码

1)创建一个类，例如

DistanceModel.cs

DistanceModel.cs

public class DistanceModel
{
   public int PlaceId { get; set; }

   public string Name { get; set; }

   public double Latitute { get; set; }

   public double Longitude { get; set; }

} 

2)然后在想要的任何文件中包含以下代码，例如

MainPage.cs

MainPage.cs

     /*Call GetAllNearestFamousPlaces() method to get list of nearby places depending 
      upon user current location.
      Note: GetAllNearestFamousPlaces() method takes 2 parameters as input
     that is GetAllNearestFamousPlaces(user_current_Latitude,user_current_Longitude) */


   public void GetAllNearestFamousPlaces(double currentLatitude,double currentLongitude)
    {
        List<DistanceModel> Caldistance = new List<DistanceModel>();
        var query = (from c in sdbml.Places
                     select c).ToList();
        foreach (var place in query)
        {
            double distance = Distance(currentLatitude, currentLongitude, place.Latitude, place.Logitude);
            if (distance < 25)          //nearbyplaces which are within 25 kms 
            {
                DistanceModel dist = new DistanceModel();
                dist.Name = place.PlaceName;
                dist.Latitute = place.Latitude;
                dist.Longitude = place.Logitude;
                dist.PlaceId = place.PlaceId;
                Caldistance.Add(getDiff);
            }
        }                      
    }

   private double Distance(double lat1, double lon1, double lat2, double lon2)
    {
        double theta = lon1 - lon2;
        double dist = Math.Sin(deg2rad(lat1)) * Math.Sin(deg2rad(lat2)) + Math.Cos(deg2rad(lat1)) * Math.Cos(deg2rad(lat2)) * Math.Cos(deg2rad(theta));
        dist = Math.Acos(dist);
        dist = rad2deg(dist);
        dist = (dist * 60 * 1.1515) / 0.6213711922;          //miles to kms
        return (dist);
    }

   private double deg2rad(double deg)
    {
        return (deg * Math.PI / 180.0);
    }

   private double rad2deg(double rad)
    {
        return (rad * 180.0 / Math.PI);
    }

它对我有用，希望对您有帮助.

这篇关于使用LINQ从数据库中查找附近的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！