Added storing of quadkey and geometry of OBM buildings

Added storing of quadkey and geometry of OBM buildings to the gde_buildings table of the GDE Tiles database.

The quadkey allows to later search (to export with gde-exporter, for example) for the buildings associated with a particular tile.

The geometry is useful because the gde_buildings table of the GDE Tiles database is different from the obm_buildings table of the OBM Buildings database, because rabotnik-obm does not gather parts of buildings that are defined by a relation. The gde-core code compensates for that and carries out this collection. This means that retrieving the geometries from either obm_buildings or the OSM Replication database adds unnecessary complexity. In the future, the bringing together of parts of relations should be done by rabotnik-obm.

Tests have been adapted accordingly.

I've carried out a test run locally using a Greek admin unit and it worked well.

gdecore/database_storage.py

@@ -154,6 +154,7 @@ class DatabaseStorage:
         occupancy_case,
         aggregated_source_id,
         obm_buildings_building_classes,
+        obm_buildings_quadkeys_geometry,
         db_gde_tiles_config,
         db_table,
     ):
@@ -174,6 +175,18 @@ class DatabaseStorage:
             aggregated_source_id (int):
                 ID of the source of the aggregated exposure model associated with the building
                 classes of the OBM buildings in 'obm_buildings_building_classes'.
+            obm_buildings_quadkeys_geometry (Pandas DataFrame):
+                DataFrame indicating the footprints and quadkeys associated with the centroids
+                of the OBM buildings in 'obm_buildings_building_classes'. It is assumed to have
+                at least the following columns:
+                    osm_id (int):
+                        OpenStreetMap (OSM) ID of the building. If the building is represented
+                        by a relation, this is the ID of the relation.
+                    quadkey (str):
+                        String indicating the quadkey of the tile to which the centroid of the
+                        building belongs.
+                    geometry (str):
+                        Footprint of the building in Well-Known Text format and EPSG:4326.
             obm_buildings_building_classes (dict):
                 Dictionary containing the building classes and their probabilities for each OBM
                 building. Dictionary keys correspond to the OSM ID of the building. Each key
@@ -214,6 +227,9 @@ class DatabaseStorage:
                         SQL enumerated type describing the building occupancy cases.
                     data_unit_id (str):
                         ID of the data unit the OBM building belongs to.
+                    quadkey (str):
+                        Quadkey of the zoom-level 18 tile to which the centroid of the building
+                        belongs.
                     building_class_names (array of str):
                         Building class as per the GEM Building Taxonomy.
                     settlement_types (array of enum):
@@ -224,6 +240,8 @@ class DatabaseStorage:
                         class.
                     probabilities (array of float):
                         Probabilities of the OBM building belonging to each building class.
+                    geometry (PSQL geometry):
+                        Footprint of the OBM building.
         """
 
         sql_commands = {}
@@ -231,15 +249,17 @@ class DatabaseStorage:
         sql_commands["query"] = "SELECT COUNT(*) FROM %s "
         sql_commands["query"] += "WHERE (osm_id=%s AND aggregated_source_id=%s);"
 
-        sql_commands["update"] = "UPDATE %s SET (occupancy_case, data_unit_id, "
+        sql_commands["update"] = "UPDATE %s SET (occupancy_case, data_unit_id, quadkey, "
         sql_commands["update"] += "building_class_names, settlement_types, occupancy_subtypes, "
-        sql_commands["update"] += "probabilities) = ('%s','%s','%s','%s','%s','%s')"
-        sql_commands["update"] += " WHERE (osm_id=%s AND aggregated_source_id=%s);"
+        sql_commands["update"] += "probabilities, geometry) = "
+        sql_commands["update"] += "('%s','%s','%s','%s','%s','%s','%s','%s') "
+        sql_commands["update"] += "WHERE (osm_id=%s AND aggregated_source_id=%s);"
 
         sql_commands["insert"] = "INSERT INTO %s(osm_id, aggregated_source_id, occupancy_case, "
-        sql_commands["insert"] += "data_unit_id, building_class_names, settlement_types, "
-        sql_commands["insert"] += "occupancy_subtypes, probabilities) "
-        sql_commands["insert"] += "VALUES (%s, %s, '%s', '%s', '%s', '%s', '%s', '%s');"
+        sql_commands["insert"] += "data_unit_id, quadkey, building_class_names, "
+        sql_commands["insert"] += "settlement_types, occupancy_subtypes, probabilities, "
+        sql_commands["insert"] += "geometry) VALUES ( "
+        sql_commands["insert"] += "%s, %s, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');"
 
         db_gde_tiles = Database(**db_gde_tiles_config)
         db_gde_tiles.create_connection_and_cursor()
@@ -259,6 +279,9 @@ class DatabaseStorage:
                         db_table,
                         occupancy_case,
                         data_unit_id,
+                        obm_buildings_quadkeys_geometry.loc[
+                            obm_buildings_quadkeys_geometry["osm_id"] == osm_id, "quadkey"
+                        ].to_numpy()[0],
                         '{"%s"}'
                         % (
                             '", "'.join(
@@ -275,6 +298,9 @@ class DatabaseStorage:
                                 list(building_classes["probabilities"].to_numpy().astype(str))
                             )
                         ),
+                        obm_buildings_quadkeys_geometry.loc[
+                            obm_buildings_quadkeys_geometry["osm_id"] == osm_id, "geometry"
+                        ].to_numpy()[0],
                         osm_id,
                         aggregated_source_id,
                     )
@@ -288,6 +314,9 @@ class DatabaseStorage:
                         aggregated_source_id,
                         occupancy_case,
                         data_unit_id,
+                        obm_buildings_quadkeys_geometry.loc[
+                            obm_buildings_quadkeys_geometry["osm_id"] == osm_id, "quadkey"
+                        ].to_numpy()[0],
                         '{"%s"}'
                         % (
                             '", "'.join(
@@ -304,6 +333,9 @@ class DatabaseStorage:
                                 list(building_classes["probabilities"].to_numpy().astype(str))
                             )
                         ),
+                        obm_buildings_quadkeys_geometry.loc[
+                            obm_buildings_quadkeys_geometry["osm_id"] == osm_id, "geometry"
+                        ].to_numpy()[0],
                     )
                 )
             else:  # this should not occur