← Advanced GIS

In-class Exercises

Part 1: Analysis

  1. Download today's data. It contains a SHP of global dams and reservoirs collected by numerous groups. The data is available here.
  2. Upload week3data.zip to your Carto account.
  3. Also unzip week3data.zip on your computer and open the PDF of metadata.
  4. Create a Carto map with the dams.
  5. Filter the data using the Filter by column value analysis. For example, show only dams where main_use is Irrigation.

Part 2: Selecting data with SQL

  1. Open the SQL cheatsheet.
  2. Open the dams dataset—not as a map.
  3. Execute a few SQL queries using the patterns outlined in the cheatsheet to answer these questions:
    • How many dams are there?
    • Which dams are in Japan?
    • How many dams are mainly used for irrigation?
    • How many dams were built before 1800?
    It's good practice to keep track of the queries you run in a text file.
  4. Which of the above work in the map preview, too?
  5. Try forming a question of your own and answering it with SQL.

Part 3: Updating data with SQL

  1. Open the dams dataset.
  2. Add a new column, name it quality_display, and make it a string field.
  3. Put a more readable value in our new column based on the quality column. For example: 
    UPDATE dams
SET quality_display = 'verified'
WHERE quality = '1: Verified'
    UPDATE dams
SET quality_display = 'good'
WHERE quality = '2: Good'
  4. Continue with the above step for the other quality values: 3: Fair, 4: Poor, 5: Unreliable.
  5. Create a map with the dataset and include the new column in the popups.