获取行驶距离与谷歌地图API和SSIS包在C＃ [英] Getting driving distance with Google maps API and SSIS package in C#
问题描述
更新：找到了谷歌distancematrix并试图相应地修改我的代码。我在这里得到一个无效的参数错误：
UPDATE: Found the Google distancematrix and tried to modify my code accordingly. I am getting an invalid arguments error here:
return new GeoLocation(dstnc, uri.ToString());
}
catch
{
return new GeoLocation(0.0, "https://");
}
基本上，我需要从两个纬度/多头知名获得的行驶距离。我使用的SSIS包，我发现一个非常接近生产，我需要的结果的奇妙教程在线
Basically, I need to get the driving distance from two known lat/longs. I am using an SSIS package and I found a fantastic tutorial online that comes very close to producing the results I need.
教程：的 http://www.sqlmusings.com/2011/03 / 25 /地理编码 - 位置 - 使用 - 谷歌 - 地图-V3-API和SSIS /
他们的所作所为是通过已知的街道地址。谷歌和读经/纬度从返回的XML。
What they are doing is passing a known street address to Google and reading the lat/long from the returned XML.
我需要做不同的什么是传递两个已知纬度/多头和读取返回的距离。
What I need to do differently is pass two known lat/longs and read the returned distance.
他们使用C＃这我不良好的足以与知道如何修改。我在花刺反正这里是我想出了：
They use C# which I am not good enough with to know how to make the modification. I took a stab at it anyway and here is what I came up with:
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的代码，但林不知道顶部在哪里进行更改，以添加一个字段为我的第二个纬度/长。
I added the distance and _distance at the top of the code but Im not sure where to make the change to add a field for my second lat/long.
以防万一，这里是Main.cs（我没有修改它在所有）：
Just in case, here is the Main.cs (I have not modified it at all):
/* 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;
}
}
在正确的方向只是一个推是所有林问。
Just a push in the right direction is all Im asking.
推荐答案
与功能的String.Format的诀窍是，在括号中的数字（例如 {0} 和 {1} ）是将由您在各自的顺序提供参数被替换的位。
The trick with the String.Format function is that the numbers in braces (e.g. {0} and {1}) are the bits that will be replaced by the arguments you provide in respective order.
您通常想要做的是这样的：
What you usually want to do is something like this:
double oLat = 32.1576780, oLng = -82.9070920;
double dLat = 27.6536997, dLng = -81.5158944;
String url = String.Format("https://maps.googleapis.com/maps/api/distancematrix/xml?origins={0},{1}&destinations={2},{3}&mode=driving&sensor=false", oLat, oLng, dLat, dLng);
有一点要记住的是，地图API拥有的条款及细则;需要某些东西，以便细则才能够使用该API是免费的。请务必阅读他们，因为您可能需要为您的使用情况下，商业许可。
One thing to keep in mind is that the Maps APIs have Terms & Conditions that require certain things in order to be able to use the API for free. Make sure you read over them, as you may need a commercial license for your use case.
这篇关于获取行驶距离与谷歌地图API和SSIS包在C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
