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