<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I think this is straightforward, if I understand you correctly...</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Assuming each year comprises a separate version of the data, have the year as a column in the table, which allows you to select by year, group by year in the database, on an indexed integer value, which is very fast.<br>
</div>
<div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
In terms of displaying as layers in QGIS, there are two approaches which should work, which one is better is something you'd need to work out for your use case:</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<ol>
<li>Open the table, apply a filter for just the desired year(s). Rename the QGIS layer as the year(s) to avoid confusion later. Apply whatever symbology you desire. Repeat for each year (or set of years) that you want to plot. This gives very good control of
each year (set) of data as a separate layer, but can be complicated with many years (layers) of data.</li><li>Open the table and set the layer symbology to categorised, then categorise by the year column. You have a single layer, but can set the symbology for each year and turn individual categories (years) on/off as desired. Not quite as powerful as completely
separate layers, but much easier when you have lots of categories. <br>
</li></ol>
<div>I'm not sure how much data you have, but if you have 100's of millions of records, using Postgis to manage geometries, Timescaledb to manage timeseries data and hstore or JSONB to manage multiple readings per sample (depending on just what your data are)
can give huge space & performance benefits. (I have just done this with a sensor dataset of 1.2b readings)</div>
<div><br>
</div>
<div>Something else I have done that you may find useful. You can write R functions to use as SQL using PL/R. This means you can select data & generate a plot directly from Postgres SQL - just run the SQL & then look at the output graphic. If you have the image
open & generate a new graphic via SQL, at least on Linux, the image viewer recognises the change & refreshes the screen. This is MUCH easier & faster than any other way I have tried to visualise my data directly from a database.
<br>
</div>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Cheers<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div id="Signature">
<div>
<div></div>
<div></div>
<div name="divtagdefaultwrapper" style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:; margin:0">
<div class="BodyFragment">
<div class="PlainText">Brent Wood<br>
<br>
Principal Technician, Fisheries<br>
NIWA<br>
DDI: +64 (4) 3860529</div>
</div>
</div>
</div>
</div>
</div>
<div>
<div id="appendonsend"></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font style="font-size:11pt" face="Calibri, sans-serif" color="#000000"><b>From:</b> Qgis-user <qgis-user-bounces@lists.osgeo.org> on behalf of Vanildo Heleno Pereira <vanildohp@gmail.com><br>
<b>Sent:</b> Saturday, November 14, 2020 12:11<br>
<b>To:</b> qgis-user@lists.osgeo.org <qgis-user@lists.osgeo.org><br>
<b>Subject:</b> [Qgis-user] Historical data in QGIS-PostGIS</font>
<div> </div>
</div>
<div>
<div dir="ltr">
<div dir="ltr">
<div>Hello everyone.</div>
<div>I would like to know if there is a possibility to work with historical versions of geographic data in QGIS / PostGIS?</div>
<div>The geometries need to be stored in a single database table (PostgreSQL) and you will receive data (new records from the same location) annually and I need to visualize each one in a different layer.</div>
<div>Thanks and regards.</div>
<div>Att.<br>
</div>
<div>
<div dir="ltr" class="x_gmail_signature">
<div dir="ltr"><font size="2">Vanildo Heleno</font>
<div><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<table style="width: 600px; font-size: 8pt; font-family: Arial" cellspacing="20">
<tbody>
<tr>
<td><a href="https://www.niwa.co.nz"><img src="https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png"></a>
</td>
<td>
<table style="width: 500px">
<tbody>
<span>Brent Wood<br>
<font color="#1793d2">Principal Technician - GIS and Spatial Data Management</font><br>
<font color="#1793d2">Programme Leader - Environmental Information Delivery</font><br>
+64-4-386-0529 | <br>
National Institute of Water & Atmospheric Research Ltd (NIWA)<br>
301 Evans Bay Parade Hataitai Wellington New Zealand<br>
<b>Connect with NIWA:</b> <a href="https://www.niwa.co.nz">niwa.co.nz</a> <a href="https://www.facebook.com/nzniwa">
Facebook</a> <a href="https://www.linkedin.com/company/niwa">LinkedIn</a> <a href="https://twitter.com/niwa_nz">
Twitter</a> <a href="https://www.instagram.com/niwa_science">Instagram</a> </span>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
<font face="Arial" size="1">To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third
parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems</font>
</body>
</html>