Add view to display number of buildings per entity in QGIS

The initializer creates a view for displaying the number of buildings per entity (here only tiles)

\approve @tara @shinde @laurens

exposureinitializer/exposureinitializer.py

@@ -437,10 +437,10 @@ class ExposureInitializer:
             # Add the respective assets by proportion of built area to `AssetReference`
             for taxonomy_id, number, structural, night in country_asset_list:
                 sql_statement = """
-                INSERT INTO AssetReference
-                (entity_id, taxonomy_id, number, structural, night)
-                VALUES (%d, %d, %f, %f, %f)
-                """ % (
+                    INSERT INTO AssetReference
+                    (entity_id, taxonomy_id, number, structural, night)
+                    VALUES (%d, %d, %f, %f, %f)
+                    """ % (
                     entity_id,
                     int(taxonomy_id),
                     float(number) * proportion,
@@ -450,6 +450,45 @@ class ExposureInitializer:
                 self.exposure_db.cursor.execute(sql_statement)
         self.exposure_db.connection.commit()
 
+    def create_view(self, country_iso_code):
+        """
+        Create a view of number of buildings per reference entity (tile) to be used in QGIS.
+        The view is named as the `country_iso_code` in lower case.
+
+        Args:
+            country_iso_code (str):
+                ISO 3166-1 alpha-3 code of the country
+        """
+
+        name = country_iso_code.lower() + "_number_buildings_per_entity"
+        sql_statement = """
+            CREATE VIEW IF NOT EXISTS %s AS
+            SELECT id as id_entity, EntityReference.geom as geometry,
+                   SUM(AssetReference.number) as number_buildings
+            FROM EntityReference
+            INNER JOIN AssetReference ON EntityReference.id = AssetReference.entity_id
+            WHERE EntityReference.country_iso_code = '%s'
+            GROUP BY AssetReference.entity_id
+            """ % (
+            name,
+            country_iso_code,
+        )
+        self.exposure_db.connection.execute(sql_statement)
+        logger.info("View %s created" % name)
+
+        # Insert necessary entry to the `view_geometry_columns` table
+        sql_statement = (
+            """
+            INSERT INTO views_geometry_columns
+            (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
+            VALUES ('%s', 'geometry', 'id_entity', 'entityreference', 'geom', 1)
+            """
+            % name
+        )
+        self.exposure_db.connection.execute(sql_statement)
+        self.exposure_db.connection.commit()
+        logger.debug("Geometry entry for view %s created" % name)
+
 
 def command_line_interface():
     """
@@ -559,20 +598,20 @@ def command_line_interface():
     logger.info("Processing start time: %s" % start_time)
 
     if exposure_database_object is not None:
-        distributor = ExposureInitializer(
+        initializer = ExposureInitializer(
             exposure_database_object, exposure_model, postgis_config
         )
         if command == "local":
-            distributor.exposure_db.create_tables()
-            distributor.retrieve_tile_set_and_boundaries(country_iso_code)
-            # distributor.create_view(country_iso_code)
+            initializer.exposure_db.create_tables()
+            initializer.retrieve_tile_set_and_boundaries(country_iso_code)
+            initializer.create_view(country_iso_code)
         else:
-            distributor.exposure_db.clean_up_reference_exposure(country_iso_code)
-            distributor.exposure_db.clean_up_asset_country(country_iso_code)
-        distributor.import_exposure(exposure_model, country_iso_code)
-        distributor.process_gaps_in_exposure(country_iso_code)
+            initializer.exposure_db.clean_up_reference_exposure(country_iso_code)
+            initializer.exposure_db.clean_up_asset_country(country_iso_code)
+        initializer.import_exposure(exposure_model, country_iso_code)
+        initializer.process_gaps_in_exposure(country_iso_code)
 
-        distributor.exposure_db.commit_and_close()
+        initializer.exposure_db.commit_and_close()
     else:
         logger.info("No database connection available")