QGIS: using postgis can I define a layer using sql?

Question

I have a vector layer (call it master) that is stored in postgis and consists of a number of line segments.

I also have a collection of sub layers that consist of subsets of the master layer. I can set up a 'link' table master_id, layer_name the says which segments belong to which sub layer -- some on the edges will be common.

Can I specify a layer using an join on the master and link tables?

Alternatively can I use a view at the postgres level to do this. I have never used views or stored procedures although I know the general principles.

Answer 1

You can write query layers without creating views in the PostGIS database. You need to use DBManager. The query can be pretty much any SQL which you would write in pgAdmin.

If you choose the database you want to use, and then select the SQL Window button.

In the top section you can enter you SQL to create the query. If you hit Execute, you will see the results of the query in the result section.

Then you can tick Load as new layer. This allows you to specify the column with unique integer values and the geometry column. You can then add a layer name, and hit Load now!. This will display the query in QGIS (the query in the image below doesn't join two tables, but it should illustrate how to get a query layer into QGIS).

I found this page useful if you need to create a unique id to load the layer into QGIS:

In PostGIS is it possible to create a View with a unique ID?

This query is then saved in the project file and will update whenever you change the underlying data.

Answer 2

Your best option is to create a View in your PostGIS database. The view can be displayed/queried in Qgis without any issues.

The exact syntax to make a view from a join will depend on the schema of your data.

I would recommend that you go through the Join Between Tables page for details on how to make a join. The Create View page will show you how to create a new view.