Welcome to Advanced GIS, Lecture 3

This is a web page that can be viewed as slides.

→ to move forward

← to go back

Advanced GIS

Class 3

a little more CartoCSS

#earthquakes {
    marker-width: 3;
    ...
    [zoom >= 10] {
        marker-width: 8;
    }
}

#earthquakes {
    marker-width: 3;
    ...
    [place = 'South of Panama'] {
        marker-width: 8;
    }
}

combine conditions with , to set properties with either condition

#earthquakes {
    marker-width: 3;
    ...
    [mag > 5.5],
    [place = 'South of Panama'] {
        marker-width: 15;
    }
}

combine conditions without a , to set properties with both conditions

#earthquakes {
    marker-width: 3;
    ...
    [mag > 4][place = 'South of Panama'] {
        marker-width: 15;
    }
}

#earthquakes {
    marker-width: 3;
    ...
    [mag > 4][place = 'South of Panama'] {
        marker-width: 15;
    }
}

make markers where mag is greater than 4 and place = "South of Panama" larger

matching text within an attribute

[place =~ ".*Africa.*"] {
     marker-width: 15;
}

[place =~ ".*Africa.*"] {
     marker-width: 15;
}

style features with "Africa" anywhere in the place field

#earthquakes {
    marker-width: 5;
    marker-fill: #ff307a;
    marker-allow-overlap: true;

    [zoom >= 4] {
        marker-width: 10;
    }
    [zoom >= 8] {
        marker-width: 15;
    }
    [zoom >= 12] {
        marker-width: 20;
    }
}

what if I want to change marker-width?

#earthquakes {
    marker-width: 6;
    marker-fill: #ff307a;
    marker-allow-overlap: true;

    [zoom >= 4] {
        marker-width: 12;
    }
    [zoom >= 8] {
        marker-width: 18;
    }
    [zoom >= 12] {
        marker-width: 24;
    }
}

there's a better way!

variables

@width: 6;
#earthquakes {
    marker-width: @width;
    marker-fill: #ff307a;
    marker-allow-overlap: true;

    [zoom >= 4] {
        marker-width: 12;
    }
    [zoom >= 8] {
        marker-width: 18;
    }
    [zoom >= 12] {
        marker-width: 24;
    }
}

@width: 6;
#earthquakes {
    marker-width: @width;
    marker-fill: #ff307a;
    marker-allow-overlap: true;

    [zoom >= 4] {
        marker-width: @width * 2;
    }
    [zoom >= 8] {
        marker-width: @width * 3;
    }
    [zoom >= 12] {
        marker-width: @width * 4;
    }
}

then I can change all the widths at once

@width: 8;
#earthquakes {
    marker-width: @width;
    marker-fill: #ff307a;
    marker-allow-overlap: true;

    [zoom >= 4] {
        marker-width: @width * 2;
    }
    [zoom >= 8] {
        marker-width: @width * 3;
    }
    [zoom >= 12] {
        marker-width: @width * 4;
    }
}

it works with colors, too

@quakecolor: #ff307a;
#earthquakes {
    marker-width: @width;
    marker-fill: @quakecolor;
    marker-line-color: @quakecolor;
    marker-line-opacity: 0.2;
    marker-allow-overlap: true;
}

geocoding

buffer

convex hull

in-class exercise, part 1

SQL

SQL

Structured Query Language

SQL

the language databases understand

you'll use SQL in two ways:

1. pulling data out of the database

2. changing the data in the database

databases are great at these types of questions

it's what they were made for

SQL helps you ask these questions in a way databases understand

SELECT *
FROM education

SELECT: choose columns from a table

SELECT *: choose all columns

if you only want a few columns, list them. for example:

SELECT students, teachers
FROM education

SELECT *
FROM education
WHERE students > 100

WHERE: choose rows from a table

these conditions are the same as the ones in CartoCSS:

>
<
=
!=
>=
<=

and you combine them with AND / OR

for example:

SELECT *
FROM education
WHERE students > 100
    AND students < 200

how would do you get just the education features with students between 100 and 150?

SELECT *
FROM education
WHERE students > 100
    AND students < 150

or you can negate a condition with NOT

for example:

SELECT *
FROM education
WHERE NOT (students > 100
  OR op_type = 'religious')

how would you get just the education features with students not between 100 and 150?

SELECT does not change the table, it only changes your view of the table

you can also use SELECT to get a better idea of what data is in your table

count matching rows:

SELECT COUNT(*)
FROM education
WHERE students > 100
  AND op_type = 'religious'

but you'll almost never want to use this statement for your map

how would you get the number of education features with students between 100 and 150?

in-class exercise, part 2

1. pulling data out of the database

2. changing the data in the database

you can also use SQL to quickly UPDATE or DELETE the data in your table

these will destroy data, so have a backup or test it with a table you don't need

this is one way to customize the way data is displayed in your pop-ups

first, create a new column on your table

then put the desired data in that column, based on some condition

UPDATE education
SET size = 'large'
WHERE students > 2500

DELETE FROM education
WHERE ...

more in the SQL reference

SQL shows up everywhere online

SELECT *, ST_area(the_geom)
FROM kiberaboundary

SELECT *,
  ST_area(ST_transform(the_geom_webmercator, 21036))
FROM kiberaboundary

source

SELECT cartodb_id,
  ST_Transform(the_geom, 3573)
    AS the_geom_webmercator
FROM my_table

in-class exercise, part 3