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

Brooke

Daniel

Daniel

Daniel

CartoCSS?

CartoCSS looks like CSS, but they're two different things

#layer {
  marker-width: 3;

  [zoom >= 10] {
    marker-width: 8;
  }
}

#layer {
  marker-width: 3;

  [bright_ti4 >= 350] {
    marker-width: 8;
  }
}

#layer[zoom < 10] {
  marker-fill: blue;
  marker-allow-overlap: true;
  marker-width: 10;
}

final project thoughts

project proposals will be due Feb 24

I will be responding to your thoughts in Canvas

please get in touch if you would like to talk over your idea

analysis

in many cases, these are shortcuts for things you'll do in a GIS

geocoding

buffer

convex hull

some take a long time or won't work at all

some are only available if you pay for credits

you can chain analyses

for example, to combine filters

source

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 dams

SELECT: choose columns from a table

SELECT *: choose all columns

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

SELECT dam_name, river
FROM dams

SELECT *
FROM dams
WHERE cap_mcm > 600

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 dams
WHERE cap_mcm > 600 AND main_use = 'Irrigation'

how would do you get just the dams in Australia or New Zealand?

SELECT *
FROM dams
WHERE country = 'Australia'
  OR country = 'New Zealand'

or you can negate a condition with NOT

for example:

SELECT *
FROM dams
WHERE NOT (
  country = 'Australia' OR
  country = 'New Zealand'
)

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 dams

SELECT COUNT(*)
FROM dams
WHERE country = 'Turkey'

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

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

UPDATE dams
SET quality_display = 'verified'
WHERE quality = '1: Verified'

these will change or 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 dams
SET quality_display = 'good'
WHERE quality = '2: Good'

DELETE FROM dams
WHERE ...

in-class exercise, part 3

more in the SQL reference

SQL is the standard language for all databases

SQL shows up everywhere online

SELECT *
FROM rooms
WHERE id = '4789513'

Carto uses a system called PostGIS

this lets you do more GIS-like things with SQL

SELECT *, ST_area(the_geom)
FROM boundary

source

SELECT *,
  ST_area(the_geom::geography)
FROM boundary

SELECT *,
  ST_area(the_geom::geography)
FROM boundary

calculate area in square meters

SELECT *,
  ST_area(ST_transform(the_geom, 2263))
FROM boundary

SELECT *,
  ST_area(ST_transform(the_geom, 2263))
FROM boundary

(reproject to EPSG:2263, then calculate the area)

SELECT *
FROM dams
WHERE ST_DWithin(
  the_geom::geography,
  cdb_latlng(40.735, -73.994)::geography,
  100000
)

SELECT *
FROM dams
WHERE ST_DWithin(
  the_geom::geography,
  cdb_latlng(40.735, -73.994)::geography,
  100000
)

select features within 100 kilometers

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

source

source

source