public WaterStation GetStationInfo(long stationID)
{
WaterStation station = new WaterStation();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand stationLookup = new SqlCommand(string.Format("SELECT * FROM WaterStations WHERE ID={0}", stationID), conn))
using (SqlDataReader reader = stationLookup.ExecuteReader())
{
if (reader.Read())
{
station.ID = reader.GetInt64(0);
station.StationNumber = reader.GetString(1);
station.StationName = reader.GetString(2);
station.Latitude = reader.GetDouble(3);
station.Longitude = reader.GetDouble(4);
}
reader.Close();
}
}
return station;
}
public ICollection<WaterStation> GetClosestStations(double siteLatitude, double siteLongitude, int range)
{
List<WaterStation> result = new List<WaterStation>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand stationLookup = new SqlCommand(string.Format("SELECT TOP 10 *, ((ACOS(SIN({0} * PI() / 180) * SIN(Latitude * PI() / 180) "
+ "+ COS({0} * PI() / 180) * COS(Latitude * PI() / 180) * COS(({1} - Longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) "
+ "AS distance FROM WaterStations WHERE ( Latitude BETWEEN ({0} - {2}) AND ({0} + {2}) AND Longitude BETWEEN ({1} - {2}) AND ({1} + {2}) ) "
+ "ORDER BY distance ASC", siteLatitude, siteLongitude, range), conn))
using (SqlDataReader reader = stationLookup.ExecuteReader())
{
WaterStation station = null;
while (reader.Read())
{
station = new WaterStation();
station.ID = reader.GetInt64(0);
station.StationNumber = reader.GetString(1);
station.StationName = reader.GetString(2);
station.Latitude = reader.GetDouble(3);
station.Longitude = reader.GetDouble(4);
result.Add(station);
}
reader.Close();
}
}
return result;
}