好的,我解决了这个问题。欢迎评论并提出更好/更快的方法。经过一些研究,似乎没有内置的SQL Server功能可以完成此操作。因此,我采用了CLR集成。
有一堆“假设”,从数学角度来看解决方案并不完全准确。但我的目标是速度。所以,我做了我该做的事情。此外,在我们的数据库中,我们将GPS和路线存储为纬度/经度和字符串(这些字符串类似于多个Web服务返回的内容,只是一系列点)
仅通过查看我们拥有的数据-我们从未拥有单独的“腿”超过1/2-1英里。我估计误差最多为0.2英里。对于我们所做的事情(估计公路卡车的剩余驾驶距离/时间),这无关紧要。与地理类型相比,性能非常不错。但如果您对改进C#代码速度有建议,请提出..
public class Functions
{
[SqlFunction]
public static double? RemainingDistance(double lat, double lon, string path, double outOfRouteThreshhold)
{
var gpsPoint = new Point { Lat = lat, Lon = lon };
var closestPointIndex = 0;
double distance = 10000;
var pointArrayStr = path.Split(',');
if (pointArrayStr.Length < 2) return null;
for (var i = 0; i < pointArrayStr.Length; i++)
{
var latLonStr = pointArrayStr[i].Split(' ');
var currentDistance = DistanceSqrt(
gpsPoint,
new Point { Lat = double.Parse(latLonStr[1]), Lon = double.Parse(latLonStr[0]) });
if (currentDistance >= distance) continue;
distance = currentDistance;
closestPointIndex = i;
}
var closestPointStr = pointArrayStr[closestPointIndex].Split(' ');
var closestPoint = new Point { Lat = double.Parse(closestPointStr[1]), Lon = double.Parse(closestPointStr[0]) };
var distanceInMeters = DistanceMeters(gpsPoint, closestPoint);
if (distanceInMeters > outOfRouteThreshhold) return null;
if (closestPointIndex == pointArrayStr.Length - 1) return 0;
var strBuilder = new StringBuilder();
for (var i = closestPointIndex; i < pointArrayStr.Length; i++) strBuilder.Append(pointArrayStr[i] + ",");
strBuilder.Remove(strBuilder.Length - 1, 1);
try
{
var geoPath = SqlGeography.STGeomFromText(new SqlChars($"LINESTRING({strBuilder})"), 4326);
var dist = geoPath.STLength().Value;
return dist;
}
catch (Exception)
{
return -1;
}
}
private static double DistanceSqrt(Point pointA, Point pointB)
{
var d1 = pointA.Lat - pointB.Lat;
var d2 = pointA.Lon - pointB.Lon;
return Math.Sqrt(d1 * d1 + d2 * d2);
}
private static double DistanceMeters(Point pointA, Point pointB)
{
var e = Math.PI * pointA.Lat / 180;
var f = Math.PI * pointA.Lon / 180;
var g = Math.PI * pointB.Lat / 180;
var h = Math.PI * pointB.Lon / 180;
var i = Math.Cos(e) * Math.Cos(g) * Math.Cos(f) * Math.Cos(h)
+ Math.Cos(e) * Math.Sin(f) * Math.Cos(g) * Math.Sin(h)
+ Math.Sin(e) * Math.Sin(g);
var j = Math.Acos(i);
var k = 6371 * j;
return k * 1000;
}
private struct Point
{
public double Lat { get; set; }
public double Lon { get; set; }
}
}