How to fix "Error, query must return columns 'id', 'source', 'target' and 'cost'"?

Question

I have one spatial table with gid, FNODE_, TNODE_ and LENGTH column in my Postgresql/PostGIS database. I am using pgRouting. Problem is, when i try with shortest path query it shows following error. But it should not be. I double check my geographical database there is a connection between 505793 and 496514 nodes. But, whenever i query it shows following error.

SELECT * FROM shortest_path('
                SELECT "gid" as id,
                         "FNODE_"::integer,
                         "TNODE_"::integer,
                         "LENGTH"::double precision as cost
                        FROM roads_dist',
                505793, 496514, false, false);



ERROR:  Error, query must return columns 'id', 'source', 'target' and 'cost'


********** Error **********

ERROR: Error, query must return columns 'id', 'source', 'target' and 'cost'
SQL state: XX000

Thanks, in advance.

Answer 1

You have to rename the columns. The names have to match exactly.

SELECT * FROM shortest_path('
            SELECT "gid" as id,
                     "FNODE_"::integer as source,
                     "TNODE_"::integer as target,
                     "LENGTH"::double precision as cost
                    FROM roads_dist',
            505793, 496514, false, false);

Answer 2

Just complementing underdark response: pgRouting queries expect a few column names to do the routing for you.

There are people that are more knowledgable of pgRouting, but my guess is that it does not need the geospatial data to do the routing process. YOu've built the network using geospatial data, and this query will provide all parameters needed for it.

IF you check the documentation, each type of routing will need a specific set of columns (with those names) in order to work. As underdark said, it's just a simple rename inside the query.