7

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.

3

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.

  • I figured this was the only way to set up something that is automated. And can be used across multiple pojects. The combination of James' and your answers is great. – Russell Fulton Mar 18 '13 at 19:44
24

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

enter image description here

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.

  • 1
    I didn't know that the SQL window had the ability to 'Load as a new layer'. This is so useful! – Devdatta Tengshe Mar 18 '13 at 8:18
  • Thanks, I had missed then sql button in the database manager. This would be really good for testing and one offs -- getting the sql right and then viewing the results in QGIS. – Russell Fulton Mar 18 '13 at 19:41
  • I use it with template projects where I have my queries set up and the users just load these. I have created Python plugins, creating dialogs to take user input and then build the SQL using python to load my data into QGIS. You always get the latest results from your data and you don't need to create extra objects (views) in your database. Its also really good for spatial analyses to (buffers, overlaps etc). – James S Mar 19 '13 at 8:36
  • Any idea if this works for rasters? When clicking 'Load' under 'Load new layer' it seems to work for vectors but when using a raster table, no layer seems to be created. This is in QGIS3. – apricity May 25 '18 at 18:10

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.