current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 2 down vote favorite

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;
share|improve this question
1  
If it's non-spatial (and this doesn't appear to be), you'll probably be better off asking on StackOverflow. –  GIS-Jonathan Oct 9 '14 at 9:48
    
Actually, it is an spatial table but I have highly simplified my question. –  Zia Oct 9 '14 at 9:58
1  
Let me edit my question rather. –  Zia Oct 9 '14 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... –  GIS-Jonathan Oct 9 '14 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. –  Craig Ringer Oct 9 '14 at 10:12

1 Answer 1

up vote 0 down vote

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.