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

## 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.  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
``````

#### 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,
WHERE
``````

#### ST_Intersects() returns `true` if the given two geometries intersects

``````SELECT
a.*
FROM
ne_10m_populated_places_simple a,
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,
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
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
)
``````

#### 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
)
``````

#### 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
WHERE
``````

``````WITH grid AS
(SELECT
row_number() over () as cartodb_id,
CDB_HexagonGrid(
ST_Buffer(the_geom_webmercator, 100000),
100000
) AS the_geom_webmercator
FROM
WHERE

SELECT
grid.the_geom_webmercator,
grid.cartodb_id
FROM
WHERE
ST_intersects(grid.the_geom_webmercator, a.the_geom_webmercator)
``````

#### 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: