/// <summary>
/// Gets the geo places from the file embedded into assembly
/// </summary>
/// <returns>List of geo places</returns>
internal static List <GeoPlace> GetGeoPlacesFromEmbeddedFile()
{
List <GeoPlace> cities = new List <GeoPlace>();
HashSet <string> insertedCityIds = new HashSet <string>();
HashSet <string> unsupportedStates = new HashSet <string>(new string[] { "ak", "hi" });
using (var stream = typeof(PlacesLoader).Assembly.GetManifestResourceStream("Yojowa.StopByStop.Places.cities1000.csv"))
{
using (var streamReader = new StreamReader(stream))
{
string line = null;
while ((line = streamReader.ReadLine()) != null)
{
string[] parts = line.Split(',');
if (parts[3] == "US")
{
string state = parts[4];
if (!unsupportedStates.Contains(state))
{
string name = parts[0];
string description = string.Format("{0}, {1}, United States", parts[0], parts[4]);
string cityId = string.Format("{0}-{1}-United-States", parts[0], parts[4]).Replace(" ", "-").ToLowerInvariant();
double lat = double.Parse(parts[1]);
double lon = double.Parse(parts[2]);
long population = long.Parse(parts[5]);
if (!insertedCityIds.Contains(cityId))
{
GeoPlace city = new GeoPlace()
{
ID = cityId,
ShortName = name,
Name = description,
Location = new Location(lat, lon),
Population = population
};
cities.Add(city);
insertedCityIds.Add(cityId);
}
}
}
}
}
}
return(cities);
}
public void VerifyCitiesTableRecords()
{
var places = PlacesLoader.GetGeoPlacesFromEmbeddedFile();
List <GeoPlace> allGeo = new List <GeoPlace>();
using (var conn = new NpgsqlConnection(PlacesLoader.PGConnection))
{
conn.Open();
string selectQuery = "SELECT id,shortname,name,lat,lng,population FROM cities order by name asc";
using (NpgsqlCommand command = new NpgsqlCommand(selectQuery, conn))
{
var dr = command.ExecuteReader();
while (dr.Read())
{
GeoPlace place = new GeoPlace();
place.ID = dr.GetString(0);
place.ShortName = dr.GetString(1);
place.Name = dr.GetString(2);
place.Location = new Location();
place.Location.Lat = dr.GetDouble(3);
place.Location.Lon = dr.GetDouble(4);
place.Population = dr.GetInt64(5);
allGeo.Add(place);
}
}
}
foreach (var place in places)
{
var exists = allGeo.Where(x => x.ID == place.ID).Any();
Assert.AreEqual <bool>(true, exists);
}
}
private Address ConvertToAddress(GeoPlace place)
{
var address = new Address
{
Id = Guid.NewGuid(),
PlaceId = place.Id,
FriendlyName = place.Name,
FullAddress = place.Address.FullAddress,
Latitude = place.Address.Latitude,
Longitude = place.Address.Longitude,
AddressType = "place",
City = place.Address.City,
State = place.Address.State,
ZipCode = place.Address.ZipCode
};
if (address.FullAddress.HasValue() &&
address.FullAddress.Contains("-"))
{
var firstWordStreetNumber = address.FullAddress.Split(' ')[0];
if (firstWordStreetNumber.Contains("-"))
{
int notImportant;
var isNumber = int.TryParse(firstWordStreetNumber.Split('-')[0].Trim(), out notImportant);
if (isNumber)
{
var newStreetNUmber = firstWordStreetNumber.Split('-')[0].Trim();
address.FullAddress = address.FullAddress.Replace(firstWordStreetNumber, newStreetNUmber);
}
}
}
address.FullAddress = AddExtraFieldsToFullAddress(address);
return(address);
}
