Implement Trigger-Based Recalculation and Re-Labelling After Geometry Updates

[jravani]

Overview

City2TABULA currently generates a final output table containing:

• processed building geometry
• derived geometric attributes (footprint, area, height, volume, tilt/azimuth)
• classification outputs (building type / TABULA variant)

However, if a user edits or corrects the geometry (e.g. using QGIS), the associated derived attributes and building labels become outdated.
Examples:

• A footprint becomes smaller → volume, height, area, tilt/azimuth need recalculation
• A wall is removed → neighbour detection changes → party-wall logic changes
• A shape correction affects TABULA variant classification
• Missing geometry is inserted → downstream attributes must be generated

To support this workflow, City2TABULA needs database-level trigger functions that automatically recompute attributes whenever a geometry is inserted or updated.

This allows users to manually fix errors without breaking the integrity of the dataset.

---

Problem Description

The current pipeline:

• Computes attributes only once, at pipeline processing time
• Stores static results in final tables
• Does not react to geometry edits made by users afterwards

This leads to inconsistent data when:

• geometry is corrected in QGIS
• geometry for missing buildings is added
• invalid geometry is replaced
• attached-neighbour relationships change
• ML features dependent on geometry become stale
• the building classification becomes invalid (because geometry changed)

A mechanism is needed to ensure that the database stays self-consistent as soon as geometry changes.

---

Required Functionality

1. Trigger on Geometry UPDATE or INSERT

A PostgreSQL trigger should execute whenever:

• geom column of a building or child feature is modified
• a new building is added
• geometry is replaced due to correction

Example trigger events:

• AFTER UPDATE OF geom
• AFTER INSERT

2. Recalculate All Geometry-Derived Attributes

Triggered function must recompute:

• footprint area
• ground area
• building height
• volume
• tilt and azimuth per surface
• bounding box
• shape metrics
• child-feature attributes
• neighbour detection
• party wall recalculation

3. Refresh Building Classification / Labelling

If geometric attributes used for TABULA classification change, the trigger must:

• re-run the classification logic
• update the assigned TABULA variant
• store updated labels and confidence scores (if ML model used)

4. Update Dependencies

Geometry edits affect:

• party-wall detection
• attached neighbour relationships
• orientation & slope
• aggregated areas (roof, walls, ground)
• ML features exported for the classifier

These must be recomputed automatically.

5. Logging / Notifications

Trigger should log:

• which building was updated
• which attributes were recalculated
• whether classification changed
• warnings if recomputation fails

---

Expected Outcome

After this feature:

• Users may correct geometry directly in QGIS or any GIS tool
• The City2TABULA database updates itself automatically
• Derived attributes remain correct
• Building classification stays up-to-date
• ML data remains consistent
• The system becomes editable, maintainable, and extensible

This transforms City2TABULA into an interactive, self-updating spatial database, not just a static export.