4

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’ 

enter image description here

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).

enter image description here

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

8
  • 1
    The JOIN might be the issue here. For spatial filtering you can use the layer's Filter feature with PostGIS functions. Sep 21, 2022 at 12:33
  • 1
    Wild guess but try: ST_WITHIN(geom, (SELECT geom FROM bundeslaender WHERE name = 'Bayern') as filter condition, does that work? Sep 21, 2022 at 14:09
  • 1
    Sorry, missed a closing ). Try ST_WITHIN(geom, (SELECT geom FROM bundeslaender WHERE name = 'Bayern')). Sep 21, 2022 at 14:22
  • 1
    Awesome, even for editing? I'll write it as an answer then. Sep 21, 2022 at 14:29
  • 1
    Yes, it works for editing !! After your answer I can also share my screenshot. Sep 21, 2022 at 14:31

2 Answers 2

4

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.

2
  • Thanks for your answer. The reality is like this: we have 50 project regions (and growing) and 20 layers for Germany. That would result in 1000 views, I guess it's a bit overwhelming to handle... Sep 21, 2022 at 14:15
  • @EnzoBaldini given your setup it looks to me like you will have to manage 1000 things one way or another - managing Views can be automated with functions and triggers, and you can manage them centralized in your DB.
    – geozelot
    Sep 21, 2022 at 17:23
3

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.

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.