postgis - How to give a table some geometry from a view on QGIS using a join?

So basically I've got this static table that people update information

Then I need it to give it some geometry from a view table. This geometry is dynamic, that's why can't just make a new geom column on the static table

Both layers have matching ids, but different info. I'd like to give the geometry from the view to the table, but when doing a join on QGIS, it doesn't offer the option of adding the geometry column.

Is there a way to join the geometry?

Answer

I'll add a simple example of what I think you need; I assume that

• a first set of users will update the 'static table' (attributes only) in QGIS and save back to DB


• a second set of users updates the 'dynamic view's base layers and its geometry independently


• the edits of the first user group must not not affect the geometry; edits to it will be ignored
  

If so, INSERT & DELETE statements need to be treated with care; you'd have to decide what to do with non-matching ids in the 'dynamic view'...and work out a similar approach with it if you want to be able to alter that views underlying data.

If you are not interested in the ability to update attributes and save changes to the DB by the first group of users, this is overkill...instead, simply create the view below without any triggers and load that into QGIS.

---

First off, let's set up a (very limited) log table to keep track of changes made to the 'static table':

CREATE TABLE _composite_log(
    _uid    SERIAL PRIMARY KEY,
    op_user TEXT,
    op_type TEXT,
    op_time TIMESTAMP,
    id_ref  INTEGER
);

Then set up the actual view that will be the working layer for the first user group (I'll call it and prefix all related items with composite):

CREATE OR REPLACE VIEW composite AS
    SELECT  a.*,
            b.geom
    FROM     AS a
    JOIN     AS b
      ON    a.id = b.id
    ORDER BY
            a.id

;

Then create the trigger procedure...:

CREATE OR REPLACE FUNCTION composite_delupsert_row()
    RETURNS TRIGGER AS

    $$
    BEGIN

        IF (TG_OP = 'UPDATE') THEN


            UPDATE 
                SET (, , ...) = (NEW., NEW., ...)
                WHERE id = OLD.id;

            INSERT INTO _composite_log(op_user, op_type, op_time, id_ref)
                VALUES (USER, 'UPDATE', NOW(), OLD.id);

            RETURN NEW;


        ELSIF (TG_OP = 'INSERT') THEN

            RETURN NULL;

        ELSIF (TG_OP = 'DELETE') THEN

            RETURN NULL;

        END IF;


    END;
    $$

    LANGUAGE plpgsql
;

...and the trigger on composite:

CREATE TRIGGER composite_delupsert
    INSTEAD OF UPDATE OR INSERT OR DELETE
    ON  composite

    FOR EACH ROW
    EXECUTE PROCEDURE composite_delupsert_row()
;

Done.

Note: The role that owns the trigger needs all relevant privileges on the 'static table'!

The trigger will catch any UPDATE (and INSERT & DELETE, but will currently simply ignore them; add the respective functionality yourself if you need) statements on thecomposite view and redirects the data to only; you need to refer each relevant column in the SET command, all not mentioned columns will stay as they were, and the NEW.geom value, even if changed, will be dropped silently if not handled elsewhere.

Additionally, the name of the operating user, the operation type, the timestamp and the (old) id of the changed row will be added to the _composite_log table.

Now you can add composite as your first groups working layer, and they can alter any attribute that is present in the 'static table' (and is mentioned in the SET column list) via QGIS Edit functionality. The geometry is 'permanently' joined, cannot be updated by the first group (edits to it will be ignored), but will reflect all changes made to it by the second group (in QGIS as soon as the canvas is updated, e.g. via zoom, pan, ...).

---

This is a very basic implementation, and if you are new to this the whole thing gets very large pretty soon. Take your time with it to see what's happening before implementing it in production. You can find some more basic examples (including views) in the docs, starting with 'Example 43.5'.