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

Clone CartoDB Repository

   git clone --recursive https://github.com/CartoDB/cartodb20.git

ADD CartoDB Personal Package Archives (PPAs)

Add CartoDB GIS PPA

   sudo add-apt-repository ppa:cartodb/gis

Add CartoDB Mapnik PPA

   sudo add-apt-repository ppa:cartodb/mapnik

Add Mapnik Boost PPA

   sudo add-apt-repository ppa:mapnik/boost

Add Chris Lea’s Nodejs Legacy PPA

   sudo add-apt-repository ppa:chris-lea/node.js-legacy

Add CartoDB Redis PPA

   sudo add-apt-repository ppa:cartodb/redis

Add CartoDB PostgreSQL PPA

   sudo add-apt-repository  ppa:cartodb/postgresql

Install unp

   sudo apt-get install unp

Install zip

   sudo apt-get install zip

Install GEOS

   sudo apt-get install libgeos-c1 libgeos-dev

Install GDAL

   sudo apt-get install gdal-bin libgdal1-dev

Install JSON-C

   sudo apt-get install libjson0 python-simplejson libjson0-dev

Install PROJ

   sudo apt-get install proj-bin proj-data libproj-dev

Install PostgreSQL

   sudo apt-get install postgresql-9.1 postgresql-client-9.1 postgresql-contrib-9.1 postgresql-server-dev-9.1

Install Plpython

   sudo apt-get install postgresql-plpython-9.1

Install PostGIS

   cd /usr/local/src
   sudo wget http://download.osgeo.org/postgis/source/postgis-2.0.2.tar.gz
   sudo tar xzf postgis-2.0.2.tar.gz
   cd postgis-2.0.2
   ./configure --with-raster --with-topology
   make
   make install

Use the file manager and navigate to the following folder

   /usr/local/sr/postgis-2.0.2

Create a new document as "es.sh"

Open the folder, paste and save the following, and exit.

   #!/usr/bin/env bash
   POSTGIS_SQL_PATH='pg_config --sharedir'/contrib/postgis-2.0
   createdb -E UTF8 template_postgis
   createlang -d template_postgis plpgsql
   psql -d postgres -c \
   "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis'"
   psql -d template_postgis -f postgis/postgis.sql
   psql -d template_postgis -f spatial_ref_sys.sql
   psql -d template_postgis -f postgis/legacy.sql
   psql -d template_postgis -f taster/rt_pg/rtpostgis.sql
   psql -d template_postgis -f topology/topology.sql
   psql -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"
   psql -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"

Back in the terminal, we are back where we started: sudo cd postgis-2.0.2

Run the following command

   sudo chmod 777 es.sh

Change user to postgres

   sudo su postgres

Run the following command

  ./es.sh

Install Ruby 1.9.1

   sudo get-apt install ruby1.9.1

Install Node.js

   sudo apt-get install nodejs=0.8.26-1chl1~precise1

Install NPM

   sudo apt-get install npm=1.3.0-1chl1~precise1

Install Redis

   sudo apt-get install redis-server

Install EASY INSTALL

   sudo apt-get install python-setuptools

Install Python dependencies

  cd cartodb20
  sudo easy_install pip
  sudo pip install -r python_requirements.txt

Install Varnish

  sudo apt-get install varnish

Install Mapnik

  sudo apt-get install libmapnik-dev python-mapnik2 mapnik-utils

Install CartoDB SQL API

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

  cd CartoDB-SQL-API
  cd config/environments/
  mv development.js.example development.js

  cd

  cd CartoDB-SQL-API 
  node app.js development

Install Windshaft-cartodb

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

   cd config/environments/
   mv development.js.example development.js

   cd

   cd Windshaft-cartodb
   node app.js development

Install Vim

   sudo apt-get install vim

Running CartoDB

  cd cartodb20

1. NOTE: the default server port is 6379

   redis-server

   rvm use 1.9.2@cartodb --create && bundle install

   mv config/app_config.yml.sample config/app_config.yml
   vim config/app_config.yml

   mv config/database.yml.sample config/database.yml
   vim config/database.yml

   echo "127.0.0.1 ${SUBDOMAIN}.localhost.lan" | sudo tee -a /etc/hosts
   sh script/create_dev_user ${SUBDOMAIN}

   $ QUEUE=* bundle exec rake resque:work

   $ bundle exec rails s -p 3000

http://<mysubdomain>.localhost.lan:3000

You should now be able to access http://<mysubdomain>.localhost.lan:3000 in your browser and login with the password specified above.

User Interface

How set up to a personal server?

Digital Ocean is the chosen host of the private server.

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