public async Task<Subdivision> LoadById(int id)
{
Subdivision result = null;
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = "select id, population, ST_AsGeoJSON(boundry) as boundry from subdivisions where id = @id";
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var subdivision = new Subdivision() { Id = Convert.ToInt32(reader["id"]) };
if (reader["population"] != DBNull.Value)
{
subdivision.Population = Convert.ToInt32(reader["population"]);
}
if (reader["boundry"] != DBNull.Value)
{
subdivision.GeoJSON = reader["boundry"] as string;
}
result = subdivision;
}
}
return result;
}
public async Task<IEnumerable<Subdivision>> Load100()
{
var result = new List<Subdivision>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = "select id, population, ST_AsGeoJSON(boundry) as boundry from subdivisions limit 100";
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var subdivision = new Subdivision() { Id = Convert.ToInt32(reader["id"]) };
if (reader["population"] != DBNull.Value)
{
subdivision.Population = Convert.ToInt32(reader["population"]);
}
if (reader["boundry"] != DBNull.Value)
{
subdivision.GeoJSON = reader["boundry"] as string;
}
result.Add(subdivision);
}
}
return result;
}
public async Task<List<Subdivision>> Density(AlcoholType alcoholType, EndOfDistribution distribution, int count)
{
var volumeField = alcoholTypeVolumeFieldMap[alcoholType];
var sortDirection = endOfDistributionMap[distribution];
var result = new List<Subdivision>();
var query = $@"select id,
population,
name,
St_AsGeoJSON(st_centroid(boundry::geometry)) as centre,
beer_volume / 1000.0 as beer_volume,
wine_volume / 1000.0 as wine_volume,
spirits_volume / 1000.0 as spirits_volume,
{volumeField} / population as density
from subdivisions
where {volumeField} > 0
order by density {sortDirection}
limit {count}";
var resultDict = new Dictionary<int, Subdivision>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
conn.Open();
using (var reader = await cmd.ExecuteReaderAsync())
{
while (reader.Read())
{
var subdiv = new Subdivision(reader);
result.Add(subdiv);
resultDict.Add(subdiv.Id, subdiv);
}
}
}
if (result.Any())
{
var stores = await GetStoresForSubdivisions(result.Select(s => s.Id), conn);
foreach (var store in stores)
{
resultDict[store.SubdivisionId].LcboStores.Add(store);
}
}
}
return result;
}
public async Task<List<Subdivision>> Top10WineDensity()
{
var result = new List<Subdivision>();
var query = @"select id,
population,
name,
St_AsGeoJSON(st_centroid(boundry::geometry)) as centre,
wine_volume,
wine_volume / population as density
from subdivisions
where wine_volume > 0
order by density desc
limit 10";
var resultDict = new Dictionary<int, Subdivision>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
conn.Open();
using (var reader = await cmd.ExecuteReaderAsync())
{
while (reader.Read())
{
var subdiv = new Subdivision(reader);
result.Add(subdiv);
resultDict.Add(subdiv.Id, subdiv);
}
}
}
if (result.Any())
{
var stores = await GetStoresForSubdivisions(result.Select(s => s.Id), conn);
foreach (var store in stores)
{
resultDict[store.SubdivisionId].LcboStores.Add(store);
}
}
}
return result;
}
