Spatial Pattern Analysis with CartoDB

Introduction to CartoDB

For many general GIS users CartoDB may seem complex and very daunting. But the great thing with CartoDB is that being a master at SQL, CSS and databases is not always required for using CartoDB. For more basic use cases it can be used with limited coding experience. It is extremely powerful and versatile with a wide array of use cases, namely data visualization, spatial analysis, and other geospatial applications.

In this tutorial it will provide a basic run down of how to set up CartoDB and provide some information on performing spatial analysis in CartoDB.

Install Guide for Ubuntu 20.04 LTS x64

In this tutorial it will guide you through the basic process of installing CartoDB on Ubuntu 20.04. Most will likely install CartoDB inside a VM on a local PC but other commercial options exist. The creators of CartoDB have a basic, 12 month free trial and paid options. Alternative hosting options are also available from Digital Ocean.

CartoDB has several dependencies which it uses to run:

• PostgreSQL
• PostGIS
• Redis
• CARTO PostgreSQL extensions
• CARTO Builder
• Maps API
• SQL API

Install GIT

This tutorial requires the use of git for the installation of some packages.

sudo apt-get install git

Install PostgreSQL

Add the custom PostgreSQL repository for CartoDB. The reason for the custom package can be found here.

sudo add-apt-repository ppa:cartodb/postgresql-10 && sudo apt-get update

Then install PostgreSQL

sudo apt-get install postgresql-10 \
postgresql-plpython-10 \
postgresql-server-dev-10

PostgreSQL Configuration

To make this installation easier, and because we are installing within a local environment we can make authentication less secure.

Simply run:

sudo nano etc/postgresql/10/main/pg_hba.conf

And make sure that the lines appear as below:

local all postgres trust
local all all trust
host all all 127.0.0.1/32 trust

Once done restart PostgreSQL:

sudo systemctl restart postgresql

Then add the required CARTO users to PostgreSQL:

sudo createuser publicuser --no-createrole --no-createdb --no-superuser -U postgres
sudo createuser tileuser --no-createrole --no-createdb --no-superuser -U postgres

Install PostgreSQL Helper Extensions

Finally install some PostgreSQL extensions that expand upon PostgreSQL to work better with the other dependencies.

git clone https://github.com/CartoDB/cartodb-postgresql.git
cd cartodb-postgresql
git checkout <LATEST cartodb-postgresql tag>
sudo make all install

Install GDAL and PostGIS

sudo add-apt-repository ppa:cartodb/gis && sudo apt-get update
sudo apt-get install gdal-bin libgdal-dev postgis

Configure PostGIS Database:
sudo createdb -T template0 -O postgres -U postgres -E UTF8 template_postgis
psql -U postgres template_postgis -c 'CREATE EXTENSION postgis;CREATE EXTENSION postgis_topology;'
sudo ldconfig

Install Redis

sudo add-apt-repository ppa:cartodb/redis-next && sudo apt-get update
sudo apt-get install redis

Install Node.js

curl -sL https://deb.nodesource.com/setup_10.x | sudo -E bash -
sudo apt-get install -y nodejs

Some more dependencies:
sudo apt-get install libpixman-1-0 libpixman-1-dev libcairo2-dev libjpeg-dev libgif-dev libpango1.0-dev

I promise we're getting close to the end.

Install SQL API

git clone git://github.com/CartoDB/CartoDB-SQL-API.git
cd CartoDB-SQL-API
npm install

Configure our local development environment and then Start Node.js:
cp config/environments/development.js.example config/environments/development.js
node app.js development

Install MAPS API

git clone git://github.com/CartoDB/Windshaft-cartodb.git
cd Windshaft-cartodb
npm install

Then configure the API and start it:
cp config/environments/development.js.example config/environments/development.js
mkdir logs
node app.js development

Install Ruby

• No not the mineral type
  

sudo apt-add-repository ppa:brightbox/ruby-ng && sudo apt-get update
sudo apt-get install ruby2.4 ruby2.4-dev ruby-bundler
sudo gem install compass

And Finally Install Builder and CartoDB

Download and install CartoDB with additonal dependencies:
git clone --recursive https://github.com/CartoDB/cartodb.git
cd cartodb
sudo apt-get install python-pip imagemagick unp zip libicu-dev
RAILS_ENV=development bundle install
sudo pip install --no-use-wheel -r python_requirements.txt

Finish up:
npm install
npm run carto-node && npm run build:static

Configure CartoDB:
cp config/app_config.yml.sample config/app_config.yml
cp config/database.yml.sample config/database.yml

Start remaining services and initialize database:
sudo systemctl start redis-server
RAILS_ENV=development bundle exec rake db:create
RAILS_ENV=development bundle exec rake db:migrate
RAILS_ENV=development bundle exec rails server

And finally close that console and then in a new one run:
RAILS_ENV=development bundle exec ./script/resque

First Time CartoDB Run

Create user account and development environment:
cd cartodb
export SUBDOMAIN=development

# Add entries to /etc/hosts in development
echo "127.0.0.1 ${SUBDOMAIN}.localhost.lan" | sudo tee -a /etc/hosts

# Create the development user
sh script/create_dev_user

Run the remaining processes:
bundle exec script/resque
bundle exec thin start --threaded -p 3000 --threadpool-size 5
cd cartodb-sql-api && node app.js
cd windshaft-cartodb && node app.js

That's it!
Go to http://<mysubdomain>.localhost.lan:3000 And enter your login created using the password entered above.

User Interface

Now that CartoDB is intstalled there are several ways to begin using it.

How to upload data?

Importing data is seamless and easy. The 'new table' icon with the plus sign will open a dialog box with options to 1) paste a url or select a file, 2) dropbox, 3) google drive data as well as a star 4) a new table from scratch.

How to explore data?

Each dataset is known as a table that corresponds to a spreadsheet that can be visualized on a map. Examination or modification of data are performed by SQL statements.

How to map data? Styling, Visualization Wizard, Base maps, Labels

Cartodb provides full control over styling of map using SQL and CSS. Visualization wizards provide options to visualize data with simple, choropleth, category, bubble, intensity, density and a animated data categorization called torque. Torque, simple and category are used in this project. Column labels are toggled on for each row to provide the user information about name and location for incubators and bixi docks with the addition of how many bikes are available at each location. There are support from base maps from Google, CartoDB, Mapbox, WMS, XYZ and more. CartoDB Dark is used in this project.

Category, Density, Torque

How to share the map?

Maps are shared from the options menu in the top right, by link, embed or as an api.

Dataset

Bicycle Stations (Bixi) and Business Incubators from the City of Toronto open data initiative are the two data sets utilized in this project. As of this writing, business incubators is no longer offered. Bixi dataset had been preprocessed to include the latitude and longitude derived from address.

Data Analysis

Cartodb has full support for both vector and raster data.

Example: SQL statement is derived from Andrew Hill: https://gist.github.com/andrewxhill/5979532.

 SELECT
 ST_MakeLine( --This function can take two or more points and make a line
  the_geom_webmercator, --We select the_geom_webmercator, since CartoDB will need it to draw your maps
  ( --This is a nested query that will run for every row in our outer query
    SELECT the_geom_webmercator FROM plout10 -- Here we select the geometry from our second dataset
    ORDER BY the_geom <-> c.the_geom -- We then order it by its distance to the geometry in the first dataset (c.the_geom)
    LIMIT 1  -- And limit it to just 1, i.e. we find just the closest
    )
    ) the_geom_webmercator -- Here we alias the result to a column we call, 'the_geom_webmercator', so that CartoDB will draw it
  FROM citibike_stations c -- Here we alias our table to 'c' so we can type it nicely above :)

The following SQL statement is used to visualize the closest Biki locations to an incubator. These statement utilizes PostGIS and PostgresSQL functions.

 SELECT 
 ST_MakeLine(the_geom_webmercator, 
   ( 
     SELECT the_geom_webmercator FROM business_incubators_toronto 
     ORDER BY the_geom <-> c.the_geom
     LIMIT 1  
   )
  ) the_geom_webmercator
  FROM station c

References

VirtualBox https://www.virtualbox.org/
Old Ubuntu Releases http://old-releases.ubuntu.com/releases/
Digital Ocean https://www.digitalocean.com/
CartoDB Repository https://github.com/CartoDB/cartodb
PostGIS Reference http://postgis.net/docs/reference.html
PostgreSQL Tutorial http://www.postgresql.org/docs/9.1/static/tutorial.html
Lord Linus's RVM tutorial https://github.com/lordlinus/cartodb
Michael Schmid's suggestions https://groups.google.com/forum/#!topic/cartodb/o5_cVk-owe0
Andrew Hill's SQL query https://gist.github.com/andrewxhill/5979532
Open Data Toronto http://www1.toronto.ca/wps/portal/contentonly?vgnextoid=1a66e03bb8d1e310VgnVCM10000071d60f89RCRD
Bixi http://www1.toronto.ca/wps/portal/contentonly?vgnextoid=ad3cb6b6ae92b310VgnVCM10000071d60f89RCRD&vgnextchannel=1a66e03bb8d1e310VgnVCM10000071d60f89RCRD