1

Through postgresql, postgis, pgrouting and nodejs I am working on a project which basically finds a path between shops.

There are three tables in my database

1.CREATE TABLE public."edges" (id int, name varchar(100), highway varchar(100), oneway varchar(100), surface varchar(100), the_geom geometry, source int, target int);

2.CREATE TABLE public."edges_noded" (id bigint, old_id int, sub_id int, source bigint, target bigint, the_geom geometry, name varchar(100), type varchar(100), distance double precision);

3.CREATE TABLE public."edges_noded_vertices_pgr" (id bigint, cnt int, chk int, ein int, eout int, the_geom geometry); –

And the query by which I am finding path

client.query( "WITH dijkstra AS (SELECT * FROM pgr_dijkstra('SELECT id,source,target,distance AS cost FROM edges_noded',"+source+","+target+",FALSE)) SELECT seq, CASE WHEN dijkstra.node = edges_noded.source THEN ST_AsGeoJSON(edges_noded.the_geom) ELSE ST_AsGeoJSON(ST_Reverse(edges_noded.the_geom)) END AS route_geom_x,CASE WHEN dijkstra.node = edges_noded.source THEN ST_AsGeoJSON(edges_noded.the_geom) ELSE ST_AsGeoJSON(ST_Reverse(edges_noded.the_geom)) END AS route_geom_y FROM dijkstra JOIN edges_noded ON(edge = id) ORDER BY seq",(err,res)=>{ })

This query works for me but taking too much time for example, If I want to find a path between 30 shops then it is taking almost 25 to 30 sec which is too much.

After searching about this problem I found this link https://gis.stackexchange.com/questions/16886/how-can-i-optimize-pgrouting-for-speed/16888 In this link Délawenis is saying that use a st_buffer so it doesn't get all ways, but just the "nearby" ways:

So I tried to apply st_buffer in above query but not got any success. If someone has any idea plz help me with this problem.

If this approach is wrong please also tell me the right way.

M.HUSSAIN
  • 153
  • 8
  • 1
    Can you post a `CREATE TABLE` statement and an `EXPLAIN ANALYZE` from this query? – Jim Jones Jan 14 '20 at 07:53
  • There are three tables in my database, 1.CREATE TABLE public."edges" (id int, name varchar(100), highway varchar(100), oneway varchar(100), surface varchar(100), the_geom geometry, source int, target int); 2.CREATE TABLE public."edges_noded" (id bigint, old_id int, sub_id int, source bigint, target bigint, the_geom geometry, name varchar(100), type varchar(100), distance double precision); 3.CREATE TABLE public."edges_noded_vertices_pgr" (id bigint, cnt int, chk int, ein int, eout int, the_geom geometry); – M.HUSSAIN Jan 14 '20 at 07:59
  • 1
    could you add it to your question? Normally people don't look for info in the comments :-D – Jim Jones Jan 14 '20 at 09:17
  • does `CREATE INDEX idx_edges_noded_id ON edges (id);` help? – Jim Jones Jan 14 '20 at 09:59

0 Answers0