更新：找到了谷歌距离矩阵并尝试相应地修改我的代码。我在这里收到无效参数错误：
return new GeoLocation(dstnc, uri.ToString());
}
catch
{
return new GeoLocation(0.0, "https://");
}
基本上，我需要从两个已知的纬度/经度获得行驶距离。我正在使用 SSIS 包，并且在网上找到了一个很棒的教程，它非常接近生成我需要的结果。
教程：http://www.sqlmusings.com/2011/03/25/geocode-locations-using-google-maps-v3-api-and-ssis/ http://www.sqlmusings.com/2011/03/25/geocode-locations-using-google-maps-v3-api-and-ssis/
他们所做的是将已知的街道地址传递给 Google，并从返回的 XML 中读取纬度/经度。
我需要做的不同是传递两个已知的纬度/经度并读取返回的距离。
例子：
他们使用 C#，我不太擅长，不知道如何进行修改。无论如何，我尝试了一下，这就是我的想法：
using System;
using System.Collections.Generic;
using System.Text;
//added these
using System.Data;
using System.Net;
using System.Web;
using System.Xml;
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
// ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
// TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
// PARTICULAR PURPOSE.
namespace sqlmusings
{
public interface IGeoLocation
{
string geocodeurl { get; set; }
float distance { get; set; }
}
public struct GeoLocation : IGeoLocation
{
private string _geocodeurl;
private Double _distance;
public GeoLocation(string geocodeurl, Double distance)
{
_geocodeurl = geocodeurl;
_distance = distance;
}
public string geocodeurl
{
get { return _geocodeurl; }
set { _geocodeurl = value; }
}
public Double distance
{
get { return _distance; }
set { _distance = value; }
}
}
public class GeoCode
{
const string _googleUri = "https://maps.googleapis.com/maps/api/distancematrix/xml?origins=";
//sample
//https://maps.googleapis.com/maps/api/distancematrix/xml?origins=32.1576780,-82.9070920&destinations=27.6536997,-81.5158944&mode=driving&units=imperial&sensor=false
private static Uri GetGeoCodeURI(string origins)
{
origins = HttpUtility.UrlEncode(origins);
string uri = String.Format("{0}{1}&sensor=false", _googleUri, origins);
return new Uri(uri);
public static GeoLocation GetCoordinates(string origins)
{
WebClient wc = new WebClient();
Uri uri = GetGeoCodeURI(origins);
try
{
string geoCodeInfo = wc.DownloadString(uri);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(geoCodeInfo);
string status = xmlDoc.DocumentElement.SelectSingleNode("status").InnerText;
double dstnc = 0.0;
XmlNodeList nodeCol = xmlDoc.DocumentElement.SelectNodes("result");
foreach (XmlNode node in nodeCol)
{
dstnc = Convert.ToDouble(node.SelectSingleNode("distance/text").InnerText, System.Globalization.CultureInfo.InvariantCulture);
}
return new GeoLocation(dstnc, uri.ToString());
}
catch
{
return new GeoLocation(0.0, "https://");
}
}
}
}
我在代码顶部添加了距离和 _distance，但我不确定在哪里进行更改以添加第二个经纬度字段。
以防万一，这里是 Main.cs（我根本没有修改它）：
/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using SC_65a998228f6546ed965116b9f8b76953.csproj;
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
// ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
// TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
// PARTICULAR PURPOSE.
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}
public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
Add your code here
*/
sqlmusings.GeoLocation geolocation = sqlmusings.GeoCode.GetCoordinates(Row.AddressLine1 + "," + Row.City + "," + Row.State + "," + Row.Country);
Row.Latitude = Convert.ToDecimal(geolocation.latitude);
Row.Longitude = Convert.ToDecimal(geolocation.longitude);
Row.GeoCodeURL = geolocation.geocodeurl;
}
}
我所要求的只是朝正确的方向推动。
