← Advanced GIS

In-class Exercises

Part 1: Analysis

  1. Download today's data. It's from Map Kibera.
  2. Add both shapefiles to your CARTO account.
  3. Create a map with both shapefiles.
  4. Add a buffer to the education layer using the Create areas of influence analysis. For this exercise, let's create buffers of 150 meters.
  5. Count the number of watsan features in each buffer by adding another analysis to the education layer: Intersect second layer. Choose the watsan layer as the intersect layer.
  6. The number of watsan features should be in a new column, count_vals. Style the school buffers using this column.

Part 2: Selecting data with SQL

  1. Open the SQL cheatsheet.
  2. Open the education dataset—not as a map.
  3. Execute a few SQL queries using the patterns outlined in the cheatsheet:
    • Count the schools
    • Select schools that are religious
    • Select schools with an email address
    • Select schools with a count of students
    • Average the count of students (you will have to convert it to a number field first)
    • Find the minimum number of students
    Keep track of the queries you run in a text file.
  4. Which of the above work in the map preview, too?

Part 3: Updating data with SQL

  1. Open the education dataset.
  2. Ensure the students and teachers columns are numbers field.
  3. Add a new column, name it st_ratio, and make it a number field.
  4. Calculate the student-teacher ratio and put it in the new column: 
    UPDATE education
SET st_ratio = students / teachers
  5. Create a map styling the education features by st_ratio.