private static void DoGeocode(ConcurrentBag <TDayAddress> goodAddr, ConcurrentBag <TDayAddress> badAddr, MsgOut msgOut, DataRow Row, int rowCnt)
{
// tblRecipient_Address, tblRecipient_Address1, tblRecipient_City, tblRecipient_State, tblRecipient_PostalCode
TDayAddress addr = new TDayAddress()
{
FirstName = EatTheDamReturns(Row[0].ToString().Replace(",", " ")),
LastName = EatTheDamReturns(Row[1].ToString().Replace(",", " ")),
Address1 = EatTheDamReturns(Row[2].ToString().Replace(",", " ")),
AptNum = EatTheDamReturns(Row[3].ToString().Replace(",", " ")),
Address2 = EatTheDamReturns(Row[4].ToString().Replace(",", " ")),
City = EatTheDamReturns(Row[5].ToString().Replace(",", " ")),
State = EatTheDamReturns(Row[6].ToString().Replace(",", " ")),
Zip = EatTheDamReturns(Row[7].ToString()),
HomePhone = EatTheDamReturns(Row[8].ToString()),
CellPHone = EatTheDamReturns(Row[9].ToString()),
DelDay = EatTheDamReturns(Row[10].ToString().Replace(",", " ")),
NumMeals = 0,
Notes = EatTheDamReturns(Row[12].ToString().Replace(",", " "))
};
int numMeals = 0;
addr.NumMeals = 0;
if (int.TryParse(Row[11].ToString(), out numMeals))
{
addr.NumMeals = numMeals;
//msgOut.WriteLine(addr);
}
GeocodeAddress(addr, goodAddr, badAddr, msgOut).Wait();
//GeocodeAddressGoogle(addr, goodAddr, badAddr, msgOut);
msgOut($"Procesed address: {goodAddr.Count + badAddr.Count} of {rowCnt} on thread: {Thread.CurrentThread.ManagedThreadId}\r", true);
}
public static void ReadExcelFile(string excelFile, string outDir, ConcurrentBag <TDayAddress> goodAddr, ConcurrentBag <TDayAddress> badAddr, MsgOut msgOut)
{
using (var stream = File.Open(excelFile, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var dataSet = reader.AsDataSet();
// Now you can get data from each sheet by its index or its "name"
var dataTable = dataSet.Tables[0];
int numCPU = Environment.ProcessorCount / 2;
msgOut($"Processing on {numCPU} threads\n");
Parallel.ForEach(dataTable.Rows.OfType <DataRow>(), new ParallelOptions {
MaxDegreeOfParallelism = numCPU
}, (Row) =>
{
// tblRecipient_Address, tblRecipient_Address1, tblRecipient_City, tblRecipient_State, tblRecipient_PostalCode
TDayAddress addr = new TDayAddress()
{
FirstName = EatTheDamReturns(Row[0].ToString().Replace(",", " ")),
LastName = EatTheDamReturns(Row[1].ToString().Replace(",", " ")),
Address1 = EatTheDamReturns(Row[2].ToString().Replace(",", " ")),
AptNum = EatTheDamReturns(Row[3].ToString().Replace(",", " ")),
Address2 = EatTheDamReturns(Row[4].ToString().Replace(",", " ")),
City = EatTheDamReturns(Row[5].ToString().Replace(",", " ")),
State = EatTheDamReturns(Row[6].ToString().Replace(",", " ")),
Zip = EatTheDamReturns(Row[7].ToString()),
HomePhone = EatTheDamReturns(Row[8].ToString()),
CellPHone = EatTheDamReturns(Row[9].ToString()),
DelDay = EatTheDamReturns(Row[10].ToString()),
NumMeals = 0,
Notes = EatTheDamReturns(Row[12].ToString())
};
int numMeals = 0;
if (int.TryParse(Row[11].ToString(), out numMeals))
{
addr.NumMeals = numMeals;
//msgOut.WriteLine(addr);
GeocodeAddress(addr, goodAddr, badAddr, msgOut).Wait();
}
msgOut($"Procesed count: {goodAddr.Count + badAddr.Count}\r");
});
// Write out the addresses
using (var writer = new StreamWriter(Path.Combine(outDir, "GoodAddresses.csv")))
using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
{
csv.WriteRecords(goodAddr);
}
using (var writer = new StreamWriter(Path.Combine(outDir, "BadAddresses.csv")))
using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
{
csv.WriteRecords(badAddr);
}
}
}
}
private static async Task GeocodeAddress(TDayAddress addr, ConcurrentBag <TDayAddress> goodAddr, ConcurrentBag <TDayAddress> badAddr, MsgOut msgOut)
{
var bingKey = "Aq03fYCDuaMZk0OxpH97nxHInqIsJDzab90p3twCHCk8CvlRoKjB4Xs5Msbgsvq6";
// ServiceManager.Proxy = new WebProxy("http://proxy.research.ge.com:80/");
var request = new GeocodeRequest()
{
Query = addr.ToAddress(),
IncludeIso2 = true,
IncludeNeighborhood = true,
MaxResults = 25,
BingMapsKey = bingKey
};
//Process the request by using the ServiceManager.
var response = await request.Execute();
if (response != null &&
response.ResourceSets != null &&
response.ResourceSets.Length > 0 &&
response.ResourceSets[0].Resources != null &&
response.ResourceSets[0].Resources.Length > 0)
{
Location foundAddr = null;
foreach (var res in response.ResourceSets[0].Resources)
{
if (((Location)res).Address.AdminDistrict == "NY" && ((Location)res).Address.PostalCode != null && ((Location)res).Address.PostalCode.StartsWith("12"))
{
foundAddr = (Location)res;
break;
}
}
//var result = response.ResourceSets[0].Resources[0] as Location;
if (foundAddr != null && foundAddr.MatchCodes[0].ToString() == "Good")
{
addr.Address1 = foundAddr.Address.AddressLine;
addr.City = foundAddr.Address.Locality;
addr.State = foundAddr.Address.AdminDistrict;
addr.Zip = foundAddr.Address.PostalCode;
var coords = foundAddr.Point.Coordinates;
if (coords != null && coords.Length == 2)
{
addr.Lat = (float)coords[0];
addr.Lon = (float)coords[1];
}
goodAddr.Add(addr);
}
else
{
badAddr.Add(addr);
}
//msgOut($"Geocode: {addr} => Results - Code: {result.MatchCodes[0]}\r");
}
}
private static void GeocodeAddressGoogle(TDayAddress addr, ConcurrentBag <TDayAddress> goodAddr, ConcurrentBag <TDayAddress> badAddr, MsgOut msgOut)
{
string YOUR_API_KEY = "AIzaSyDSyz9RnSKz7AMs_EQhDVhpXvuhHmyDTGc";
string address = addr.ToAddress();
string requestUri = string.Format("https://maps.googleapis.com/maps/api/geocode/xml?key={1}&address={0}&sensor=false", Uri.EscapeDataString(address), YOUR_API_KEY);
WebRequest request = WebRequest.Create(requestUri);
WebResponse response = request.GetResponse();
XDocument xdoc = XDocument.Load(response.GetResponseStream());
addr.Restaurant = GetRestaurant(addr.Zip);
if (xdoc.Element("GeocodeResponse").Element("status").Value == "OK")
{
XElement result = xdoc.Element("GeocodeResponse").Element("result");
string [] vals = result.Element("formatted_address").ToString().Split(',');
string[] tmp = vals[2].TrimStart(' ').Split(' ');
vals[2] = tmp[0];
vals[3] = tmp[1];
//msgOut($"Addr: {addr.ToAddress()} Return: {addrFormat.ToString()}");
if (vals[3] != addr.Zip)
{
msgOut($"Changed zip: {addr.ToAddress()} => {result.Element("formatted_address").ToString()}");
}
XElement locationElement = result.Element("geometry").Element("location");
addr.Address1 = vals[0];
addr.City = vals[1];
addr.State = vals[2];
addr.Zip = vals[3];
addr.Lat = float.Parse(locationElement.Element("lat").Value);
addr.Lon = float.Parse(locationElement.Element("lng").Value);
goodAddr.Add(addr);
}
else
{
badAddr.Add(addr);
}
}
private static async Task GeocodeAddress(TDayAddress addr, ConcurrentBag <TDayAddress> goodAddr, ConcurrentBag <TDayAddress> badAddr, MsgOut msgOut)
{
var bingKey = "Aq4xnynDuADw0OMoyrHEvCrM8VqhI8HlGIusrLdL30LGJBz71X9JtUCMcVU6mj7Y";
// ServiceManager.Proxy = new WebProxy("http://proxy.research.ge.com:80/");
var request = new GeocodeRequest()
{
Query = addr.ToAddress(),
IncludeIso2 = true,
IncludeNeighborhood = true,
MaxResults = 25,
BingMapsKey = bingKey
};
//Process the request by using the ServiceManager.
var response = await request.Execute();
if (response != null &&
response.ResourceSets != null &&
response.ResourceSets.Length > 0 &&
response.ResourceSets[0].Resources != null &&
response.ResourceSets[0].Resources.Length > 0)
{
Location foundAddr = null;
foreach (var res in response.ResourceSets[0].Resources)
{
if (((Location)res).Address.AdminDistrict == "NY" && ((Location)res).Address.PostalCode != null && ((Location)res).Address.PostalCode.StartsWith("12"))
{
foundAddr = (Location)res;
//if (((Location)res).Address.PostalCode != addr.Zip)
//{
// msgOut($"Changed zip: {addr.ToAddress()} {foundAddr.Address.FormattedAddress}");
//}
break;
}
}
//var result = response.ResourceSets[0].Resources[0] as Location;
addr.Restaurant = GetRestaurant(addr.Zip);
if (foundAddr != null && foundAddr.MatchCodes[0].ToString() == "Good")
{
//addr.Address1 = foundAddr.Address.AddressLine;
//addr.City = foundAddr.Address.Locality;
//addr.State = foundAddr.Address.AdminDistrict;
//addr.Zip = foundAddr.Address.PostalCode;
var coords = foundAddr.Point.Coordinates;
if (coords != null && coords.Length == 2)
{
addr.Lat = (float)coords[0];
addr.Lon = (float)coords[1];
}
goodAddr.Add(addr);
}
else
{
badAddr.Add(addr);
}
//msgOut($"Geocode: {addr} => Results - Code: {result.MatchCodes[0]}\r");
}
}
