4 Exercise 2 Data Structure Design and Creation
In this exercise we will focus on designing and creating the initial database schema for capturing information about:
dwellings,
water distribution points, and
educational facilities
…within the Kutupalong-Balukhali Expansion Site, a refugee camp in Chattogram, Bangladesh.
4.1 Analyzing Requirements and Designing the Data Schema
Now you will delve into the crucial process of analyzing requirements and designing the data schema. Imagine that your manager requested you to generate data about the dwellings, water distribution points, and educational facilities within the refugee camp.
To initiate any data modeling process, it’s crucial to tackle the following fundamental question:
- ‘What specific information is essential to capture for each dwelling, water distribution point, and educational facility within the refugee camp?’"
By identifying the different aspects to the information you’re interested in collecting, we can ensure that the database comprehensively represents the attributes and characteristics of these essential refuge camp components.
First, you need to generate a simple table to represent your data needs, e.g. as follows:
Table | Table Name | Attribute Information | Geometry Type |
---|---|---|---|
Dwellings | dwellings | capacity, occupancy_status | Polygon |
Water Distribution Points | water_distribution_points | capacity, operational_status | Point |
Educational Facilities | educational_facilities | facility_type, capacity | Point |
4.2 Creating the Necessary Tables
Then, it’s time to create the tables with the following SQL that generates three tables and 3 to 4 columns with their corresponding data types:
-- Creating the Dwellings table
CREATE TABLE dwellings (
INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
dwelling_id INTEGER,
capacity VARCHAR(255),
occupancy_status 32646)
geom GEOMETRY(POLYGON,
);
-- Creating the Water Distribution Points table
CREATE TABLE water_distribution_points (
INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
point_id INTEGER,
capacity VARCHAR(255),
operational_status 32646)
geom GEOMETRY(POINT,
);
-- Creating the Educational Facilities table
CREATE TABLE educational_facilities (
INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
facility_id VARCHAR(255),
facility_type INTEGER,
capacity 32646)
geom GEOMETRY(POLYGON, );
Note that the code after the geometry type refers to the Coordinate Reference System (CRS). This CRS is commonly used in the UTM (Universal Transverse Mercator) projection for the region around Bangladesh, including our region of interest (Chattogram).
Now you can launch QGIS and browse the tables available through the PostGIS connection you’ve established. Note that there’s no data yet, you can just see that the tables already exist:
Note that you can also see tables like “geography_columns” and “geometry_columns” which come with the PostGIS extension.
Great! Now you’ve created a few data structures for tables.