Add "caretaker label" to info section of each tree

[julizet]

Background: Why this issue?

Some trees are tended and watered by the authorities of Berlin (Bezirke, SGA, Dienstleister). They`re focus is mainly on young trees which are watered in regular intervalls (everey single / every second week). Bezirk Mitte already sent us some Excel about their watering activites. Data should could be used to show users which trees are already nurtured.

Deliverables: How to fix the issue?

Do not process data manually each week but rather add a caretaker label with regard the nurtured trees.
--> pre-process Excel from authorities (python Skript)
--> add a carataker column to table in Postgres SQL
--> if possible: add expiring date column to table in Postgres SQL (so caretaker label disappears automatically in case the authorities "maintance" for a tree expired)
--> enlarge info-section in frontend

Output: Gain of fixing the issue?

• streamline watering activies
• show activities of authorities
• enrich content on website
• enrich data model

[Lisa-Stubert]

I updated the DB with a caretaker column in the trees table. For now I duplicated the table and named it "trees_new", so to use the caretaker label we have to redirect Gieß den Kiez on this table. I updated the table also for new trees, it includes now all trees that are recently available as Open Data ( #210 ).

[ff6347]

@Lisa-Stubert Does that include the materilized view?

[Lisa-Stubert]

No need of materialized views any more ( I've already deleted them). All the info is included in the new table "trees_new" now.

[ff6347]

Okay. So we just need to migrate the API to use the new table?

[Lisa-Stubert]

Yes, but the "trees_new" is 2 days old now. Maybe we should execute the SQL query for generating "trees_new" from "trees" again before migrating the API (because "trees" stores the actual precipitation data in column radolan_days, or am I wrong?)

[ff6347]

This query updates the new trees table with the caretaker label

CREATE VIEW trees_new0 as
SELECT * FROM trees
UNION ALL
SELECT * FROM trees_temp;
CREATE VIEW trees_new1 as
SELECT trees_new0.*, trees_extention."KENNZEICH",trees_extention."STANDORTNR"
FROM trees_new0
LEFT JOIN trees_extention
ON trees_new0.id=trees_extention.id_;
CREATE TABLE trees_new as
SELECT trees_new1.*, trees_watered_batch.username as caretaker
FROM trees_new1
LEFT JOIN trees_watered_batch
ON trees_new1.id=trees_watered_batch.tree_id;
DROP VIEW trees_new1;
DROP VIEW trees_new0;