private GeographicSpeciesListDto ReadSpeciesDistributionFromDatabase(GeographyPoint point)
{
var speciesInfoDictionary = new Dictionary <String, GeographicSpeciesInfoDto>();
using (var sqlConnection = new SqlConnection(WhatIsThatDbConnString))
{
sqlConnection.Open();
var cmd = new SqlCommand("SELECT * FROM SpeciesGeography WHERE LatitudeX10 = @LatitudeX10 AND LongitudeX10 = @LongitudeX10")
{
CommandType = CommandType.Text,
Connection = sqlConnection
};
cmd.Parameters.AddWithValue("@LatitudeX10", Math.Round(point.Latitude * 10, 0));
cmd.Parameters.AddWithValue("@LongitudeX10", Math.Round(point.Longitude * 10, 0));
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var speciesInfoDto = new GeographicSpeciesInfoDto
{
Kingdom = reader["Kingdom"].ToString(),
Phylum = reader["Phylum"].ToString(),
Class = reader["Class"].ToString(),
Order = reader["Order"].ToString(),
Family = reader["Family"].ToString(),
Genus = reader["Genus"].ToString(),
Species = reader["Species"].ToString(),
PresenceCode = 1
//TODO: This may or may not be relevant for our purposes. For now, assuming not.
};
var key = speciesInfoDto.Species.Trim().ToLower();
//Presence code of >= 4 means the species probably doesn't exist in the area ("Possibly extinct")
if (!speciesInfoDictionary.ContainsKey(key) && speciesInfoDto.PresenceCode < 4)
{
speciesInfoDictionary.Add(key, speciesInfoDto);
}
}
}
}
}
var geographicSpeciesListDto = new GeographicSpeciesListDto
{
Latitude = point.Latitude,
Longitude = point.Longitude,
SpeciesInfoDtoList = speciesInfoDictionary.Values.ToList()
};
return(geographicSpeciesListDto);
}
private GeographicSpeciesListDto ReadSpeciesDistributionFromShapeFiles(ShapeFile shapeFile, GeographyPoint coordinates)
{
var speciesInfoDictionary = new Dictionary <String, GeographicSpeciesInfoDto>();
var geometryObject = Geometry.DefaultFactory.CreatePoint(new Coordinate(coordinates.Longitude, coordinates.Latitude));
var ds = new FeatureDataSet();
//example uses a map image, but this could be a layer generated with code
shapeFile.ExecuteIntersectionQuery(geometryObject.Centroid, ds);
foreach (var dt in ds.Tables)
{
foreach (DataRow row in dt.Rows)
{
var speciesInfoDto = new GeographicSpeciesInfoDto
{
Kingdom = row.ItemArray[17].ToString(),
Phylum = row.ItemArray[18].ToString(),
Class = row.ItemArray[19].ToString(),
Order = row.ItemArray[20].ToString(),
Family = row.ItemArray[21].ToString(),
Genus = row.ItemArray[22].ToString(),
Species = row.ItemArray[23].ToString(),
PresenceCode = Int32.Parse(row.ItemArray[14].ToString())
};
var key = speciesInfoDto.Species.Trim().ToLower();
//Presence code of >= 4 means the species probably doesn't exist in the area ("Possibly extinct")
if (!speciesInfoDictionary.ContainsKey(key) && speciesInfoDto.PresenceCode < 4)
{
speciesInfoDictionary.Add(key, speciesInfoDto);
}
}
}
var geographicSpeciesListDto = new GeographicSpeciesListDto
{
Latitude = coordinates.Latitude,
Longitude = coordinates.Longitude,
SpeciesInfoDtoList = speciesInfoDictionary.Values.ToList()
};
return(geographicSpeciesListDto);
}
