public StateInfo GetStateInfo(string name)
{
try
{
//Error Check
if (string.IsNullOrEmpty(name))
{
return(null);
}
using (var context = new GeoLocationEntities())
{
return(context.State
.Where(x => x.Name == name || x.Abbr == name)
.Select(x => new StateInfo()
{
Abbr = x.Abbr,
Name = x.Name,
Id = x.StateId,
})
.FirstOrDefault());
}
}
catch (Exception ex)
{
Logger.LogError(ex);
throw;
}
}
private static void CheckCanada()
{
try
{
var notFound = 0;
var total = 0;
var missed = 0;
var connectionString = @"server=.\SQL2014;initial catalog=GeoLocation;integrated security=SSPI;";
using (var context = new GeoLocationEntities(connectionString))
{
using (var textReader = File.OpenText(@"C:\temp\CanadianZipCodes201805.csv"))
{
using (var csv = new CsvReader(textReader))
{
while (csv.Read())
{
var postalCode = csv.GetField <string>(0).Replace(" ", "").Trim();
var city = csv.GetField <string>(1);
var prov = csv.GetField <string>(2);
if (!context.CanadaPostalCode.Any(x => x.PostalCode == postalCode))
{
notFound++;
Console.WriteLine($"Total={total}, NotFound={notFound}, PostalCode={postalCode}");
//Get same city
var template = context.CanadaPostalCode.FirstOrDefault(x => x.City == city);
if (template != null)
{
//Add new
context.AddItem(new CanadaPostalCode {
PostalCode = postalCode, City = template.City, Latitude = template.Latitude, Longitude = template.Longitude
});
context.SaveChanges();
}
else
{
missed++;
}
}
total++;
}
}
}
}
Console.WriteLine($"Total={total}, NotFound={notFound}, Missed={missed}");
}
catch (Exception ex)
{
throw;
}
}
public ActionResult GeocodeCity(string city, string state)
{
var context = new GeoLocationEntities();
var loc = context.USCities.Where(c => c.City.Equals(city, StringComparison.InvariantCultureIgnoreCase) && c.Region.Equals(state, StringComparison.InvariantCultureIgnoreCase)).FirstOrDefault();
if (loc != null)
{
return(Json(new Location {
City = city, State = state, Latitude = loc.Latitude.GetValueOrDefault().ToString(), Longitude = loc.Longitude.GetValueOrDefault().ToString()
}, "text/plain", JsonRequestBehavior.AllowGet));
}
return(Json(new Location(), "text/plain", JsonRequestBehavior.AllowGet));
}
public ActionResult ReverseGeocode(string latitude, string longitude)
{
var context = new GeoLocationEntities();
//todo: calculate nearest city
var loc = context.USCities.Where(c => c.Latitude.ToString() == latitude && c.Longitude.ToString() == longitude).FirstOrDefault();
if (loc != null)
{
return(Json(new Location {
City = loc.City, State = loc.Region, Latitude = latitude, Longitude = longitude
}, "text/plain", JsonRequestBehavior.AllowGet));
}
return(Json(new Location(), "text/plain", JsonRequestBehavior.AllowGet));
}
private static void ResetPopulations()
{
try
{
var index = 0;
var connectionString = @"server=.\SQL2014;initial catalog=GeoLocation;integrated security=SSPI;";
using (var context = new GeoLocationEntities(connectionString))
{
var allZips = context.City.ToList();
var states = context.State.ToList();
var input = @"C:\temp\cities.csv";
using (var sr = new StreamReader(input))
{
using (var csv = new CsvReader(sr))
{
while (csv.Read())
{
var record = csv.GetRecord <CityItem>();
var population = record.Population.ToInt();
if (population > 0)
{
var state = states.Where(x => x.Name.Match(record.State)).Select(x => x.Abbr).FirstOrDefault();
var dbZip = allZips.FirstOrDefault(x => x.Name.Match(record.City) && x.State == state);
if (dbZip == null)
{
context.AddItem(new Gravitybox.GeoLocation.EFDAL.Entity.City {
Name = record.City, Population = population, State = state
});
Console.WriteLine($"Index={index}, Action=Add, City={record.City}, State={state}, Population={record.Population}");
}
else if (dbZip != null && dbZip.Population != population)
{
Console.WriteLine($"Index={index}, Action=Update, City={record.City}, State={state}, Population={record.Population}, Population2={dbZip.Population}");
dbZip.Population = population;
}
}
index++;
}
}
}
context.SaveChanges();
}
}
catch (Exception ex)
{
throw;
}
}
public ActionResult GeocodeZip(string zipcode)
{
var context = new GeoLocationEntities();
var loc = (from USCity c in context.USCity
let zip = context.USCity.Select(x => c.Zipcode).Cast <string>().FirstOrDefault()
where zip == zipcode
select c).FirstOrDefault();
if (loc != null)
{
return(Json(new Location {
City = loc.City, State = loc.State, Latitude = loc.Lat.ToString(), Longitude = loc.Lon.ToString(), Zipcode = zipcode
}, "text/plain", JsonRequestBehavior.AllowGet));
}
return(Json(new Location(), "text/plain", JsonRequestBehavior.AllowGet));
}
public ZipInfo GetZipFromCityState(string city, string state)
{
var found = false;
var timer = Stopwatch.StartNew();
try
{
if (string.IsNullOrEmpty(state))
{
return(null);
}
using (var context = new GeoLocationEntities())
{
//If it is Not an abbr then find by name
if (state.Length != 2)
{
state = context.State.Where(x => x.Name == state).Select(x => x.Abbr).FirstOrDefault();
}
var list = context.Zip.Where(x =>
x.City == city &&
x.State == state)
.ToList();
if (list.Count > 0)
{
found = true;
return(list.First().ToZipInfo());
}
return(null);
}
}
catch (Exception ex)
{
Logger.LogError(ex);
throw;
}
finally
{
timer.Stop();
Logger.LogInfo("GetZipFromCityState: Elapsed=" + timer.ElapsedMilliseconds + ", Found=" + found + ", Term=" + city + "|" + state);
}
}
private static void DumpSQL()
{
try
{
var connectionString = @"server=.\SQL2014;initial catalog=GeoLocation;integrated security=SSPI;";
using (var context = new GeoLocationEntities(connectionString))
{
var allZips = context.City.OrderBy(x => x.Name).ThenBy(x => x.State).ToList();
var sb = new StringBuilder();
foreach (var item in allZips)
{
sb.AppendLine($"INSERT INTO [City]([Name],[State],[Population]) VALUES ('{item.Name.Replace("'","''")}','{item.State}',{item.Population});");
}
File.WriteAllText(@"c:\temp\city-gen.sql", sb.ToString());
}
}
catch (Exception ex)
{
throw;
}
}
public ZipInfo GetZipFromCoordinates(double latitude, double longitude, bool isExact = true)
{
try
{
using (var context = new GeoLocationEntities())
{
var z = context.Zip.FirstOrDefault(x => x.Latitude == latitude && x.Longitude == longitude);
if (isExact && z == null)
{
return(null);
}
if (z == null)
{
var sb = new StringBuilder();
sb.AppendLine($"select top 1 z.*, dbo.CalcDistance({latitude}, {longitude}, z.Latitude, z.Longitude) as distance");
sb.AppendLine("from [ZIP] z");
sb.AppendLine($"order by dbo.CalcDistance({latitude}, {longitude}, z.Latitude, z.Longitude)");
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["GeoLocationEntities"].ConnectionString;
var ds = SqlHelper.GetDataset(connectionString, sb.ToString());
if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 1)
{
var zipId = (int)ds.Tables[0].Rows[0]["zipid"];
z = context.Zip.FirstOrDefault(x => x.ZipId == zipId);
}
if (z == null)
{
return(null);
}
}
return(z.ToZipInfo());
}
}
catch (Exception ex)
{
Logger.LogError(ex);
throw;
}
}
public List <StateInfo> GetStateList()
{
try
{
var retval = new List <StateInfo>();
using (var context = new GeoLocationEntities())
{
return(context.State.Select(x => new StateInfo()
{
Abbr = x.Abbr,
Name = x.Name,
Id = x.StateId,
})
.ToList());
}
}
catch (Exception ex)
{
Logger.LogError(ex);
throw;
}
}
private List <EFDAL.Entity.Zip> Lookup(string term)
{
try
{
var retval = new List <EFDAL.Entity.Zip>();
if (string.IsNullOrEmpty(term))
{
return(retval);
}
//Strip comma in case have not enter state yet
term = term.TrimEnd(new char[] { ',' });
//Now search for the different location formats
using (var context = new GeoLocationEntities())
{
//If numeric then assume it is zip
if (int.TryParse(term, out int zipCode))
{
//If zip code then return zips
retval.AddRange(
context.Zip
.Where(x => x.Name.Contains(term))
.ToList());
Logger.LogInfo($"GetLookup: Path1, term={term}, Count={retval.Count}");
return(retval);
}
//Check Canada postal code
//If found one then return it
var cpostTerm = term.Replace(" ", string.Empty);
if (cpostTerm.Length == 6)
{
var cpost = context.CanadaPostalCode.FirstOrDefault(x => x.PostalCode == cpostTerm);
if (cpost != null)
{
retval.Add(new EFDAL.Entity.Zip
{
City = cpost.City,
Latitude = cpost.Latitude,
Longitude = cpost.Longitude,
Name = cpost.PostalCode,
Country = "Canada",
});
return(retval);
}
}
//If two strings with comma then assume city state
var arr = term.Split(',');
if (arr.Length == 2)
{
var cityValue = arr[0].Trim();
var stateValue = arr[1].Trim();
var zipValue = string.Empty;
//If there is numeric ZIP on the end then split it out too
var arr2 = stateValue.Split(' ');
if (arr2.Length == 2 && arr2[1].ToInt32() > 0)
{
stateValue = arr2[0].Trim();
zipValue = arr2[1].Trim();
}
//If there is a ZIP then use it
//Zips can span cities so there may be multiple
var zipItems = context.Zip
.Where(x => x.Name == zipValue)
.ToList();
EFDAL.Entity.Zip singleZip = null;
//If there is a ZIP code included and not a single match
//determine if can match city, state
if (!string.IsNullOrEmpty(zipValue) && zipItems.Count > 1)
{
var lmabda = zipItems.Where(x => x.City.ToLower() == cityValue.ToLower());
if (lmabda.Count() == 1)
{
singleZip = lmabda.First();
}
}
//If the "state" value is 6 chars, check if it is Canadian
//Check Canada postal code
//If found one then return it
var cpostTerm2 = stateValue.Replace(" ", string.Empty);
if (cpostTerm2.Length == 6)
{
var cpost = context.CanadaPostalCode.FirstOrDefault(x => x.PostalCode == cpostTerm2);
if (cpost != null && (string.IsNullOrEmpty(cityValue) || cityValue.ToLower() == cpost.City.ToLower()))
{
retval.Add(new EFDAL.Entity.Zip
{
City = cpost.City,
Latitude = cpost.Latitude,
Longitude = cpost.Longitude,
Name = cpost.PostalCode,
});
return(retval);
}
}
if (singleZip == null && zipItems.Count == 1)
{
singleZip = zipItems.FirstOrDefault();
}
else if (singleZip == null) //Look for specific city, state
{
singleZip = context.Zip.FirstOrDefault(x => x.City == cityValue && x.State == stateValue);
if (singleZip == null)
{
singleZip = context.Zip.FirstOrDefault(x => x.City.Contains(cityValue) && x.State == stateValue);
}
}
//If the ZIP was found then return it
if (singleZip != null)
{
retval.Add(singleZip);
}
//If it was not found then look again, less specifically
if (!retval.Any())
{
var state = context.State.FirstOrDefault(x => x.Name.Contains(stateValue) || x.Abbr == stateValue);
if (state != null)
{
stateValue = state.Abbr;
}
//If (city AND state) match OR (city and ZIP) match OR (city, state zip)
var list = (from z in context.Zip
join c in context.City on new { z.City, z.State } equals new { City = c.Name, c.State } into q
from c in q.DefaultIfEmpty()
where ((z.City.Contains(cityValue) && (z.State.Contains(stateValue))) ||
(z.City.Contains(cityValue) && (z.Name.Contains(stateValue))))
orderby c.Population descending
select new { z, c })
.ToList()
.Select(x => new EFDAL.Entity.Zip {
City = x.z.City, State = x.z.State, Population = x.c?.Population, Latitude = x.z.Latitude, Longitude = x.z.Longitude
}) //, Name = x.Name
.Distinct(new ZipComparer())
.OrderByDescending(x => x.Population)
.Take(50)
.ToList();
retval.AddRange(list);
}
Logger.LogInfo($"GetLookup: Path2, term={term}, Count={retval.Count}");
}
else
{
//Group by city/state and sum population so can order by largest overall population, not individual zip code
var list = (from z in context.Zip
join c in context.City on new { z.City, z.State } equals new { City = c.Name, c.State } into q
from c in q.DefaultIfEmpty()
where (z.City.Contains(term) || z.Name.Contains(term) || z.State.Contains(term))
orderby c.Population descending
select new { z, c })
.ToList()
.Select(x => new EFDAL.Entity.Zip {
City = x.z.City, State = x.z.State, Population = x.c?.Population, Latitude = x.z.Latitude, Longitude = x.z.Longitude
}) //, Name = x.Name
.OrderByDescending(x => x.Population)
.Distinct(new ZipComparer())
.Take(50)
.ToList();
retval.AddRange(list);
Logger.LogInfo($"GetLookup: Path3, term={term}, Count={retval.Count}");
}
return(retval);
}
}
catch (Exception ex)
{
Logger.LogError(ex);
throw;
}
}
private static void CleanCity()
{
try
{
var index = 0;
var connectionString = @"server=.\SQL2014;initial catalog=GeoLocation;integrated security=SSPI;";
using (var context = new GeoLocationEntities(connectionString))
{
var allZips = context.City.ToList();
//Set to largest population
Dictionary <string, int> _cache = new Dictionary <string, int>();
foreach (var item in allZips)
{
var key = (item.Name + "|" + item.State).ToLower();
if (!_cache.ContainsKey(key))
{
_cache.Add(key, item.Population ?? 0);
}
var newPopulation = item.Population ?? 0;
if (_cache[key] < newPopulation)
{
_cache[key] = newPopulation;
}
index++;
Console.WriteLine($"Loop 1: Index={index}, Count={allZips.Count}");
}
index = 0;
//Reset the populations
foreach (var item in allZips)
{
var key = (item.Name + "|" + item.State).ToLower();
item.Population = _cache[key];
index++;
Console.WriteLine($"Loop 2: Index={index}, Count={allZips.Count}");
}
context.SaveChanges();
allZips = context.City.ToList();
//Delete duplicates
index = 0;
var dups = new HashSet <int>();
_cache = new Dictionary <string, int>();
foreach (var item in allZips)
{
var key = (item.Name + "|" + item.State).ToLower();
if (_cache.ContainsKey(key))
{
dups.Add(item.CityId);
}
else
{
_cache.Add(key, 0);
}
index++;
Console.WriteLine($"Loop 3: Index={index}, Count={allZips.Count}");
}
index = 0;
foreach (var key in dups)
{
context.City.Where(x => x.CityId == key).Delete();
context.SaveChanges();
Console.WriteLine($"Loop 4: Index={index}, Count={dups.Count}");
}
Console.WriteLine("Done");
}
}
catch (Exception ex)
{
throw;
}
}
