padjo-2017 Spatial SQL and joins

_inclass-spatial-sql-and-joins.md

Spatial SQL Lesson

SQL and PostGIS and CARTO Builder! How to make data maps with SQL.

You can find some of the datasets listed in this lesson all on this particular landing page (for other ideas)

Styling maps and spatial points

Earthquakes

Connect this dataset: USGS earthquakes M3.5+ since 2000

Schools

Connect this dataset: School demographic and free lunch participation (ccd_schools)

---

Shapes

Connect: congress districts 114th

Connect: members of the 114th US Congress

Connect: Presidential elections by state

Connect: US State Boundaries

notes.md

Spatial SQL Lesson

SQL and PostGIS and CARTO Builder! How to make data maps with SQL.

You can find some of the datasets listed in this lesson all on this particular landing page (for other ideas)

Styling maps and spatial points

Earthquakes

Connect this dataset: USGS earthquakes M3.5+ since 2000

Map styling

• Change the basemap
• Publish the map
• Edit the layer
  • Edit dot style
  • Show time map

Using SQL and Data

• Check out the Data panel
• Use SQL to do a count by year
• Use SQL to filter by year
• Make multiple layers

Schools

Connect this dataset: School demographic and free lunch participation (ccd_schools)

Map styling

• Edit the layer
  • Change dot color by value

Using SQL and Data

• Check out the Data panel
• SELECT ROUND(free_or_reduced_lunch_count * 100 / enrollment) AS pctfreelunch, ccd_schools.* FROM ccd_schools;

Style the map so that schools are colored by their free-lunch-participation.

---

Shapes

Connect: congress districts 114th

Connect: members of the 114th US Congress

Connect: Presidential elections by state

Connect: US State Boundaries

Other data

• Census zipcode shapes
• Census county shapes
• Starbucks worldwide
• ATF licensed dealers

---

Reading

PADJO walkthroughs

• Getting started with CARTO Builder - how to log in, connect/import a dataset, some basic map styling (missing a few captions)

• Mapping NYPD precincts with CartoDB

• Joining crime data to NYPD precincts to create a choropleth map in CartoDB

• How to map both the quantity and change of NYPD precinct-level crime data

• Creating a multi-layered CartoDB map of Census household incomes and Starbucks locations in NYC

• Carto, Political Boundaries, SQL, and Guns and Coffee

• Using PostGIS, SQL, and CartoDB to identify schools at risk from earthquake - Kind of an all-on-one what-is-data-sql-geospatial-census lesson. Might be a little long for most people.

Official CARTO docs

All the Guides

Styling

• Basemaps for Rendering Map Backgrounds
• Style by Value
• Choropleth Map for Statistical Data
• Animating Maps with Point Data

Carto Builder and SQL

• Introducing Carto Builder
• SQL and PostGIS in CARTO
  • Learning SQL through the CARTO Editor
  • PostGIS in CartoDB
  • JOINs in SQL and PostGIS