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; } }