[Qgis-user] Historical data in QGIS-PostGIS

[Brent Wood]

I think this is straightforward, if I understand you correctly...

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.

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:

  1.  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.
  2.  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.

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)

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.


Cheers

Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529

________________________________
From: Qgis-user <qgis-user-bounces at lists.osgeo.org> on behalf of Vanildo Heleno Pereira <vanildohp at gmail.com>
Sent: Saturday, November 14, 2020 12:11
To: qgis-user at lists.osgeo.org <qgis-user at lists.osgeo.org>
Subject: [Qgis-user] Historical data in QGIS-PostGIS

Hello everyone.
I would like to know if there is a possibility to work with historical versions of geographic data in QGIS / PostGIS?
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.
Thanks and regards.
Att.
Vanildo Heleno

[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png]<https://www.niwa.co.nz>
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529 |
National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz<https://www.niwa.co.nz> Facebook<https://www.facebook.com/nzniwa> LinkedIn<https://www.linkedin.com/company/niwa> Twitter<https://twitter.com/niwa_nz> Instagram<https://www.instagram.com/niwa_science>
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20201114/488af19a/attachment.html>