Difference between revisions of "Spatial Pattern Analysis with CartoDB"

From CUOSGwiki
Jump to navigationJump to search
 
(119 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 and zip
 
   
  +
<code>sudo apt-get install postgresql-10 \</code><br>
unp is required for archive file upload support
 
  +
<code> postgresql-plpython-10 \</code><br>
sudo apt-get install unp
 
  +
<code> postgresql-server-dev-10</code>
   
  +
==== PostgreSQL Configuration ====
zip is required for table exports
 
  +
To make this installation easier, and because we are installing within a local environment we can make authentication less secure.
sudo apt-get install zip
 
   
  +
Simply run:
Install GEOS
 
GEOS is required for geometry function support.
 
sudo apt-get install libgeos-c1 libgeos-dev
 
   
  +
<code>sudo nano etc/postgresql/10/main/pg_hba.conf</code>
Install GDAL
 
  +
GDAL is requires for raster support.
 
  +
And make sure that the lines appear as below:
sudo apt-get install gdal-bin libgdal1-dev
 
   
  +
<code>local all postgres trust</code><br>
Install JSON-C
 
  +
<code>local all all trust</code><br>
JSON-C is required for GeoJSON support.
 
  +
<code>host all all 127.0.0.1/32 trust</code><br>
sudo apt-get install libjson0 python-simplejson libjson0-dev
 
   
  +
Once done restart PostgreSQL:
Install PROJ
 
PROJ4 is required for reprojection support.
 
sudo apt-get install proj-bin proj-data libproj-dev
 
   
  +
<code>sudo systemctl restart postgresql</code>
Install PostgreSQL
 
PostgreSQL is the relational database that powers CartoDB.
 
sudo apt-get install postgresql-9.1 postgresql-client-9.1 postgresql-contrib-9.1 postgresql-server-dev-9.1
 
   
plpython is required for Python support
+
Then add the required CARTO users to PostgreSQL:
sudo apt-get install postgresql-plpython-9.1
 
   
  +
<code>sudo createuser publicuser --no-createrole --no-createdb --no-superuser -U postgres</code><br>
Install PostGIS
 
  +
<code>sudo createuser tileuser --no-createrole --no-createdb --no-superuser -U postgres</code>
PostGIS is the geospatial extension that allows PostgreSQL to support geospatial queries. This is the heart of CartoDB!
 
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
 
   
  +
====Install PostgreSQL Helper Extensions====
We are going to use the file manager and navigate to the following folder
 
  +
Finally install some PostgreSQL extensions that expand upon PostgreSQL to work better with the other dependencies.
starting from the computer folder and ending with postgis-2.0.2
 
/usr/local/sr/postgis-2.0.2
 
   
  +
<code>git clone https://github.com/CartoDB/cartodb-postgresql.git</code><br>
Create a new empty document, name/label it es.sh
 
  +
<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===
Open the folder, paste and save the following, and finally close it.
 
   
  +
<code>sudo add-apt-repository ppa:cartodb/gis && sudo apt-get update</code><br>
#!/usr/bin/env bash
 
  +
<code>sudo apt-get install gdal-bin libgdal-dev postgis</code>
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;"
 
   
  +
Configure PostGIS Database:<br>
Back in the terminal, we are back where we started: sudo cd postgis-2.0.2
 
  +
<code>sudo createdb -T template0 -O postgres -U postgres -E UTF8 template_postgis</code><br>
  +
<code>psql -U postgres template_postgis -c 'CREATE EXTENSION postgis;CREATE EXTENSION postgis_topology;'</code><br>
  +
<code>sudo ldconfig</code>
   
  +
===Install Redis===
Run the following command
 
  +
<code>sudo add-apt-repository ppa:cartodb/redis-next && sudo apt-get update</code><br>
  +
<code>sudo apt-get install redis</code>
   
  +
===Install Node.js===
sudo chmod 777 es.sh
 
  +
<code>curl -sL https://deb.nodesource.com/setup_10.x | sudo -E bash -</code><br>
  +
<code>sudo apt-get install -y nodejs</code>
   
  +
Some more dependencies:<br>
Change user to postgres
 
  +
<code>sudo apt-get install libpixman-1-0 libpixman-1-dev libcairo2-dev libjpeg-dev libgif-dev libpango1.0-dev</code>
   
  +
I promise we're getting close to the end.
sudo su postgres
 
   
  +
===Install SQL API===
Rune the following command
 
  +
<code>git clone git://github.com/CartoDB/CartoDB-SQL-API.git</code><br>
  +
<code>cd CartoDB-SQL-API</code><br>
  +
<code>npm install</code>
   
  +
Configure our local development environment and then Start Node.js:<br>
./es.sh
 
  +
<code>cp config/environments/development.js.example config/environments/development.js</code><br>
  +
<code>node app.js development</code>
   
Install Ruby 1.9.1
+
===Install MAPS API===
  +
<code>git clone git://github.com/CartoDB/Windshaft-cartodb.git</code><br>
  +
<code>cd Windshaft-cartodb</code><br>
  +
<code>npm install</code>
   
  +
Then configure the API and start it:<br>
sudo get-apt install ruby1.9.1
 
  +
<code>cp config/environments/development.js.example config/environments/development.js</code><br>
  +
<code>mkdir logs</code><br>
  +
<code>node app.js development</code>
   
Install Node.js
+
===Install Ruby===
  +
*No not the mineral type<br>
sudo apt-get install nodejs=0.8.26-1chl1~precise1
 
   
  +
<code>sudo apt-add-repository ppa:brightbox/ruby-ng && sudo apt-get update</code><br>
Install NPM
 
sudo apt-get install npm=1.3.0-1chl1~precise1
+
<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===
Install Redis
 
Components of CartoDB, like Windshaft or the SQL API depend on Redis.
 
sudo apt-get install redis-server
 
   
  +
Download and install CartoDB with additonal dependencies:<br>
Install EASY INSTALL
 
  +
<code>git clone --recursive https://github.com/CartoDB/cartodb.git</code><br>
sudo apt-get install python-setuptools
 
  +
<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>
Install Python dependencies
 
  +
<code>npm install</code><br>
cd cartodb20
 
  +
<code>npm run carto-node && npm run build:static</code>
$ sudo easy_install pip
 
$ sudo pip install -r python_requirements.txt
 
   
  +
Configure CartoDB:<br>
Install Varnish
 
  +
<code>cp config/app_config.yml.sample config/app_config.yml</code><br>
sudo apt-get install varnish
 
  +
<code>cp config/database.yml.sample config/database.yml</code>
   
  +
Start remaining services and initialize database:<br>
Install Mapnik
 
  +
<code>sudo systemctl start redis-server</code><br>
sudo apt-get install libmapnik-dev python-mapnik2 mapnik-utils
 
  +
<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>
  +
<code>RAILS_ENV=development bundle exec ./script/resque</code>
   
  +
===First Time CartoDB Run===
Install CartoDB SQL API
 
The CartoDB SQL API component powers the SQL queries over HTTP. To install it:
 
git clone git://github.com/CartoDB/CartoDB-SQL-API.git
 
cd CartoDB-SQL-API
 
git checkout master
 
npm install
 
   
  +
Create user account and development environment:<br>
cd CartoDB-SQL-API
 
  +
<code>cd cartodb</code><br>
cd config/environments/
 
  +
<code>export SUBDOMAIN=development</code>
mv development.js.example development.js
 
   
  +
<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 CartoDB-SQL-API
 
  +
<code>sh script/create_dev_user</code>
node app.js development
 
   
  +
Run the remaining processes:<br>
Install Windshaft-cartodb
 
  +
<code>bundle exec script/resque</code><br>
The Windshaft-cartodb component powers the CartoDB Maps API. To install it:
 
  +
<code>bundle exec thin start --threaded -p 3000 --threadpool-size 5</code><br>
git clone git://github.com/CartoDB/Windshaft-cartodb.git
 
  +
<code>cd cartodb-sql-api && node app.js</code><br>
cd Windshaft-cartodb
 
  +
<code>cd windshaft-cartodb && node app.js</code>
git checkout master
 
npm install
 
   
  +
That's it!<br>
  +
Go to http://<mysubdomain>.localhost.lan:3000
  +
And enter your login created using the password entered above.
   
  +
== User Interface ==
cd Windshaft-cartodb
 
  +
Now that CartoDB is intstalled there are several ways to begin using it.<br>
cd config/environments/
 
  +
The three most popular ones that many people use are directly throught the web builder interface online, through Jupiter Notebook or with the PythonSDK.
mv development.js.example development.js
 
   
  +
===How do you upload data?===
cd
 
  +
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?===
cd Windshaft-cartodb
 
  +
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.
node app.js development
 
   
  +
===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 [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.
== User Interface ==
 
  +
  +
===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 :)
   
== 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