Resolve "[BUG] Processing gaps crashes when a country contains too many tiles"

Closes #8 (closed)

\approve @laurens @shinde @tara

exposureinitializer/exposureinitializer.py

@@ -367,13 +367,15 @@ class ExposureInitializer:
     def process_gaps_in_exposure(self, country_iso_code):
         """
         In the case of missing exposure data for districts in the country, tiles containing
-        built area will not be covered with assets. This function detects such tiles and adds
+        built area will not be covered with assets. Also, tiles at the border with the centroid
+        located outside the border but inside a wider country border (e.g. coastal tiles) need
+        to be processed and filled with assets. This function detects such tiles and adds
         reference entities for these tiles and uses the country-average asset distribution when
-        adding the reference assets in the proportion of the tile built area compared ot the
+        adding the reference assets in the proportion of the tile built area compared to the
         total built area of the country.
         If only parts of the exposure model covers a district, this function will not detect
-        this. If significant parts of the country are not included in the model, the
-        initializer will only distribute the number of buildings in the model over the entire
+        this. Even if significant parts of the country are not included in the model,
+        Initializer will only distribute the number of buildings in the model over the entire
         country.
 
         Args:
@@ -381,6 +383,7 @@ class ExposureInitializer:
                 ISO 3166-1 alpha-3 code of the country
         """
 
+        # Get the assets of the average building in the country
         sql_statement = f"""
                 SELECT taxonomy_id, number, structural, night
                 FROM AssetCountry
@@ -389,53 +392,51 @@ class ExposureInitializer:
         self.exposure_db.cursor.execute(sql_statement)
         country_asset_list = self.exposure_db.cursor.fetchall()
 
+        # Calculate the total built area in the country
+        logger.info(f"Calculating the total built area in country {country_iso_code}.")
+        sql_statement = f"""
+             SELECT SUM(built_area_size) AS total_built_area
+             FROM {self.exposure_db.tile_view}
+             WHERE (country_iso_code = '{country_iso_code}')
+                 AND (built_area_size IS NOT NULL)
+            """
+        self.exposure_db.cursor.execute(sql_statement)
+        total_built_area = self.exposure_db.cursor.fetchone()[0]
+        logger.info(f"Total built area in country {country_iso_code}: {total_built_area}.")
+
         # Query explanation: Subquery A selects all tiles located in the country for the given
         # ISO code and the built area not being `NULL`. A is joined with the table
-        # `EntityReference` to identify the tiles without a reference entity, resulting in
-        # subquery named T. Query T is then used twice (first to retrieve the built-area size
-        # per selected tile and, second, to calculate the sum of the built-area sizes) and the
-        # proportion of the built-area size per tile compared to the total is computed.
-        country_boundary_id = country_iso_code + "-COUNTRY"
+        # `EntityReference` to identify the tiles without a reference entity and the joined
+        # query is used to retrieve the built-area size per selected tile.
+        logger.info("Retrieving all tiles with built area but without reference entities.")
         sql_statement = f"""
-            WITH T AS
+            SELECT A.quadkey, built_area_size
+            FROM
             (
-                SELECT A.quadkey, built_area_size
-                FROM
-                (
-                    SELECT quadkey, built_area_size, country_iso_code,
-                        {self.exposure_db.geometry_field}
-                    FROM {self.exposure_db.tile_view}
-                    WHERE (country_iso_code = '{country_iso_code}')
-                        AND (built_area_size IS NOT NULL)
-                        AND (built_area_size > 0)
-                ) AS A
-                LEFT JOIN EntityReference AS E
-                ON E.quadkey = A.quadkey
-                WHERE E.quadkey IS NULL
-            )
-            SELECT T.quadkey, T.built_area_size/SumQuery.total_sum as proportion
-            FROM T
-            CROSS JOIN
-            (
-                SELECT SUM(T.built_area_size) as total_sum FROM T
-            ) AS SumQuery
+                SELECT quadkey, built_area_size
+                FROM {self.exposure_db.tile_view}
+                WHERE (country_iso_code = '{country_iso_code}')
+                    AND (built_area_size IS NOT NULL)
+            ) AS A
+            LEFT JOIN EntityReference AS E
+            ON E.quadkey = A.quadkey
+            WHERE E.quadkey IS NULL
             """
         self.exposure_db.cursor.execute(sql_statement)
         tiles = self.exposure_db.cursor.fetchall()
         logger.info(
-            "%d tiles without reference entities found in boundary %s."
-            % (len(tiles), country_boundary_id)
+            f"{len(tiles)} tiles without reference entities found in {country_iso_code}."
         )
         if len(tiles) == 0:
             return
-
-        for quadkey, proportion in tiles:
+        for quadkey, built_area_size in tiles:
             # Check if entity exists in EntityReference and create if necessary
             entity_id = self.exposure_db.get_reference_entity_id(quadkey)
             if entity_id is None:
                 entity_id = self.exposure_db.insert_reference_entity(quadkey, country_iso_code)
 
             # Add the respective assets by proportion of built area to `AssetReference`
+            proportion = built_area_size / total_built_area
             reference_assets = [
                 [
                     entity_id,