sql查询问题

本文关键字：问题 查询 sql | 更新日期: 2023-09-27 18:11:06

我在SQL中有以下查询:

SELECT TOP 25 *, SQRT(POWER(Latitude - 51.4644, 2) + POWER(Longitude - 0.0988, 2)) * 62.1371192 AS DistanceFromAddress
FROM [Stops].[dbo].[Stops_edited_small]
WHERE ABS(Latitude - 51.4644) < 0.09 AND ABS(Longitude - 0.0988) < 0.09
ORDER BY DistanceFromAddress

我需要把它转换成sql

I have got this far so far:

 public List<Stops_edited_small> GetMembers(double curLatitude, double curLongitude, int number)
    {
     using (DataClasses1DataContext db = new DataClasses1DataContext())
        {
             int DistanceFromAddress;
            var members = (from member in db.Stops_edited_smalls
                          where Math.Sqrt(Math.Pow(Convert.ToDouble(member.Latitude) - curLatitude, 2) + Math.Pow(Convert.ToDouble(member.Longitude) - curLongitude, 2)) * 62.1371192 as DistanceFromAddress
                          where Math.Abs(Convert.ToDouble(member.Latitude) - curLatitude) < 0.05
                          && Math.Abs(Convert.ToDouble(member.Longitude) - curLongitude) < 0.05
                          select member).Take(25);
            return members.ToList();
        }
}

我失去了如何处理作为DistanceFromAddress部分，以及如何正确地将其集成到我的c#中?我想我的where从句也太多了。谢谢你的帮助。

编辑:包含整个类来显示返回类型(这是一个WCF服务)

sql查询问题

我不熟悉这个距离计算公式，但是我构建了一组类，您可以使用Haversine公式来计算距离。希望在LINQ查询中实现这一点更容易一些，因为它都被包装成两个类。

GeocodedPosition类:

public class GeocodedPosition
{
    private double lat;
    private double lon;
    public GeocodedPosition(double latitude, double longitude)
    {
        lat = latitude;
        lon = longitude;
    }
    public double Latitude
    {
        get
        {
            return lat;
        }
    }
    public double Longitude
    {
        get
        {
            return lon;
        }
    }
}

GeocodeCalculator类:

public class GeocodeCalculator
{
    private const int earthRadiusMiles = 3960;
    private const int earthRadiusKilometers = 6371;
    public enum DistanceType
    {
        Miles,
        Kilometers
    }
    /// <summary>
    /// Uses the Haversine formula to calculate the distance between two locations
    /// </summary>
    /// <param name="pos1"></param>
    /// <param name="pos2"></param>
    /// <param name="type"></param> 
    /// <returns></returns>
    public double Distance(GeocodedPosition PositionA, GeocodedPosition PositionB, DistanceType type)
    {
        double r = (type.Equals(DistanceType.Miles)) ? earthRadiusMiles : earthRadiusKilometers;
        double dLat = ToRadian(PositionB.Latitude - PositionA.Latitude);
        double dLon = ToRadian(PositionB.Longitude - PositionA.Longitude);
        double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) + Math.Cos(ToRadian(PositionA.Latitude)) * Math.Cos(ToRadian(PositionB.Latitude)) * Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
        double c = 2 * Math.Asin(Math.Min(1, Math.Sqrt(a)));
        double d = r * c;
        return d;
    }
    /// <summary>
    /// Convert to Radians
    /// </summary>
    /// <param name=”val”></param>
    /// <returns></returns>
    private double ToRadian(double val)
    {
        return (Math.PI / 180) * val;
    }
}

是这样的吗?

        var members = (from member in db.Stops_edited_smalls
                       where Math.Abs(Convert.ToDouble(member.Latitude) - curLatitude) < 0.05
                       && Math.Abs(Convert.ToDouble(member.Longitude) - curLongitude) < 0.05
                       select new { member, DistanceFromAddress = Math.Sqrt(Math.Pow(Convert.ToDouble(member.Latitude) - curLatitude, 2) + Math.Pow(Convert.ToDouble(member.Longitude) - curLongitude, 2)) * 62.1371192 }).Take(25);

解决方案是如此简单，我甚至没有考虑它!

 var members = (from member in db.Stops_edited_smalls
                          where Math.Abs(Convert.ToDouble(member.Latitude) - curLatitude) < 0.05
                          && Math.Abs(Convert.ToDouble(member.Longitude) - curLongitude) < 0.05
                          orderby (Math.Sqrt(Math.Pow(Convert.ToDouble(member.Latitude) - curLatitude, 2) + Math.Pow(Convert.ToDouble(member.Longitude) - curLongitude, 2)) * 62.1371192)
                          select member).Take(number);
            members.ToList();

刚刚添加了命令行

相关文章：