Geographic Information Systems Asked by Michal De on June 16, 2021
I apologize for the delicate duplication of my TOPIC but I am still standing.
I have the table, which has information about pairs between source places and points (without geometry).
I have used this code so far:
drop table if exists dijkstra;
create table dijkstra as (
SELECT a."id_pk"
, a.mz_id
, a.poi_id
, case when a.nearest_mz!=a.nearest_poi then SUM(a.cost)
else 0
end AS "tmp_cost"
FROM
( SELECT "id_pk", mz_id, poi_id, nearest_mz, nearest_poi
, (pgr_dijkstra( 'select id::integer, source, target
, ST_Length(geom) as cost from ways',
"nearest_mz", "nearest_poi", false )).*
FROM pairs_mz_poi_node where id_pk <100 -- limit for test )
AS a
GROUP BY a."id_pk"
, a.mz_id
, a.poi_id
, a.nearest_mz
, a.nearest_poi)
;
It works fine, but now I have too many pairs and I have to calculate it faster than before.
I’m trying to add bounding box in my code.
I used this codes:
drop table if exists test;
create table test as (
SELECT a."id_pk"
, a.mz_id
, a.poi_id
, case when a.nearest_mz!=a.nearest_poi then SUM(a.cost)
else 0
end AS "tmp_cost"
FROM
( SELECT "id_pk", mz_id, poi_id, nearest_mz, nearest_poi
, pgr_dijkstra ( 'SELECT gid AS id, source, target,
ST_length(geom) AS cost FROM ways
WHERE geom && ST_Expand(
(SELECT ST_Collect(the_geom) FROM ways_vertices_pgr d
join pairs_mz_poi_node b
on b.nearest_mz=d.id
join pairs_mz_poi_node c
on c.nearest_poi=d.id
WHERE id IN( b.nearest_mz,c.nearest_poi ) )
nearest_mz i nearest_poi
, 1) ',
"nearest_mz", "nearest_poi", false )).*
FROM pairs_mz_poi_node where id_pk <100 )
AS a
GROUP BY a."id_pk", a.mz_id
, a.poi_id
, a.nearest_mz, a.nearest_poi);
And this:
drop table if exists test2;
Create table test2 as (
SELECT a."id_pk"
, a.mz_id
, a.poi_id
, case when a.nearest_mz!=a.nearest_poi then SUM(a.cost)
else 0
end AS "tmp_cost"
FROM
( SELECT "id_pk", mz_id, poi_id, nearest_mz, nearest_poi
, (pgr_dijkstra( 'WITH
buffer AS (SELECT id, ST_Buffer(the_geom, 1500) AS geom FROM ways_vertices_pgr)
select d.id::integer, d.source, d.target
, ST_Length(d.geom) as cost from ways d join buffer
on buffer.id=d.source or buffer.id=d.target
where d.geom=buffer.geom'
, "nearest_mz", "nearest_poi", false )).*
FROM pairs_mz_poi_node where id_pk <100 )
AS a
GROUP BY a."id_pk", a.mz_id
, a.poi_id
, a.nearest_mz, a.nearest_poi
);
I found this topic but I don’t know, how I can change ID number to nearest_mz and nearest_poi from my table.
Prepare the bbox and pass it as text into the edges_sql
:
Updated
SELECT nodes.id,
SUM(pgr.cost) AS "Aggregated Cost"
FROM (
SELECT pwn.id,
pwn."nearest_mz",
pwn."nearest_poi",
ST_Collect(vt1.geom, vt2.geom)::TEXT AS bbox
FROM pairs_mz_poi_node AS pwn
JOIN <vertice_table> AS vt1
ON pwn."nearest_mz" = vt1.id
JOIN <vertice_table> AS vt2
ON pwn."nearest_poi" = vt2.id
) AS nodes,
LATERAL PGR_Dijkstra(
'
SELECT id::INT,
source,
target,
ST_Length(geom) AS cost
FROM ways
WHERE geom && ST_Expand(''' || nodes.bbox || '''::GEOMETRY, 0.001)
',
nodes."nearest_mz",
nodes."nearest_poi",
FALSE
) AS pgr
GROUP BY
nodes.id
;
This works on the assumption that all relevant edges will tend to be within the bbox of the two vertices that you use as source and target; I added ST_Expand
to slightly increase that bbox, allowing for slight detours in all directions from and to the source and target. You may want to adjust the size parameter.
Correct answer by geozelot on June 16, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP