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