1

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. enter image description here 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;

enter image description here

1 Answer 1

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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