//Calulate where on a route, a busstop should be placed public static List<List<string>> CalculateBusStopsForRoute(List<string> stops, List<string> chosenRouteID, string routeNumber, int subRoute) { List<List<string>> RouteAndStops = new List<List<string>>(); List<string> chosenRouteLatLng = new List<string>(); List<string> RouteWithStopsID = new List<string>(chosenRouteID); List<string> StopOnRoute = new List<string>(); int stopCounter = 0; chosenRouteLatLng = DBConnection.GetLatLngForRoute(chosenRouteID); decimal currDistToStartOfRoute; decimal currDistToEndOfRoute; decimal leastDistToStartOfRoute = -1; decimal leastDistToEndOfRoute = -1; int leastStartStopID = 0; int leastEndStopID = 0; string leastStartStopName = ""; string leastEndStopName = ""; foreach (string s in stops) { decimal leastDist = -1; decimal currentDist; BusStops stop = new BusStops(); int pointBeforeStopIndex = 0; stop = DBConnection.GetIDLatLngForStop(s); for (int k = 0; k < chosenRouteLatLng.Count - 2; k = k + 2) { currentDist = RouteMath.CalculateBusStopToRouteDist(stop.Lat, stop.Lng, (decimal.Parse(chosenRouteLatLng[k])), decimal.Parse(chosenRouteLatLng[k + 1]), decimal.Parse(chosenRouteLatLng[k + 2]), decimal.Parse(chosenRouteLatLng[k + 3])); if ((currentDist < leastDist || leastDist == -1) && currentDist <= 15 && currentDist != -1) { leastDist = currentDist; pointBeforeStopIndex = k / 2; } } if (stops.IndexOf(s) == 0 ) { RouteWithStopsID.Insert(0, stop.ID.ToString()); StopOnRoute.Add(s); stopCounter++; continue; } else if (stops.IndexOf(s) == stops.Count - 1 ) { RouteWithStopsID.Add(stop.ID.ToString()); StopOnRoute.Add(s); stopCounter++; continue; } else if (leastDist != -1) { RouteWithStopsID.Insert(pointBeforeStopIndex + stopCounter + 1, stop.ID.ToString()); StopOnRoute.Add(s); stopCounter++; } } RouteAndStops.Add(RouteWithStopsID); RouteAndStops.Add(StopOnRoute); return RouteAndStops; }
public static BusStops GetIDLatLngForStop(string stopname) { using (var connection = new MySqlConnection(DBConnection.getConnectionString())) { using (var cmd = connection.CreateCommand()) { BusStops stop = new BusStops(); cmd.CommandText = string.Format("Select RoutePoint.ID,RoutePoint.Latitude, RoutePoint.Longitude from RoutePoint " + "inner join BusStop on RoutePoint.ID = BusStop.fk_RoutePoint " + "where BusStop.StopName='{0}'", stopname); connection.Open(); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { stop.ID = int.Parse(reader["ID"].ToString()); stop.Lat = decimal.Parse(reader["Latitude"].ToString()); stop.Lng = decimal.Parse(reader["Longitude"].ToString()); } reader.Close(); connection.Close(); return stop; } } }