About pgr_analyzeGraph

[cvvergara]

was looking at this documentation:
https://docs.pgrouting.org/latest/en/pgr_analyzeGraph.html
All the examples show the notices, but non show the contents of the vertices table.
And it so happens that ein & eout are allways NULL (found out that those are filled with pgr_analyzeOneWay)
terrible, terrible
I want to deprecate the function mainly because it alters tables by adding indexes and columns that might be unwanted by the user And maybe the user does not have the rights to those tables.

To be able to get rid of the function lets see in detail how everything can be replaced.

The "normal" process

from here

SELECT  pgr_createTopology('edge_table',0.001, clean := true);
NOTICE:  PROCESSING:
NOTICE:  pgr_createTopology('edge_table', 0.001, 'the_geom', 'id', 'source', 'target', rows_where := 'true', clean := t)
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)

SELECT  pgr_analyzeGraph('edge_table',0.001);
NOTICE:  PROCESSING:
NOTICE:  pgr_analyzeGraph('edge_table',0.001,'the_geom','id','source','target','true')
NOTICE:  Performing checks, please 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: 2
NOTICE:                            Dead ends: 7
NOTICE:  Potential gaps found near dead ends: 1
NOTICE:               Intersections detected: 1
NOTICE:                      Ring geometries: 0
 pgr_analyzegraph
------------------
 OK
(1 row)

Create the routing topology

instead of pgr_createTopology and/or pgr_createVerticesTable

Use these instructions
https://docs.pgrouting.org/dev/en/pgr_extractVertices.html#create-a-routing-topology
Note that the user is in charge of

• naming and creating the table as needed,
• adding indexes as see fit
  This step gives control to the user on what columns the vertex table will have. In the example all columns returned by pgr_extractVertices are saved in the vertex table.

Thus,

• pgr_CreateTopology needs explaination of when things are updated #880
  is no longer valid as the user is in charge.

Isolated segments

The value:

NOTICE:                    Isolated segments: 2

There has been an evolution with pgRouting, and now it has components family of functions,
Using pgr_connectedComponents

SELECT * FROM pgr_connectedComponents(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table'
);
seq | component | node
-----+-----------+------
   1 |         1 |    1
   2 |         1 |    2
   3 |         1 |    3
   4 |         1 |    4
   5 |         1 |    5
   6 |         1 |    6
   7 |         1 |    7
   8 |         1 |    8
   9 |         1 |    9
  10 |         1 |   10
  11 |         1 |   11
  12 |         1 |   12
  13 |         1 |   13
  14 |        14 |   14
  15 |        14 |   15
  16 |        16 |   16
  17 |        16 |   17

Note that it is more interesting to know the components than the isolated segments, because routing between components gives EMPTY SET as result regardless of the size of the components

Saving is so desired (this is not done by pgr_analyzeGraph)

ALTER TABLE vertices_table ADD column component BIGINT;
UPDATE vertices_table AS v SET component = c.component
FROM (SELECT * FROM pgr_connectedComponents('SELECT id, source, target, cost, reverse_cost FROM edge_table')) AS c                                
WHERE v.id = c.node;

Obtaining the number of isolated segments

SELECT count(*) 
FROM (SELECT component, count(distinct segment) FROM vertices_table, unnest(in_edges || out_edges) as segment
    group by component) a 
WHERE count = 1;
 count 
-------
     2
(1 row)

Obtaining the segments that belong to all components:

SELECT component, array_agg(distinct segment) FROM vertices_table, unnest(in_edges || out_edges) as segment
GROUP BY component;
 component |                array_agg                 
-----------+------------------------------------------
         1 | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}
         2 | {17}
        13 | {18}
(3 rows)

obtain the cnt values (aka degree of the vertices) of the whole graph

Obtaining cnt values of the whole graph

SELECT id, array_length(in_edges || out_edges, 1) AS cnt FROM vertices_table;
 id | cnt
----+-----
  1 |   1
  2 |   1
  3 |   2
  4 |   1
  5 |   1
  6 |   3
  7 |   4
  8 |   3
  9 |   1
 10 |   3
 11 |   4
 12 |   3
 13 |   1
 14 |   1
 15 |   2
 16 |   3
 17 |   2

Saving the values if desired on the vertex table and with the desired column name

ALTER TABLE vertices_table ADD column degree BIGINT;                                                                    
UPDATE vertices_table AS v SET degree = cnt                                                                             
FROM (SELECT id, array_length(in_edges || out_edges, 1) AS cnt FROM vertices_table) AS d                                
WHERE v.id = d.id;

Yet again,

• pgr_CreateTopology needs explaination of when things are updated #880
  is no longer valid as the user is in charge.

dead ends (aka leaf nodes on an undirected graph)

The value:

NOTICE:                            Dead ends: 7

Can be obtained:

SELECT count(*) FROM vertices_table WHERE degree <= 1; 
 count 
-------
     7
(1 row)

Not only that, it can be found which nodes are dead ends:

SELECT id FROM vertices_table WHERE degree <= 1; 
 id 
----
  1
  2
  4
  5
  9
 13
 14
(7 rows)

As degree is a characteristic of graph's nodes suppose that pgr_degree exist:

SELECT * FROM pgr_degree(
  $$SELECT id FROM edge_table WHERE id < 17$$,
  $$SELECT id, in_edges, out_edges
    FROM pgr_extractVertices('SELECT id, the_geom AS geom FROM edge_table')$$);
 id | degree
----+--------
   1 |   1
  2 |   1
  3 |   2
  4 |   1
  5 |   1
  6 |   3
  7 |   4
  8 |   3
  9 |   1
 10 |   3
 11 |   4
 12 |   3
 13 |   1
 14 |   1
 15 |   2
 16 |   3
 17 |   2
(17 rows)

gaps

NOTICE:  Potential gaps found near dead ends: 1

To know how many gaps:

WITH
graph AS (SELECT * FROM edge_table)
SELECT count(*)
FROM vertices_table AS v, edge_table AS e
WHERE v.geom <-> e.the_geom < 0.001 AND v.geom <-> e.the_geom != 0;
 count 
-------
     1
(1 row)

But information that is not given by the function is where are those gaps:

WITH                                                                                                                    
graph AS (SELECT * FROM edge_table)                                                                                     
SELECT v.id AS vid, e.id AS eid                                                                                         
FROM vertices_table AS v, edge_table AS e                                                                               
WHERE v.geom <-> e.the_geom < 0.001 AND v.geom <-> e.the_geom != 0;
 vid | eid 
-----+-----
   4 |  14
(1 row)

intersection

NOTICE:               Intersections detected: 1

Can be calculated as follows:

SELECT count(*) FROM edge_table AS a, edge_table AS b WHERE a.id < b.id AND st_crosses(a.the_geom, b.the_geom);
 count 
-------
     1
(1 row)

But information that the function does not give is which edges cross each other

SELECT a.id, b.id FROM edge_table AS a, edge_table AS b WHERE a.id < b.id AND st_crosses(a.the_geom, b.the_geom);
 id | id
----+----
 13 | 18
(1 row)

Ring geometries:

NOTICE:                      Ring geometries: 0

The value can be obtained as follows:

SELECT count(*) FROM edge_table WHERE ST_IsRing(the_geom); 
 count 
-------
     0
(1 row)

In case that there was a ring geometry, to know which on is it can be done as follows:

SELECT id FROM edge_table WHERE ST_IsRing(the_geom);
 id 
----
(0 rows)

Conclusions

• Dangerous to keep as it modifies the database, by creating tables and/or adding columns to tables and/or adding values to columns of tables.
• Create pgr_degree to calculate cnt (user decides if it is worth to save on vertex table)
• Use pgr_connectedComponents to find out for disconnected components (routing between different components gives EMTPY SET as result
• Knowing the "how many" rings its useless, User can directy use ST_isRing to find ring geometries if needed because of other reasons that are not pgRouting concerns. (pgRouting functions do not care about the geometry information)
• Knowing the "how many" gaps its useless, the user needs to still look for them and it will depend on the table names and column names. If you can find them then you can count them.
• Knowing the "how many" dead ends its useless, the user needs to still look for them and it will depend on the table names and column names. If you can find them then you can count them.
• Knowing the "how many" intersections its useless, the user needs to still look for them and it will depend on the table names and column names. If you can find them then you can count them and then verify if it needs to be nodded (aka a bridge geometry segment intersects the road geometry segment bellow it, but the intersection does not need to be nodded).

[cvvergara]

The generated topology using the alternate method

[cvvergara]

Edges do not change identifier value
Nodes changes identifier value
see image

From	to
1	5
2	6
3	10
4	15
5	7
6	11
7	1
8	3
9	16
10	8
11	12
12	17
13	9
14	2
15	4
16	13
17	14

[cvvergara]

Note for legwork:
mv v7 -> vX
mv v5 -> v7
mv v1 -> v5
mv vX -> v1

mv v14 -> vX
mv v17 -> v14
mv v12 -> v17
mv v11 -> v12
mv v6 -> v11
mv v2 -> v6
mv vX -> v2

mv v8 -> vX
mv v19 -> v8
mv v3 -> v10
mv vX -> v8

mv v15 -> VX
mv v4 -> v15
mv vX -> v4

mv v9 -> vX
mv v13 -> v9
mv v16 -> v13
mv vX -> v16