2

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 correct AS $$ import psycopg2 import osgeo.ogr statement1 = ("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[i]['node']==run2[i]['source']: # Code to append node, edge, the_geom and slope_st_pt as slope to row1 elif run2[i]['node']==run2[i]['target']: # Code to append node, edge, the_geom and slope_end_pt as slope to row1 return row1; # Return row1 table just created using the for loop above $$ LANGUAGE 'plpythonu' VOLATILE;
Improve this question
10
  • 1
    If it's non-spatial (and this doesn't appear to be), you'll probably be better off asking on StackOverflow. Commented Oct 9, 2014 at 9:48
  • Actually, it is an spatial table but I have highly simplified my question. Commented Oct 9, 2014 at 9:58
  • 1
    Let me edit my question rather. Commented Oct 9, 2014 at 10:03
  • 2
    You don't appear to be referencing the spatial component in your problem. The reason for suggesting SO is because there are a lot more experts in PL and Python there than there will be here, so you're more likely to get an answer (let alone one that works). Although Craig seems to think it's not possible so... Commented Oct 9, 2014 at 10:08
  • 1
    I think you're better off posting a properly detailed question on stackoverflow, with the error message, the code you've attempted in PL/Python so far, and an explanation of what you are attempting to do in words. Commented Oct 9, 2014 at 10:12

1 Answer 1

Reset to default
1

Thanks to Craig Ringer for suggestions.

We don't actually need any procedural function to accomplish the task stated above (In fact it would be an ill-practice to prepare a procedural function for the same).

The following SQL code is equally solving the problem.

with x as ( 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 tablename', 350, 329, false, false) a LEFT JOIN tablename b ON (a.id2 = b.gid) ) select seq, node, edge, cost, the_geom, case when node=source then slope_st_pt else slope_end_pt end as slope from x
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.