Skip to content

Instantly share code, notes, and snippets.

@dannguyen
Last active May 29, 2021 20:19
Show Gist options
  • Save dannguyen/eff734eacd66c252cc8526e93ae97ba9 to your computer and use it in GitHub Desktop.
Save dannguyen/eff734eacd66c252cc8526e93ae97ba9 to your computer and use it in GitHub Desktop.
padjo-2017 Spatial SQL and joins

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

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


Reading

PADJO walkthroughs

Official CARTO docs

All the Guides

Styling

Carto Builder and SQL

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment