1

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?

4
  • Am I correctly understand you would like to possess the whole set of attributes once again in the same Attribute table, for which the "ph"-value is NULL? What do you intend to do with geometry of those feature? Shall they be doubled or what?
    – Taras
    Commented Jul 23, 2020 at 9:30
  • What I want is to record the change in attributes of a set of geographic features over time. As I said, maybe I'm just approaching the problem incorrectly, but it seems to me that the way to do that is to have a set of geometries that get duplicated as new features for every new date. Preferably I would also be able to duplicate the values of some of the attributes (like "name") but not others (like "pH").
    – vorpal
    Commented Jul 23, 2020 at 10:17
  • How is your data stored and in which Format?
    – Taras
    Commented Jul 23, 2020 at 12:42
  • At the moment they are gpkg, but I'm still in the setup phase, so changing formats is easy if that helps.
    – vorpal
    Commented Jul 23, 2020 at 21:25

2 Answers 2

1

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

1
  • Thank you. I was hoping there would be something a bit more streamlined, because it will be an end user who will be the one who adds new data for each new date.
    – vorpal
    Commented Jul 23, 2020 at 21:32
0

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.

input

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.

result

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.