pgr_findCloseEdges
¶
pgr_findCloseEdges
- Encuentra las aristas cercanas a una geometría puntual.
Disponibilidad
Versión 3.8.0
Error messages adjustment.
Opción
partial
se elimina.Función promovida a oficial.
Versión 3.4.0
Nueva función propuesta.
Descripción¶
pgr_findCloseEdges
- Función de utilidad que encuentra la arista más cercana a una geometría de puntos.
Las geometrías deben estar en el mismo sistema de coordenadas (tener el mismo SRID).
El código para realizar los cálculos puede obtenerse para realizar los ajustes específicos que necesite la aplicación.
EMPTY SET
se devuelve en ejecuciones en seco
Firmas¶
Resumen
[cap, dryrun]
(edge_id, fraction, side, distance, geom, edge)
Un punto¶
[cap, dryrun]
(edge_id, fraction, side, distance, geom, edge)
- Ejemplo:
Get two close edges to points of interest with
cap => 2
SELECT
edge_id, fraction, side, distance,
distance, ST_AsText(geom) AS point, ST_AsText(edge) As edge
FROM pgr_findCloseEdges(
$$SELECT id, geom FROM edges$$,
(SELECT geom FROM pointsOfInterest WHERE pid = 5),
0.5, cap => 2);
edge_id | fraction | side | distance | distance | point | edge
---------+----------+------+----------+----------+----------------+---------------------------
5 | 0.8 | l | 0.1 | 0.1 | POINT(2.9 1.8) | LINESTRING(2.9 1.8,3 1.8)
8 | 0.9 | r | 0.2 | 0.2 | POINT(2.9 1.8) | LINESTRING(2.9 1.8,2.9 2)
(2 rows)
Muchos puntos¶
[cap, dryrun]
(edge_id, fraction, side, distance, geom, edge)
- Ejemplo:
For each points of interests, find the closest edge.
SELECT
edge_id, fraction, side, distance,
ST_AsText(geom) AS point,
ST_AsText(edge) AS edge
FROM pgr_findCloseEdges(
$$SELECT id, geom FROM edges$$,
(SELECT array_agg(geom) FROM pointsOfInterest),
0.5);
edge_id | fraction | side | distance | point | edge
---------+----------+------+----------+----------------+---------------------------
1 | 0.4 | l | 0.2 | POINT(1.8 0.4) | LINESTRING(1.8 0.4,2 0.4)
6 | 0.3 | r | 0.2 | POINT(0.3 1.8) | LINESTRING(0.3 1.8,0.3 2)
12 | 0.6 | l | 0.2 | POINT(2.6 3.2) | LINESTRING(2.6 3.2,2.6 3)
15 | 0.4 | r | 0.2 | POINT(4.2 2.4) | LINESTRING(4.2 2.4,4 2.4)
5 | 0.8 | l | 0.1 | POINT(2.9 1.8) | LINESTRING(2.9 1.8,3 1.8)
4 | 0.7 | r | 0.2 | POINT(2.2 1.7) | LINESTRING(2.2 1.7,2 1.7)
(6 rows)
Parámetros¶
Parámetro |
Tipo |
Descripción |
---|---|---|
|
SQL de aristas descritas más adelante. |
|
punto |
|
La geometría del punto |
puntos |
|
Una matriz de geometrías de puntos |
tolerancia |
|
Distancia máxima entre geometrías |
Parámetros opcionales¶
Parámetro |
Tipo |
x Defecto |
Descripción |
---|---|---|---|
|
|
Limitar las filas de salida |
|
|
|
|
|
Consultas Internas¶
SQL aristas¶
Columna |
Tipo |
Descripción |
---|---|---|
|
ENTEROS |
Identificador de la arista. |
|
|
La geometría |
Columnas de resultados¶
Regresa conjunto de (edge_id, fraction, side, distance, geom, edge)
Columna |
Tipo |
Descripción |
---|---|---|
|
|
Identificador de la arista.
|
|
|
Valor entre <0,1> indica la posición relativa desde el primer punto de la arista. |
|
|
Valor en
|
|
|
Distancia del punto a la arista. |
|
|
Geometría original |
|
|
Geometría |
Ejemplos Adicionales¶
Un punto en la arista¶
SELECT edge_id, fraction, side, distance, ST_AsText(geom) geom, ST_AsText(edge) edge
FROM pgr_findCloseEdges(
$$SELECT 1 AS id, ST_MakeLine(ST_makePoint(2,0), ST_MakePoint(2,3)) AS geom$$,
ST_MakePoint(0,1.5), 5);
edge_id | fraction | side | distance | geom | edge
---------+----------+------+----------+--------------+-------------------------
1 | 0.5 | l | 2 | POINT(0 1.5) | LINESTRING(0 1.5,2 1.5)
(1 row)
![digraph D {
subgraph cluster0 {
label="data";
point [shape=circle;style=filled;color=green;fontsize=8;width=0.3;fixedsize=true];
point [pos="0,1.5!"]
sp, ep [shape=circle;fontsize=8;width=0.3;fixedsize=true];
sp[pos="2,0!"]
ep[pos="2,3!"]
sp -> ep:s
}
subgraph cluster1 {
label="results";
geom [shape=circle;style=filled;color=green;fontsize=8;width=0.3;fixedsize=true];
geom [pos="3,1.5!"]
np11 [shape=point;color=black;size=0;fontsize=8;fixedsize=true];
np11 [pos="5,1.5!";xlabel="fraction=0.5"];
sp1, ep1 [shape=circle;fontsize=8;width=0.3;fixedsize=true];
sp1[pos="5,0!"]
ep1[pos="5,3!"]
sp1:n -> np11:s [dir=none]
np11:n -> ep1:s
geom -> np11 [color=red,label="edge"];
}
}](_images/graphviz-3ffa1dee8d42ee90a88ffba10eebddf0b7a60f25.png)
El nodo verde es el punto original.
geom
Tiene el valor del punto original.La geometría
edge
, es una línea que conecta al punto original con el punto más cercano de la arista .El punto original está situado a la izquierda de la arista.
Ejecución en seco de un punto¶
Usando la consulta del ejemplo anterior:
Devuelve
CONJUNTO VACÍO
.dryrun => true
Genera un
NOTICE
de PostgreSQL con el código utilizado.The generated code can be used as a starting base code for additional requirements, like taking into consideration the SRID.
SELECT edge_id, fraction, side, distance, ST_AsText(geom) geom, ST_AsText(edge) edge
FROM pgr_findCloseEdges(
$$SELECT 1 AS id, ST_MakeLine(ST_makePoint(2,0), ST_MakePoint(2,3)) AS geom$$,
ST_MakePoint(0,1.5), 5, dryrun => true);
NOTICE:
WITH
edges_sql AS (SELECT 1 AS id, ST_MakeLine(ST_makePoint(2,0), ST_MakePoint(2,3)) AS geom),
point_sql AS (SELECT unnest('{01010000000000000000000000000000000000F83F}'::geometry[]) AS point),
results AS (
SELECT
id::BIGINT AS edge_id,
ST_LineLocatePoint(geom, point) AS fraction,
CASE WHEN ST_Intersects(ST_Buffer(geom, 5, 'side=right endcap=flat'), point)
THEN 'r'
ELSE 'l' END::CHAR AS side,
geom <-> point AS distance,
point,
ST_MakeLine(point, ST_ClosestPoint(geom, point)) AS new_line
FROM edges_sql, point_sql
WHERE ST_DWithin(geom, point, 5)
ORDER BY geom <-> point),
prepare_cap AS (
SELECT row_number() OVER (PARTITION BY point ORDER BY point, distance) AS rn, *
FROM results)
SELECT edge_id, fraction, side, distance, point, new_line
FROM prepare_cap
WHERE rn <= 1
edge_id | fraction | side | distance | geom | edge
---------+----------+------+----------+------+------
(0 rows)
Ejemplos de muchos puntos en una arista¶
Los nodos verdes son los puntos originales
La geometría
geom
, marcada como g1 y g2 son los puntos originalesLa geometría
edge
, marcada como edge1 y edge2 es una línea que conecta el punto original con el punto más cercano de la arista .
SELECT edge_id, fraction, side, distance, ST_AsText(geom) geom, ST_AsText(edge) edge
FROM pgr_findCloseEdges(
$$SELECT 1 AS id, ST_MakeLine(ST_makePoint(1,0), ST_MakePoint(1,3)) AS geom$$,
ARRAY[ST_MakePoint(0,2), ST_MakePoint(3,1)]::GEOMETRY[], 5);
edge_id | fraction | side | distance | geom | edge
---------+----------------+------+----------+------------+---------------------
1 | 0.666666666667 | l | 1 | POINT(0 2) | LINESTRING(0 2,1 2)
1 | 0.333333333333 | r | 2 | POINT(3 1) | LINESTRING(3 1,1 1)
(2 rows)
![digraph G {
subgraph cluster0 {
p1,p2 [shape=circle;style=filled;color=green;fontsize=8;width=0.3;fixedsize=true];
p1 [pos="0,2!"]
p2 [pos="3,1!"]
sp, ep [shape=circle;fontsize=8;width=0.3;fixedsize=true];
sp[pos="1,0!"]
ep[pos="1,3!"]
sp -> ep:s
}
subgraph cluster1 {
g1, g2 [shape=circle;style=filled;color=green;fontsize=8;width=0.3;fixedsize=true];
g1 [pos="4,2!"]
g2 [pos="7,1!"]
np11,np21 [shape=point;color=black;size=0;fontsize=8;fixedsize=true];
np11 [pos="5,2!";xlabel="fraction=0.63"];
np21 [pos="5,1!";xlabel="fraction=0.33"];
sp1, ep1 [shape=circle;fontsize=8;width=0.3;fixedsize=true];
sp1[pos="5,0!"]
ep1[pos="5,3!"]
sp1:n -> np21:s [dir=none]
np21:n -> np11:s [dir=none]
np11:n -> ep1:s
g1 -> np11 [color=red;label="edge"];
g2 -> np21 [color=red;label="edge"]
}
}](_images/graphviz-113cd343a60605166f45d1c605be02f223c9c1a1.png)
Ejecución en seco de muchos puntos¶
Devuelve
CONJUNTO VACÍO
.dryrun => true
No procesar la consulta
Generar un
NOTICE
de PostgreSQL con el código utilizado para calcular todas las columnas
SELECT edge_id, fraction, side, distance, ST_AsText(geom) geom, ST_AsText(edge) edge
FROM pgr_findCloseEdges(
$$SELECT 1 AS id, ST_MakeLine(ST_makePoint(1,0), ST_MakePoint(1,3)) AS geom$$,
ARRAY[ST_MakePoint(0,2), ST_MakePoint(3,1)]::GEOMETRY[], 5, dryrun=>true);
NOTICE:
WITH
edges_sql AS (SELECT 1 AS id, ST_MakeLine(ST_makePoint(1,0), ST_MakePoint(1,3)) AS geom),
point_sql AS (SELECT unnest('{010100000000000000000000000000000000000040:01010000000000000000000840000000000000F03F}'::geometry[]) AS point),
results AS (
SELECT
id::BIGINT AS edge_id,
ST_LineLocatePoint(geom, point) AS fraction,
CASE WHEN ST_Intersects(ST_Buffer(geom, 5, 'side=right endcap=flat'), point)
THEN 'r'
ELSE 'l' END::CHAR AS side,
geom <-> point AS distance,
point,
ST_MakeLine(point, ST_ClosestPoint(geom, point)) AS new_line
FROM edges_sql, point_sql
WHERE ST_DWithin(geom, point, 5)
ORDER BY geom <-> point),
prepare_cap AS (
SELECT row_number() OVER (PARTITION BY point ORDER BY point, distance) AS rn, *
FROM results)
SELECT edge_id, fraction, side, distance, point, new_line
FROM prepare_cap
WHERE rn <= 1
edge_id | fraction | side | distance | geom | edge
---------+----------+------+----------+------+------
(0 rows)
Encontrar como máximo dos rutas a un punto dado¶
Usando pgr_withPoints - Proposed
SELECT * FROM pgr_withPoints(
$e$ SELECT * FROM edges $e$,
$p$ SELECT edge_id, fraction, side
FROM pgr_findCloseEdges(
$$SELECT id, geom FROM edges$$,
(SELECT geom FROM pointsOfInterest WHERE pid = 5),
0.5, cap => 2)
$p$,
1, ARRAY[-1, -2]);
seq | path_seq | end_pid | node | edge | cost | agg_cost
-----+----------+---------+------+------+------+----------
1 | 1 | -2 | 1 | 6 | 1 | 0
2 | 2 | -2 | 3 | 7 | 1 | 1
3 | 3 | -2 | 7 | 8 | 0.9 | 2
4 | 4 | -2 | -2 | -1 | 0 | 2.9
5 | 1 | -1 | 1 | 6 | 1 | 0
6 | 2 | -1 | 3 | 7 | 1 | 1
7 | 3 | -1 | 7 | 8 | 1 | 2
8 | 4 | -1 | 11 | 9 | 1 | 3
9 | 5 | -1 | 16 | 16 | 1 | 4
10 | 6 | -1 | 15 | 3 | 1 | 5
11 | 7 | -1 | 10 | 5 | 0.8 | 6
12 | 8 | -1 | -1 | -1 | 0 | 6.8
(12 rows)
Una tabla de puntos de interés¶
Manejo de puntos fuera del gráfico.
Puntos de interés¶
Algunas veces las aplicaciones trabajan «sobre la marcha» comenzando desde una localización que no es un vértice en el grafo. Esas localizaciones, en pgRrouting se llaman puntos de interés.
La información necesaria en los puntos de interés es pid
, edge_id
, side
, fraction
.
En esta documentación habrá unos 6 puntos de interés fijos y se almacenarán en una tabla.
Columna |
Descripción |
---|---|
|
Un identificador único. |
|
Identificador del segmento mas próximo. |
|
Está a la izquierda, a la derecha o a ambos lados del segmento |
|
En qué parte del segmento se encuentra el punto. |
|
La geometría de los puntos. |
|
La distancia entre |
|
Un segmento que conecta la |
|
A point on segment |
CREATE TABLE pointsOfInterest(
pid BIGSERIAL PRIMARY KEY,
edge_id BIGINT,
side CHAR,
fraction FLOAT,
distance FLOAT,
edge geometry,
newPoint geometry,
geom geometry);
IF v > 3.4 THEN
Llenado de puntos de interés¶
Insertando los puntos de interés.
INSERT INTO pointsOfInterest (geom) VALUES
(ST_Point(1.8, 0.4)),
(ST_Point(4.2, 2.4)),
(ST_Point(2.6, 3.2)),
(ST_Point(0.3, 1.8)),
(ST_Point(2.9, 1.8)),
(ST_Point(2.2, 1.7));
Llenando el resto de la tabla.
UPDATE pointsofinterest SET
edge_id = poi.edge_id,
side = poi.side,
fraction = round(poi.fraction::numeric, 2),
distance = round(poi.distance::numeric, 2),
edge = poi.edge,
newPoint = ST_EndPoint(poi.edge)
FROM (
SELECT *
FROM pgr_findCloseEdges(
$$SELECT id, geom FROM edges$$,(SELECT array_agg(geom) FROM pointsOfInterest), 0.5) ) AS poi
WHERE pointsOfInterest.geom = poi.geom;
Any other additional modification: In this manual, point
UPDATE pointsOfInterest SET side = 'b' WHERE pid = 6;
Los puntos de interés:
SELECT
pid, ST_AsText(geom) geom,
edge_id, fraction AS frac, side, distance AS dist,
ST_AsText(edge) edge, ST_AsText(newPoint) newPoint
FROM pointsOfInterest;
pid | geom | edge_id | frac | side | dist | edge | newpoint
-----+----------------+---------+------+------+------+---------------------------+--------------
1 | POINT(1.8 0.4) | 1 | 0.4 | l | 0.2 | LINESTRING(1.8 0.4,2 0.4) | POINT(2 0.4)
4 | POINT(0.3 1.8) | 6 | 0.3 | r | 0.2 | LINESTRING(0.3 1.8,0.3 2) | POINT(0.3 2)
3 | POINT(2.6 3.2) | 12 | 0.6 | l | 0.2 | LINESTRING(2.6 3.2,2.6 3) | POINT(2.6 3)
2 | POINT(4.2 2.4) | 15 | 0.4 | r | 0.2 | LINESTRING(4.2 2.4,4 2.4) | POINT(4 2.4)
5 | POINT(2.9 1.8) | 5 | 0.8 | l | 0.1 | LINESTRING(2.9 1.8,3 1.8) | POINT(3 1.8)
6 | POINT(2.2 1.7) | 4 | 0.7 | b | 0.2 | LINESTRING(2.2 1.7,2 1.7) | POINT(2 1.7)
(6 rows)
Ver también¶
Índices y tablas