2

I am developing a QGIS project template for a client that uses multiple PostGIS layers for viewing/editing structures that are identified from aerial images. In each new project derived from that template, users should be able to limit features from all PostGIS layers to the surrounding of the area of the specific project, to save from loading the whole tables into QGIS, which would get slower with ever increasing table sizes. Project areas are saved in a separete PostGIS table, so it would be easy to reference from QGIS, but the layer filter does not work with project variables or similar. My first idea was to use a project variable to tell QGIS which project area we are interested in in this specific project, then query the project_area layer for its geometry, then apply a layer filter to all other postgis layers, limiting to those features that intersect with a buffer around the project area.

What would be another solution to filter database layers for intersection with another layers geometry, without manually changing layer filters or hand-writing SQL in every project derived from the template?

0

1 Answer 1

2

First off: QGIS queries data within the canvas extent only; I doubt that you can get more performance than with this pre-selection on well-maintained (indexed) relations.


However, if your users connect with their own credentials, you could work with a View that selects from your AOI table by matching session_user (System Information Functions); this assumes your AOI table has a column username to match against:

CREATE VIEW <per_user_selection AS
    SELECT  a.*
    FROM    <features> AS a
    JOIN    <aoi> AS b
      ON    ST_Intersects(a.geom, b.geom)
    WHERE   b.username = SESSION_USER::TEXT
;

If you intend to also include the proximity of your features (you mention "[...] intersect with a buffer [...]"), use ST_DWithin instead!

You may need to add ROW_NUMBER() OVER() AS _uid_ to satisfy QGIS' unique key dependency.


Since that View is not automatically updateable (CREATE VIEW docs, see Updateable Views), you will need to have an INSTEAD OF trigger in place to reroute edits:

CREATE OR REPLACE FUNCTION <per_user_selection>_delupsert_func()
  RETURNS TRIGGER AS

  $TRIGGER$    
  BEGIN

    IF (TG_OP = 'UPDATE') THEN

      UPDATE <features>
        SET (<col1>, <col2>, ...) = (NEW.<col1>, NEW.<col2>, ...)
        WHERE <id> = OLD.<id>
      ;    

      RETURN NEW;

    ELSIF (TG_OP = 'INSERT') THEN

      INSERT INTO <features> (<col1>, <col2>, ...)
        VALUES (NEW.<col1>, NEW.<col2>, ...)
      ;

      RETURN NEW;

    ELSIF (TG_OP = 'DELETE') THEN

      DELETE FROM <features>
        WHERE <id> = OLD.<id>
      ;

      RETURN NULL;

    END IF;

  END;
  $TRIGGER$

  LANGUAGE plpgsql
;


CREATE TRIGGER <per_user_selection>_delupsert_trigger
    INSTEAD OF INSERT OR UPDATE OR DELETE ON <per_user_selection>
    FOR EACH ROW EXECUTE PROCEDURE <per_user_selection>_delupsert_func()
;

Note that this example is simple; you can extend both the View as well as the Trigger to include multiple relations instead of only <feature> (e.g. UNION ALL), but this likely makes querying slower compared to an individual View/Trigger on each relation.

1
  • 1
    Thank you for your detailed answer, @ThingumaBob! I wasn't aware QGIS is limiting the query to the canvas extent. Maybe then there is sth else slowing down QGIS here. Unfortunately, I dont have a user column on the AOI, because projects are handed over between users in a pipeline (preparation of project file > image georeferencing > image analysis / feature digitization). Also very interesting solution with the trigger. I need to test it out, never worked with triggers before. Will report back later. Jan 15, 2020 at 11:30

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.