Create new date for temporal layer in QGIS

Question

I have a bunch of features (e.g. 'sampling point 1') and am storing a bunch of environmental data for each feature (e.g. 'pH', 'EC', etc.) plus a date. The new time manager for QGIS makes exploring this data great.
However, what I can't figure out is some simple way of inputting new data. Specifically I would like to recreate each feature for each new date so that the attribute table that originally looked something like this:
fid | name    | pH  | date
--- | ---     | --- | ---
1   | point 1 | 6   | 1990-01-01
2   | point 2 | 5   | 1990-01-01
3   | point 1 | 7   | 2020-05-01 
4   | point 2 | 7   | 2020-05-01

Could be updated to something like this:
fid | name    | pH   | date
--- | ---     | ---  | ---
1   | point 1 | 6    | 1990-01-01
2   | point 2 | 5    | 1990-01-01
3   | point 1 | 7    | 2020-05-01 
4   | point 2 | 7    | 2020-05-01
5   | point 1 | NULL | 2020-06-01
6   | point 2 | NULL | 2020-06-01

I can filter by the latest date, select the filtered features, copy the selected features, paste as a new temporary layer, change the date and attributes and then copy these features and paste them back into the original layer. But that seems very convoluted.
Is there a simpler way to create a new date for the features in a layer? Or am I just approaching the problem wrong way?

Taras · Accepted Answer

Another probably a more simpler solution can be achieved with the following workflow
Step 1. Select all features in that layer
Step 2. Start editing the layer
Step 3. Copy and paste selected features in the same layer
Step 4. Modify values in "pH" field with '' using the Field Calculator. Do not forget to click "Update selected"
Step 5. Update the "id" for new feature with maximum("id") + @row_number  using the Field Calculator. Do not forget to click "Update selected"
Step 6. Change the date with to_date('2020-06-' || format_date(to_date("date"), 'dd'))

Taras · Answer

I hope the Question was interpreted correctly.
In QGIS I can suggest using a Virtual Layer through Layer > Add Layer > Add/Edit Virtual Layer...
Let's assume there is one point layer "test" with its corresponding attribute table, see image below.

With the following query, it is possible to achieve the result, i.e. to duplicate all points in the same layer while modifying some values as well generating new ids.
SELECT *
FROM (
    -- number series
    WITH RECURSIVE generate_series(category) AS (
        SELECT conf.start
        FROM conf
        UNION ALL
        SELECT category + conf.step
        FROM generate_series, conf
        WHERE category + conf.step <= conf.stop
    ),

-- configuration
    conf AS (
        SELECT MAX(CAST("id"  AS INTEGER)) + 1 AS start,
               2*COUNT() AS stop,
               1 AS step
        FROM test
    )
    
    -- creating new table with the corresponding order of values
    SELECT t."osm_id",
           NULL AS "lastchange",
           gs.category AS id,
           t."lat",
           t."lon",
           t."Name",
           t.geometry 
    FROM generate_series AS gs, conf AS c
    JOIN test AS t ON t."id" = gs.category - c.stop/2
)

UNION ALL

SELECT *
FROM test

The output Virtual Layer will look like as following.

where

new values where set to NULL for the Attribute "lastchange"
added an "id"s for new values

Note that each location contains double geometries

References:

W3Schools | SQL CAST() Function

Create new date for temporal layer in QGIS

2 Answers

Add your own answers!

Ask a Question