Advanced CartoDB for Data Journalists

This advanced workshop was given by Ernesto Martínez on May 6, 2016 at . As taught, it was 1.5 hours long.
  • Speaker: Ernesto Martínez · ernesto@cartodb.com · @ernesmb
  • May 6th 2016
  • JPD16· IV Jornadas de Periodismo de Datos · Madrid

http://bit.ly/cdb-adv-jpd16

Continue from Introduction to CartoDB Workshop for Data Journalists

Contents

CartoDB as a Platform

  • SQL API allows to interact with CartoDB tables. Query and modify CartoDB tables
  • Import API allows to upload new data to CartoDB
  • Maps API allows to visualize the underlying data

The CartoDB Editor is a client of the platform

PostGIS

geometry vs. geography data types

  • Geometry uses a cartesian plane to measure and store features. CRS units

    • The basis for the PostGIS geometry type is a plane.
    • The shortest path between two points on the plane is a straight line.
    • That means calculations on geometries (areas, distances, lengths, intersections, etc) can be calculated using cartesian mathematics and straight line vectors.

  • Geography uses a sphere to measure and store features. Geographical coordinates

    • The basis for the PostGIS geographic type is a sphere.
    • The shortest path between two points on the sphere is a great circle arc.
    • That means that calculations on geographies (areas, distances, lengths, intersections, etc) must be calculated on the sphere, using more complicated mathematics. For more accurate measurements, the calculations must take the actual spheroidal shape of the world into account, and the mathematics becomes very complicated indeed.

cart vs sph

LA-CDG

Source: Boundless Postgis intro

CartoDB make maps using SQL queries, not tables!

the_geom vs. the_geom_webmercator

CartoDB tables have two geometry fields:

  • the_geom EPSG:4326
    • Geographical unprojected coordinates in decimal degrees (Lon/Lat).
    • WGS84 Spheroid.
  • the_geom_webmercator EPSG:3857
    • Spherical Mercator projected coordinates in meters.
    • Widely accepted as a ‘de facto’ standard in webmapping.

In CartoDB, the_geom_webmercator column is the one we see represented in the map. We can use that column with any other CRS using ST_Transform() like in this example

Some extra resources:

PostGIS Spatial Analysis Queries

We are going to make use of the following datasets, available from CartoDB’s Data Library:

Check this visualization to see the result from each of the queries below

ST_Buffer() creates a round area with a given radius

SELECT
  cartodb_id,
  name,
  ST_Transform(
    ST_Buffer(the_geom::geography, 100000)::geometry
    ,3857
  ) AS the_geom_webmercator
FROM
  ne_10m_populated_places_simple
WHERE
  adm0name LIKE 'Spain'

ST_Difference() calculates the difference between two geometries

SELECT
  a.cartodb_id,
    ST_Difference(
        a.the_geom_webmercator,
        b.the_geom_webmercator
  ) AS the_geom_webmercator
FROM
  ne_50m_land a,
  ne_adm0_europe b
WHERE
  b.adm0_a3 like 'ESP'

ST_Intersects() returns true if the given two geometries intersects

SELECT
  a.*
FROM
  ne_10m_populated_places_simple a,
  ne_adm0_europe b
WHERE
  ST_Intersects(
    b.the_geom_webmercator,
    a.the_geom_webmercator
  )

Using ST_Intersects() to get the number of points inside a polygon

Using GROUP BY:

SELECT
  b.cartodb_id,
  b.name,
  b.the_geom_webmercator,
  count(*) AS pp_count,
  sum(a.pop_max) as sum_pop
FROM
  ne_10m_populated_places_simple a,
  ne_adm0_europe b
WHERE
  ST_Intersects(a.the_geom, b.the_geom)
GROUP BY
  b.cartodb_id,
  b.name,
  b.the_geom_webmercator

Using LATERAL:

SELECT
  a.cartodb_id,
  a.name,
  a.the_geom_webmercator,
  counts.number_cities,
  to_char(counts.sum_pop,'999,999,999') as sum_pop --decimal separator
FROM
  ne_adm0_europe a
CROSS JOIN LATERAL
  (
    SELECT
      count(*) as number_cities,
      sum(pop_max) as sum_pop
    FROM
      ne_10m_populated_places_simple b
    WHERE
      ST_Intersects(a.the_geom, b.the_geom)
  ) AS counts

ST_DWithin() to know wether a geometry is within the given range from another geometry

SELECT
  a.*
FROM
  ne_10m_populated_places_simple a,
  ne_10m_populated_places_simple b
WHERE
    a.cartodb_id != b.cartodb_id
  AND ST_DWithin(
      a.the_geom_webmercator,
      b.the_geom_webmercator,
      150000
    )
  AND a.adm0name = 'Spain'
  AND b.adm0name = 'Spain'

ST_DWithin() + ST_MakeLine() to connect nearby cities

SELECT
  ST_MakeLine(a.the_geom_webmercator, b.the_geom_webmercator) AS the_geom_webmercator
FROM
  ne_10m_populated_places_simple a,
  ne_10m_populated_places_simple b
WHERE
    a.cartodb_id != b.cartodb_id
  AND ST_DWithin(
      a.the_geom_webmercator,
      b.the_geom_webmercator,
      150000
    )
  AND a.adm0name = 'Spain'
  AND b.adm0name = 'Spain'

Generating Grids with CDB functions

Rectangular grid

SELECT
  row_number() over () as cartodb_id,
  CDB_RectangleGrid(
    ST_Buffer(the_geom_webmercator,125000),
  250000,
  250000
  ) AS the_geom_webmercator
FROM 
  ne_adm0_europe
WHERE 
  adm0_a3 IN ('ITA','GBR')

Adaptative Hexagonal grid

WITH grid AS 
(SELECT
  row_number() over () as cartodb_id,
  CDB_HexagonGrid(
    ST_Buffer(the_geom_webmercator, 100000),
    100000
  ) AS the_geom_webmercator
FROM 
  ne_adm0_europe
WHERE 
  adm0_a3 IN ('ESP','ITA'))

SELECT 
  grid.the_geom_webmercator, 
  grid.cartodb_id
FROM
  grid, ne_adm0_europe a
WHERE 
    ST_intersects(grid.the_geom_webmercator, a.the_geom_webmercator)
  AND a.adm0_a3 IN ('ESP','ITA')

Extra resources

Some CartoDB blogposts about spatial SQL

Webmaps with CartoDB.js

CartoDB.js is the JavaScript library that allows to create webmapping apps using CartoDB services quickly and efficiently.

It’s built upon the following components:

Examples

Some advanced examples