SQL error using pgRouting-2.1

Question

I follow the guide pgRouting 2.0 for Windows quick guide to try using pgRouting to do some simple routing task. I successfully installed Postgres 9.5, PostGIS 2.2 and pgRouting 2.1, and used my own shapefile to test (I could not download test data described in the guide).

As described in the guide, I used the following SQL to make the line vector file routable:

alter table public.tc_2000 add column source integer;
alter table public.tc_2000 add column target integer;
select pgr_createTopology('public.tc_2000', 0.0001, 'geom', 'gid');

and the resulting table looked fine:

I then used the following SQL code (modified from the guide) trying to route:

SELECT seq, id1 AS node, id2 AS edge, cost, geom
FROM pgr_dijkstra(
'SELECT id, source, target, st_length(geom) as cost FROM public.tc_2000',
1, 2, false, false
) as di
JOIN public.tc_2000 pt
ON di.id2 = pt.id ;

then the error poped out:

ERROR:  An expected column was not found in the query
HINT:  Please veryfy the column names: id, source, target, cost
CONTEXT:  PL/pgSQL function pgr_dijkstra(text,bigint,bigint,boolean,boolean) line 6 at assignment
********** Error **********

ERROR: An expected column was not found in the query
SQL state: P0001
Hint: Please veryfy the column names: id, source, target, cost
Context: PL/pgSQL function pgr_dijkstra(text,bigint,bigint,boolean,boolean) line 6 at assignment

I am new to SQL, and I don't know how to modify my code to get results.

Answer 1

You could try to set the type of your cost to float8 as it is expecting this data type. st_length(geom)::float8.

But didn´t you calculate your cost earlier? The length of the lines will lead to extremly huge costs (or an assumed speed of 1m per minute).