CBMS Module III-A: Thematic Mapping using CBMS Data
Topic 4
Creating Features from
Tabular Data
Scan the QR
Code to
access GMD’s
iMapa
Prepared by the Geospatial Management Division for 2021-2023 CBMS Pilot/Rollout Areas
CBMS Module III-A: Thematic Mapping using CBMS Data
Topic Outline
Loading of GIS-ready tables........................................................................................................................ 3
Mini Exercise 2: Loading of GIS-ready Tables to QGIS.............................................................................................4
Exploring Tabular Data (Extracting Features from Tabular Data) ........................................................... 9
Combining table features using Join function ....................................................................................... 13
Mini Exercise 3: Joining GIS-ready Tables to Shapefiles ........................................................................................16
Creation of Bivariate Map ........................................................................................................................ 20
Exercise 2: Creation of bivariate map..................................................................................................... 24
Page 2 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
Learning Objectives
1. Convert collected tabular data into files that can be loaded in QGIS.
2. Create a map showing the collected data.
Loading of GIS-ready tables
In CBMS, geotagging of facilities and households is done to determine its presence in specific areas. These
collected data on coordinates, along with other essential information, are compiled using tables. To easily
visualize these, we need to learn how to load the collected data in QGIS to aid us in further analyzing them.
1. Open Limasawa_bldg_materials.xlsx from Day 2 - 2_Datasets - Dataset 2 folder. The file
contains codes regarding the location, data on housing characteristics, and the coordinates of each
geotagged household.
2. Save the file as Limasawa_bldg_materials_table. Ensure that the Save as Type is set to CSV
(Comma Delimited).
Page 3 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
The .CSV (Comma Delimited) file format is used to easily import high volume of data in QGIS since they are
plain-text files. The user may also load the data in .xlsx (Excel) file format, however, a plugin is needed for loading
such.
3. Open a new QGIS project, load the Limasawa_Southern Leyte_bgyboundary.shp located in the
Day 2 - 2_Datasets - Dataset 2 folder.
4. To add the .csv file in QGIS, select Layer in the menu bar, then Add Layer, and click Add Delimited
Text Layer…
Mini Exercise 2: Loading of GIS-ready Tables to QGIS
Time limit: 15 - 20 minutes
5. In the Delimited Text tab, set the following parameters:
• File name: Limasawa_bldg_materials_table.csv
• Layer name: Limasawa_bldg_materials_table
• Geometry Definition: Point coordinates
• X field: Longitude
• Y field: Latitude
• Geometry CRS: EPSG: 4326 – WGS 84.
Page 4 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
Click Add, then Close. The Limasawa_bldg_materials_table layer shall be added in the Layers
panel, and the points representing each household shall be loaded in the map canvas.
6. Export the delimited text layer as a shapefile by using either of the two (2) methods:
The csv file shall be exported as a shapefile so that the user can perform edits in the attribute table
when necessary.
• Using the Refactor Fields function
a. Type refactor in the processing toolbox
Page 5 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
The Refactor fields function may be used when the column header is too long. If the user directly
exports the layer as shapefile, the column headers may not appear as it was initially loaded in
QGIS.
a plugin is needed for loading such.
b. Set the following parameters:
• Input layer: Limasawa_bldg_materials_table
• Name for construction material of floor: Flooring
• Name for construction material of walls: Walls
• Name for construction material of Roof: Roofing
• Load fields from template layer: Limasawa_bldg_materials_table
In the Refactored field, click browse button and save the file as
Limasawa_bldg_materials.shp. Click Run, then Close.
Limasawa_bldg_materials_table [EPSG: 4326]
Limasawa_bldg_materials_table
• Directly exporting the layer to a shapefile (.shp)
a. Selecting the Limasawa_bldg_materials_table, right-click, select Export, then Save
Feature As…
Page 6 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
b. Save the file as Limasawa_bldg_materials. Ensure that the Save As Type is set to ESRI
Shapefile (.shp).
After exporting, the newly-exported shapefile, Limasawa_bldg_materials, shall appear in the
Layers panel. The user may now delete the previously loaded delimited text layer.
7. To visualize the categories of the housing characteristics, right-click the Limasawa_bldg_materials
layer and select Properties.
8. Click the Symbology tab, select Categorized, choose flooring for the Value. Click Classify.
Remove the all other categories value by clicking the (delete) button.
Page 7 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
9. To visualize the light and strong materials, set the following parameters in the Symbol Selector
window:
• Size: 2.0 Millimeters
• Fill Color: Light materials– HEX #b2df8a
Strong materials– HEX #fb9a99
• Stroke Color: HEX #808080
• Stroke Width: 0.1 Millimeters
• Join Style: Bevel
• Cap Style: Square
Click OK, then Apply.
Page 8 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
The categorized values shall appear in the Layers panel and shall also be loaded in the map canvas as
shown.
Exploring Tabular Data (Extracting Features from Tabular Data)
Following the previous topic wherein location of all geotagged households was loaded in QGIS, the user
needs to extract the specific characteristics of the housing units to further assess the data according to the
target.
For instance, the characteristics of housing materials of each household—walls, roofs, and floors—are
collected and geotagged. These data on housing characteristics, categorized into light or strong materials,
may be used in determining potential areas prone to damage caused by calamities, or as poverty indicator,
among others. To easily visualize the location and distribution of households with either light or strong
housing materials, these tabular data can be loaded in and assessed using QGIS.
1. Open Limasawa_bldg_materials.xlsx located in Day 2 - 2_Datasets - Dataset 2 folder.
Page 9 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
2. Create a new column named bgy beside Barangay column.
3. In the bgy column, encode =TEXT(D2,”000”), and drag the cell to fill the remaining empty cells
below.
4. Create a new column bgy_id beside bgy.
5. In the bgy_id column, encode =CONCATENATE(A2,B2,C2,E2), and drag the cell to fill the
remaining empty cells below.
Page 10 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
6. Copy the bgy_id column and paste it again in the same column as VALUES, to prevent removal
of the leading zeros upon loading in QGIS.
7. Insert Pivot Table and choose table/range.
8. Set the following parameters:
Rows
• Construction Material of the Roof
• Bgy_id
Values
• Bgy_id
Page 11 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
9. Open a new excel file and create a new table with column names bgy_id and light_roofing, and
paste as VALUES the generated data from the pivot table.
10. Go back to the generated Pivot Table. Remove Construction Material for the Roof and set the
following parameters:
Rows
• Construction Material of the Outer Walls
• Bgy_id
Values
• Bgy_id
Page 12 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
11. Using the same excel file with the light_roofing data, add another column for light_wall and paste
the generated values. Put “0” in the value corresponding to light_roofing for bgy_id 086419004 to
denote that there are no households with light roofing materials in the barangay.
12. Save the file as Limasawa_lightroof_lightwall. Ensure that the Save as Type is set to CSV
(Comma Delimited).
Combining table features using Join function
The Join function allows linkage of two attribute tables using common values of features for further
processing and analysis.
1. In the menu bar, select Layer and click Data Source Manager.
Page 13 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
2. In the Data Source Manager window, click Delimited Text. Click the browse button and locate
Limasawa_lightroof_lightwall.csv
• Set the layer name to Limasawa_lightroof_lightwall.
• Uncheck Detect field types in the Record and Fields Options to retain the leading zeros in the
data
• Select No Geometry under Geometry Definition.
Click Add, then Close.
3. Open the attribute tables of Limasawa_Southern Leyte_bgyboundary and
Limasawa_lightroof_lightwall. Identify the common fields of the attribute tables for the Join
function. In this case, the geocode and bgy_id fields will be used.
Page 14 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
4. Open the attribute table of the Limasawa_Southern Leyte_bgyboundary and click Toggle
editing mode. Click the Open field calculator button.
5. In the Field Calculator window, set the following parameters:
• Tick the checkbox beside Create a New Field
• Output field name: bgy_id
• Output field type: Text (string)
• Output field length: 10
In the Expression, encode:
Region code Number of digits
• Ensure that there are no spaces in
lpad(('08'+"geocode"),9,'') •
the code.
TWO (2) SINGLE quotation marks
are used before the last closing
Click OK. And Save edits. parenthesis.
Page 15 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
• The encoding of lpad(('08'+"geocode"),9,'') is done to generate a common field to perform
the JOIN function for two (2) separate layers.
• Ensure the proper encoding of PSGC codes in the respective areas
• In the event that the leading zeros in the bgy_id of the csv file did not load in QGIS, the user
shall encode lpad(('8'+"geocode"),8,'') to generate a common field.
A new bgy_id column containing the same values as the bgy_id of the Limasawa_lightroof_lightwall
layer shall be generated in the Limasawa_Southern Leyte_bgyboundary layer.
6. Open the Limasawa_Southern Leyte_bgyboundary Layer Properties window, select Joins
tab and click Add new join button.
Mini Exercise 3: Joining GIS-ready Tables to Shapefiles
Time limit: 15 - 20 minutes
Page 16 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
7. In the Add Vector Join window, set the following parameters:
• Join layer: Limasawa_lightroof_lightwall.csv
• Join field: bgy_id
• Target field: bgy_id
Cache join layer in memory is enabled by default to speed up the Attribute table lookup.
Tick the checkbox beside Joined fields and select light_roofing and light_wall. Click OK.
8. Click Apply, then OK.
9. Check the Limasawa_Southern Leyte_bgyboundary layer attribute table if the merging is
successful.
Page 17 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
In attribute table joins, the data from the joined layer has not migrated to the target layer’s
attribute table, but rather a symbolic link.
10. Edit the name of the joined field by using the Refactor fields. In the Processing Toolbox,
type refactor.
11. Set the parameters in the Refactor Fields window:
• Input layer: Limasawa_Southern Leyte_bgyboundary
• Name for light_roofing layer: light_roof
• Name for light_wall layer: light_wall
• Ensure that the Type for both fields is changed from TEXT to INTEGER (64 bit). Set the
length to 10.
• Load fields from layer: Limasawa_Southern Leyte_bgyboundary
Page 18 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
In the Refactored field, click browse, then Save to file, and save the file as
ME3_LastName_FirstName.shp
Click Run, then Close.
The exported shapefile shall be added to the Layers panel.
12. Open the attribute table of the newly-exported refactored layer. When there are NULL values in
the light_roof and light_wall columns, click the Toggle editing mode button, and manually
encode 0 in the fields with NULL values to denote that there are no households constructed with
light materials in that specific area.
Page 19 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
Creation of Bivariate Map
A choropleth map is a thematic map wherein values are usually presented in colors or patterns using one
(1) variable and is referred to as a univariate map. This map can be generated by using the Graduated
Symbology in QGIS.
On the other hand, a bivariate map uses two (2) variables in a single map. It is often used to compare or
determine the relationship between these variables.
An example of two (2) univariate maps combined to generate a bivariate map
Source: [Link]
Using the refactored layer from the previous section, create a bivariate map using the data on housing
materials with light roofs and light walls.
1. Duplicate the refactored layer. Rename the 1st layer as Limasawa_Southern Leyte_lightroof and
the duplicate copy as Limasawa_Southern Leyte_lightwall.
Page 20 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
2. Open the layer properties of Limasawa_Southern Leyte_lightroof, select Graduated in the
Symbology tab. Click Classify, remove the all other categories value by clicking the button.
Set the following parameters:
• Value: light_roof
• Color ramp: BuGn
• Mode: Natural Breaks (jenks)
• Blending mode (Layer): Multiply
3. Set the Classes to 3 and encode 0.00-1.00, 1.00-10.00, and 10.00-15.00 in the Values. Click
Apply, then OK.
For better visualization and interpretation of the bivariate map, the recommended number of classes is
three (3). The range to be set per class shall depend on the collected data.
4. Open the layer properties of Limasawa_Southern Leyte_lightwall, select Graduated in the
Symbology tab. Click Classify, remove the all other categories value by clicking the button.
Set the following parameters:
• Value: light_wall
• Color ramp: Blues
• Mode: Natural Breaks (jenks)
Page 21 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
5. Set the Classes to 3 and encode 4.00-12.00, 12.00-43.00, and 43.00-64.00 in the Values. Click
Apply, then OK.
Upon clicking the Apply button, the map canvas shall load as shown.
Page 22 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
6. Install the Bivariate legend plugin. This plugin generates the legend to be used for bivariate maps.
7. Open the Bivariate legend plugin and set the following parameters:
• Top layer: Limasawa_Southern Leyte_lightroof
• Tick the checkbox beside Reverse colors
• Bottom layer: Limasawa_Southern Leyte_lightwall
• Square width: 30
• Multiply
8. Click Export legend to image and save the file as Limasawa_bivariate.png, click Save.
Page 23 of 24
Topic 4: Creating Features from Tabular Data
CBMS Module III-A: Thematic Mapping using CBMS Data
9. Create a map layout using the generated data on construction materials and the bivariate legend.
10. Save the project as ME3_LastName_FirstName.qgs.
Exercise 2: Creation of bivariate map
Time limit: 1 – 1.5 hours
Using Padre Burgos_bldg_materials.xlsx and Padre Burgos_Southern Leyte_bggboundary.shp
located in Day 2 - 2_Datasets - Dataset 2 folder, create a bivariate map using the data on light roof and light
wall construction materials.
Save the map as: E2_LastName_FirstName.png
Page 24 of 24
Topic 4: Creating Features from Tabular Data