private void WriteSpeciesInfoToDatabase(GeographyPoint point, GeographicSpeciesInfoDto speciesInfoDto, SqlConnection sqlConnection)
{
var cmd = new SqlCommand("UPDATE SpeciesGeography " +
"SET LatitudeX10=@LatitudeX10, LongitudeX10=@LongitudeX10, Kingdom=@Kingdom, Phylum=@Phylum, Class=@Class, [Order]=@Order, Family=@Family, " +
"Genus=@Genus, Species=@Species " +
"WHERE LatitudeX10=@LatitudeX10 AND LongitudeX10=@LongitudeX10 AND Kingdom=@Kingdom AND Phylum=@Phylum AND Class=@Class AND [Order]=@Order AND Family=@Family AND " +
"Genus=@Genus AND Species=@Species " +
"IF @@ROWCOUNT=0 INSERT INTO SpeciesGeography (LatitudeX10, LongitudeX10, Kingdom, Phylum, Class, [Order], Family, Genus, Species) " +
"VALUES (@LatitudeX10, @LongitudeX10, @Kingdom, @Phylum, @Class, @Order, @Family, @Genus, @Species)")
{
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));
cmd.Parameters.AddWithValue("@Kingdom", speciesInfoDto.Kingdom);
cmd.Parameters.AddWithValue("@Phylum", speciesInfoDto.Phylum);
cmd.Parameters.AddWithValue("@Class", speciesInfoDto.Class);
cmd.Parameters.AddWithValue("@Order", speciesInfoDto.Order);
cmd.Parameters.AddWithValue("@Family", speciesInfoDto.Family);
cmd.Parameters.AddWithValue("@Genus", speciesInfoDto.Genus);
cmd.Parameters.AddWithValue("@Species", speciesInfoDto.Species);
cmd.ExecuteNonQuery();
}
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);
}