Utilizing Geospatial Databases for the Analysis and Management of Earthquake Hazards

Analysis plays a crucial role in decision-making during every project's planning phase, whether for new construction or reconstruction. This analysis depends on existing data—both cumulative statistics and specific knowledge about the mission at hand. Raw data becomes meaningful information through targeted queries, forming the foundation of the analytical process.

This project demonstrates the importance of spatial data analysis for urban and architectural planning in earthquake prevention scenarios using PostgreSQL. The analysis examines key elements of urban patterns—buildings, man-made structures, mobility networks, and public spaces—to improve the quality of a specific urban area. By evaluating these patterns as void and solid spaces, we can better understand the relationship between public gathering areas during disaster scenarios. The analysis includes examining buildings' material properties and their spatial relationships (proximity and adjacency). These database queries help establish the neighbourhood's current model, providing planners with essential information for decision-making for preparedness.

In our case, the neighborhood is divided into three zones. Each spatial property (buildings, roads, parks, etc.) is related to one of these zones.

Map of the neighborhood

---

What is a Geo-object?

First, we need to understand the definition and main characteristics of geo-objects.

In the topological definition, a geo-object is a spatial entity made out of simplexes, including both geometric and thematic properties.

When the model is generated by TINs (Triangulated Irregular Networks) the geometry is a topological geometry, and any kind of object (buildings, water bodies, networks, terrain models, etc.) is made out of simplicial simplexes.

A simplex is the simplest possible n-dimensional shape. source: https://mathatxaviers.wordpress.com/2023/01/17/brouwers-fixed-point-theorem-a-proof-involving-elementary-algebraic-topology/

But in our case, we use a polygon as a footprint to represent real-world entities, which generates the surfaces. In the class diagram given below, it can be seen that surfaces are fundamental atomic components of real-world entities (buildings, streets, open spaces, parks..). It should also be stated that the UML diagram doesn't represent each geo-object but rather classes. Within the footprint of a building (as a Polygon), you can query atomic geometric properties like points (vertices), edges (line segments), and the surface (the polygon itself). For instance, a building's surfaces consist of points and edges that form polygons. This structure allows us to query and analyze these atomic geo-objects—for example, to determine whether two buildings share an edge or which polygons overlap.

Class diagram of the neighborhood area. In principle, this shows a rough data model of the Neighborhood. (Tap to expand)

The hierarchy between classes indicates the relationship between each component, which structures the urban area that is called a Neighborhood. In order to query the spatial relationships (ST_Contains, ST_Overlaps, etc) between geo-objects, a geometric footprint is also added to the class diagram. In the UML diagram, the attributes which indicate the Geometry data type (see Buildings, Streets, OpenSpaces classes) can allow use these PostGIS functions.

---

Interacting with the Spatial Data

After gaining an insight about the basics, the next part can be discussed, which is the information that will emerge within the questions stated below. For this purpose, we use an extension for spatial data called PostGIS, which spatially enables PostgreSQL to model real-world objects in a database and answer questions of where and how far.*

Here are some PostGIS spatial functions used in this project:

ST_Distance: returns the minimum 2D Cartesian (planar) distance between two geometries.

ST_Area: returns the area of the surface if it is a polygon or multi-polygon.

ST_Transform: returns a new geometry with its coordinates transformed to a different spatial reference system. In this project default SRID is 4326. When we want to measure a spatial value like distances, the values display as degrees (longitude, latitude). ST_Transform converts the geometry to a projected coordinate system (e.g., SRID 3857) that uses metric units.

For more spatial queries, please check out the website.

---

Queries

Here are some SQL queries given below. Results from each question can be used as an analysis source, and further, this data can be visualized and used in particular emergency plans or to propose new regulations to municipalities.

1. How many of the total buildings are residential buildings?

SELECT 
    (SELECT COUNT(*) FROM buildings WHERE type = 'Residential') AS residential_count,
    (SELECT COUNT(*) FROM buildings) AS total_count;

The ratio of residential buildings in the neighborhood

%38 of the buildings are residential, nearly half of the total. This can help us to calculate approx. need for tents by calculating the people per apartment.

2. Assuming that the number of cracks indicates the risk of collapse of a building based on the scientific research (whether the crack is detected on load-bearing or not, 50 and above carries risk), determine the number of buildings in zone 1 within their owner and the material type that have the risk of collapse.

SELECT owner, building_material, COUNT(id) AS buildings_risk
FROM buildings
WHERE detected_crack > 50
AND zone = 1
GROUP BY owner, building_material

According to the dataset, in Zone 1, the buildings which has high risk mostly consist of steel structure

3. During an earthquake disaster, it’s important to gather people in open public spaces to avoid chaos and any potential harm. Considering this, what is the minimum distance between a building with risk and an open space such as a park or square?

   
   

SELECT DISTINCT ON (b.id)
  b.id AS building_id,
  o.name AS closest_open_space,
ST_Distance(ST_Transform(b.location, 3857), ST_Transform(o.area, 3857))
AS distance_meters
FROM buildings b
JOIN open_spaces o ON ST_Transform(b.location, 3857) IS NOT NULL
AND ST_Transform(o.area, 3857) IS NOT NULL
WHERE b.detected_crack > 50
ORDER BY b.id, distance_meters;

This output indicates all the high-risk buildings and the closest open space to each of them

Building 13 is considered a high-risk building and also the furthest building from any open space. The nearest park is 226 m away from it. This means more attention must be paid specifically to this building.

4. How many buildings built after the year 2000 are at risk? List them with their owners’ information.

SELECT id, owner, built_year
FROM buildings
WHERE built_year > 2000
AND detected_crack > 50
ORDER BY id;

List of buildings older built after 2000

5. We know that the detected crack directly on load-bearing elements such as columns, beams or carrier walls has a positive and direct correlation with a high rate of collapse. Based on this, list all the buildings with their structural materials which has been damaged on vertical load-bearing.

SELECT
   b.id,
   b.building_material,
   v.type AS damaged_component
FROM buildings b
JOIN vertical_carriers v ON b.id = v.building_id
WHERE v.has_crack = true
ORDER BY b.id;

About 33.3% of the buildings which damaged in the vertical components consist of hybrid structures (frame, shear wall, masonry systems)

6. Analysis of the solid-void ratio and querying its relationship is a must to gain insight about the site we’re working on. In this case, determine the ratio of solid-void in the neighborhood area. How much m2 of the neighborhood can be considered as void (open space)?

SELECT 
    SUM(ST_Area(ST_Transform(o.area, 32633))) AS total_open_space_m2,
    SUM(ST_Area(ST_Transform(n.area, 32633))) AS total_neighborhood_m2
FROM 
    open_spaces o,
    neighborhood n;

%12.8 of the total neighborhood area can be considered as open space. This indicates that the void-solid ratio is quite weak.

7. Based on this information, indicate the zone with the most gathering areas considering the m2 of its open spaces.

SELECT zone,
     SUM(ST_Area(ST_Transform(area, 32633))) AS total_ area_in_square_meters
FROM open_spaces
GROUP BY zone
ORDER BY total_area_in_square_meters DESC
LIMIT 1;

This indicates that Zone 2 has more potential to be used as the gathering area

It can be seen that nearly half of the open areas are in Zone 2. The distribution of parks and squares is not balanced across the total area.

8. Considering the size of a small disaster aid tent (approx. 2.5 m x 2.5 m), List all the squares and parks where at least 150 tents can be placed.

-- 2.5 x 2.5 = 6.25 m2 for a tent. For 150 tents, we need 6.25 x 150 = 937.5 m2 in total.
SELECT name, zone, 
       ST_Area(ST_Transform(area, 32633)) AS area_in_square_meters
FROM open_spaces
WHERE ST_Area(ST_Transform(area, 32633)) >= 937.5;

The list of open spaces that can be used as a gathering area

There are only 10 areas in the open space category in the neighborhood, and 6 of them can be used for temporary placement in emergency cases, since they are large enough to place 150 emergency tents.

9. Which residential buildings in zone 1, has both damaged in vertical and horizontal components? Please indicate the type of carriers

SELECT
   b.id AS building_id
   b.zone AS zone,
   'both' AS damaged_carrier_type
FROM buildings b
JOIN vertical_carriers v ON b.id = v.building_id
JOIN horizontal_carriers h ON b.id = h.building_id
WHERE b.zone = 1
AND b.type = 'Residential' 
AND v.has_crack = true
AND h.has_crack = true;

List of all the buildings carries a risk since both load-bearing (vertical & horizontal carriers) types are damaged

These buildings indicate a high risk since a damage has been detected in both types of load-bearing elements.

10. The number of detected_crack in the building table indicates a damage but we don't know whether the damage is on the surface or directly on load-bearing elements. For this purpose, we need to dive deeper and query the VerticalCarriers and HorizontalCarriers tables, which are dependent components of the buildings table. In our buildings table, every building has cracks. Therefore, when we examine for instance, a building and see there's no damage on either vertical or horizontal carriers, we can understand that the detected crack is probably on the façade, plaster, or any non-structural element. This helps us to analyse the risk potential. In this regard, show all the buildings where the detected crack isn't directly on the load-bearing element.

SELECT
   b.id AS building_id
   b.zone AS zone,
   'non-structural' AS damage_type
FROM buildings b
JOIN vertical_carriers v ON b.id = v.building_id
JOIN horizontal_carriers h ON b.id = h.building_id
WHERE v.has_crack = false
AND h.has_crack = false;

The list of all the buildings has less risk potential

These buildings can be categorized as less risky because the detected cracks are probably on the façade, plaster or decorative elements.

---

What can be done next?

In the previous part of the project, queries progressed from a broad urban scale down to a building scale, examining structural components. The system can query even finer details such as façade ornaments and window types. However, determining appropriate limits for the Level of Detail (LOD) or Level of Information (LOI) remains challenging, as the depth of hierarchy structure varies based on each research case.

As in many data-driven projects, the most important part is to be able to read the data and its outcome in a proper way. Otherwise, the effort that has been put in means nothing but a few tuples. In other words, knowledge is the crucial skill when we're working with data, therefore, the significance of experience can't be overlooked.

This project demonstrates the significant impact of data-driven methods in planning and decision-making processes, particularly when dealing with the complex, interconnected structure of urban environments.

NOTE: The database used in this project does not contain any real-world information and was created solely for this project.

References

• Obe, Regina O., and Leo S. Hsu. PostGIS in Action. Second edition. Shelter Island, NY: Manning Publications, 2015.

• https://postgis.net/docs/using_postgis_query.html

• https://inspire-mif.github.io/uml-models/approved/html/