讲述如何使用SQL CLR表值函数进行扩展
2007-05-17 09:36:00 来源:WEB开发网核心提示: GetProximity 使用城市名和两位数的州代码来代表初始位置,它返回实体数,讲述如何使用SQL CLR表值函数进行扩展(9),和要搜索的实体类型名,它会搜索 n 个最近的实体,我找到了初始位置和找到的实体之间的行车路线,返回值是封装有行车路线的一组 Route 对象,其中,n 由计算
GetProximity 使用城市名和两位数的州代码来代表初始位置。它返回实体数,和要搜索的实体类型名。它会搜索 n 个最近的实体,其中,n 由计算参数指定,实体类型由 entityTypeName 参数指定。它会返回一个表和一幅地图(二进制文件图像),表中包含名称列、地址列,地图中包含每个实体的路线。
C# 方法签名如下所示:
public static IEnumerable
InitMap(string city, string state, int count,
string entityTypeName)
public static void FillRow(Object obj,
out SqlChars name, out SqlChars
address, out SqlBinary map)
请注意,nvarchar Transact-SQL 数据类型映射到 SqlChars .NET Framework 数据类型,而 varbinary Transact-SQL 数据类型映射到 SqlBinary .NET Framework 数据类型。有关数据类型之间的映射的完整列表,请参阅 System.Data.SqlTypes 命名空间的文档。
在 InitMap 方法中,我将城市和州转换成经度和纬度。接着,我找到了与此坐标接近的所有实体。最后,我找到了初始位置和找到的实体之间的行车路线。返回值是封装有行车路线的一组 Route 对象。
public static IEnumerable
InitMap(string city, string state,
int count, string entityTypeName)
{
FindServiceSoap find = new FindServiceSoap();
find.PreAuthenticate = true;
find.Credentials = new NetworkCredential(username, passwd);
// 对初始城市和州进行地理编码(Geocode)
FindAddressSpecification findSpec
= new FindAddressSpecification();
Address findAddr = new Address();
findAddr.CountryRegion = "US";
findAddr.Subdivision = state;
findAddr.PrimaryCity = city;
findSpec.InputAddress = findAddr;
findSpec.DataSourceName = "MapPoint.NA";
findSpec.Options = new FindOptions();
findSpec.Options.ThresholdScore = 0.45;
FindResults results = find.FindAddress(findSpec);
if (results.NumberFound > 0)
{
// 如果城市和州已经存在,则获取经度和纬度
Location startLocation = results.Results[0].FoundLocation;
LatLong startPoint = startLocation.LatLong;
// 查找附近的实体
FindNearbySpecification findNearby = new
FindNearbySpecification();
FindFilter filter = new FindFilter();
filter.EntityTypeName = entityTypeName;
findNearby.Filter = filter;
FindOptions options = new FindOptions();
options.Range = new FindRange();
// 设置计数限制
options.Range.Count = count;
findNearby.Options = options;
findNearby.DataSourceName = "NavTech.NA";
findNearby.LatLong = startPoint;
findNearby.Distance = 10.0;
results = find.FindNearby(findNearby);
Route[] routes = new Route[results.Results.Length];
RouteServiceSoap routeService = new RouteServiceSoap();
routeService.PreAuthenticate = true;
routeService.Credentials = new NetworkCredential(username,passwd);
RouteSpecification spec = new RouteSpecification();
spec.DataSourceName = "MapPoint.NA";
// 创建到每个实体的路线
spec.Segments = new SegmentSpecification[2];
spec.Segments[0] = new SegmentSpecification();
spec.Segments[0].Waypoint = new Waypoint();
spec.Segments[0].Waypoint.Location = startLocation;
spec.Segments[0].Waypoint.Name = "start";
for (int x = 0; x < results.Results.Length; x++)
{
spec.Segments[1] = new SegmentSpecification();
spec.Segments[1].Waypoint = new Waypoint();
spec.Segments[1].Waypoint.Location =
results.Results[x].FoundLocation;
spec.Segments[1].Waypoint.Name = "end";
routes[x] = routeService.CalculateRoute(spec);
}
return routes;
}
return null;
}
更多精彩
赞助商链接