Resolve "Merge Damage views of different states into one"

does partially #71 (closed)

exposurelib/database.py

@@ -794,40 +794,36 @@ class SpatiaLiteExposure(SpatiaLiteDatabase, AbstractExposure):
         logger.debug("Table `AssetCountry` created")
 
     def create_damage_view(
-        self, view_name, entity, assessment_source_id, damage_state_id=None, overwrite=True
+        self, view_name, entity_type, assessment_source_id, damage_states, overwrite=True
     ):
         """
         Creates a view of damage assessments with the geometry from the respective entity.
-        The `source_id` selects the assessments to be selected. If `damage_state_id` is given,
-        the view selects assessments for only this state and delivers the respective
-        damage probabilities; if set to None, the view returns all damage states assuming
-        a deterministic damage assessment.
+        The `source_id` defines the source of the assessment to be selected. For all the
+        available `damage_states`, the view delivers the respective damage probabilities
+        in one single view. It also selects the damage type with the highest probability of
+        occurrence for the entity along with its probability value.
 
         Args:
             view_name (str):
                 Name of the view. The name will be inserted in the geometry table (mandatory)
                 in lower case so that all names added are distinct when transformed
                 to lower case.
-            entity (str):
+            entity_type (str):
                 Defines what types of entities to select:
                 `building` for selecting buildings,
-                `residual` for selecting residual exposure on tiles not covered by building
-                data,
+                `residual` for selecting residual exposure on incomplete tiles (tiles in
+                which not all the buildings are mapped)
                 `tile` for all entities within a tile (sum of buildings and residual exposure)
             assessment_source_id (int):
                 Defines for which `source_id` the damage values should be selected.
-            damage_state_id (int):
-                Defines for which `damage_state_id` the values should be selected. If set
-                to `None`, all `damage_state_id`s are selected to allow for deterministic
-                damage assessment to be displayed. `None` only works for `building` entities,
-                because deterministic damage assessment can only be provided for `building`s
-                and by sources other than the loss-calculator.
+            damage_states (list):
+                A list of all damage states to be included in the damage view.
             overwrite (bool):
                 If set, an potentially existing view of the same name is removed first.
         """
 
         # Check for viable parameter combination
-        if (damage_state_id is None) and (entity != "building"):
+        if (damage_states is None) and (entity_type != "building"):
             raise ValueError("Invalid parameter combination for damage view")
 
         # View names must be in lower case (SQLite requirement)
@@ -836,55 +832,57 @@ class SpatiaLiteExposure(SpatiaLiteDatabase, AbstractExposure):
         # If view already exists, overwrite or raise an exception if overwrite set to False
         if self.view_exists(name):
             if not overwrite:
-                raise Exception("View %s already exists" % name)
+                raise Exception(f"View {name} already exists")
             else:
                 self.delete_view(name)
 
         # Create view
-        if (entity == "building") or (entity == "residual"):
-            sql_statement = "CREATE VIEW %s AS " % name
-            sql_statement += "SELECT Assessment.id AS idx, Entity.geom AS geometry, "
-            sql_statement += "Entity.quadkey, "
-            if damage_state_id is None:
-                sql_statement += "Damage.damage_state_id "
-            else:
-                sql_statement += "Damage.damage_probability "
-            sql_statement += "FROM Entity "
-            sql_statement += "INNER JOIN Assessment "
-            sql_statement += "ON Assessment.entity_id = Entity.id "
-            sql_statement += "INNER JOIN Damage ON Damage.assessment_id = Assessment.id "
-            sql_statement += (
-                "WHERE Assessment.assessment_source_id = %d " % assessment_source_id
+        if (entity_type == "building") or (entity_type == "residual"):
+            damage_state_selection = ",".join(
+                [
+                    f"{damage_state_level}.damage_probability as {damage_state_level}_damage "
+                    for _, damage_state_level in damage_states
+                ]
             )
-            if entity == "building":
-                sql_statement += "AND Entity.osm_id IS NOT NULL "
-            else:
-                sql_statement += "AND Entity.osm_id IS NULL "
-            if damage_state_id is not None:
-                sql_statement += "AND Damage.damage_state_id = %d " % damage_state_id
-        else:  # entity == "tile"
-            sql_statement = "CREATE VIEW %s AS " % name
-            sql_statement += "SELECT Entity.id as idx, Entity.geom as geometry, "
-            sql_statement += "Entity.quadkey, Q.sum AS damage_probability "
-            sql_statement += "FROM "
-            sql_statement += "( "
-            sql_statement += "    SELECT Entity.quadkey, SUM(Damage.damage_probability) as sum "
-            sql_statement += "    FROM Entity "
-            sql_statement += "    LEFT OUTER JOIN Assessment "
-            sql_statement += "    ON Assessment.entity_id = Entity.id "
-            sql_statement += "    INNER JOIN Damage "
-            sql_statement += "    ON damage.assessment_id = Assessment.id "
-            sql_statement += (
-                "    WHERE Assessment.assessment_source_id = %d " % assessment_source_id
+
+            damage_state_tables = ""
+            for damage_state_id, damage_state_level in damage_states:
+                damage_state_tables += f"""
+                    INNER JOIN (
+                        SELECT damage_probability, assessment_id
+                        FROM Damage
+                        WHERE Damage.damage_state_id = {damage_state_id}
+                    ) AS {damage_state_level}
+                        ON {damage_state_level}.assessment_id = Assessment.id
+                                       """
+            selection_statement = (
+                f"WHERE Assessment.assessment_source_id = {assessment_source_id} "
             )
-            sql_statement += "    AND Damage.damage_state_id = %d " % damage_state_id
-            sql_statement += "    GROUP BY Entity.quadkey "
-            sql_statement += ") AS Q "
-            sql_statement += "INNER JOIN Entity ON Q.quadkey = Entity.quadkey "
-            sql_statement += "WHERE Entity.osm_id IS NULL"
+            if entity_type == "building":
+                selection_statement += "AND Entity.osm_id IS NOT NULL "
+            else:
+                selection_statement += "AND Entity.osm_id IS NULL "
 
-        self.connection.execute(sql_statement)
-        logger.debug("View %s created" % name)
+            sql_statement = f"""
+                Create VIEW {name} AS
+                SELECT Assessment.id AS idx, Entity.geom AS geometry,
+                Entity.quadkey, most_probable.damage_state_name,
+                {damage_state_selection}
+                FROM Entity
+                INNER JOIN Assessment ON Assessment.entity_id = Entity.id
+                {damage_state_tables}
+                INNER JOIN (
+                    SELECT MAX(damage_probability), damage_state_id,
+                    DamageState.damage_state_name, assessment_id
+                    FROM Damage
+                    INNER JOIN DamageState ON
+                        Damage.damage_state_id = DamageState.id
+                    GROUP BY assessment_id
+                )  AS most_probable ON most_probable.assessment_id = Assessment.id
+                {selection_statement}
+                """
+            self.connection.execute(sql_statement)
+            logger.debug("View %s created" % name)
 
         # Insert necessary entry to the `view_geometry_columns` table
         sql_statement = "INSERT INTO views_geometry_columns "
@@ -892,6 +890,7 @@ class SpatiaLiteExposure(SpatiaLiteDatabase, AbstractExposure):
         sql_statement += "f_table_name, f_geometry_column, read_only) "
         sql_statement += "VALUES ('%s', 'geometry', 'idx', 'entity', 'geom', 1)" % name
         self.connection.execute(sql_statement)
+        self.connection.commit()
         logger.debug("Geometry entry for view %s created" % name)
 
     def view_exists(self, view_name):