PostGIS 2.3, pgRouting 2.4, PostgreSQL 9.6 install guide

Regina Obe and Leo Hsu

Newest Addition: pgRouting: A Practical Guide http://locatepress.com/pgrouting

http://www.postgis.us / http://www.bostongis.com / http://www.postgresonline.com
Using RevealJS

Navigating the Slides

To toggle between full slide deck and individual, use the ESC key.

Use s key to see the slide notes.

These instructions apply to all the slides we'll be posting. Best experienced with Firefox or Chrome.

Mac Install

Homebrew http://braumeister.org/formula/postgis http://braumeister.org/formula/pgrouting

Latest recipes include PostgreSQL 9.6, PostGIS 2.3 (with sfcgal 1.3), pgRouting 2.4

Windows Install

  • PostgreSQL EDB (32-bit and 64-bit ) http://postgis.net/windows_downloads Stable version: Get from Application Stackbuilder. PostGIS 2.3 (with SFCGAL 1.3) for PostgreSQL 9.5-9.6 for 32-bit and 64-bit windows, includes pgRouting 2.4 and ogr_fdw.

  • BigSQL. Has PostgreSQL 9.3-9.6 ( for 64-bit), PostGIS 2.3, ogr_fdw (included as part of PostGIS) More detailed instructions from Holly on using the pretty-good commandline (PGC) installer.

    BigSQL does not include pgRouting yet, but for windows you can use the zip files from - http://postgis.net/windows_downloads/ to get pgRouting. Make sure to use the zip files (not the installer) (and change the path where you copy so pg96/share/postgresql/share instead of share folder, lib in pg96/lib/postgresql folder, bin in pg96/bin). You'll also need the libstdc++-6.dll which you can find in the osm2pgrouting zip files in the extra_deps folder. Copy this to pg96/bin.

RedHat EL, CentOS, Scientific Linux Install

RedHat EL, CentOS, Scientific Linux, Amazon Linux: Use PostgreSQL Yum repo: http://yum.postgresql.org has PostgreSQL 9.6, pgRouting 2.4, and PostGIS 2.3

Some instructions you might find useful: An almost idiot's guide to install PostgreSQL 9.5, PostGIS 2.2 and pgRouting 2.1.0 with Yum

Ubuntu and Debian, or bootable media

Create a database

Create a database if you don't have one already. No the database called postgres does not count, and please don't install these extensions in postgres.

CREATE DATABASE lab;
ALTER DATABASE lab SET search_path=public,contrib,postgis;

Connect to your database first and then run the below commands. If you are in psql already, you can connect as follows:

\connect lab;

We'll first create schemas to hold our extensions and then reconnect

CREATE SCHEMA postgis;
CREATE SCHEMA contrib;
set search_path=public,contrib,postgis;

Extensions: Spatially enable your database


CREATE EXTENSION postgis SCHEMA postgis;
CREATE EXTENSION pgrouting SCHEMA postgis;
CREATE EXTENSION hstore SCHEMA contrib; --popularly used with OSM data

Verify install of PostGIS

SELECT postgis_full_version();

POSTGIS="2.3.3 r15473" GEOS="3.6.1-CAPI-1.10.1 r4317" 
PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.2.1, released 2017/06/23" 
LIBXML="2.7.8" LIBJSON="0.12" RASTER
(1 row)

Verify install of pgRouting

SELECT * FROM pgr_version();

pgRouting 2.2 or higher is generally good enough

 version |  tag   |   hash    | branch | boost
---------+--------+-----------+--------+--------
 2.4.1   | v2.4.1 | 722e04e03 | master | 1.59.0
(1 row)