public List <Route> readRoutesStartingWith(String name)
{
List <Route> routes = new List <Route>();
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM routes WHERE route_name LIKE :name", connection);
command.Parameters.AddWithValue(":name", name + "%");
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Route route = new Route();
route.routePoints = new List <DPoint>();
route.guid = (reader[0] == DBNull.Value) ? null : (String)reader[0];
route.name = (reader[1] == DBNull.Value) ? null : (String)reader[1];
route.countryId = (reader[2] == DBNull.Value) ? -1 : (int)reader[2];
route.routeTypeId = (reader[3] == DBNull.Value) ? -1 : (int)reader[3];
route.owner = (reader[4] == DBNull.Value) ? null : (String)reader[4];
routes.Add(route);
}
reader.Close();
foreach (Route route in routes)
{
// get route points
command.CommandText = "SELECT * FROM routes_points WHERE route_guid=:guid";
command.Parameters.Add(new NpgsqlParameter("guid", route.guid));
reader = command.ExecuteReader();
while (reader.Read())
{
DPoint point = new DPoint((double)reader[2], (double)reader[3]);
route.routePoints.Add(point);
}
reader.Close();
}
return(routes);
}
public Route readRouteByName(String name)
{
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM routes WHERE route_name=:name", connection);
command.Parameters.Add(new NpgsqlParameter("name", name));
NpgsqlDataReader reader = command.ExecuteReader();
// get route (if there is one, if not YOU GET NOTHING! DEAL WITH IT!)
if (!reader.Read())
{
reader.Close();
return(null);
}
Route route = new Route();
route.routePoints = new List <DPoint>();
route.guid = (reader[0] == DBNull.Value) ? null : (String)reader[0];
route.name = (reader[1] == DBNull.Value) ? null : (String)reader[1];
route.countryId = (reader[2] == DBNull.Value) ? -1 : (int)reader[2];
route.routeTypeId = (reader[3] == DBNull.Value) ? -1 : (int)reader[3];
route.owner = (reader[4] == DBNull.Value) ? null : (String)reader[4];
reader.Close();
// get route points
command.CommandText = "SELECT * FROM routes_points WHERE route_guid=:guid";
command.Parameters.Add(new NpgsqlParameter("guid", route.guid));
reader = command.ExecuteReader();
while (reader.Read())
{
DPoint point = new DPoint((double)reader[2], (double)reader[3]);
route.routePoints.Add(point);
}
reader.Close();
return(route);
}
public async Task <Route> generateRouteByShortestPath(String routeName, DPoint source, DPoint dest)
{
// assumption: source and dest are on a sidewalk
shPath path = await RouteWebAPI.FindShortPath("0", source.x, source.y, dest.x, dest.y, false);
Route route = new Route();
route.countryId = 0;
route.guid = Util.CreateGuid();
route.name = routeName;
route.owner = null;
route.routeTypeId = 0;
route.routePoints = new List <DPoint>();
foreach (shPoint point in path.Points)
{
route.routePoints.Add(new DPoint(point.x, point.y));
}
return(route);
}
public List <Route> readAllRoutes()
{
List <Route> routes = new List <Route>();
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM routes", connection);
NpgsqlDataReader reader = command.ExecuteReader();
// read the routes themselves
while (reader.Read())
{
Route route = new Route();
route.routePoints = new List <DPoint>();
route.guid = (reader[0] == DBNull.Value) ? null : (String)reader[0];
route.name = (reader[1] == DBNull.Value) ? null : (String)reader[1];
route.countryId = (reader[2] == DBNull.Value) ? -1 : (int)reader[2];
route.routeTypeId = (reader[3] == DBNull.Value) ? -1 : (int)reader[3];
route.owner = (reader[4] == DBNull.Value) ? null : (String)reader[4];
routes.Add(route);
}
reader.Close();
// now for all route add his points
foreach (Route route in routes)
{
command.CommandText = "SELECT * FROM routes_points WHERE route_guid=:guid";
command.Parameters.Add(new NpgsqlParameter("guid", route.guid));
reader = command.ExecuteReader();
while (reader.Read())
{
DPoint point = new DPoint((double)reader[2], (double)reader[3]);
route.routePoints.Add(point);
}
reader.Close();
}
return(routes);
}
static async Task addWaypointRoutes(String connectionParams)
{
NpgsqlConnection connection = new NpgsqlConnection(connectionParams);
connection.Open();
NpgsqlTransaction transaction = connection.BeginTransaction();
try
{
// travel waypoints
//DPoint[] travelCoordinates = new DPoint[] { new DPoint(34.8496, 32.0996), new DPoint(34.8506, 32.099), new DPoint(34.8486, 32.099),
// new DPoint(34.8503, 32.0981), new DPoint(34.8487, 32.0983),
// new DPoint(34.8486, 32.099), new DPoint(34.851, 32.0997),
// new DPoint(34.8515, 32.0992), new DPoint(34.8514, 32.0983),
// new DPoint(34.8511, 32.0973), new DPoint(34.8501, 32.0977),
// new DPoint(34.8487, 32.0982), new DPoint(34.8478, 32.0986)};
DPoint[] travelCoordinates = new DPoint[] { new DPoint(34.848627448082, 32.0995901398799), new DPoint(34.8495876789093, 32.0996264945646),
new DPoint(34.8505747318268, 32.0996492162353),
new DPoint(34.850612282753, 32.0982768171897), new DPoint(34.8492550849915, 32.0980950409352),
new DPoint(34.8486435413361, 32.098308627997), new DPoint(34.8514652252197, 32.0996855708965),
new DPoint(34.851508140564, 32.0986403686134), new DPoint(34.8511004447937, 32.0973452100618),
new DPoint(34.8498612642288, 32.0977905648985), new DPoint(34.8485255241394, 32.0982631839831),
new DPoint(34.8479408025742, 32.0971543430385), new DPoint(34.8504567146301, 32.096090933751) };
// read all barrier coordinates
BarriersDB barriersDB = new BarriersDB(connection);
List <Barrier> barriers = barriersDB.readAllBarriers();
RouteGenerator generator = new RouteGenerator(connection);
// generate routes from waypoint to other waypoints
for (int i = 0; i < travelCoordinates.Count(); i++)
{
for (int j = 0; j < travelCoordinates.Count(); j++)
{
if (i == j)
{
continue;
}
Route waypointRoute = await generator.generateRouteByShortestPath("WaypointToWaypoint_" + i + "," + j, travelCoordinates[i], travelCoordinates[j]);
generator.saveRouteToDB(waypointRoute);
}
}
// generate routes from waypoint to barriers
for (int i = 0; i < travelCoordinates.Count(); i++)
{
for (int j = 0; j < barriers.Count(); j++)
{
if (i == j)
{
continue;
}
DPoint barrierCoordinates = new DPoint(barriers[j].x, barriers[j].y);
Route waypointRoute = await generator.generateRouteByShortestPath("WaypointToBarrier_" + i + "," + j, travelCoordinates[i], barrierCoordinates);
generator.saveRouteToDB(waypointRoute);
}
}
transaction.Commit();
}
catch (Exception exception)
{
try
{
transaction.Rollback();
}
catch (Exception rollbackException)
{
Console.WriteLine("Rollback failed :(");
}
}
connection.Close();
}
public List<Route> readAllRoutes()
{
List<Route> routes = new List<Route>();
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM routes", connection);
NpgsqlDataReader reader = command.ExecuteReader();
// read the routes themselves
while (reader.Read())
{
Route route = new Route();
route.routePoints = new List<DPoint>();
route.guid = (reader[0] == DBNull.Value) ? null : (String)reader[0];
route.name = (reader[1] == DBNull.Value) ? null : (String)reader[1];
route.countryId = (reader[2] == DBNull.Value) ? -1 : (int)reader[2];
route.routeTypeId = (reader[3] == DBNull.Value) ? -1 : (int)reader[3];
route.owner = (reader[4] == DBNull.Value) ? null : (String)reader[4];
routes.Add(route);
}
reader.Close();
// now for all route add his points
foreach (Route route in routes)
{
command.CommandText = "SELECT * FROM routes_points WHERE route_guid=:guid";
command.Parameters.Add(new NpgsqlParameter("guid", route.guid));
reader = command.ExecuteReader();
while (reader.Read())
{
DPoint point = new DPoint((double)reader[2], (double)reader[3]);
route.routePoints.Add(point);
}
reader.Close();
}
return routes;
}
public async Task<Route> generateRouteByShortestPath(String routeName, DPoint source, DPoint dest)
{
// assumption: source and dest are on a sidewalk
shPath path = await RouteWebAPI.FindShortPath("0", source.x, source.y, dest.x, dest.y, false);
Route route = new Route();
route.countryId = 0;
route.guid = Util.CreateGuid();
route.name = routeName;
route.owner = null;
route.routeTypeId = 0;
route.routePoints = new List<DPoint>();
foreach (shPoint point in path.Points)
{
route.routePoints.Add(new DPoint(point.x, point.y));
}
return route;
}
public Route readRouteByName(String name)
{
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM routes WHERE route_name=:name", connection);
command.Parameters.Add(new NpgsqlParameter("name", name));
NpgsqlDataReader reader = command.ExecuteReader();
// get route (if there is one, if not YOU GET NOTHING! DEAL WITH IT!)
if (!reader.Read())
{
reader.Close();
return null;
}
Route route = new Route();
route.routePoints = new List<DPoint>();
route.guid = (reader[0] == DBNull.Value) ? null : (String)reader[0];
route.name = (reader[1] == DBNull.Value) ? null : (String)reader[1];
route.countryId = (reader[2] == DBNull.Value) ? -1 : (int)reader[2];
route.routeTypeId = (reader[3] == DBNull.Value) ? -1 : (int)reader[3];
route.owner = (reader[4] == DBNull.Value) ? null : (String)reader[4];
reader.Close();
// get route points
command.CommandText = "SELECT * FROM routes_points WHERE route_guid=:guid";
command.Parameters.Add(new NpgsqlParameter("guid", route.guid));
reader = command.ExecuteReader();
while (reader.Read())
{
DPoint point = new DPoint((double)reader[2], (double)reader[3]);
route.routePoints.Add(point);
}
reader.Close();
return route;
}
public List<Route> readRoutesStartingWith(String name)
{
List<Route> routes = new List<Route>();
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM routes WHERE route_name LIKE :name", connection);
command.Parameters.AddWithValue(":name", name + "%");
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Route route = new Route();
route.routePoints = new List<DPoint>();
route.guid = (reader[0] == DBNull.Value) ? null : (String)reader[0];
route.name = (reader[1] == DBNull.Value) ? null : (String)reader[1];
route.countryId = (reader[2] == DBNull.Value) ? -1 : (int)reader[2];
route.routeTypeId = (reader[3] == DBNull.Value) ? -1 : (int)reader[3];
route.owner = (reader[4] == DBNull.Value) ? null : (String)reader[4];
routes.Add(route);
}
reader.Close();
foreach (Route route in routes)
{
// get route points
command.CommandText = "SELECT * FROM routes_points WHERE route_guid=:guid";
command.Parameters.Add(new NpgsqlParameter("guid", route.guid));
reader = command.ExecuteReader();
while (reader.Read())
{
DPoint point = new DPoint((double)reader[2], (double)reader[3]);
route.routePoints.Add(point);
}
reader.Close();
}
return routes;
}
public void addPolygonEscapePoint(String polygonName, int edgeNum, DPoint escapePoint)
{
Polygon polygon = getPolygonByName(polygonName);
String query = "INSERT INTO polygon_openings_escape_points(polygon_guid, polygon_edge_num, route_x, route_y)"
+ " VALUES (:polygon_guid, :polygon_edge_num, :route_x, :route_y)";
NpgsqlCommand command = new NpgsqlCommand(query, connection);
command.Parameters.Add(new NpgsqlParameter("polygon_guid", polygon.guid));
command.Parameters.Add(new NpgsqlParameter("polygon_edge_num", edgeNum));
command.Parameters.Add(new NpgsqlParameter("route_x", escapePoint.x));
command.Parameters.Add(new NpgsqlParameter("route_y", escapePoint.y));
command.ExecuteNonQuery();
}
static async Task addWaypointRoutes(String connectionParams)
{
NpgsqlConnection connection = new NpgsqlConnection(connectionParams);
connection.Open();
NpgsqlTransaction transaction = connection.BeginTransaction();
try
{
// travel waypoints
//DPoint[] travelCoordinates = new DPoint[] { new DPoint(34.8496, 32.0996), new DPoint(34.8506, 32.099), new DPoint(34.8486, 32.099),
// new DPoint(34.8503, 32.0981), new DPoint(34.8487, 32.0983),
// new DPoint(34.8486, 32.099), new DPoint(34.851, 32.0997),
// new DPoint(34.8515, 32.0992), new DPoint(34.8514, 32.0983),
// new DPoint(34.8511, 32.0973), new DPoint(34.8501, 32.0977),
// new DPoint(34.8487, 32.0982), new DPoint(34.8478, 32.0986)};
DPoint[] travelCoordinates = new DPoint[] { new DPoint(34.848627448082, 32.0995901398799), new DPoint(34.8495876789093, 32.0996264945646),
new DPoint(34.8505747318268, 32.0996492162353),
new DPoint(34.850612282753, 32.0982768171897), new DPoint(34.8492550849915, 32.0980950409352),
new DPoint(34.8486435413361, 32.098308627997), new DPoint(34.8514652252197, 32.0996855708965),
new DPoint(34.851508140564, 32.0986403686134), new DPoint(34.8511004447937, 32.0973452100618),
new DPoint(34.8498612642288, 32.0977905648985), new DPoint(34.8485255241394, 32.0982631839831),
new DPoint(34.8479408025742, 32.0971543430385), new DPoint(34.8504567146301, 32.096090933751)};
// read all barrier coordinates
BarriersDB barriersDB = new BarriersDB(connection);
List<Barrier> barriers = barriersDB.readAllBarriers();
RouteGenerator generator = new RouteGenerator(connection);
// generate routes from waypoint to other waypoints
for (int i = 0; i < travelCoordinates.Count(); i++)
{
for (int j = 0; j < travelCoordinates.Count(); j++)
{
if (i == j) continue;
Route waypointRoute = await generator.generateRouteByShortestPath("WaypointToWaypoint_" + i + "," + j, travelCoordinates[i], travelCoordinates[j]);
generator.saveRouteToDB(waypointRoute);
}
}
// generate routes from waypoint to barriers
for (int i = 0; i < travelCoordinates.Count(); i++)
{
for (int j = 0; j < barriers.Count(); j++)
{
if (i == j) continue;
DPoint barrierCoordinates = new DPoint(barriers[j].x, barriers[j].y);
Route waypointRoute = await generator.generateRouteByShortestPath("WaypointToBarrier_" + i + "," + j, travelCoordinates[i], barrierCoordinates);
generator.saveRouteToDB(waypointRoute);
}
}
transaction.Commit();
}
catch (Exception exception)
{
try
{
transaction.Rollback();
}
catch (Exception rollbackException)
{
Console.WriteLine("Rollback failed :(");
}
}
connection.Close();
}