Introduction to Databases in CARTO

Learn how to query and work with SQL and PostGIS in CARTO with CARTO franchise.

CARTO database is Postgres. This section covers how to leverage Postgres and PostGIS, its geospatial extension, to understand and manage your data. Most of this content applies to any Postgres installation, not just CARTO.

Exercises

• Introduction to SQL
• Common SQL Operations
• Spatial SQL
• Advanced Postgres & PostGIS workshop
• Stored procedures and triggers examples

Set up

This workshop uses CARTO for database interaction with no installation required. The client is Franchise, accessible at https://franchise.carto.io/.

Connection parameters:

• Host name: carto.com
• User name: carto-workshops
• API key: (leave empty)

Available public datasets include:

• ne_10m_populated_places_simple: Natural Earth populated places
• ne_110m_admin_0_countries: Natural Earth country boundaries
• railroad_data: Railroad accidents in the USA
• barcelona_building_footprints: Barcelona blocks
• lineas_madrid: Madrid metro lines
• listings_madrid: Madrid Airbnb listings

Example Query

select *
  from listings_madrid
 where bathrooms >= 3

Run queries using Control+Enter (PC) or Command+Enter (Mac), or click the green play button.

Styling Maps

Click the CARTO icon in the result panel to switch to geographical view. Customize styling using CartoCSS or TurboCARTO:

marker-width: ramp([bathrooms], range(5, 20), quantiles(5));

Resources

• PostGIS Project and official docs
• WKT encoding
• Modern SQL
• Use the Index, Luke
• The 10 most powerful SQL queries
• Pattern matching expressions
• EPSG codes databases:
  • http://epsg.io/
  • http://spatialreference.org/
• Free your maps from Web Mercator

Cartodbfy a Table

CARTO requires special columns and indexes to work with tables in the interface. After creating a table, run:

CREATE TABLE wb2 AS
SELECT w.*,
       count(pp.*) AS places,
       sum(pp.pop_max)  AS cities_pop
  FROM world_borders AS w
  JOIN ne_10m_populated_places_simple AS pp
    ON ST_Intersects(w.the_geom, pp.the_geom)
 GROUP BY w.cartodb_id
 ORDER BY cities_pop DESC
 LIMIT 10;

Then execute:

SELECT CDB_CartoDBFyTable('username', 'wb2');

Note: Include your username if you’re an organization user.