I am using OSM data to run pgr_dijkstra() for weighted-path calculation. But after importing OSM data using osm2pgsql I populated some additional columns into my ways table. So now it has gid serial, the_geom geometry, source integer, target integer, source_height double precision, target_height double precision plus some other columns. The source_height and target_height columns I have populated using the DEM of the region.
Simply running the pgr_dijkstra() on ways will give seq, id1 (node), id2 (edge), cost columns. What I want is to also determine the source_height or target_height for a particular edge if the node is equal to the source or target. I was trying to write a procedural function using PL/Python (although I don't know which procedural language is better) but couldn't complete.
Following is the attempt to create a PL/Python function. The problem is I don't know how to append all the rows coming from the for loop into a variable to return it as a table.
CREATE OR REPLACE FUNCTION test1(source int, target int, tablename text)RETURNS setof varchar # I am not sure if this return type is correctAS $$import psycopg2import osgeo.ogrstatement1 = ("select count(*) from pgr_dijkstra(' SELECT gid AS id, source::integer, target::integer, length_dem::double precision AS cost FROM %s', %s, %s, false, false) a LEFT JOIN %s b ON (a.id2 = b.gid)" % (tablename, source, target, tablename))statement2 = ("select seq, id1 AS node, id2 AS edge, cost, b.the_geom, b.source, b.target, b.slope_st_pt, b.slope_end_pt from pgr_dijkstra(' SELECT gid AS id, source::integer, target::integer, length_dem::double precision AS cost FROM %s', %s, %s, false, false) a LEFT JOIN %s b ON (a.id2 = b.gid)" % (tablename, source, target, tablename))count1 = plpy.execute(statement1)run2 = plpy.execute(statement2)len1 = count1[0]['count']row1 = [];for i in range(len1): if run2['node']==run2['source']: # Code to append node, edge, the_geom and slope_st_pt as slope to row1elif run2['node']==run2['target']: # Code to append node, edge, the_geom and slope_end_pt as slope to row1return row1; # Return row1 table just created using the for loop above$$ LANGUAGE 'plpythonu' VOLATILE;
أكثر...
Simply running the pgr_dijkstra() on ways will give seq, id1 (node), id2 (edge), cost columns. What I want is to also determine the source_height or target_height for a particular edge if the node is equal to the source or target. I was trying to write a procedural function using PL/Python (although I don't know which procedural language is better) but couldn't complete.
Following is the attempt to create a PL/Python function. The problem is I don't know how to append all the rows coming from the for loop into a variable to return it as a table.
CREATE OR REPLACE FUNCTION test1(source int, target int, tablename text)RETURNS setof varchar # I am not sure if this return type is correctAS $$import psycopg2import osgeo.ogrstatement1 = ("select count(*) from pgr_dijkstra(' SELECT gid AS id, source::integer, target::integer, length_dem::double precision AS cost FROM %s', %s, %s, false, false) a LEFT JOIN %s b ON (a.id2 = b.gid)" % (tablename, source, target, tablename))statement2 = ("select seq, id1 AS node, id2 AS edge, cost, b.the_geom, b.source, b.target, b.slope_st_pt, b.slope_end_pt from pgr_dijkstra(' SELECT gid AS id, source::integer, target::integer, length_dem::double precision AS cost FROM %s', %s, %s, false, false) a LEFT JOIN %s b ON (a.id2 = b.gid)" % (tablename, source, target, tablename))count1 = plpy.execute(statement1)run2 = plpy.execute(statement2)len1 = count1[0]['count']row1 = [];for i in range(len1): if run2['node']==run2['source']: # Code to append node, edge, the_geom and slope_st_pt as slope to row1elif run2['node']==run2['target']: # Code to append node, edge, the_geom and slope_end_pt as slope to row1return row1; # Return row1 table just created using the for loop above$$ LANGUAGE 'plpythonu' VOLATILE;
أكثر...