SQL Server 地理空间数据和C# - 其中之一是错误的！

发布于 2024-11-07 05:01:43 字数 1428 浏览 1 评论 0原文

所以这个问题的内容很少，但希望有人有足够的知识来提供帮助。

在我的 SQL Server 数据库中，我有 2 个位置记录，其属性为纬度(nLatitude)、经度(nlongtitue) 和geography(gGeoLocation) 值按该顺序排列。

记录 1：39.283638、-76.568567、0xE6100000010C91F3FE3F4EA443406EA5D766632453C0

记录 2：39.285200、-76.554366、0xE6100000010CDC68006F81A443 40EB0088BB7A2353C0

我使用下面的 SQL 查询创建了地理位置值：

UPDATE [Location] SET gGeoLocation = geography::STPointFromText('POINT(' + CAST(nlongtitue) AS VARCHAR(20)) + ' ' + CAST([nLatitude] AS VARCHAR(20)) + ')', 4326)

现在假设我要搜索位置表以确定哪些位置位于特定半径内特定纬度/经度 (39.290555,-76.609604) 我使用以下查询：

SELECT * FROM [Location] WHERE gGeoLocation.STDistance(geography::Point(@Latitude, @Longitude, 4326)) < ((@Miles * 1000) * 0.621371192)

然后我对照 C# 函数检查我的结果，两个结果不相同，所以其中一个肯定是错误的，或者我遗漏了一些东西。这是 C# 函数：

//calculate haversine distance for linear distance - Miles
public static double haversine_mi(double lat1, double long1, double lat2, double long2)
{
    double dlong = (long2 - long1) * d2r;
    double dlat = (lat2 - lat1) * d2r;
    double a = Math.Pow(Math.Sin(dlat / 2.0), 2) + Math.Cos(lat1 * d2r) * Math.Cos(lat2 * d2r) * Math.Pow(Math.Sin(dlong / 2.0), 2);
    double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
    double d = 3956 * c;

    return d;
}

最终，如果有人可以为我提供一个 SQL 查询，该查询将搜索特定半径内的位置表，并返回位置和搜索点之间的距离（以英里和公里为单位），那就太棒了。

但我也想了解为什么我在应该返回相同结果的情况下得到不同的结果。我对地理空间数据还很陌生。

原文

So this questions has few parts to it, but hopefully someone will have enough knowledge to help.

In my SQL Server Database I have 2 Location records with the following properties latitude(nLatitude), longitude(nlongtitue) & geography(gGeoLocation) values in that order.

Record 1: 39.283638, -76.568567, 0xE6100000010C91F3FE3F4EA443406EA5D766632453C0

Record 2: 39.285200, -76.554366, 0xE6100000010CDC68006F81A44340EB0088BB7A2353C0

I created the Geography values by using the SQL query below:

UPDATE [Location] SET gGeoLocation = geography::STPointFromText('POINT(' + CAST(nlongtitue AS VARCHAR(20)) + ' ' + CAST([nLatitude] AS VARCHAR(20)) + ')', 4326)

Now lets say I want to search my Location table to determine which of those locations are with in a certain radius of certain latitude/longitude (39.290555,-76.609604) I was using the following query:

SELECT * FROM [Location]
WHERE gGeoLocation.STDistance(geography::Point(@Latitude, @Longitude, 4326)) < ((@Miles * 1000) * 0.621371192)

Then I was checking my results against a C# function and two results are not the same, so one of these has to be wrong or I am missing something. Here is the C# function:

//calculate haversine distance for linear distance - Miles
public static double haversine_mi(double lat1, double long1, double lat2, double long2)
{
    double dlong = (long2 - long1) * d2r;
    double dlat = (lat2 - lat1) * d2r;
    double a = Math.Pow(Math.Sin(dlat / 2.0), 2) + Math.Cos(lat1 * d2r) * Math.Cos(lat2 * d2r) * Math.Pow(Math.Sin(dlong / 2.0), 2);
    double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
    double d = 3956 * c;

    return d;
}

Ultimately if some could provide me a SQL query that would search the location table within a certain radius and also return the distance between the location and the search point in miles and in km that would awesome.

But also I would like to understand why I am getting different results in what should return the same. I am pretty new to geo-spacial data.

分享到QQ

分享到微博