Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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);
        }
Esempio n. 3
0
        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);
        }
Esempio n. 4
0
        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);
        }
Esempio n. 5
0
        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();
        }
Esempio n. 6
0
        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;
        }
Esempio n. 7
0
        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;
        }
Esempio n. 8
0
        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;
        }
Esempio n. 9
0
        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;
        }
Esempio n. 10
0
 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();
 }
Esempio n. 11
0
        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();

        }