Difference between revisions of "Spatial Pattern Analysis with CartoDB"

From CUOSGwiki
Jump to navigationJump to search
 
(59 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.
CartoDB is a cloud based geospatial database with GIS capabilities. Options for running Cartodb are limited to two and further two from each node. 1) A local cloud based instance and or an instance on a private server. 2) A free option with limited storage and a paid option with unlimited storage from Cartodb.com. Data is public in free and consequently searchable, downloadable and transferable. Furthermore, there is a limit of five datasets at any given instance. A local and or private instance are perhaps the best means to familiarize and evaluate the software, and build a customized version to meet each and unique requirements.The aim of this tutorial is to showcase the capabilities of cartodb, as an open source, and web alternative to proprietary and open source GIS software such as ArcGIS and QGIS.
 
   
  +
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 tested on Ubuntu x64 12.04 Precise==
 
   
  +
== Install Guide for Ubuntu 20.04 LTS x64 ==
===Clone CartoDB Repository===
 
git clone --recursive https://github.com/CartoDB/cartodb20.git
 
   
  +
This tutorial will guide you through the basic process of installing CartoDB on Ubuntu 20.04.
ADD CartoDB Personal Package Archives (PPAs)
 
  +
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:
===Add CartoDB GIS PPA===
 
  +
* PostgreSQL
sudo add-apt-repository ppa:cartodb/gis
 
  +
* PostGIS
===Add CartoDB Mapnik PPA===
 
  +
* Redis
sudo add-apt-repository ppa:cartodb/mapnik
 
  +
* CARTO PostgreSQL extensions
===Add Mapnik Boost PPA===
 
  +
* CARTO Builder
sudo add-apt-repository ppa:mapnik/boost
 
  +
* Maps API
===Add Chris Lea’s Nodejs Legacy PPA===
 
  +
* SQL API
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===
+
===Install GIT===
  +
This tutorial requires the use of git for the installation of some packages.
sudo apt-get install unp
 
===Install zip===
 
sudo apt-get install zip
 
   
  +
<code>sudo apt-get install git</code>
===Install GEOS===
 
sudo apt-get install libgeos-c1 libgeos-dev
 
   
===Install GDAL===
+
===Install PostgreSQL===
  +
Add the custom PostgreSQL repository for CartoDB. The reason for the custom package can be found here.
sudo apt-get install gdal-bin libgdal1-dev
 
   
  +
<code>sudo add-apt-repository ppa:cartodb/postgresql-10 && sudo apt-get update</code>
===Install JSON-C===
 
sudo apt-get install libjson0 python-simplejson libjson0-dev
 
   
  +
Then install PostgreSQL
===Install PROJ===
 
sudo apt-get install proj-bin proj-data libproj-dev
 
   
  +
<code>sudo apt-get install postgresql-10 \</code><br>
===Install PostgreSQL===
 
  +
<code> postgresql-plpython-10 \</code><br>
sudo apt-get install postgresql-9.1 postgresql-client-9.1 postgresql-contrib-9.1 postgresql-server-dev-9.1
 
  +
<code> postgresql-server-dev-10</code>
   
  +
==== PostgreSQL Configuration ====
===plpython is required for Python support===
 
  +
To make this installation easier, and because we are installing within a local environment we can make authentication less secure.
sudo apt-get install postgresql-plpython-9.1
 
   
  +
Simply run:
===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
 
   
  +
<code>sudo nano etc/postgresql/10/main/pg_hba.conf</code>
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
 
  +
And make sure that the lines appear as below:
/usr/local/sr/postgis-2.0.2
 
   
  +
<code>local all postgres trust</code><br>
Create a new empty document, name/label it es.sh
 
  +
<code>local all all trust</code><br>
  +
<code>host all all 127.0.0.1/32 trust</code><br>
   
  +
Once done restart PostgreSQL:
Open the folder, paste and save the following, and exit.
 
   
  +
<code>sudo systemctl restart postgresql</code>
#!/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;"
 
   
  +
Then add the required CARTO users to PostgreSQL:
Back in the terminal, we are back where we started: sudo cd postgis-2.0.2
 
   
  +
<code>sudo createuser publicuser --no-createrole --no-createdb --no-superuser -U postgres</code><br>
Run the following command
 
  +
<code>sudo createuser tileuser --no-createrole --no-createdb --no-superuser -U postgres</code>
sudo chmod 777 es.sh
 
   
  +
====Install PostgreSQL Helper Extensions====
Change user to postgres
 
  +
Finally install some PostgreSQL extensions that expand upon PostgreSQL to work better with the other dependencies.
sudo su postgres
 
   
  +
<code>git clone https://github.com/CartoDB/cartodb-postgresql.git</code><br>
Run the following command
 
  +
<code>cd cartodb-postgresql</code><br>
./es.sh
 
  +
<code>git checkout <LATEST cartodb-postgresql tag></code><br>
  +
<code>sudo make all install</code>
   
===Install Ruby 1.9.1===
+
===Install GDAL and PostGIS===
sudo get-apt install ruby1.9.1
 
   
  +
<code>sudo add-apt-repository ppa:cartodb/gis && sudo apt-get update</code><br>
===Install Node.js===
 
sudo apt-get install nodejs=0.8.26-1chl1~precise1
+
<code>sudo apt-get install gdal-bin libgdal-dev postgis</code>
   
  +
Configure PostGIS Database:<br>
===Install NPM===
 
  +
<code>sudo createdb -T template0 -O postgres -U postgres -E UTF8 template_postgis</code><br>
sudo apt-get install npm=1.3.0-1chl1~precise1
 
  +
<code>psql -U postgres template_postgis -c 'CREATE EXTENSION postgis;CREATE EXTENSION postgis_topology;'</code><br>
  +
<code>sudo ldconfig</code>
   
 
===Install Redis===
 
===Install Redis===
  +
<code>sudo add-apt-repository ppa:cartodb/redis-next && sudo apt-get update</code><br>
sudo apt-get install redis-server
 
  +
<code>sudo apt-get install redis</code>
   
===Install EASY INSTALL===
+
===Install Node.js===
  +
<code>curl -sL https://deb.nodesource.com/setup_10.x | sudo -E bash -</code><br>
sudo apt-get install python-setuptools
 
  +
<code>sudo apt-get install -y nodejs</code>
   
===Install Python dependencies===
+
Some more dependencies:<br>
  +
<code>sudo apt-get install libpixman-1-0 libpixman-1-dev libcairo2-dev libjpeg-dev libgif-dev libpango1.0-dev</code>
cd cartodb20
 
sudo easy_install pip
 
sudo pip install -r python_requirements.txt
 
   
  +
I promise we're getting close to the end.
===Install Varnish===
 
sudo apt-get install varnish
 
   
===Install Mapnik===
+
===Install SQL API===
  +
<code>git clone git://github.com/CartoDB/CartoDB-SQL-API.git</code><br>
sudo apt-get install libmapnik-dev python-mapnik2 mapnik-utils
 
  +
<code>cd CartoDB-SQL-API</code><br>
  +
<code>npm install</code>
   
  +
Configure our local development environment and then Start Node.js:<br>
===Install CartoDB SQL API===
 
  +
<code>cp config/environments/development.js.example config/environments/development.js</code><br>
  +
<code>node app.js development</code>
   
  +
===Install MAPS API===
git clone git://github.com/CartoDB/CartoDB-SQL-API.git
 
  +
<code>git clone git://github.com/CartoDB/Windshaft-cartodb.git</code><br>
cd CartoDB-SQL-API
 
  +
<code>cd Windshaft-cartodb</code><br>
git checkout master
 
npm install
+
<code>npm install</code>
   
  +
Then configure the API and start it:<br>
cd CartoDB-SQL-API
 
cd config/environments/
+
<code>cp config/environments/development.js.example config/environments/development.js</code><br>
  +
<code>mkdir logs</code><br>
mv development.js.example development.js
 
  +
<code>node app.js development</code>
   
  +
===Install Ruby===
cd
 
  +
*No not the mineral type<br>
   
  +
<code>sudo apt-add-repository ppa:brightbox/ruby-ng && sudo apt-get update</code><br>
cd CartoDB-SQL-API
 
  +
<code>sudo apt-get install ruby2.4 ruby2.4-dev ruby-bundler</code><br>
node app.js development
 
  +
<code>sudo gem install compass</code>
   
  +
===And Finally Install Builder and CartoDB===
[[File:cartoapi.png]]
 
   
  +
Download and install CartoDB with additonal dependencies:<br>
===Install Windshaft-cartodb===
 
git clone git://github.com/CartoDB/Windshaft-cartodb.git
+
<code>git clone --recursive https://github.com/CartoDB/cartodb.git</code><br>
cd Windshaft-cartodb
+
<code>cd cartodb</code><br>
  +
<code>sudo apt-get install python-pip imagemagick unp zip libicu-dev</code><br>
git checkout master
 
  +
<code>RAILS_ENV=development bundle install</code><br>
npm install
 
  +
<code>sudo pip install --no-use-wheel -r python_requirements.txt</code>
   
  +
Finish up:<br>
cd config/environments/
 
  +
<code>npm install</code><br>
mv development.js.example development.js
 
  +
<code>npm run carto-node && npm run build:static</code>
   
  +
Configure CartoDB:<br>
cd
 
  +
<code>cp config/app_config.yml.sample config/app_config.yml</code><br>
  +
<code>cp config/database.yml.sample config/database.yml</code>
   
  +
Start remaining services and initialize database:<br>
cd Windshaft-cartodb
 
  +
<code>sudo systemctl start redis-server</code><br>
node app.js development
 
  +
<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>
[[File:wind.png]]
 
  +
<code>RAILS_ENV=development bundle exec ./script/resque</code>
   
===Install Vim===
+
===First Time CartoDB Run===
sudo apt-get install vim
 
   
  +
Create user account and development environment:<br>
===Running CartoDB ===
 
  +
<code>cd cartodb</code><br>
cd cartodb20
 
  +
<code>export SUBDOMAIN=development</code>
   
  +
<code># Add entries to /etc/hosts in development</code><br>
# NOTE: the default server port is 6379
 
  +
<code>echo "127.0.0.1 ${SUBDOMAIN}.localhost.lan" | sudo tee -a /etc/hosts</code>
redis-server
 
   
  +
<code># Create the development user</code><br>
[[File:redis.png]]
 
  +
<code>sh script/create_dev_user</code>
   
  +
Run the remaining processes:<br>
rvm use 1.9.2@cartodb --create && bundle install
 
  +
<code>bundle exec script/resque</code><br>
  +
<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>
[[File:gem.png]]
 
  +
Go to http://<mysubdomain>.localhost.lan:3000
 
  +
And enter your login created using the password entered above.
mv config/app_config.yml.sample config/app_config.yml
 
vim config/app_config.yml
 
 
[[File:vim2.png]]
 
 
mv config/database.yml.sample config/database.yml
 
vim config/database.yml
 
 
[[File:vim1.png]]
 
 
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 ==
 
== User Interface ==
  +
Now that CartoDB is intstalled there are several ways to begin using it.<br>
  +
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 set up to a personal server?===
+
===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?===
Digital Ocean is the chosen host of the private server. Once a server ‘droplet’ was created. I logged in as the root user and followed the installation guide outlined above.
 
  +
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.
ssh root@xxx.xxx.xxx.xx
 
   
  +
===How do you map data? How do you visualize data? A short note on styling, the visualization wizard, base maps, and labels.===
===How to upload data?===
 
  +
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.
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.
 
   
  +
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.
[[File:import.png|800px]]
 
 
===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.
 
 
[[File:toggle.png|800px]]
 
   
 
===How to share the map?===
 
===How to share the map?===
   
Maps are shared from the options menu in the top right, enabling users to share by link, embed or as an api.
+
Maps are shared from the options menu in the top right, by link, embed or as an api.
   
 
== Dataset ==
 
== Dataset ==
Line 211: Line 189:
   
 
== Data Analysis ==
 
== Data Analysis ==
Cartodb has full support for both vector and raster data.
+
Cartodb has full support for both vector and raster data. The example below takes advantage of a short SQL script.
 
What is the shortest distance between Biki locations and an incubator?
 
 
This SQL statement is derived from Andrew Hill: https://gist.github.com/andrewxhill/5979532.
 
   
  +
Example: SQL statement is derived from Andrew Hill: https://gist.github.com/andrewxhill/5979532.
Example:
 
   
 
SELECT
 
SELECT
Line 231: Line 205:
   
   
The following SQL statement is used to visualize the closest distance from Biki dock to an incubator location. These statement utilized PostGIS and PostgresSQL functions.
+
The following SQL statement is used to visualize the closest Biki locations to an incubator. These statement utilizes PostGIS and PostgresSQL functions.
   
 
SELECT
 
SELECT
Line 242: Line 216:
 
) the_geom_webmercator
 
) the_geom_webmercator
 
FROM station c
 
FROM station c
 
== Results ==
 
The final map can be assessed from the following url: http://cdb.io/1dBOjEu <br>
 
 
Category
 
 
[[File:cat.png|800px]]<br>
 
 
Density
 
 
[[File:matrix.png|800px]] <br>
 
 
Torque
 
 
[[File:torque.png|800px]] <br>
 
   
 
== 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>
 
VirtualBox https://www.virtualbox.org/ <br>
 
Old Ubuntu Releases http://old-releases.ubuntu.com/releases/ <br>
 
Old Ubuntu Releases http://old-releases.ubuntu.com/releases/ <br>
Line 271: Line 231:
 
Open Data Toronto http://www1.toronto.ca/wps/portal/contentonly?vgnextoid=1a66e03bb8d1e310VgnVCM10000071d60f89RCRD <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>
 
Bixi http://www1.toronto.ca/wps/portal/contentonly?vgnextoid=ad3cb6b6ae92b310VgnVCM10000071d60f89RCRD&vgnextchannel=1a66e03bb8d1e310VgnVCM10000071d60f89RCRD <br>
 
==Takeout==
 
Siefat Fatin http://cdb.io/1dBOjEu --http://siefatfatin.com
 
 
==Made with love in Canada==
 

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