YinaZ / AccessMapLite

0 stars 0 forks source link

Error "Unexpected Column 'cost' type" during query in app.js #2

Closed YinaZ closed 7 years ago

YinaZ commented 7 years ago

According to the tutorial (also what I did while developing), the grade COLUMN in the routing_info table is initialized like this:

ALTER TABLE routing_info ADD COLUMN grade NUMERIC(6, 4);

So the data type is numeric. But when I try to use the dijkstra algorithm from pgRouting and pass in grade COLUMN as the cost parameter like this (below is a part of the long query stored as string in app.js line 30):

CREATE OR REPLACE FUNCTION getRoute(source integer, target integer)
RETURNS TABLE(seq integer, path_seq integer, node bigint, edge bigint, cost double precision, agg_cost double precision) AS $$
  SELECT d.seq, d.path_seq, d.node, d.edge, d.cost, d.agg_cost 
  FROM pgr_dijkstra('SELECT osm_id AS id, source, target, grade AS cost FROM routing_info WHERE grade IS NOT NULL', $1, $2, false) AS d;
$$ LANGUAGE sql;

I get this following error when I use getRoute function:

osm=# SELECT * FROM getRoute(22986, 22992);
ERROR:  Unexpected Column 'cost' type. Expected ANY-NUMERICAL
CONTEXT:  PL/pgSQL function pgr_dijkstra(text,bigint,bigint,boolean) line 4 at RETURN QUERY
SQL function "getroute" statement 1

So currently I'm passing ST_Length(geom) + grade as the cost parameter like this to avoid the error, so that the app can still run:

CREATE OR REPLACE FUNCTION getRoute(source integer, target integer)
RETURNS TABLE(seq integer, path_seq integer, node bigint, edge bigint, cost double precision, agg_cost double precision) AS $$
  SELECT d.seq, d.path_seq, d.node, d.edge, d.cost, d.agg_cost 
  FROM pgr_dijkstra('SELECT osm_id AS id, source, target, ST_Length(geom) + grade AS cost FROM routing_info WHERE grade IS NOT NULL', $1, $2, false) AS d;
$$ LANGUAGE sql;

However, there should be a way to only pass in grade.

YinaZ commented 7 years ago

The issue is resolved by casting the grade column to type double precision.