Entities, Assets and Taxonomy

The databases have three main tables: Taxonomy, Asset and Entity. The Taxonomy table defines the building type of an asset. The Asset table defines the quantities (population and monetary value), as well as the probability of an entity being of a certain building type. An Entity feature is either a building or a aggregated tile and defines the geographical location. So, one building or tile can have many assets, that each have exactly one taxonomy string.

In [1]:

import geopandas
from shapely import from_wkt, plotting
from databaselib import SpatiaLiteDatabase

import geopandas from shapely import from_wkt, plotting from databaselib import SpatiaLiteDatabase

In [2]:

db = SpatiaLiteDatabase('data/2023-011_Schorlemmer-et-al_ALB.Albania.db')
db.connect(init_spatial_metadata=False)

db = SpatiaLiteDatabase('data/2023-011_Schorlemmer-et-al_ALB.Albania.db') db.connect(init_spatial_metadata=False)

Info on an entity¶

In [3]:

quadkey = '120233300222032132'
db.cursor.execute(f"SELECT id, ST_AsText(geom) FROM entity WHERE quadkey = '{quadkey}'")
entity_id, geometry_wkt = db.cursor.fetchone()
geometry = from_wkt(geometry_wkt)

gdf = geopandas.GeoDataFrame({"id": [entity_id], "geometry": [from_wkt(geometry_wkt)]}, crs='EPSG:4326')    
gdf.explore()

quadkey = '120233300222032132' db.cursor.execute(f"SELECT id, ST_AsText(geom) FROM entity WHERE quadkey = '{quadkey}'") entity_id, geometry_wkt = db.cursor.fetchone() geometry = from_wkt(geometry_wkt) gdf = geopandas.GeoDataFrame({"id": [entity_id], "geometry": [from_wkt(geometry_wkt)]}, crs='EPSG:4326') gdf.explore()

Out[3]:

Make this Notebook Trusted to load map: File -> Trust Notebook

Info on an asset¶

In the following SQL query we select all assets that belong to the tile that we have selected before. We can retrieve the number of buildings (number), the population at night (night) and the structural value (structural).

In [4]:

db.cursor.execute(f"""
    SELECT taxonomy_id, entity_id, number, night, structural
    FROM asset
    WHERE entity_id = {entity_id}
    ORDER BY number
    """)

total_entity_population = 0
total_buildings = 0
for taxonomy_id, entity_id, number, night, structural in db.cursor:
    print(f"{taxonomy_id}: {number:.3f} buildings of this type in tile. Population at night: {night}")
    total_entity_population += night
    total_buildings += number
print(f"Total population: {total_entity_population:.3f}")
print(f"Total number of buildings: {total_buildings:.1f}")

db.cursor.execute(f""" SELECT taxonomy_id, entity_id, number, night, structural FROM asset WHERE entity_id = {entity_id} ORDER BY number """) total_entity_population = 0 total_buildings = 0 for taxonomy_id, entity_id, number, night, structural in db.cursor: print(f"{taxonomy_id}: {number:.3f} buildings of this type in tile. Population at night: {night}") total_entity_population += night total_buildings += number print(f"Total population: {total_entity_population:.3f}") print(f"Total number of buildings: {total_buildings:.1f}")

7113: 0.002 buildings of this type in tile. Population at night: 0.004556433
7143: 0.003 buildings of this type in tile. Population at night: 0.01918498
7169: 0.010 buildings of this type in tile. Population at night: 0.05755494
7165: 0.017 buildings of this type in tile. Population at night: 0.045564327
7167: 0.021 buildings of this type in tile. Population at night: 0.11510988
7168: 0.035 buildings of this type in tile. Population at night: 0.38274038
7144: 0.042 buildings of this type in tile. Population at night: 0.1093544
7112: 0.048 buildings of this type in tile. Population at night: 0.058603182
7163: 0.052 buildings of this type in tile. Population at night: 0.136693
7166: 0.061 buildings of this type in tile. Population at night: 0.15947515
7139: 2.354 buildings of this type in tile. Population at night: 2.900857
7164: 2.354 buildings of this type in tile. Population at night: 2.9008572
Total population: 6.891
Total number of buildings: 5.0

Getting a taxonomy string¶

In the following SQL query we select the building type of the last asset (ID 7164). The taxonomy string is formatted like the GEM Taxonomy.

In [5]:

db.cursor.execute(f"SELECT taxonomy_string FROM taxonomy WHERE id = {taxonomy_id}")
taxonomy_string = db.cursor.fetchone()[0]

print(f"ID {taxonomy_id} is GEM Taxonomy type {taxonomy_string}")

db.cursor.execute(f"SELECT taxonomy_string FROM taxonomy WHERE id = {taxonomy_id}") taxonomy_string = db.cursor.fetchone()[0] print(f"ID {taxonomy_id} is GEM Taxonomy type {taxonomy_string}")

ID 7164 is GEM Taxonomy type #/MCF/LWAL+CDN/H:1//RES////////

In [ ]: