QGIS2.6 postgis relation between tables

Question

I have three tables in Postgis:

1. points called location(gid (pkey),name) example (1,Paris)
2. temperatures named Temp(idTemp(pkey),TempValue,date) example (1,12,2015-01-01)
3. relation between the two: TemperatureAtLocation(gid,idTemp)

The aim is to display all temperatures detected in Paris in Qgis if possible when hovering or clicking on the city symbol or any user friendly solution. I checked if any actions available for vector layer exist but got little success.

Answer 1

I don't think you can or should do it with QGIS.

I'd do it on the database with a view. That is simple, and will work with any software, plus you don't have to recreate it every time.

You should be aware of how to do the join, but just FYI, here's pseudo code:

select * from Location as L
join TemperatureAtLocation as J on L.gid = J.gid
outer join Temp  as T on J.idTemp = T.idTemp

(I forget which types of joins, just play around till it works!). Obviously, when you have your select working, make it a view with 'create view vTempLocations as ...'.

Now QGIS will load that view as if it were a spatial table. You could also make some summary views by doing group by clause at the end, and doing average(tempvalue). Grouping by Day might be nice, then having a min/max/mean?

Answer 2

I finllay got my answer with a more accurate research through with website. At the moment it does what I expect, it's not very nice but it's ok. The main idea is that Qgis can manage by itself relational tables with it "relation" functionnality in project properties. I should have had a closer look at it. So here's the step by step description : http://blog.vitu.ch/10112013-1201/qgis-relations