I have a QGIS graphical model workflow that I would like to be able to use with a connected PostGIS database. I am able to export the layers into the database and associated schema, but when I try and execute a SQL command in the PostgreSQL Execute and Load algorithm it gives the following error:
Prepare algorithm: qgis:postgisexecuteandloadsql_2
Running PostgreSQL execute and load SQL 'pgr_dijkstra' to find closest downstream road crossing from crossing [38/46]
Input Parameters:
{ DATABASE: 'stream_network', GEOMETRY_FIELD: 'geom', ID_FIELD: 'id', SQL: '-- build topology for waterbody crossing distance calculations\nALTER TABLE rd_crossing.rd_streams ADD COLUMN "source" integer; -- add source column for pg routing\nALTER TABLE rd_crossing.rd_streams ADD COLUMN "target" integer; -- add target column for pg routing\nSELECT pgr_createTopology(\'rd_crossing.rd_streams\', 0.00001, \'geom\', \'id\'); -- prepare stream layer using pg routing builder to build a network topology based on the geometry information.\n\n-- add pgr networking information\nALTER TABLE rd_crossing.rd_streams ADD COLUMN length float8; -- add length field for pgr cost\nALTER TABLE rd_crossing.rd_streams ADD COLUMN cost float8; -- add cost column\nALTER TABLE rd_crossing.rd_streams ADD COLUMN reverse_cost float8; -- add reverse_cost column\nUPDATE rd_crossing.rd_streams SET length = ST_Length(geom::geography);\n\n-- create cost and reverse costs for stream network based on fldir field\nUPDATE rd_crossing.rd_streams\nSET cost = length(geom::geography) \nWHERE fldir IN (20,32,34); -- not identified(both direction), flow direction and the digitizing direction are the same, and not applicable values\n\nUPDATE rd_crossing.rd_streams\nSET cost = 1000000\nWHERE fldir = 33; -- The flow direction and the digitizing direction are opposite.\n\nUPDATE rd_crossing.rd_streams\nSET reverse_cost = length(geom::geography)\nWHERE fldir IN (20, 34, 33);\n\nUPDATE rd_crossing.rd_streams\nSET reverse_cost= 1000000\nWHERE fldir =32;\n\n-- Begin Dijkstra algorthm shortest path query\nCREATE TABLE rd_crossing.pgr_routes AS -- Create table of edge = -1 results\nWITH all_pairs AS (\n -- all pairs of start and end geometries with IDs\n -- that get carried through so the routing results\n -- match with the pt IDs you know.\n SELECT f.id AS fid, f.geom as fgeom,\n t.id as tid, t.geom as tgeom\n FROM public.from_pts AS f,\n rd_crossing.to_pts AS t\n), vertices AS (\n SELECT fid, tid,\n (SELECT id -- proximity search for closest from vertex\n FROM rd_crossing.rd_streams_vertices_pgr\n ORDER BY the_geom <-> fgeom\n LIMIT 1) as fv,\n (SELECT id -- proximity search for closest to vertex\n FROM rd_crossing.rd_streams_vertices_pgr\n ORDER BY the_geom <-> tgeom\n LIMIT 1) as tv\n FROM all_pairs\n), pgr_result AS (\n SELECT fid, tid, pgr_Dijkstra(\n \'SELECT id, source, target, length AS cost, reverse_cost FROM rd_crossing.rd_streams\',\n fv, tv, \n directed := true\n ) from vertices\n)\nSELECT fid, tid, (pgr_dijkstra).* FROM pgr_result\nWHERE (pgr_dijkstra).edge = -1;\n\n-- min distance value of agg_cost results\nCREATE TABLE rd_crossing.pgr_routes_min AS\nSELECT DISTINCT ON (fid) fid, tid, agg_cost\nFROM rd_crossing.pgr_routes\nORDER BY fid, agg_cost;' }
This layer is invalid!
Please check the PostGIS log for error messages.
Error encountered while running PostgreSQL execute and load SQL 'pgr_dijkstra' to find closest downstream road crossing from pipeline crossing
Execution failed after 17.48 seconds
Below is the graphical model. The two far left hand boxes export a point feature and a line network into the database. These components work and the features are found in the database after execution of the model. The execute and load box doesn't work.
When I execute the script in the database via pgAdmin, it works fine. Below is my script:
-- build topology for waterbody crossing distance calculations
ALTER TABLE waterbody.water_streams ADD COLUMN "source" integer; -- add source column for pg routing
ALTER TABLE waterbody.water_streams ADD COLUMN "target" integer; -- add target column for pg routing
SELECT pgr_createTopology('waterbody.water_streams', 0.00001, 'geom', 'id'); -- prepare stream layer using pg routing builder to build a network topology based on the geometry information.
-- add pgr networking information
ALTER TABLE waterbody.water_streams ADD COLUMN length float8; -- add length field for pgr cost
ALTER TABLE waterbody.water_streams ADD COLUMN cost float8; -- add cost column
ALTER TABLE waterbody.water_streams ADD COLUMN reverse_cost float8; -- add reverse_cost column
UPDATE waterbody.water_streams SET length = ST_Length(geom::geography);
-- create cost and reverse costs for stream network based on fldir field
UPDATE waterbody.water_streams
SET cost = length(geom::geography)
WHERE fldir IN (20,32,34); -- not identified(both direction), flow direction and the digitizing direction are the same, and not applicable values
UPDATE waterbody.water_streams
SET cost = 1000000
WHERE fldir = 33; -- The flow direction and the digitizing direction are opposite.
UPDATE waterbody.water_streams
SET reverse_cost = length(geom::geography)
WHERE fldir IN (20, 34, 33);
UPDATE waterbody.water_streams
SET reverse_cost= 1000000
WHERE fldir =32;
-- Begin Dijkstra algorthm shortest path query
CREATE TABLE waterbody.pgr_routes AS -- Create table of edge = -1 results
WITH all_pairs AS (
-- all pairs of start and end geometries with IDs
-- that get carried through so the routing results
-- match with the pt IDs you know.
SELECT f.id AS fid, f.geom as fgeom,
t.id as tid, t.geom as tgeom
FROM public.from_pts AS f,
waterbody.to_pts AS t
), vertices AS (
SELECT fid, tid,
(SELECT id -- proximity search for closest from vertex
FROM waterbody.water_streams_vertices_pgr
ORDER BY the_geom <-> fgeom
LIMIT 1) as fv,
(SELECT id -- proximity search for closest to vertex
FROM waterbody.water_streams_vertices_pgr
ORDER BY the_geom <-> tgeom
LIMIT 1) as tv
FROM all_pairs
), pgr_result AS (
SELECT fid, tid, pgr_Dijkstra(
'SELECT id, source, target, length AS cost, reverse_cost FROM waterbody.water_streams',
fv, tv,
directed := true
) from vertices
)
SELECT fid, tid, (pgr_dijkstra).* FROM pgr_result
WHERE (pgr_dijkstra).edge = -1;
-- min distance value of agg_cost results
CREATE TABLE waterbody.pgr_routes_min AS
SELECT DISTINCT ON (fid) fid, tid, agg_cost
FROM waterbody.pgr_routes
ORDER BY fid, agg_cost;
Best Answer
Remove the comments from the SQL query.
We can see in the code that new lines are replaced by a space, so anything after the 1st comment becomes part of the comment.
That being said, you also can't have multiple statements in the query.