Methodology - Step by Step Guide

1.0 Data Collection

1.1 Downloading Data

Firstly, you are required to download the following data from data.gov.sg:

1. Master Plan 2019 Subzone Boundary (No Sea)

2. General Information of Schools

3. Singapore Residents by Planning Area / Subzone, Single Year of Age and Sex, June 2011-2020

Next, you are required to download geospatial data from geofabrik: 

1. Roads data from OpenStreetMap (OSM) data sets 
   • Click on Asia → Malaysia, Singapore, and Brunei → Download malaysia-singapore-brunei-latest-free.shp.zip 

Next, you are required to download MRT data (Train Station) from the Land Transport Authority (LTA) Data Mall. The data set provides the locations of MRT stations in Singapore. 

1.2 Managing the Imported Data

Assuming the root directory of this project is called GISProject, create a sub-folder called data.

• Place the downloaded data from data.gov.sg and LTA Data Mall into the data sub-folder

  • Unzip the respective zipped files,

  • Extract the zipped files, and

  • Place them in data folder

2.0 Data Preparation, Data Cleaning and QGIS Project Setup (Primary School)

2.1 Creating a New Project and Assigning Project Coordinate System for New Project

1. From Windows Desktop, launch QGIS. 

2. You will start a new QGIS project. From the menu bar, select Project → New. 

3. Ensure that your projection system at the bottom right bar is using EPSG:3414, if not reassign the projection system by selecting SVY21 / Singapore TM.

Notice that the projection system has been updated to EPSG:3414 now.

2.1.1 Saving your project

It is time to save the project. 

1. From the menu bar, click Project → Save. 

2. At the Choose a QGIS project file dialog window, navigate to the root project folder, then provide a proper project name such as GISProject in QGIS files (.qgs) format from the Save as type: dropdown list. Your screen should look similar to the screenshot below.

2.2 Preparing base layer for study area

Our study area would be the Singapore Planning Subzone excluding all the outer islands  such as Pulau Ubin and Pulau Tekong.

1. From the Browser panel, navigate to the path of GISProject as shown in the screenshot below. 

2. Double click on URA_MP19_SUBZONE_NO_SEA_PL.shp Notice that URA_MP19_SUBZONE_NO_SEA_PL.shp is added in the Layer panel.  

3. The View window should look like this. 

We will now select the main island of Singapore, excluding the outer islands. 

1. From the toolbar, click Select Features by Polygon

2. Select the main island of Singapore. Your screen should look similar to the screenshot below.

2.3 Storing your data sets in GeoPackage format

1. Save all your data sets into an integrated database called GISProject.

2. From the Layers panel, right-click on URA_MP19_SUBZONE_NO_SEA_PL layer.

3. Select Export → Save Selected Features As from the context menu.

   1. The Save Vector Layer dialog window appears. For Format, select GeoPackage from the drop-down list. 

   2. For Save as, click on the Browse button. The Save Layer As dialog window appears

   3. Navigate to \GISProject\Geopackage\ sub-folder. 

   4. For File Name, type GISProject. 

   5. Rename your layer as MP19_SUBZONE. 

   6. Check that the CRS is Project CRS: EPSG:3414 – SVY21

From the Layers panel, right click the URA_MP19_SUBZONE_NO_SEA_PL layer and click Remove Layer.

2.4 Preparation of Primary School Layer

Review the content of general-information-of-schools.csv file. Locate and open the file with Excel.

Your screen should look like the figure below.

1. Using appropriate Excel functions to sort the schools according to the  mainlevel_code field, retain only the schools categorized as Primary. 

Take note that there are 3 Primary Schools: Catholic High School, CHIJ St. Nicholas Girls’  School and Maris Stella High School categorized as Mixed Levels under the mainlevel_code. 

2. Using appropriate Excel function, retain the school_name, address,  postal_code, and mainlevel_code field and delete the unwanted fields. 

3. Create two new fields: country and city fields

4. Fill the two fields with Singapore

5. The final general-information-of-schools.csv should look like this. Save the file.

2.4.1 Geocoding Prepared Data

1. Geocode the general-information-of-schools data set by using SLA OneMap API. Save the geocoded data set in  geocoded_school.csv. The geocoded data set should have the new fields Latitude and Longitude.

The geocoded_school.csv should look similar to this.

Notice the first 4 entries do not have values under Latitude and Longitude.

2. Collate the 4 primary schools without any values in the Latitude and Longitude fields and save them in not-foundschools.csv. 

   1. Create two new fields: country and city 

   2. Fill the cell below country and city fields with Singapore. 

The final not-foundschools.csv should look similar to this. 

2.4.1.1 Installing the MMQGIS Plugin

To install the MMQGIS plugin, you will need to load the QGIS plugin repository by following the steps below.

• From the menu bar, select Plugins → Manage and Install Plugins.

Plugins dialog window appears as shown below.

Notice that there is a long list of plugins available. We can use the Search function to locate mmqgis plugin easily.

• At Search, type mmqgis.

Your screen should look similar to the figure below.

• Click on Install Plugin button to run the installer.

After installing mmqgis plugin, remember to close the Plugin dialog window.

• Click on Close button.

2.4.1.2 Geocoding using MMQGIS Plugin

Now, you are ready geocode not-foundschools dataset.

• From the menu bar, select MMQGIS → Geocode → Geocode CSV with Web Service.

Web Service Geocode dialog window appears.

• From Input CSV File (UTF-8), click on Browse button.

Select a file dialog window appears.

• Navigate to the path where  not-foundschools.csv reside.

• Click on not-foundschools.csv.

• Click on Open button.

Your screen should similar to the screenshot below.

• For Web Service, select OpenStreetMap/Nominatim from the drop-down list.

Geocode Tool will generate two output files. They are a point feature GIS data set along with a Not Found CSV file containing all rows that could not be geocoded (for whatever reason). We need to tell Geocode Tool where to keep these two output files.

• Use File Explorer to create a new sub-folder called geocoding in GISProject directory.

• For Output File Name, click on Browse button.

Create or select a file dialog window appears.

• Navigate to the newly create geocoding sub-folder.

• For File name, type geocoded_sch.shp

• Click on Save button.

You also need to provide the Not Found List Output list a place holder.

• For Not Found Output list, click on Browse button.

Create or select a file dialog window appears.

• Navigate to the newly create geocoding sub-folder.

• For File name, type not_found_sch.csv

• Click on Save button.

The completed dialog window should look similar to the screenshot below.

Now, you are ready to perform the geocoding function.

• At the Web Service Geocode dialog window, click on Apply button.

When the geocoding function is completed, notice that a new shapefile layer will be added on QGIS map window. At the same time, the progress bar will indicate numbers of records that have been geocoded successfully.

• Click on the Close button to close the dialog window.

At the Layers panel, right-click on  geocoded_schs layer → Open Attribute Table

• Extract the values from the latlong field and fill in the missing Latitude and Longitude of the 3 primary schools in the geocoded_school.csv. 

• The final geocoded_school.csv should look similar to the screenshot below. Save the file as primary_schools.csv.

2.4.2 Adding Primary School Layer into QGIS Project

We will now import the primary school csv into the project. 

• Click Layer → Add Layer → Add Raster Layer.

Data Source Manager | Delimited Text dialog window appears. 

• For File name, navigate to the folder where the primary school csv resides

• For Geometry Definition, click Point coordinates and ensure X field is set as Longitude and Y field is set as Latitude

• Click Add button.

• Click Close button.

In the Layers panel, notice that the primary_schools layer has been added. 

2.5 Preparation of Road Network Layer

Using the steps learned from the previous section, import the Road Network Data (gis_osm_roads_free_1.shp) into QGIS. The Road Network data contains road networks from Malaysia, Brunei and Singapore.

2.5.1 Selecting all road networks within the study area

Using spatial query function of QGIS, extract the road network features that fall within Singapore mainland study area. 

1. From the icon bar, click on the drop-down list next to Selection icon

   1. Select Select Features by Polygon from the drop-down list

2. At Browser panel, click on gis_osm_roads_free_1 to make sure that it is the active layer.

3. Click and drape to form a freehand coloured shape to cover all the road network features that fall within Singapore mainland. 

4. Next, release the mouse and notice that all the selected features are highlighted.

5. Using the steps learned from previous sections, save the selected features in GeoPackage format. Name the layer all_road_networks.Your screen should look similar to the screenshot below.

To further filter the road network layer, we will exclude roads that are not walking or motor roads.

1. Under the Layers panel, right click on all_road_networks → Open Attribute Table

2. Click on the Select features using an expression icon. The Select features by expression dialog window will appear.

3. At the upper left corner of the window, click on the Expression tab.

   1. Under the expression pane, key in the following:

“fclass” not in (‘bridleway’, ‘track’, ‘track_grade1’, ‘track_grade2’, ‘track_grade3’, ‘track_grade4’, ‘track_grade5’, ‘unclassified’, ‘unknown’)

The query statement in the Expression pane should look similar to this:

6. Click on Select Features button and close the window.

7. Using the steps learned from previous sections, save the selected features in GeoPackage format. Name the layer all_roads.

2.5.2 Extracting Motor Vehicle Road Network

4. Under the Layers panel, right click on all_roads → Open Attribute Table

5. Click on the Select features using an expression icon. The Select features by expression dialog window will appear.

6. At the upper left corner of the window, click on the Expression tab.

   1. Under the expression pane, key in the following:

“fclass” = ‘motorway’ or “fclass” = ‘motorway_link’ or “fclass” = ‘primary’ or “fclass” = ‘primary_link’ or “fclass” = ‘secondary’ or “fclass” = ‘secondary_link’ or  “fclass” = ‘tertiary’ or “fclass” = ‘tertiary_link’ or “fclass” = ‘residential’ or  “fclass” = ‘trunk’ or “fclass” = ‘trunk_link’

The query statement in the Expression pane should look similar to this:

7. Click on Select Features button and close the window.

8. Using the steps learned from previous sections, save the selected features in GeoPackage format. Name the layer motor_roads.The motor_roads layer should look similar to the screenshot below.

2.5.3 Extracting Pedestrian Path Network

Using appropriate QGIS functions learnt from previous sections, extract the pedestrian path network from the all_road_networks layer.

1. Under the expression pane, key in the following:

“fclass” = ‘residential’ or “fclass” = ‘living_street’ or “fclass” = ‘pedestrian’ or  “fclass” = ‘service’ or “fclass” = ‘cycleway’ or “fclass” = ‘footway’ or “fclass” = ‘path’ or  “fclass” = ‘steps’

2. Using the steps learned from previous sections, save the selected features in GeoPackage format. Name the layer walking_roads.The walking_roads layer should look similar to the screenshot below.

2.6 Preparation of Young People Distribution Layer

Using the steps learned previously, import Singapore Residents by Planning Area / Subzone, Single Year of Age and Sex, June 2011-2020 layer (respopagesex2011to2020) into the project. 

2.6.1 Extracting Youth Population

We will now use SQL to extract the distribution of the youth population from respopagesex2011to2020.

1. Click Processing → Toolbox

2. Type Execute SQLinto the search bar.

3. Execute SQL toolbox appears.

   1. For Additional input datasources, select respopagesex2011to2020

   2. For SQL query, type

Select “PA”, “SZ”, sum(“Pop”) as “Young People”  

from respopagesex2011to2020 

where “Time” = “2019” and “Age” in (‘0’,‘1’,‘2’,‘3’,‘4’,‘5’,‘6’,‘7’,‘8’,‘9’,‘10’,‘11’,‘12’,‘13’,‘14’,‘15’,‘16’,‘17’,‘18’,’) 

group by “PA”,“SZ”

3. For Geometry type, select No geometry

   

4. Click Run button. 

5. A new layer called SQL Output will appear in the Layers panel.

2.6.2 Creating Relational Join

We will now perform relational join between the SQL Output layer and the MP19_SUBZONE layer.We will use the subzone name as the unique identifier to join these two layers. 

1. Right-click on SQL Output → select Open Attribute Table. Observe that the SZ column values are currently in lower case. We will change the values to upper case.

2. Click Open field calculator icon

3. Click Update existing field

4. For Value, select SZ

5. Under Expression panel, type upper(“SZ”)

6. Click OK button

Notice that the SZ column values are now in upper case.

Using the steps learned previously, save SQL Output  into Geopackage format. Name the layer Young People Distribution.

We will now join the Young People Distribution layer and the MP19_SUBZONE layer. 

1. Right click on MP19_SUBZONE layer → click Properties → click Joins

2. Click on the + icon at the bottom of the screen.

3. The Add Vector Join dialog window appears.

   1. For Join layer, select Young People

   2. For Join field, select SZ

   3. For Target field, select SUBZONE_N

   

4. Click OK button.

Notice that a relational join between Young People and MP19_SUBZONE has been created.

2.6.3 Creating Choropleth Map to Display Youth Population Distribution

We will now prepare the choropleth map to analyse the distribution of the youth population.

1. From the Layers panel, right-click on MP19_SUBZONE layer → click Properties.

2. The Layer Properties dialog window appears. Click on Symbology tab

   1. At Symbol selection drop-down list, select Graduated from the drop-down list

   2. For Value, select Young_People_Distribution_Young_People

   3. For Classes, keep it as 5

   4. For Color ramp, choose Blues

   5. For Mode, select Natural Breaks (Jenks) from the drop-down list

   6. Click Classify button

   

3. Click the OK button.Notice that a choropleth map is now formed.

3.0 Preparing Maps for Analysis

3.1 Creating the hexagon layer

Now, we are going to create a hexagon layer by using the MP19_SUBZONE layer as the base. The hexagon distance is 250m.

• From the menu bar, select Vector → Research Tools → Create Grids.

Create Grids dialog window appears.

• For Grid type: select Hexagon (Polygon) from the drop down list.

• For Grid extend:, select Calculate from Layer →  MP19_SUBZONE

• For Horizontal spacing, type 500.

• For Vertical spacing, type 500.

• For Grid CRS, make sure that EPSG 3414 is used.

When you are ready to run the process,

• Click on Run button.

Read the Log before closing the dialog window.

• Click on Close button.

Notice that a new temporary layer called Grid is added on the Layer pane and display on Map window.

3.1.1 Editing the hexagon layer

Using the Clipped function, intersect the Grid layer and MP19_SUBZONE layer.

Using Select features by polygon function as learnt in the previous section, select the area that falls under Singapore mainland. 

Your screen should look similar to the screenshot below.

3.1.2 Saving the hexagon layer

Using the steps you had learned in previous sections, save the edited Grid layer into GeoPackage format. Name the layer hexagon.

Before you move on to the next section, remember to remove the temporary Grid layer.

3.1.3  Computing hexagon centroid

In general, network analysis required the demand in a point feature. Hexagon, on the other hand, is a polygon feature. In order to meet the analysis need, we will compute the centroids of the hexagons.

• From the menu bar, select Vector → Geometry Tools → Centroids.

Centroids dialog window appears.

• For Input layer, select hexagon from the drop-down list.

When you are ready to run the process,

• Click on Run button.

Reminder: Read the log before closing.

• Click on Close button.

Notice that a new temporary layer called Centroids is added onto the Layers panel and display on Map view.

3.1.4 Saving the centroid layer

Using the steps you had learned in previous hands-on exercise, save the temporary Centroids layer into GeoPackage format. Name the newly created layer hex_centroids .

Before you move on to the next section, remember to remove the temporary Centroids layer.

3.2 Network Accessibility Analysis - Primary

3.2.1 Install QNEAT Plugin

1. From the menu bar, select Plugins → Manage and Install plugins. Plugins dialog window appears. 

2. At the query, type QNEAT3. Notice that QNEAT3 appears on the search output list.

3. Click on QNEAT3. 

   1. Click on Install Plugin button. 

When the installation is completed, click on the Close button. 

3.2.2 Working with OD Matrix Tool

Next, we will use the Origin-Destination Matrix (OD Matrix) tool of QNEAT3 plugin to calculate the distances between hexagon centrois (as the demand points) and primary schools (as the supply points).

• From the menu bar, click on Processing → Toolbox

• At the Search pane, type OD Matrix.

Your screen should look similar to the screenshot below.

• Click on OD Matrix Layers as Table (m:n).

OD Matrix Layers as Table (m:n) dialog window appears.

• For Network Layer, select motor_roads from the drop-down list.

• For From-Point Layer, select hex_centroids from the drop-down list.

• For Unique Point ID Field, select fid from the drop-down list.

• For To-Point Layer, select primary_schools from the drop-down list.

• For Unique Point ID Field, select field_1 from the drop-down list.

• For Optimization Criterion, select Shortest Path (distance optimization) from the drop-down list.

• For Entry Cost calculation method, select Ellipsoidal from the drop-down list.

• For Direction field, select oneway from the drop-down list.

• For Value for forward direction, type F.

• For Value for backward direction, type T.

• For Value for both direction, type B.

• For Topology tolerance, type 0.5 (i.e. 0.5 m).

The completed dialog window should look similar to the screenshot below.

When you are ready to run the process.

• At the OD Matrix dialog window, click on Run button.

When you are ready to close the dialog window, click on the Close button.

Notice that a new temporary table called Output OD Matrix is added onto Layers panel.

• At the Layers panel, right-click on Output OD Matrix and select Open Attribute Table from the context menu.

A data table that looks similar to the screenshot below appears.

 Using the steps learnt in previous sections, save the temporary Output OD Matrix table as GeoPackage format. Name the layer OD_primaryschools.

3.2.3 Extracting shortest distance pairs

Next, we will use the SQL tool of QGIS to select destination points with the shortest distance.

• At the Search pane of Processing Toolbox, type SQL.

SQL function appears on the list.

• Double-click on Execute SQL of Vector general.

Execute SQL dialog window appears.

• For Additional input datasources, select on the button at the right end.

• Click on the checkbox Output OD Matrix.

• Click on OK button.

• At SQL query panel, type the following SQL

• For Geometry type, select No Geometry from the drop-down list.

Notice that a temporary table called SQL Output is added onto Layers panel. It consists of four fields. The values in shortest_distance field are the shortest distance between demand points and its nearest primary school.

Using the steps you learnt in previous sections save the temporary SQL Output table as GeoPackage format. Name the layer acc_primaryschools.

For the SQL Output created from walking_roads, there will be several null values in the shortest_distance column that prevents us from joining this layer to the hexagon layer

in the next step. To circumvent this, we will create a new column for shortest_distance that is type integer.

• Right-click on acc_primaryschools_walking_beforemerger → Open Attribute Table.

• Click on Create new field icon

  • For Output field name, type shortest_distance_new

  • For Output field type, select Decimal number (real)

In Expression panel, type shortest_distance

• Click OK button. A new column called  shortest_distance_new is created.

Repeat the above steps for acc_primaryschools_aftermerger.

3.2.4 Mapping Accessibility Values

3.2.4.1 Creating a Duplicate Layer

Before we getting started, let us create a duplicate copy of hexagon layer

• At the Layers panel, right-click on hexagon layer and select Duplicate Layer from the context menu.

A new layer called hexagon copy is added onto Layers panel.

• Rename the layer to Accessibility to primary schools.

3.2.4.2 Performing relational join

Before we can prepare the choropleth map, we need to join acc_primaryschools data table to the newly created  Accessibility to primary schools by using fid of acc_primaryschools data table and fid of Accessibility to primary schools attribute table as unique join fields.

• At the Layer panel, right-click on Accessibility to primary schools layer and select Properties from the context menu.

The Properties dialog window appears.

• At the option panel, click on Joins.

• Click on + button to add a join.

The Add Vector Join dialog window appears.

• For Join layer, select acc_primaryschools from th drop-down list.

• For Join field, select fid from the drop-down list.

• For Target field, select fid from the drop-down lsit.

• Keep the checkbox in front of Cache join layer in memory checked.

• When you are ready, click on OK button.

3.2.4.3 Preparing Choropleth Map

Prepare a choropleth map to show the distribution of accessibility to primary schools.

1. From the Layers panel, double-click on the Accessibility to primary schools layer. The Layer Properties dialog window appears. 

2. Click on the Symbology tab. 

3. At Symbol selection drop-down list, select Graduated from the drop-down list.

4. For Value, select access_primaryschools_shortest_distance from the drop down list.

5. For Classes, keep it as 5.

6.  For Color ramp, choose Blues or any colour of your choice from the drop-down  list.

   1. Select Invert Color Ramp

7. For Mode, choose Equal Count (Quantile) from the drop-down list.

8. Click on the Classify button. 

Your screen should look similar to the screenshot below.

Your choropleth map should look similar to the screenshot below.

Repeat the above steps to obtain choropleth maps for

1. Accessibility to Primary Schools After Merger (Driving)

2. Accessibility to Primary Schools Before Merger (Walking)

3. Accessibility to Primary Schools After Merger (Walking)

To obtain b and c, under section 3.2.2.2, for Network Layer, select walking_roads from the drop-down list.

3.2.4.4 Preparing Bivariate Choropleth Map

We will now create a bivariate choropleth map to compare the driving and walking accessibility to primary schools, both before and after the merger of schools. To do this,

1. Create a copy of the Accessibility to Primary Schools Before Merger (Driving) and  Accessibility to Primary Schools Before Merger (Walking). Right-click on the layer and select Duplicate layer

2. For Accessibility to Primary Schools Before Merger (Driving) layer, right-click → Properties → Symbology

   1. Select Graduated

   2. For Value, select acc_primaryschools_beforemerger_shortest_distance

   3. For Classes

      1. <2km

         1. Color: #be64ac

         2. Values: 0-2000

      2. 2-5km

         1. Color: #dfb0d6

         2. Values: 2000-5000

      3. >5km

         1. Color: #e8e8e8

         2. Values: 5000-52359.78

3. For Accessibility to Primary Schools BeforeMerger (Driving) layer, right-click → Properties → Symbology

   1. Select Graduated

   2. For Value, select acc_primaryschools_walking_beforemerger_shortest_distance_new

   3. For Classes

      1. <1km

         1. Color: #5ac8c8

         2. Values: 0-1000

      2. 2-5km

         1. Color: #ace4e4

         2. Values: 1000-2000

      3. >2km

         1. Color: #e8e8e8

         2. Values: 2000-53051

4. For Accessibility to Primary Schools Before Merger (Driving) layer, on Symbology page, click Layer Rendering

   1. For Blending mode, select Multiply

      

   2. Click the OK button and close the window.

5. A bivariate choropleth map will appear.

6. To create the bivariate legend, install the plugin called Bivariate Legend. Using the Bivariate legend, the parameters will be:

   1. Top layer: Accessibility to Primary Schools Before Merger (Driving) 

   2. Bottom layer: Accessibility to Primary Schools Before Merger (Walking) 

   3. Square width: 48

   4. Multiply

      

   5. Click Generate legend and Export legend to image, and save it as a .png file

   6. To create the report, go to Project → New Print Layout. Create the print layout.

4.0 Data Preparation for Secondary School

4.1 Data Preparation

4.1.1 Data Cleaning

a. Clean School Directory and Information file downloaded from General Information of Schools by keeping the column like shown below, filtering the mainlevel_code with the value of SECONDARY and MIXED_LEVEL.

b. Carefully check each school labelled with mixed_level, and remove schools that do not include secondary school, for example Singapore Sports School and School of the Arts, save it into the Data folder and name it Singapore_Secondary_Schools.

4.2 Geocode the prepared data

1. Open MMQGIS → Geocode → Geocode CSV with Web Service

2. Navigate to the singapore_secondary_schools.csv by clicking the button at the end of the bar under Input CSV File (UTF-8 )

• For Address, select address from the dropdown list

• For City, select City

• For Country, select Country 

• For Web Service, select OpenStreetMap / Nominatim

• Save it in your geocoded file

3. Now you may notice a layer called geocoded appears. Let us rename it to ‘SecondarySchool’

4. Navigate to the notfound file in your geocode folder. There are some schools that are not geocoded properly, and we need to manually find them on the map.

5. Now click Browser → double click OpenStreetMap

6. For example, Bowen Secondary school is not geocoded properly, we can manually find it by using the OpenStreetMap by using its address.

7. After you find Bowen Secondary School on Open Map, active and tick the SecondarySchool layer → right click and select Toggle Editing

8. Click the Add Point Feature icon that is circled in red.

9. Fill in the necessary information according to your notfound.csv file

10. Repeat the steps for the other school in the notfound.csv file, and open the attribute table. There should be 153 features. Save it into the Geopackage database.
    

4.3 Prepare data for analysing accessibility (After Merger)  

In 2019, there were 3 pairs of secondary school merged: 

Source: Ministry of Education

1. Use the SecondarySchool layer, open attribute table and press ctrl+F

2. In the field of school_name, type ‘East View Secondary’ and click Select Feature. 

3. Click Toggle Editing 

4. Select the highlighted point which is East View Secondary school 

5. Click the Delete Selected icon: to remove the East View Secondary school
   

6. Repeat the same step to remove the other merged schools 

7. Save the rest of the school in the database and name it Secondary School_After merged 

4.4 Network Accessibility Analysis - Secondary (Walking and Driving)

The logic for preparing maps including creating the hexagon layer and Network Accessibility Analysis are the same as demonstrated in the previous section. Follow the instructions from section 3.2 with secondary school before and after merger layers instead.

4.5 Network Accessibility Analysis - Secondary (MRT)

4.5.1 Data Preparation

1. Load theMRTLRTStnPt data from LTA data mall. Save it into GeoPackage as MRTstations_after_merger. 

2. Load a second MRTLRTStnPtt layer. Open its attribute table and remove the following stations which were not open at the time of the mergers

   1. Woodlands North

   2. Woodlands South

   3. Springleaf

   4. Lentor

   5. Mayflower

   6. Bright Hill

   7. Upper Thompson

   8. Caldecott (On the Thompson East Coast Line side)

3. Save it into GeoPackage as MRTstations_before_merger

4. Load in the gis_osm_railways from Geofabrik. Select all the MRT, LRT tracks using  “fclass” in ( ‘light_rail’ , ‘monorail’ , ‘rail’ , ‘subway’ )

5. Save the selected features as MRT_after_merger

6. Create a duplicate of MRT_after_merger and call it MRT_before_merger. Delete the TEL from the layer (Highlighted in bright yellow)

4.5.2 Extracting Shortest Distance from Hexagon Centroid to School via MRT

4.5.2.1 OD Matrix for hex centroid to MRT stations

Perform the same steps as in section 3.2.2, but with the following parameters changed:

• For Network Layer, select pedestrian_roads from the drop-down list.

• For From-Point Layer, select hex_centroids from the drop-down list.

• For Unique Point ID Field, select fid from the drop-down list.

• For To-Point Layer, select MRTstations_before_merger from the drop-down list.

• For Unique Point ID Field, select fid from the drop-down list.

Save this in GeoPackage as OD_Secondary_before_merger_MRT_walk

Repeat the steps with MRTstations_after_merger to get OD_Secondary_after_merger_MRT_walk

4.5.2.2 OD Matrix for MRT station to MRT station

Perform the same steps as in section 3.2.2, but with the following parameters changed:

• For Network Layer, select MRT_before_merger from the drop-down list.

• For From-Point Layer, select MRTstations_before_merger from the drop-down list.

• For Unique Point ID Field, select fid from the drop-down list.

• For To-Point Layer, select MRTstations_before_merger from the drop-down list.

• For Unique Point ID Field, select fid from the drop-down list.

Save this in GeoPackage as OD_before_merger_MRT_travel

Repeat the steps with MRT_after_merger and MRTstations_after_merger to get OD_after_merger_MRT_travel

4.5.2.3 OD Matrix for MRT station to Secondary School

Perform the same steps as in section 3.2.2, but with the following parameters changed:

• For Network Layer, select pedestrian_roads from the drop-down list.

• For From-Point Layer, select Secondary_before_merger from the drop-down list.

• For Unique Point ID Field, select fid from the drop-down list.

• For To-Point Layer, select MRTstations_before_merger from the drop-down list.

• For Unique Point ID Field, select fid from the drop-down list.

Save this in GeoPackage as OD_before_merger_MRT-school

Repeat the steps with Secondary_after_merger and MRTstations_after_merger to get OD_after_merger_MRT-school

4.5.2.4 SQL to get accessibility

Repeat steps 3.2.3 but with the following parameters changed:

1. For Additional input datasources, select OD_before_merger_MRT-school as input1 and OD_before_merger_MRT_travel

2. Enter the following as the SQL query:

   1. select min(input1.total_cost) as shortest_distance, input2.total_cost, input2.origin_id, input2.destination_id, input1.origin_id as school from input1, input2 where input1.destination_id = input2.destination_id group by input1.origin_id, input2.origin_id

3. Save the output as before_merger_sql

4. Open execute SQL again

5. For Additional input datasources, select OD_Secondary_before_merger_MRT_walk as input1 and before_merger_sql as input2

6. Enter the following SQL query:

   1. select input1.origin_id as origin_id, input1.destination_id as origin_MRT, input2.destination_id as destination_MRT, input2.school as destination_id, min(input1.total_cost) as dist1, min(input2.total_cost) as dist2, input2.shortest_distance as dist3 from input1, input2 where input1.destination_id = input2.origin_id and input1.total_cost < 1000 group by input1.origin_id

7. Save the output as acc_Secondary_before_merger_MRT

Repeat the above steps with the after merger OD matrices.

4.5.3 Mapping Accessibility Values

Repeat the steps in section 3.2.4 to create chroropleth/ bivariate choropleth maps for Walking/ MRT/ Driving Accessibility maps for both Secondary Schools both before and after merger. The choropleth thresholds used for walking and driving maps are the same as in Primary School, while MRT uses the same thresholds for driving

5.0 Data Preparation for JC

5.1 Data Preparation

1. Clean School Directory and Information file downloaded from General Information of Schools by keeping the column like shown below, filtering the mainlevel_code with the value of JUNIOR COLLEGE and MIXED_LEVEL.

2. Carefully check each school labelled with mixed_level, and remove schools that do not include Junior Colleges or are special schools, for example Singapore Sports School and School of the Arts, save it into the Data folder and name it JC_After_Mergers.

3. Duplicate the file and change the following schools:

   1. Anderson Serangoon → Anderson

   2. Jurong Pioneer → Pioneer

   3. Tampines Meridian → Meridian

   4. Yishun Innova → Yishun

   5. Add Serangoon Junior College → 1033 Upper Serangoon Road

   6. Add Jurong Junior College → 800 Corporation Road

   7. Add Tampines Junior College → 2 Tampines Avenue 9

   8. Add Innova Junior College → 21 Champions Way

4. Save this file in the Data folder and name it JC_Before_Mergers.

5.2 Geocoding

1. Open MMQGIS → Geocode → Geocode CSV with Web Service
   

2. Navigate to the JC_before_mergers.csv by clicking the button at the end of the bar under Input CSV File (UTF-8 )

• For Address, select address from the dropdown list

• For City, select City

• For Country, select Country 

• For Web Service, select OpenStreetMap / Nominatim

• Save it in your geocoded file

3. Now you may notice a layer called geocoded appears. Let us rename it to ‘JC_before_merger’

4. Navigate to the notfound file in your geocode folder. There are some schools that are not geocoded properly, and we need to manually find them on the map.

5. Now click Browser -> double click OpenStreetMap

6. For example, Dunman High School is not geocoded properly. We can manually find it by using the OpenStreetMap by using its address.

7. After you find Dunman High School on Open Map, active and tick the JC_before_merger layer, right click and select Toggle Editing.

8. Click the Add Point Feature icon that is circled in red

9. Fill in the necessary information according to your notfound.csv file

10. Repeat the steps for the other schools in the notfound.csv file, now open the attribute table, there should be 20 features, now save it into the Geopackage database

11. Repeat the steps for JC_after_merger as well. You should end up with 16 features.

5.3 Network Accessibility Analysis - JC

The logic for preparing maps including creating the hexagon layer and Network Accessibility Analysis are the same as demonstrated in a previous section. Follow the instructions from section 3.2 with JC before and after merger layers to get walking and driving accessibility maps.

Repeat section 4.4.2 using JC before and after merger data to get the accessibility map for MRT travel.

The thresholds used for driving are 0 - 5 km, 5 - 8 km and more than 8 km.

The thresholds for walking is the same as Primary and Secondary School.

The thresholds used for MRT are 0 - 5 km, 5 - 10 km, more than 10 km.