^{Originally posted by cvvergara May 16, 2022} I am reviewing the advanced topics of pgRouting's concepts documentation. I was originally written more than 10 years ago, and many changes have happened to pgRouting since the section was originally created. The first thing that changed about 8 years ago is that code is to be executed and included in the documentation, and currently in that section of the page, is not executed. So the code has some issues, which I will be pointing out. (links do not work on discussions)

• Alter table produces errors
• Need to cleanup
• No fcc column
• conclusion

Alter table produces errors

ALTER TABLE edge_table
    ADD COLUMN source integer,
    ADD COLUMN target integer,
    ADD COLUMN cost_len double precision,
    ADD COLUMN cost_time double precision,
    ADD COLUMN rcost_len double precision,
    ADD COLUMN rcost_time double precision,
    ADD COLUMN x1 double precision,
    ADD COLUMN y1 double precision,
    ADD COLUMN x2 double precision,
    ADD COLUMN y2 double precision,
    ADD COLUMN to_cost double precision,
    ADD COLUMN rule text,
    ADD COLUMN isolated integer;
ERROR:  column "source" of relation "edge_table" already exists

This works

ALTER TABLE edge_table
    ADD COLUMN cost_len double precision,
    ADD COLUMN cost_time double precision,
    ADD COLUMN rcost_len double precision,
    ADD COLUMN rcost_time double precision,
    ADD COLUMN to_cost double precision,
    ADD COLUMN rule text,
    ADD COLUMN isolated integer;
ALTER TABLE

need to cleanup

before creating the topology data needs to be cleaned up so that the whole process can be seen from scratch

UPDATE edge_table SET source = NULL, target = NULL;
UPDATE 18
DROP TABLE edge_table_vertices_pgr;
DROP TABLE
SELECT pgr_createTopology('edge_table', 0.000001, 'the_geom', 'id');
NOTICE:  PROCESSING:
NOTICE:  pgr_createTopology('edge_table', 1e-06, 'the_geom', 'id', 'source', 'target', rows_where := 'true', clean := f)
NOTICE:  Performing checks, please wait .....
NOTICE:  Creating Topology, Please wait...
NOTICE:  -------------> TOPOLOGY CREATED FOR  18 edges
NOTICE:  Rows with NULL geometry or NULL id: 0
NOTICE:  Vertices table for table public.edge_table is: public.edge_table_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createtopology
--------------------
 OK
(1 row)

No fcc column

An alternative that works is:

  speed_kmh = CASE WHEN id <=10 THEN 104                                                                                
                   ELSE 56 END,                                                                                         
  speed_mph = CASE WHEN id <= 10  THEN 65                                                                               
                   ELSE 35 END;                                                                                         

No data reference:

From the looks of this result:

SELECT pgr_analyzeGraph('mytab', 0.000002);
NOTICE:  Performing checks, pelase wait...
NOTICE:  Analyzing for dead ends. Please wait...
NOTICE:  Analyzing for gaps. Please wait...
NOTICE:  Analyzing for isolated edges. Please wait...
NOTICE:  Analyzing for ring geometries. Please wait...
NOTICE:  Analyzing for intersections. Please wait...
NOTICE:              ANALYSIS RESULTS FOR SELECTED EDGES:
NOTICE:                    Isolated segments: 158
NOTICE:                            Dead ends: 20028
NOTICE:  Potential gaps found near dead ends: 527
NOTICE:               Intersections detected: 2560
NOTICE:                      Ring geometries: 0
pgr_analyzeGraph
----------
   OK
(1 row)

Some data was used to create this section, like 158 isolated segments and 20028 dead ends, the sample data of the documentation only has 18 edges and 17 vertices. The reference to the data to create this result does not exist.

Conclusion

• The section of advanced documentation needs to be rewritten,
  • Using edge_table of the sample data
• The sample data to be reviewed in terms of columns actually needed. to keep them to a minimum.
• Using functions that now exist on the extension that do not modify the data base