I have two layers.

Layer one is a multipolygon layer (can be split to single polygons if needed).

Layer two contains points, lines and polygons.

Using layer one, I want to spatially filter layer two so that when I load this new layer into QGIS, I only see the features from layer two that intersect with layer one.

I am aware this is called a view in Postgres and I have conducted research on this, but unfortunately I do not understand any of the guidance or documentation. Is it possible someone can explain this in a simple fashion, or point me to some simple guidance? I am quite new to Postgres, so maybe need to understand some other basic concepts first?

Here are some crude drawings on paint to demonstrate what I'm trying to do:

Layer 1:

enter image description here

Layer 2:

enter image description here

Resulting layer:

enter image description here


If you haven't already, download the QGIS DB Manager Plugin.

In the DB Manager select the database you are connecting to and then click the SQL Window button, second along from the left.

In here type

Select * from LAYER2
JOIN LAYER1 on ST_Intersects(LAYER1.geom, LAYER2.geom)

I am assuming the geometry has been left as default (geom), but replace that with the geometry column name if not.

Then click execute to see if it works, if it does then click the create a view button.

This is probably the simplest way through QGIS, but I would recommend doing it through the admin program for PostgreSQL. We use PGadmin which is quite simple. It's the same query above but with 'Create Veiw veiwnamehere' before it. The * can be replaced with the feilds you'd like in your result.

  • Thanks! Having a problem creating the view. The query itself worked fine, but when i press create view and enter a name e.g. test, it throws the error: column geom specified more than once
    – JClarkson
    Nov 12 '18 at 15:50
  • 1
    You cant' do select * then. You have to specify the column names, so layer1.id, layer1.geom, layer1.other, layer2.id as id2. The resulting table cannot have two columns with the same name. Nov 12 '18 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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