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