PostGIS 2.3, PostgreSQL 9.6 install guide

Regina Obe and Leo Hsu

Book in progress: 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.

Where to Get it?

Covered here: http://postgis.net/install, but we'll repeat a bit.

Mac

Quickest and Newbie friendly you have 2 options:

Homebrew another popular option 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

  • 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.3-9.6 for 32-bit and 64-bit windows, includes pgRouting 2.3, ogr_fdw, and pgpointcloud companion extensions.

  • 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.

  • Bleeding Edge PostGIS extensions including with SFCGAL 3D support and PostGIS 2.4, and pgRouting 2.4 (built for PostgreSQL 9.3-9.6) - get from Winnie, the PostGIS windows build-bot. Winnie builds a fresh pot whenever any changes in PostGIS or pgRouting code base. She has updates for stable releases as well. Zips will work for both BigSQL and EDB.

RedHat EL, CentOS, Scientific Linux

RedHat EL, CentOS, Scientific Linux, Amazon Linux: Use PostgreSQL Yum repo: http://yum.postgresql.org has PostgreSQL 9.6, pgRouting 2.3, 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

Create a database

Create a database if you don't have one already. No postgres database does not count, and please don't install PostGIS 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;

Extensions: Spatially enable your database


CREATE EXTENSION postgis SCHEMA postgis;
CREATE EXTENSION hstore SCHEMA contrib; --popularly used with OSM data
CREATE EXTENSION fuzzystrmatch SCHEMA contrib; --required for postgis geocoder
CREATE EXTENSION postgis_tiger_geocoder;

Other extensions commonly used or packaged with PostGIS

CREATE EXTENSION pgrouting SCHEMA postgis;
-- You'll see these more in PostGIS 2.2
CREATE EXTENSION postgis_topology; --packaged with postgis
CREATE EXTENSION postgis_sfcgal SCHEMA postgis; --not always present
CREATE EXTENSION address_standardizer SCHEMA tiger; --packaged with postgis, but not always present
CREATE EXTENSION pointcloud SCHEMA postgis; --usually packaged separately
CREATE EXTENSION pointcloud_postgis SCHEMA postgis; --packaged with pointcloud extension
CREATE EXTENSION ogr_fdw SCHEMA postgis; --yum, BigSQL, and EDB windows have it in binary form

Enabling Drivers for Raster

For security reasons, drivers may be disabled by default. You'll know this if you run query below and get no records. Refer to Enabling GDAL Drivers

SELECT * FROM ST_GDALDrivers();

You can enable all by doing this and then reconnecting to your database.

ALTER DATABASE lab SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';

For all databases in your cluster do at system level

ALTER SYSTEM SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';

Or for case by case session

SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';

No Extensions: Spatially enable your database

Connect to your database first preferably using psql and then run the following scripts which are usually located in /share/contrib/postgis-2.1 of your postgresql install.

\i postgis.sql
\i spatial_ref_sys.sql
-- if you happen to have raster and want it
\i rtpostgis.sql

For tiger geocoder, run the correct scripts Tiger geocoder install without extensions.

Verify install of PostGIS

SELECT postgis_full_version();

POSTGIS="2.3.2 r15302" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" 
GDAL="GDAL 1.11.3, released 2015/09/16" 
LIBXML="2.9.3" LIBJSON="0.11.99" RASTER(1 row)

If you have sfcgal extension installed as well, then your output will look like:

POSTGIS="2.3.2 r15302" GEOS="3.5.0-CAPI-1.9.0 r4084" SFCGAL="1.2.2" 
PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, 
released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" RASTER
(1 row)

Verify install of pgRouting

SELECT * FROM pgr_version();
 version |  tag   |   hash    | branch | boost
---------+--------+-----------+--------+--------
 2.3.2   | v2.3.2 | 1f2af3c52 | master | 1.58.0
(1 row)

pgRouting 2.4.1 came out a couple of days ago, so you might see this

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