Introduction to databases in CARTO
CARTO database is Postgres on this section of the workshop we cover how to leverage Postgres and PostGIS, its geospatial extension, to understand and manage your data. Most of this content is actually not specific to CARTO and can be applied to any other Postgres installation.
- Introduction to SQL
- Common SQL Operations
- Spatial SQL
- Advanced Postgres & PostGIS workshop
- Stored procedures and triggers examples
For this workshop, we will use CARTO as a convenient way to interact with the database, which will require no installation or configuration by you. You won’t even need an account; we’re using public demo datasets.
As a client for this workshop, we will use a web application that can interact with CARTO: Franchise. You can access to an instance of it, with the CARTO connector enabled, here: https://franchise.carto.io/.
From the side menu, navigate to ‘CARTO’, then use the following parameters to connect:
- Host name:
- User name:
- API key: you can leave this empty
Once connected, you can run
SELECT queries against any public dataset from that account.
Some tables you have available to you from this account are:
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
Try entering a simple query like the one below. To run the query, type
Control+Enter on PCs,
Cmd+Return on macs, or press the green
play button in the bottom right corner of the SQL panel.
select * from listings_madrid where bathrooms >= 3
The results of your query will be displayed in a typical table view, which will allow you to explore all returned fields and rows.
If you hit the small CARTO icon in the bottom right of the result panel, Franchise switches to a geographical result.
This map uses the CartoCSS language to define how data is rendered. By default, all three geometry types (points, lines, polygons) are rendered with default symbology, defined in the panel to the left. You can, however, alter the cartoCSS at any point. After making your edits, apply them by typing
Cmd+Enter. You can even leverage TurboCARTO to generate style ramps quickly. For example, if we wanted to style our points’ size by the number of bathrooms at each point, we can change the default
marker-width from this:
marker-width: ramp([bathrooms], range(5, 20), quantiles(5));
Both CartoCSS and TurboCARTO are out of the scope of this training, but you can find more training materials on the cartography section of this repository.
Other useful links:
- PostGIS Project and official docs
- WKT encoding
- Modern SQL
- Use the Index, Luke
- The 10 most powerful SQL queries
- Pattern matching expressions
- Fill the gaps between two geometries
- Join by proximity
- Jump across the Date Line
- EPSG codes databases:
- Free your maps from Web Mercator
- Stored procedures that return the Antipode’s point for an input geometry
Extra ball: Cartodbfy a table
CARTO needs some special columns, indexes, etc in order to work with tables and show them on the interface.
For this, there is a function that is executed upon import, but that you may need to call manually if you created a table with a
CREATE TABLE query:
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;
Note that your
username is needed as first parameter if you’re an organization user.