Installing Tilestache to serve custom ArcGIS layers using Postgres 10 on Ubuntu 16 (Xenial Xerus)

Here I share my work notes to get custom layers that were once in ArcGIS to serve on a Ubuntu web host.

sudo apt-get update
sudo apt-get upgrade
sudo apt install curl
curl -O -L https://bootstrap.pypa.io/get-pip.py
sudo python get-pip.py
sudo pip install -U pillow modestmaps simplejson uuid tilestache
Sudo pip install -U werkzeug

Install postgres-10

sudo touch /etc/apt/sources.list.d/pgdg.list
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get install wget ca-certificates
sudo apt-get update
sudo apt-get install -y postgresql-10 --allow-unauthenticated
sudo apt-get install postgis postgresql-10-postgis-scripts --allow-unauthenticated
- Make sure you have a database extension control file: find /usr -name postgis.control
Install pgRouting 2.3 package -sudo apt-get install -y postgresql-10-pgrouting --allow-unauthenticated

Create a database

In order to first interact with postgresql you need to be a user that is in the postgres superusers list. User “postgres”
- To become the postgres user you need to run: sudo -u postgres psql or sudo -u postgres -i
Create a database
- From the bash shell as the postgres user
  - createdb -E UTF-8 -T template0 <collection_name>
- From the postgresql cli:
  - CREATE DATABASE <collection_name>;

Creating user

When the postgres database is created it automatically creates the user postgres but you might need to create a second user

From Bash:
- sudo -u postgres createuser
From postgres cli:
- create user superuser password '';

Setting to halt on any errors

\set ON_ERROR_STOP on

Giving the user a password

This is needed since the default password for default postgres user is ‘postgres’ From Bash:

sudo -u postgres psql

From PSQL CLI:

ALTER USER with encrypted password ''; or
\PASSWORD <postgresql_user>

By this point in time you will have two users, check with

Notice how one user ‘postgres’ has many more roles than the new user you just created. We will be fixing that in the next step

Grant database privileges on user

psql=#
- ALTER USER <username> createdb superuser createrole replication bypassrls;

Granting privileges on database

psql=#
- grant all privileges on database to ;

Install extensions on table

\connect <database_name>
CREATE EXTENSION adminpack;
CREATE EXTENSION postgis;
CREATE EXTENSION hstore;
CREATE EXTENSION plpgsql; (Usually already added. As it is a part of the postgres install by default)
\quit

As one step in bash:$

psql -d <database_name> -c 'CREATE EXTENSION postgis; CREATE EXTENSION hstore; CREATE EXTENSION adminpack;'

Import ArcGIS database

sudo apt-get -y install gdal-bin
ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=<database_name> user=postgres password=<user_password>" Charlotte.gdb -overwrite -progress --config PG_USE_COPY YES
If you want to import select attributes from the database you can filter adding an sql query to the ogr2ogr command
- sql "SELECT * FROM infile WHERE ID='1'"

Check if it loaded correctly sudo -u postgres psql to open postgres cli

\list to see all databases
\connect epri
\dt to see tables

Download mapnik

Using a combination of steps located here: https://github.com/mapnik/mapnik/wiki/UbuntuInstallation

git init
wget https://github.com/mapnik/mapnik/releases/download/v3.0.17/mapnik-v3.0.17.tar.bz2
tar xvjf mapnik-v3.0.17.tar.bz2
dd mapnik-v3.0.17
git submodule update --init
sudo apt-get install zlib1g-dev clang make pkg-config

Install mapnik

sudo add-apt-repository -y ppa:ubuntu-toolchain-r/test
sudo apt-get update -y
sudo apt-get install -y gcc-6 g++-6 clang-3.8
export CXX="clang++-3.8" && export CC="clang-3.8"

Harfbuzz needs to be installed before you install boost

Check if its already installed

pkg-config --libs --cflags harfbuzz
wget https://www.freedesktop.org/software/harfbuzz/release/harfbuzz-1.7.2.tar.bz2
tar xvfj harfbuzz-1.4.8.tar.bz2
cd harfbuzz
./configure --prefix=/usr --with-gobject && make
sudo make install
sudo apt-get install python zlib1g-dev clang make pkg-config curl
source bootstrap.sh

Return back to your mapnik folder

./configure CUSTOM_CXXFLAGS="-D_GLIBCXX_USE_CXX11_ABI=0" CXX=${CXX} CC=${CC}
Make JOBS=6
make test
Might get an test issue with postgis
sudo make install
Enable mapnik extensions
python scons/scons.py INPUT_PLUGINS='all'

Install TileStache (Tileserver is a WSGI application)

wget https://github.com/TileStache/TileStache/archive/v1.51.5.tar.gz
- (V1.51.5 (Jan 25, 2017))
tar -xzf v1.51.5.tar.gz
cd TileStache-1.51.5
sudo apt-get install gdal-bin
sudo pip install -U TileStache
sudo python setup.py install

Run the tileserver

Run from the ~/Tilestache folder
- python scripts/tilestache-server.py -i ip route get 8.8.8.8 | awk '{print $NF; exit}' -p 8080
Test the tileserver
- http://localhost:8080/vector-postgis-points/8/68/102.geojson

gabefair/Tilestache-guide.md