pgr_degree – Proposed

pgr_degree — For each vertex in an undirected graph, return the count of edges incident to the vertex.

Advertencia

Funciones propuestas para la próxima versión mayor.

  • No están oficialmente en la versión actual.

  • Es probable que oficialmente formen parte del próximo lanzamiento:

    • Las funciones hacen uso de ENTEROS y FLOTANTES

    • Probablemente el nombre no cambie. (Pero todavía puede)

    • Es posible que la firma no cambie. (Pero todavía puede)

    • Probablemente la funcionalidad no cambie. (Pero todavía puede)

    • Se han hecho pruebas con pgTap. Pero tal vez se necesiten más.

    • Es posible que la documentación necesite un refinamiento.

Disponibilidad

  • Versión 3.4.0

    • Nueva función propuesta

Descripción

Calculates the degree of the vertices of an undirected graph

Firmas

pgr_degree(SQL de aristas , SQL de vértices, [dryrun])
REGRESA CONJUNTO DE (node, degree)
OR EMTPY SET
Ejemplo:

Extraer la información del vértice

pgr_degree can utilize output from pgr_extractVertices or can have pgr_extractVertices embedded in the call. For decent size networks, it is best to prep your vertices table before hand and use that vertices table for pgr_degree calls.

DROP TABLE IF EXISTS tmp_edges_vertices_pgr;
NOTICE:  table "tmp_edges_vertices_pgr" does not exist, skipping
DROP TABLE
CREATE TEMP TABLE tmp_edges_vertices_pgr AS
SELECT id, in_edges, out_edges
    FROM pgr_extractVertices('SELECT id, geom FROM edges');
SELECT 17
SELECT * FROM pgr_degree(
  $$SELECT id FROM edges$$,
  $$SELECT id, in_edges, out_edges
    FROM tmp_edges_vertices_pgr$$);
 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)

Parámetros

Parámetro

Tipo

Descripción

SQL de aristas

TEXT

SQL de aristas como se describe a continuación

Vertex SQL

TEXT

Vertex SQL as described below

Parámetros opcionales

Parámetro

Tipo

x Defecto

Descripción

dryrun

BOOLEAN

false

  • Cuando verdadero, no procesar y recibir un AVISO de la consulta resultante.

Consultas Internas

SQL aristas

Columna

Tipo

Descripción

id

BIGINT

Identificador de la arista.

Vertex SQL

Columna

Tipo

Descripción

id

BIGINT

Identificador del primer vértice de la arista.

in_edges

BIGINT[]

Arreglo de identificadores de las aristas que tienen el vértice id como primer punto final.

  • When missing, out_edges must exist.

out_edges

BIGINT[]

Arreglo de identificadores de las aristas que tienen el vértice id como segundo punto final.

  • When missing, in_edges must exist.

Columnas de Resultados

Columna

Tipo

Descripción

node

BIGINT

Identificador de vértice

degree

BIGINT

Number of edges that are incident to the vertex id

Ejemplos Adicionales

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

If you have a vertices table already built using pgr_extractVertices and want the degree of the whole graph rather than a subset, you can forgo using pgr_degree and work with the in_edges and out_edges columns directly.

Callejones sin salida

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 - Familia de funciones 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 - Familia de funciones functions can be used to divide the problem.

Ver también

Índices y tablas