Advanced GIS

Assignment 3: SQL

Resources

The CARTO Tips and Tricks page has a handy list of SQL functions and techniques you can use in CARTO and the Guides page has a section covering Analysis.

If you want to learn more about SQL in general (outside of CARTO and mapping), Learn SQL The Hard Way and SQL School are good places to start.

Part 1: SQL

Write at least five SQL statements for datasets in your CARTO account. Base them on a variety of the queries under Common Queries in the SQL Reference. Some should be mappable and some should be unmappable (eg, selecting the COUNT of a table, since that cannot be mapped).

Submit:

the SQL that you performed,
what you wanted to find with the SQL, and
the output of the SQL, either a screenshot of the resulting dataset or of the map, as appropriate.

Part 2: Spatial SQL, Map Kibera

We're going to work through using SQL to locate features within other features using the Map Kibera data. This looks long, but that's mostly because we're going step-by-step. Follow along:

Download education and bound aries data if you don't already have them.
Upload the education and boundaries data to CARTO. Rename the boundaries dataset to kiberaboundaries.
Let's try selecting boundaries by name:
1. Open the kiberaboundaries dataset.
2. In the SQL tab enter the following:
```
SELECT * 
FROM kiberaboundaries
WHERE name = 'Makina'
```
3. Two rows should be shown. Click the preview link to see just those features.
4. Try another name from the dataset and see what the result is.
Now let's look at the education dataset:
1. Open the education dataset.
2. In the SQL tab delete the old query and enter the following:
```
SELECT *
FROM education
WHERE type = 'primary'
```
3. Only the primary schools should show be displayed. Look at the preview. This is not a required step, just making sure that the dataset is loaded correctly and SQL works.
We're going to join the education dataset with the kiberaboundaries dataset. While looking at the education dataset, delete the old SQL query and enter the following SQL:
```
SELECT education.*
FROM education, kiberaboundaries
WHERE ST_within(education.the_geom, kiberaboundaries.the_geom)
AND kiberaboundaries.name = 'Makina'
```
Let's look at this statement line by line:
1. ```
SELECT education.*
```
  Selects all the columns from education.
2. ```
FROM education, kiberaboundaries
```
  We want to have education for its columns, but we also want kiberaboundaries to be available for the next line.
3. ```
WHERE ST_within(education.the_geom, kiberaboundaries.the_geom)
```
  This joins the education rows where the_geom (the point for the row) is within one of the kiberaboundaries rows' the_geom, which is equivalent to a spatial join in a GIS.
4. ```
AND kiberaboundaries.name = 'Makina'
```
  Finally, we're only selecting the kiberaboundaries rows where name is Makina. Because of the join above, this will filter education rows to this
Look at preview and confirm that only education points in Makina appear.
Now that we have this query, we can change the name and see different education features appear on the map. In your SQL, replace Makina with Ayany. Confirm that only those features are selected by looking at the preview.
Finally, we can filter the education filters more by adding conditions to our query. Switch the name back to Makina and add the following line:
```
AND education.type = 'primary'
```
Now we're only selecting education features in Makina that are primary schools.

That's it! You're using spatial SQL queries. Submit a screenshot with the result and your final code.

Part 3: Using Analyses for Spatial Filtering

Using the Analysis feature in CARTO, do something similar to Part 2--filter a points layer to only show those in the polygons of another layer. Style and publish a map using the polygons and filtered points. You will want to at least use the Intersect second layer analysis on the points layer to filter.

(OPTIONAL) Bonus: Advanced CartoCSS

Take a look at the CartoCSS behind Stamen's base layer Toner. Specifically look at the airport labeling section:


#airports[zoom>=10][zoom<=13] {
    marker-width: 18;
    marker-file: url('images/airplane.svg');
}

#airports[zoom>=13][scalerank<=12]
{
    text-name: "[name]";
    [name="Tacoma Int'l"] {
      text-name: "Seattle-" + [name];
    }
    text-face-name: @text_font_poi_bold;
    text-fill: @label_color_poi;
    text-size: @text_font_size_medium;
    text-halo-fill: @label_color_poi_halo;
    text-halo-radius: @text_font_halo_radius_large;
    text-wrap-width: 128;
    text-dy: 14;
    text-allow-overlap: true;
  
    [zoom>=14] {
        text-dy: 0;
    }
}
#airports {
      text-name: "[name]";
      text-face-name: @text_font_poi_bold;
      text-fill: red;
}

Answer the following for up to 5 bonus points this week:

What does text-name do?
How are airports displayed between zoom levels 10 and 13?
What is going on with Tacoma International? How will it be displayed at and above zoom level 13?

Submitting your work

Part 1: submit SQL used and the resulting screenshots.
Part 2: submit SQL used and the resulting screenshot.
Part 3: submit a public, published link to your map.
Bonus: submit the text of your answers, if any.

You may optionally choose to include a short paragraph response or summary of your work.