Introduction to postgis: Using Shortest Path shooting star to calculate a directed path

 

In my spare time, I learned about pgrouting's path calculation for Directed Graphs,

There is an article on the official website to deal with the "one-way" path computing: http://www.pgrouting.org/docs/howto/oneway.html

Next I will prepare a map of Wuxi for relevant learning:

1. This is not an introduction to software installation. The optimal path of the software version used is the same as that of Introduction to postgis.
2. The map data on my hand is in the tab format. To ensure the integrity of the imported database data, I convert the tab to SHP and import it to the database. (Tab to SHP: The MapInfo software comes with a conversion tool, for example)
3. Load the converted SHP data into ArcMap:
4. Because we need to establish a topological relationship with the road in the database. For simple processing, here I combine the main road, secondary road, and overpass into a layer (National Highway is not considered for the time being ). Use the merge tool in toolbox to merge these layers:

5. After merging layers, perform topology processing:

   This process is only generic, and some operations need to be performed based on the actual situation, such:

6. After the data is processed, we can import the database. Here we use qgis to import:
7. After the database is imported, I rename it as the roads_polyline_merge table. The next step is more important.
8. Add fields to the table:

   1 ALTER TABLE wuxirouting.roads_polyline_merge ADD COLUMN source integer;
   2 ALTER TABLE wuxirouting.roads_polyline_merge ADD COLUMN target integer;
   3 ALTER TABLE wuxirouting.roads_polyline_merge ADD COLUMN cost double precision;
   4 ALTER TABLE wuxirouting.roads_polyline_merge ADD COLUMN reverse_cost double precision;
   5 ALTER TABLE wuxirouting.roads_polyline_merge ADD COLUMN x1 double precision;
   6 ALTER TABLE wuxirouting.roads_polyline_merge ADD COLUMN y1 double precision;
   7 ALTER TABLE wuxirouting.roads_polyline_merge ADD COLUMN x2 double precision;
   8 ALTER TABLE wuxirouting.roads_polyline_merge ADD COLUMN y2 double precision;

9. The following values are assigned to the source and target fields:

   set search_path = public,wuxirouting;
   SELECT assign_vertex_id('roads_polyline_merge',.00001,'the_geom','gid');

   Assign values to other fields:

   UPDATE wuxirouting.roads_polyline_merge SET cost = st_length(the_geom)*10000;
   UPDATE wuxirouting.roads_polyline_merge SET reverse_cost = 1000000;
   UPDATE wuxirouting.roads_polyline_merge SET x1 = st_x(st_startpoint(the_geom));
   UPDATE wuxirouting.roads_polyline_merge SET y1 = st_y(st_startpoint(the_geom));
   UPDATE wuxirouting.roads_polyline_merge SET x2 = st_x(st_endpoint(the_geom));
   UPDATE wuxirouting.roads_polyline_merge SET y2 = st_y(st_endpoint(the_geom));

10. Execute the path query and store it in a table so that the qgis displays:

    SELECT gid,the_geom,edge_id 
    into wuxirouting.routing 
    FROM shortest_path_astar('SELECT gid as id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM wuxirouting.roads_polyline_merge',149,158, true,true)as r,wuxirouting.roads_polyline_merge as w
    where r.edge_id=w.gid