private static GeoFile[] GetDumps(GeoFileDownloader downloader)
{
return(new[] {
new GeoFile {
Filename = "admin1CodesASCII.txt", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadAdmin1Codes(fn).Count(); })
},
new GeoFile {
Filename = "admin2Codes.txt", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadAdmin2Codes(fn).Count(); })
},
new GeoFile {
Filename = "allCountries.zip", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadExtendedGeoNames(fn).Count(); })
},
new GeoFile {
Filename = "alternateNames.zip", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadAlternateNames(fn).Count(); })
},
new GeoFile {
Filename = "alternateNamesV2.zip", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadAlternateNamesV2(fn).Count(); })
},
new GeoFile {
Filename = "cities1000.zip", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadExtendedGeoNames(fn).Count(); })
},
new GeoFile {
Filename = "cities15000.zip", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadExtendedGeoNames(fn).Count(); })
},
new GeoFile {
Filename = "cities5000.zip", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadExtendedGeoNames(fn).Count(); })
},
new GeoFile {
Filename = "countryInfo.txt", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadCountryInfo(fn).Count(); })
},
//Featurecodes are downloaded by GetCountryDumps()
new GeoFile {
Filename = "hierarchy.zip", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadHierarchy(fn).Count(); })
},
new GeoFile {
Filename = "iso-languagecodes.txt", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadISOLanguageCodes(fn).Count(); })
},
new GeoFile {
Filename = "no-country.zip", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadExtendedGeoNames(fn).Count(); })
},
new GeoFile {
Filename = "timeZones.txt", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadTimeZones(fn).Count(); })
},
new GeoFile {
Filename = "userTags.zip", Test = (f) => ExecuteTest(f, (fn) => { return GeoFileReader.ReadUserTags(fn).Count(); })
},
}.Union(GetCountryDumps(downloader)).ToArray());
}
public static void Populate(IConfiguration configuration)
{
Console.WriteLine("Getting ready to populate alternate names…");
var connectionString = configuration["ConnectionString"];
var dataPath = configuration["DataSourcePath"];
var alternatesPath = Path.Combine(dataPath, @"alternateNamesV2.txt");
var alternateLanguages = configuration["GeoNames:AlternateNamesLanguageCodes"] ?? String.Empty;
var targetLanguages = alternateLanguages.Split(',');
if (!File.Exists(alternatesPath))
{
Console.WriteLine("Downloading alternate names data…");
var downloader = GeoFileDownloader.CreateGeoFileDownloader();
downloader.DownloadFile("alternateNamesV2.zip", dataPath);
}
var results = GeoFileReader.ReadAlternateNamesV2(alternatesPath).OrderBy(p => p.Id);
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
Console.WriteLine("Populating alternate names…");
var enableIdentityInserts = connection.CreateCommand();
enableIdentityInserts.CommandText = @"SET IDENTITY_INSERT AlternateNames ON";
try
{
enableIdentityInserts.ExecuteNonQuery();
}
catch (SqlException exception)
{
Console.WriteLine("SQL Exception occurred. Error Code: " + exception.ErrorCode);
}
const string sql = @"INSERT INTO AlternateNames (ID, GeoNameId, ISOLanguage, AlternateName, IsPreferredName, IsShortName,
IsColloquial, IsHistoric, FromDate, ToDate)
VALUES (@ID, @GeoNameId, @ISOLanguage, @AlternateName, @IsPreferredName, @IsShortName, @IsColloquial, @IsHistoric,
@FromDate, @ToDate)";
var command = connection.CreateCommand();
command.CommandText = sql;
string[] parameterNames = new[]
{
"@ID",
"@GeoNameId",
"@ISOLanguage",
"@AlternateName",
"@IsPreferredName",
"@IsShortName",
"@IsColloquial",
"@IsHistoric",
"@FromDate",
"@ToDate"
};
DbParameter[] parameters = parameterNames.Select(pn =>
{
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = pn;
command.Parameters.Add(parameter);
return(parameter);
})
.ToArray();
foreach (var r in results)
{
if (targetLanguages.Length > 0 && !targetLanguages.Contains(r.ISOLanguage))
{
continue;
}
parameters[0].Value = r.Id;
parameters[1].Value = r.GeoNameId;
parameters[2].Value = r.ISOLanguage.HasValueOrDBNull();
parameters[3].Value = r.Name.HasValueOrDBNull();
parameters[4].Value = r.IsPreferredName;
parameters[5].Value = r.IsShortName;
parameters[6].Value = r.IsColloquial;
parameters[7].Value = r.IsHistoric;
parameters[8].Value = r.From.HasValueOrDBNull();
parameters[9].Value = r.To.HasValueOrDBNull();
command.ExecuteNonQuery();
Console.WriteLine("Alternate Name ID: " + r.Id);
}
var disableIdentityInserts = connection.CreateCommand();
disableIdentityInserts.CommandText = @"SET IDENTITY_INSERT AlternateNames OFF";
try
{
disableIdentityInserts.ExecuteNonQuery();
}
catch (SqlException exception)
{
Console.WriteLine("SQL Exception occurred. Error Code: " + exception.ErrorCode);
}
Console.WriteLine();
}
}
