How can I use spatially filtered PostGIS layers in QGIS without losing the ability to edit them in QGIS?

Question

I have some vector layers for a whole country stored in a PostGIS database. I want to use that data in different QGIS projects that stand for different regions of the country. In the regional QGIS projects I want to spatially filter the vector-layers to the specific regions.

In other words: different projects should use different filters but the same data source.

I used a spatial SQL Query in QGIS, but the problem is I can't edit that filtered layer anymore (blue dots), the edit button is greyed out.

This is the SQL query I used:

select d.*
from db_betriebsstellen as d
join bundeslaender as b
on ST_WITHIN(d.geom, b.geom) where b.name = ‘Bayern’ 

I remember using a spatial filter toolbar in Geomedia in the past. The specific spatial filter preference was saved in the Geoworkspace (.gws) which is in Geomedia the same thing like the Project in QGIS (.qgz).

Now I would like to have a similar functionality in QGIS / PostGIS. Any ideas on how to achieve that? Maybe a plugin?

Answer 1

QGIS won't let you edit query result sets - these are virtual tables, and would require complex logic implemented to guarantee a link to the respective physical sources of the query.

Instead, you want to use Views: while QGIS doesn't support that behavior above by itself, PostgreSQL does to some extend - if a View is simple enough (see the docs under "Updatable Views") PostgreSQL let's you run CRUD operations on them; in your case, instead of a JOIN that renders the View not update-able, you can use a simple EXISTS filter:

CREATE OR REPLACE VIEW db_betriebsstellen__bayern AS
  SELECT
    d.*
  FROM
    db_betriebsstellen AS d
  WHERE
    EXISTS (
      SELECT
        1
      FROM
        bundeslaender AS b
      WHERE
        ST_Intersects(d.geom, b.geom)
        AND
        b.name = 'Bayern'
    )
;

---

As long as your requirements are expressible with a single table as source (or another update-able View) and corresponding WHERE filter predicates, you can use the above approach. If you have more complex needs, you can make a View update-able using INSTEAD OF triggers - a little more complex to set up, but yielding ultimate control over specific data extraction and its CRUD permissions and abilities.

Answer 2

You can use a filter on the plain layer instead (rightclick the layer in the layer tree, "Filter"). For PostGIS layers you can use full SQL in there.

This is a filter query string that should resemble your query and you will be able to edit the filtered layer:

ST_WITHIN(geom, (SELECT geom FROM bundeslaender WHERE name = 'Bayern'))

If you would like a nice GUI for this, check out the https://plugins.qgis.org/plugins/spatial_filter/ plugin.