Difference between revisions of "Spatial Pattern Analysis with CartoDB"

From CUOSGwiki
Jump to navigationJump to search
 
(111 intermediate revisions by 3 users not shown)
Line 1: Line 1:
== Introduction ==
+
== 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.
   
  +
This tutorial 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 ==
   
  +
This tutorial 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 [https://carto.com/pricing/ free trial] and paid options. Alternative hosting options are also available from Digital Ocean.
   
  +
CartoDB has several dependencies which it uses to run:
== Install Guide==
 
  +
* 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.
   
  +
<code>sudo apt-get install git</code>
git clone --recursive https://github.com/CartoDB/cartodb20.git
 
   
  +
===Install PostgreSQL===
ADD CartoDB Personal Package Archives (PPAs)
 
  +
Add the custom PostgreSQL repository for CartoDB. The reason for the custom package can be found here.
   
  +
<code>sudo add-apt-repository ppa:cartodb/postgresql-10 && sudo apt-get update</code>
===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
 
   
  +
Then install PostgreSQL
Install unp
 
sudo apt-get install unp
 
Install zip
 
sudo apt-get install zip
 
   
  +
<code>sudo apt-get install postgresql-10 \</code><br>
Install GEOS
 
  +
<code> postgresql-plpython-10 \</code><br>
sudo apt-get install libgeos-c1 libgeos-dev
 
  +
<code> postgresql-server-dev-10</code>
   
  +
==== PostgreSQL Configuration ====
Install GDAL
 
  +
To make this installation easier, and because we are installing within a local environment we can make authentication less secure.
sudo apt-get install gdal-bin libgdal1-dev
 
   
  +
Simply run:
Install JSON-C
 
sudo apt-get install libjson0 python-simplejson libjson0-dev
 
   
  +
<code>sudo nano etc/postgresql/10/main/pg_hba.conf</code>
Install PROJ
 
  +
sudo apt-get install proj-bin proj-data libproj-dev
 
  +
And make sure that the lines appear as below:
   
  +
<code>local all postgres trust</code><br>
Install PostgreSQL
 
  +
<code>local all all trust</code><br>
sudo apt-get install postgresql-9.1 postgresql-client-9.1 postgresql-contrib-9.1 postgresql-server-dev-9.1
 
  +
<code>host all all 127.0.0.1/32 trust</code><br>
   
  +
Once done restart PostgreSQL:
plpython is required for Python support
 
sudo apt-get install postgresql-plpython-9.1
 
   
  +
<code>sudo systemctl restart postgresql</code>
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
 
   
  +
Then add the required CARTO users to PostgreSQL:
We are going to use the file manager and navigate to the following folder
 
starting from the computer folder and ending with postgis-2.0.2
 
/usr/local/sr/postgis-2.0.2
 
   
  +
<code>sudo createuser publicuser --no-createrole --no-createdb --no-superuser -U postgres</code><br>
Create a new empty document, name/label it es.sh
 
  +
<code>sudo createuser tileuser --no-createrole --no-createdb --no-superuser -U postgres</code>
   
  +
====Install PostgreSQL Helper Extensions====
Open the folder, paste and save the following, and finally close it.
 
  +
Finally install some PostgreSQL extensions that expand upon PostgreSQL to work better with the other dependencies.
   
  +
<code>git clone https://github.com/CartoDB/cartodb-postgresql.git</code><br>
  +
<code>cd cartodb-postgresql</code><br>
  +
<code>git checkout <LATEST cartodb-postgresql tag></code><br>
  +
<code>sudo make all install</code>
   
  +
===Install GDAL and PostGIS===
#!/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;"
 
   
  +
<code>sudo add-apt-repository ppa:cartodb/gis && sudo apt-get update</code><br>
Back in the terminal, we are back where we started: sudo cd postgis-2.0.2
 
  +
<code>sudo apt-get install gdal-bin libgdal-dev postgis</code>
   
  +
Configure PostGIS Database:<br>
Run the following command
 
  +
<code>sudo createdb -T template0 -O postgres -U postgres -E UTF8 template_postgis</code><br>
sudo chmod 777 es.sh
 
  +
<code>psql -U postgres template_postgis -c 'CREATE EXTENSION postgis;CREATE EXTENSION postgis_topology;'</code><br>
  +
<code>sudo ldconfig</code>
   
  +
===Install Redis===
Change user to postgres
 
  +
<code>sudo add-apt-repository ppa:cartodb/redis-next && sudo apt-get update</code><br>
sudo su postgres
 
  +
<code>sudo apt-get install redis</code>
   
  +
===Install Node.js===
Rune the following command
 
  +
<code>curl -sL https://deb.nodesource.com/setup_10.x | sudo -E bash -</code><br>
./es.sh
 
  +
<code>sudo apt-get install -y nodejs</code>
   
  +
Some more dependencies:<br>
Install Ruby 1.9.1
 
  +
<code>sudo apt-get install libpixman-1-0 libpixman-1-dev libcairo2-dev libjpeg-dev libgif-dev libpango1.0-dev</code>
sudo get-apt install ruby1.9.1
 
   
  +
I promise we're getting close to the end.
Install Node.js
 
sudo apt-get install nodejs=0.8.26-1chl1~precise1
 
   
Install NPM
+
===Install SQL API===
  +
<code>git clone git://github.com/CartoDB/CartoDB-SQL-API.git</code><br>
sudo apt-get install npm=1.3.0-1chl1~precise1
 
  +
<code>cd CartoDB-SQL-API</code><br>
  +
<code>npm install</code>
   
  +
Configure our local development environment and then Start Node.js:<br>
Install Redis
 
  +
<code>cp config/environments/development.js.example config/environments/development.js</code><br>
sudo apt-get install redis-server
 
  +
<code>node app.js development</code>
   
Install EASY INSTALL
+
===Install MAPS API===
  +
<code>git clone git://github.com/CartoDB/Windshaft-cartodb.git</code><br>
sudo apt-get install python-setuptools
 
  +
<code>cd Windshaft-cartodb</code><br>
  +
<code>npm install</code>
   
  +
Then configure the API and start it:<br>
Install Python dependencies
 
  +
<code>cp config/environments/development.js.example config/environments/development.js</code><br>
cd cartodb20
 
  +
<code>mkdir logs</code><br>
sudo easy_install pip
 
  +
<code>node app.js development</code>
sudo pip install -r python_requirements.txt
 
   
Install Varnish
+
===Install Ruby===
  +
*No not the mineral type<br>
sudo apt-get install varnish
 
   
  +
<code>sudo apt-add-repository ppa:brightbox/ruby-ng && sudo apt-get update</code><br>
Install Mapnik
 
sudo apt-get install libmapnik-dev python-mapnik2 mapnik-utils
+
<code>sudo apt-get install ruby2.4 ruby2.4-dev ruby-bundler</code><br>
  +
<code>sudo gem install compass</code>
   
  +
===And Finally Install Builder and CartoDB===
   
  +
Download and install CartoDB with additonal dependencies:<br>
Install CartoDB SQL API
 
  +
<code>git clone --recursive https://github.com/CartoDB/cartodb.git</code><br>
  +
<code>cd cartodb</code><br>
  +
<code>sudo apt-get install python-pip imagemagick unp zip libicu-dev</code><br>
  +
<code>RAILS_ENV=development bundle install</code><br>
  +
<code>sudo pip install --no-use-wheel -r python_requirements.txt</code>
   
  +
Finish up:<br>
git clone git://github.com/CartoDB/CartoDB-SQL-API.git
 
  +
<code>npm install</code><br>
cd CartoDB-SQL-API
 
  +
<code>npm run carto-node && npm run build:static</code>
git checkout master
 
npm install
 
   
cd CartoDB-SQL-API
+
Configure CartoDB:<br>
  +
<code>cp config/app_config.yml.sample config/app_config.yml</code><br>
cd config/environments/
 
  +
<code>cp config/database.yml.sample config/database.yml</code>
mv development.js.example development.js
 
   
  +
Start remaining services and initialize database:<br>
cd
 
  +
<code>sudo systemctl start redis-server</code><br>
  +
<code>RAILS_ENV=development bundle exec rake db:create</code><br>
  +
<code>RAILS_ENV=development bundle exec rake db:migrate</code><br>
  +
<code>RAILS_ENV=development bundle exec rails server</code>
   
  +
And finally close that console and then in a new one run:<br>
cd CartoDB-SQL-API
 
  +
<code>RAILS_ENV=development bundle exec ./script/resque</code>
node app.js development
 
   
  +
===First Time CartoDB Run===
Install Windshaft-cartodb
 
git clone git://github.com/CartoDB/Windshaft-cartodb.git
 
cd Windshaft-cartodb
 
git checkout master
 
npm install
 
   
  +
Create user account and development environment:<br>
cd config/environments/
 
  +
<code>cd cartodb</code><br>
mv development.js.example development.js
 
  +
<code>export SUBDOMAIN=development</code>
   
  +
<code># Add entries to /etc/hosts in development</code><br>
cd
 
  +
<code>echo "127.0.0.1 ${SUBDOMAIN}.localhost.lan" | sudo tee -a /etc/hosts</code>
   
  +
<code># Create the development user</code><br>
cd Windshaft-cartodb
 
  +
<code>sh script/create_dev_user</code>
node app.js development
 
   
  +
Run the remaining processes:<br>
Install Vim
 
  +
<code>bundle exec script/resque</code><br>
sudo apt-get install vim
 
  +
<code>bundle exec thin start --threaded -p 3000 --threadpool-size 5</code><br>
  +
<code>cd cartodb-sql-api && node app.js</code><br>
  +
<code>cd windshaft-cartodb && node app.js</code>
   
  +
That's it!<br>
Running CartoDB
 
  +
Go to http://<mysubdomain>.localhost.lan:3000
cd cartodb20
 
  +
And enter your login created using the password entered above.
   
  +
== User Interface ==
# NOTE: the default server port is 6379
 
  +
Now that CartoDB is intstalled there are several ways to begin using it.<br>
redis-server
 
  +
The three most popular ones that many people use are directly throught the web builder interface online, through Jupiter Notebook or with the PythonSDK.
   
  +
===How do you upload data?===
rvm use 1.9.2@cartodb --create && bundle install
 
  +
Importing data is seamless and easy. The 'new table' icon with the plus sign will open a dialog box with options to:
  +
* paste a url or select a file (e.x. shapefiles),
  +
* Online cloud storage such as dropbox or google drive data,
  +
* or create a new table from scratch or other database services
   
  +
===How do you explore added data?===
mv config/app_config.yml.sample config/app_config.yml
 
  +
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 or using the GUI.
vim config/app_config.yml
 
   
  +
===How do you map data? How do you visualize data? A short note on styling, the visualization wizard, base maps, and labels.===
mv config/database.yml.sample config/database.yml
 
  +
Cartodb provides full control over styling of map using SQL, Python 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.
vim config/database.yml
 
   
  +
Many great examples of [https://carto.com/spatial-data-catalog/ projects] created with CartoDB and further [https://vimeo.com/channels/carto video tutorials] or [https://carto.com/help/ help centers] exist online.
echo "127.0.0.1 ${SUBDOMAIN}.localhost.lan" | sudo tee -a /etc/hosts
 
sh script/create_dev_user ${SUBDOMAIN}
 
   
  +
===How to share the map?===
$ QUEUE=* bundle exec rake resque:work
 
   
  +
Maps are shared from the options menu in the top right, by link, embed or as an api.
$ bundle exec rails s -p 3000
 
   
  +
== Dataset ==
You should now be able to access http://<mysubdomain>.localhost.lan:3000 in your browser and login with the password specified above.
 
   
  +
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.
== User Interface ==
 
   
  +
== Data Analysis ==
  +
Cartodb has full support for both vector and raster data. The example below takes advantage of a short SQL script.
   
  +
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 :)
   
== Results ==
 
   
  +
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 ==
 
== References ==
  +
CartoDB Developers Page: https://carto.com/developers/<br>
  +
CartoDB Official Documentation: https://cartodb.readthedocs.io/en/latest/install.html<br>
  +
VirtualBox https://www.virtualbox.org/ <br>
  +
Old Ubuntu Releases http://old-releases.ubuntu.com/releases/ <br>
  +
Digital Ocean https://www.digitalocean.com/ <br>
  +
CartoDB Repository https://github.com/CartoDB/cartodb <br>
  +
PostGIS Reference http://postgis.net/docs/reference.html <br>
  +
PostgreSQL Tutorial http://www.postgresql.org/docs/9.1/static/tutorial.html <br>
  +
Lord Linus's RVM tutorial https://github.com/lordlinus/cartodb <br>
  +
Michael Schmid's suggestions https://groups.google.com/forum/#!topic/cartodb/o5_cVk-owe0 <br>
  +
Andrew Hill's SQL query https://gist.github.com/andrewxhill/5979532 <br>
  +
Open Data Toronto http://www1.toronto.ca/wps/portal/contentonly?vgnextoid=1a66e03bb8d1e310VgnVCM10000071d60f89RCRD <br>
  +
Bixi http://www1.toronto.ca/wps/portal/contentonly?vgnextoid=ad3cb6b6ae92b310VgnVCM10000071d60f89RCRD&vgnextchannel=1a66e03bb8d1e310VgnVCM10000071d60f89RCRD <br>

Latest revision as of 14:17, 19 October 2020

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.

This tutorial 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

This tutorial 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.
The three most popular ones that many people use are directly throught the web builder interface online, through Jupiter Notebook or with the PythonSDK.

How do you upload data?

Importing data is seamless and easy. The 'new table' icon with the plus sign will open a dialog box with options to:

  • paste a url or select a file (e.x. shapefiles),
  • Online cloud storage such as dropbox or google drive data,
  • or create a new table from scratch or other database services

How do you explore added 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 or using the GUI.

How do you map data? How do you visualize data? A short note on styling, the visualization wizard, base maps, and labels.

Cartodb provides full control over styling of map using SQL, Python 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.

Many great examples of projects created with CartoDB and further video tutorials or help centers exist online.

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. The example below takes advantage of a short SQL script.

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

CartoDB Developers Page: https://carto.com/developers/
CartoDB Official Documentation: https://cartodb.readthedocs.io/en/latest/install.html
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