pgr_degree - Proposed

pgr_degree - Calculates the vertices degree

Warning

Proposed functions for next mayor release.

  • They are not officially in the current release.

  • They will likely officially be part of the next mayor release:

    • The functions make use of ANY-INTEGER and ANY-NUMERICAL

    • Name might not change. (But still can)

    • Signature might not change. (But still can)

    • Functionality might not change. (But still can)

    • pgTap tests have being done. But might need more.

    • Documentation might need refinement.

Availability

  • Version 3.4.0

    • New proposed function

Description

Calculates the degree of the vertices of an undirected graph

Signatures

pgr_degree( Edges SQL , Vertex SQL , [ dryrun ])
RETURNS SETOF (node, degree)
OR EMTPY SET
Example :

Extracting the vertex information 

SELECT * FROM pgr_degree(
  $$SELECT id FROM edges$$,
  $$SELECT id, in_edges, out_edges
    FROM pgr_extractVertices('SELECT id, geom FROM edges')$$);
 node  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)

Parameters

Parameter

Type

Description

Edges SQL

TEXT

Edges SQL as described below

Vertex SQL

TEXT

Vertex SQL as described below

Optional parameters

Parameter

Type

Default

Description

dryrun

BOOLEAN

false

  • When true do not process and get in a NOTICE the resulting query.

Inner Queries

Edges SQL

Column

Type

Description

id

BIGINT

Identifier of the edge.

Vertex SQL

Column

Type

Description

id

BIGINT

Identifier of the first end point vertex of the edge.

in_edges

BIGINT[]

Array of identifiers of the edges that have the vertex id as first end point .

  • When missing, out_edges must exist.

out_edges

BIGINT[]

Array of identifiers of the edges that have the vertex id as second end point .

  • When missing, in_edges must exist.

Result Columns

Column

Type

Description

node

BIGINT

Vertex identifier

degree

BIGINT

Number of edges that are incident to the vertex id

Additional Examples

Degree of a sub graph 

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

Dry run execution

To get the query generated used to get the vertex information, use dryrun => true .

The results can be used as base code to make a refinement based on the back end development needs. 

SELECT * FROM pgr_degree(
  $$SELECT id FROM edges WHERE id < 17$$,
  $$SELECT id, in_edges, out_edges
    FROM pgr_extractVertices('SELECT id, geom FROM edges')$$,
  dryrun => true);
NOTICE:
    WITH

    -- a sub set of edges of the graph goes here
    g_edges AS (
      SELECT id FROM edges WHERE id < 17
    ),

    -- sub set of vertices of the graph goes here
    all_vertices AS (
      SELECT id, in_edges, out_edges
    FROM pgr_extractVertices('SELECT id, geom FROM edges')
    ),

    g_vertices AS (
      SELECT id,
        unnest(
          coalesce(in_edges::BIGINT[], '{}'::BIGINT[])
          
          coalesce(out_edges::BIGINT[], '{}'::BIGINT[])) AS eid
      FROM all_vertices
    ),

    totals AS (
      SELECT v.id, count(*)
      FROM g_vertices AS v
      JOIN g_edges AS e ON (e.id = eid) GROUP BY v.id
    )

    SELECT id::BIGINT, coalesce(count, 0)::BIGINT FROM all_vertices LEFT JOIN totals USING (id)
    ;
 node  degree
------+--------
(0 rows)

Degree from an existing table

Dead ends

To get the dead ends: 

SELECT id FROM vertices
WHERE array_length(in_edges  out_edges, 1) = 1;
 id
----
  1
  5
  9
 13
 14
  2
  4
(7 rows)

That information is correct, for example, when the dead end is on the limit of the imported graph.

Visually node \(4\) looks to be as start/ending of 3 edges, but it is not.

Is that correct?

  • Is there such a small curb:

    • That does not allow a vehicle to use that visual intersection?

    • Is the application for pedestrians and therefore the pedestrian can easily walk on the small curb?

    • Is the application for the electricity and the electrical lines than can easily be extended on top of the small curb?

  • Is there a big cliff and from eagles view look like the dead end is close to the segment?

When there are many dead ends, to speed up, the Contraction - Family of functions functions can be used to divide the problem.

Linear edges

To get the linear edges: 

SELECT id FROM vertices
WHERE array_length(in_edges  out_edges, 1) = 2;
 id
----
  3
 15
 17
(3 rows)

This information is correct, for example, when the application is taking into account speed bumps, stop signals.

When there are many linear edges, to speed up, the Contraction - Family of functions functions can be used to divide the problem.

See Also

Indices and tables