pgr_separateCrossing

pgr_separateCrossing - From crossing geometries generates geometries that do not cross.

Disponibilidad

Versión 3.8.0

• Función promovida a oficial.

• Proposed function.

Descripción

This is an auxiliary function for separating crossing edges.

Firma

pgr_separateCrossing(Edges SQL, [tolerance, dryrun])

RETURNS (seq,id,sub_id,geom)

Ejemplo:

Get the segments of the crossing geometries

SELECT id, sub_id, ST_AsText(geom)
FROM pgr_separateCrossing('SELECT id, geom FROM edges')
ORDER BY id, sub_id;
 id | sub_id |         st_astext
----+--------+---------------------------
 13 |      1 | LINESTRING(3 3,3.5 3)
 13 |      2 | LINESTRING(3.5 3,4 3)
 18 |      1 | LINESTRING(3.5 2.3,3.5 3)
 18 |      2 | LINESTRING(3.5 3,3.5 4)
(4 rows)

Parámetros

Parámetro	Tipo	Descripción
SQL de aristas	TEXT	SQL de aristas como se describe a continuación

Parámetros opcionales

Parámetro	Tipo	x Defecto	Descripción
tolerance	FLOAT	0.01	Used in ST_Snap before ST_Split
dryrun	BOOLEAN	false	Cuando verdadero, no procesar y recibir un AVISO de la consulta resultante.

Consultas Internas

SQL aristas

Columna	Tipo	Descripción
id	ANY-INTEGER	(Opcional) identificador de la arista.
geom	LINESTRING	Geometría de la arista.

Ejemplos

Get the code for further refinement.

When there are special details that need to be taken care of because of the final application or the quality of the data, the code can be obtained On a PostgreSQL NOTICE using the dryrun flag.

SELECT *
FROM pgr_separateCrossing('SELECT id, geom FROM edges', dryrun => true);
NOTICE:
    WITH
        edges_table AS (
          SELECT id, geom FROM edges
        ),

    get_crossings AS (
      SELECT e1.id id1, e2.id id2, e1.geom AS g1, e2.geom AS g2, ST_Intersection(e1.geom, e2.geom) AS point
      FROM edges_table e1, edges_table e2
      WHERE e1.id < e2.id AND ST_Crosses(e1.geom, e2.geom)
    ),

    crossings AS (
      SELECT id1, g1, point FROM get_crossings
      UNION
      SELECT id2, g2, point FROM get_crossings
    ),

    blades AS (
      SELECT id1, g1, ST_UnaryUnion(ST_Collect(point)) AS blade
      FROM crossings
      GROUP BY id1, g1
    ),

    collection AS (
      SELECT id1, (st_dump(st_split(st_snap(g1, blade, 0.01), blade))).*
      FROM blades
    )

    SELECT row_number() over()::INTEGER AS seq, id1::BIGINT, path[1], geom
    FROM collection;
    ;
 seq | id | sub_id | geom
-----+----+--------+------
(0 rows)

Encontrando una intersección

In this example the original edge table will be used to store the additional geometries.

An example use without results

Routing from \(1\) to \(18\) gives no solution.

SELECT *
FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM edges', 1, 18);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
(0 rows)

Analyze the network for intersections.

SELECT
  e1.id id1, e2.id id2,
  ST_AsText(ST_Intersection(e1.geom, e2.geom)) AS point
FROM edges e1, edges e2
WHERE e1.id < e2.id AND ST_Crosses(e1.geom, e2.geom);
 id1 | id2 |    point
-----+-----+--------------
  13 |  18 | POINT(3.5 3)
(1 row)

The analysis tell us that the network has an intersection.

Prepare tables

Additional columns to control the origin of the segments.

ALTER TABLE edges ADD old_id BIGINT;
ALTER TABLE

Adding new segments.

Calling pgr_separateCrossing and adding the new segments to the edges table.

INSERT INTO edges (old_id, geom)
SELECT id, geom
FROM pgr_separateCrossing('SELECT id, geom FROM edges');
INSERT 0 4

Update other values

In this example only cost and reverse_cost are updated, where they are based on the length of the geometry and the directionality is kept using the sign function.

WITH
costs AS (
  SELECT e2.id, sign(e1.cost) * ST_Length(e2.geom) AS cost,
  sign(e1.reverse_cost) * ST_Length(e2.geom) AS reverse_cost
  FROM edges e1 JOIN edges e2 ON (e1.id = e2.old_id)
)
UPDATE edges e
SET (cost, reverse_cost) = (c.cost, c.reverse_cost)
FROM costs AS c WHERE e.id = c.id;
UPDATE 4

Actualizar la topología

Insert the new vertices if any.

WITH
new_vertex AS (
  SELECT ev.*
  FROM pgr_extractVertices('SELECT id, geom FROM edges WHERE old_id IS NOT NULL') ev
  LEFT JOIN vertices v using(geom)
  WHERE v IS NULL)
INSERT INTO vertices (in_edges, out_edges,x,y,geom)
SELECT in_edges, out_edges,x,y,geom FROM new_vertex;
INSERT 0 1

Actualizar la salida y el destino en la tabla de aristas.

/* -- set the source information */
UPDATE edges AS e
SET source = v.id, x1 = x, y1 = y
FROM vertices AS v
WHERE source IS NULL AND ST_StartPoint(e.geom) = v.geom;
UPDATE 4
/* -- set the target information */
UPDATE edges AS e
SET target = v.id, x2 = x, y2 = y
FROM vertices AS v
WHERE target IS NULL AND ST_EndPoint(e.geom) = v.geom;
UPDATE 4

The example has results

Routing from \(1\) to \(18\) gives a solution.

SELECT *
FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM edges', 1, 18);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         1 |      18 |    1 |    6 |    1 |        0
   2 |        2 |         1 |      18 |    3 |    7 |    1 |        1
   3 |        3 |         1 |      18 |    7 |   10 |    1 |        2
   4 |        4 |         1 |      18 |    8 |   12 |    1 |        3
   5 |        5 |         1 |      18 |   12 |   19 |  0.5 |        4
   6 |        6 |         1 |      18 |   18 |   -1 |    0 |      4.5
(6 rows)

Ver también

Topología - Familia de Funciones para una visión general de una topología para algoritmos de enrutamiento.

Índices y tablas

• Índice

• Página de Búsqueda