2

I would like to use the QGIS modeller (or similar) to import SQL query results form my Database directly into my workspace.

I am asking as part of my job is to repetitively add vector and DTM layers from our Server and clip them to a smaller size based on the Site boundary provided by the client. I have now managed to at least add the vector layers correctly, Haven't had much luck with the Raster layers yet TBH, and buffer this Boundary by 1km and insert the Layer using DB manager.

The SQL I have used is as follows-

SELECT vectors_index__21.gid, vectors_index__21.fid,vectors_index__21.geom, st_contains(ST_Buffer(public.site_boundary.geom,1000)public.vectors_index__21.geom)

FROM public.site_boundary, public.vectors_index__21

WHERE st_contains(ST_Buffer(public.site_boundary.geom,1000), public.vectors_index__21.geom) IS TRUE

GROUP BY vectors_index__21.gid,vectors_index__21.fid,vectors_index__21.geom, site_boundary.geom

Can I add this code to a QGIS model or script that will do this task multiple times, ie. with different vector and raster layers so that the workspace basically sets itself up each time once the new site boundary has been added to my database and SQL syntax updated?

I have looked eveywhere for this type of functionality but so far my only option seems to be the DB manager to import my results to a workspace. Has anyone done this before or have any suggestions?Everyone loves a good bit of automation in their lives.

Improve this question
2
  • so, you have a bunch of vector tables in the DB and some DTMs on disk, and you get new boundaries (polygons?) as vectors from your client. whenever you get a new boundary (like, twice a day or sth.?), you have to clip all DB layers and the DTM layers to the boundary (and export as map?)?
    – geozelot
    Commented Sep 27, 2018 at 15:19
  • . I have vectors of roads and buildings and only need a small portion around each Site boundary loaded at a time rather than all of the layer in its entireity. I can do the work of buffering, clipping and inserting the data i need using DB manager but would like to know if there is any way of automating this task in a Model or something similar so that it could be as easy for my colleagues to do as press a button and the data they require from the database would be loaded in QGIS for them, based on the site boundary they provide
    – David Laverty
    Commented Sep 27, 2018 at 15:40

1 Answer 1

Reset to default
2

You can create a view in your database.

For example :

CREATE VIEW v_myview as (SELECT vectors_index__21.gid, vectors_index__21.fid,vectors_index__21.geom, st_contains(ST_Buffer(public.site_boundary.geom,1000)public.vectors_index__21.geom)
FROM public.site_boundary, public.vectors_index__21 
WHERE st_contains(ST_Buffer(public.site_boundary.geom,1000), public.vectors_index__21.geom) IS TRUE 
GROUP BY vectors_index__21.gid,vectors_index__21.fid,vectors_index__21.geom, site_boundary.geom)

In qgis 3, you can also add public.site_boundary in you project and right clic on it then "Update SQL Layer" Where you can copy-paste your query. But your layer will only be available in your project. When postgis wiew will be available to anyone.

BTW You should make your join explicit this way, but this is not the question :-) ex:

SELECT
  vectors_index__21.gid,
  vectors_index__21.fid,
  vectors_index__21.geom,
  st_contains(ST_Buffer(public.site_boundary.geom, 1000), public.vectors_index__21.geom)
FROM public.site_boundary
JOIN public.vectors_index__21 on st_contains(ST_Buffer(public.site_boundary.geom, 1000), public.vectors_index__21.geom)
GROUP BY vectors_index__21.gid, vectors_index__21.fid, vectors_index__21.geom, site_boundary.geom
Improve this answer
7
  • I cannot get the CREATE VIEW SQl to run. Do i do this in DB manager or in PgAdmin itself? Also your second SQL was a lot faster than my attempt, thank you verey much. I tried the QGIS 3 "update SQL layer" like you described, but this only updated the site.boundary layer's attributes table to that of my query results. none of the vector layers appeared in my workspace. I will keep trying but i am still just learning my way in SQL and fear i have a lot still to learn
    – David Laverty
    Commented Sep 27, 2018 at 13:17
  • It's hard to tell without the error itself. I rechecked and you can put any form of SQL in qgis DB manager.
    – Rémi Desgrange
    Commented Sep 27, 2018 at 14:55
  • syntax error at or near "public" LINE 1: ...contains(ST_Buffer(public.site_boundary.geom,1000)public.vec... ^
    – David Laverty
    Commented Sep 27, 2018 at 15:10
  • that is the error I am getting when i run your code in the DB manager. Im not quite sure why as it doesnt bother the Interpreter when it says 'public.' in the second piece of script you provided me. Can you tell me what CREATE VIEW is supposed to do? I am not familiar with this command
    – David Laverty
    Commented Sep 27, 2018 at 15:12
  • 1
    This is because your query have an error. You forgot a semi-column : st_contains(ST_Buffer(public.site_boundary.geom, 1000), public.vectors_index__21.geom)
    – Rémi Desgrange
    Commented Sep 27, 2018 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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