In this post we are going to talk about How to install PostGIS and PostgreSQL in ubuntu 16.4 (LTS) or Linux system. As we know geographic information system (GIS) is a system designed to capture, store, manipulate, manage, and present spatial or geographic data. And PostGIS adds support for geographic objects allowing location queries to be run in SQL database. As PostGIS is a spatial database extender for PostgreSQL object-relational database. So without wasting time, Let’s start the post to know How to install PostGIS and PostgreSQL in your system.
Intro to PostGIS and PostgreSQL – Install
What is PostGIS?
PostGIS is an open source software program that adds support for geographic objects to the PostgreSQL object-relational database. PostGIS follows the Simple Features for SQL specification from the Open Geospatial Consortium (OGC).
Technically PostGIS was implemented as a PostgreSQL’s extension. And There are a large number of software products that can use PostGIS as a database backend.
The PostGIS implementation is based on “light-weight” geometries and indexes optimized to reduce disk and memory footprint. Using light-weight geometries helps servers increase the amount of data migrated up from physical disk storage into RAM, improving query performance substantially.
PostGIS is registered as “implements the specified standard” for “Simple Features for SQL” by the OGC. PostGIS has not been certified as compliant by the OGC.
What is PostgreSQL?
PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance. As a database server, its primary functions are to store data securely and return that data in response to requests from other software applications. It can handle workloads ranging from small single-machine applications to large Internet-facing applications (or for data warehousing) with many concurrent users; on macOS Server, PostgreSQL is the default database and it is also available for Microsoft Windows and Linux (supplied in most distributions). PostgreSQL is a powerful, open source object-relational database system and It runs on all major operating systems.
It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages)
PostgreSQL supports compound, unique, partial, and functional indexes which can use any of its B-tree, R-tree, hash, or GiST storage methods. GiST(Generalized Search Tree) serves as a foundation for many public projects that use PostgreSQL such a OpenFTS and PostGIS. And as I described above PostGIS is a project which adds support for geographic objects in PostgreSQL, allowing it to be used as a spatial database for geographic information systems (GIS), much like ESRI’s SDE or Oracle’s Spatial extension.
Install PostGIS and PostgreSQL
Installation of PostGIS and PostgreSQL is much simpler then you think. PostgreSQL is available in all Ubuntu versions by default. However, Ubuntu “snapshots” a specific version of PostgreSQL that is then supported throughout the lifetime of that Ubuntu version. Other versions of PostgreSQL are available through the PostgreSQL apt repository.
The PostgreSQL apt repository supports LTS versions of Ubuntu (14.04 and 16.04) on amd64, i386 and ppc64el architectures as well as select non-LTS versions(17.04). While not fully supported, the packages often work on other non-LTS versions as well, by using the closest LTS version available.
The following describes how to install Postgresql 9.6, PostGIS 2.3, pgRouting 2.3, PGAdmin on Ubuntu version 16.04. It is assumed to also work on Linux Mint, Lubuntu, and Xubuntu.
Run these in terminal:
Verifying version of ubuntu
sudo lsb_release -a
Add Repository to sources.list
For xenial (16.4.2 LTS)
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt xenial-pgdg main" >> /etc/apt/sources.list'
Adding keys
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update
Installation
The following will install postgresql 9.6, PostGIS 2.3, PGAdmin4, pgRouting 2.3 and additional supplied modules including the adminpack extension:
sudo apt-get install postgresql-9.6 sudo apt-get install postgresql-9.6-postgis-2.3 postgresql-contrib-9.6 postgresql-9.6-postgis-scripts #to get the commandline tools shp2pgsql, raster2pgsql you need to do this sudo apt-get install postgis
To get pgRouting
# Install pgRouting 2.3 package sudo apt-get install postgresql-9.6-pgrouting
Now we have successfully installed PostgreSQL and PostGIS into the system and pgAdmin lets you create everything using user interface. You don’t need to remember queries to create tables sequence etc. But I am still showing how to create the database from command line .
You may look over full pgRouting postgresql post in detail.
Enable Adminpack
While in terminal, log in to the psql console as postgres user:
sudo -u postgres psql CREATE EXTENSION adminpack;
Never install PostGIS in the postgres database, create a user database You can also enable the PostGIS extension here (or with the GUI as described below):
sudo -u postgres psql
CREATE DATABASE gisdb;\connect gisdb; CREATE SCHEMA postgis; ALTER DATABASE gisdb SET search_path=public, postgis, contrib; \connect gisdb; -- this is to force new search path to take effect CREATE EXTENSION postgis SCHEMA postgis; SELECT postgis_full_version();
should give you output something like this:
postgis_full_version ----------------------------------------------------------------------- POSTGIS="2.4.3 r16312" PGSQL="96" 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" LIBPROTOBUF="1.2.1" RASTER (1 row)
Install pgRouting – postgis and postgresql
CREATE EXTENSION pgrouting; SELECT * FROM pgr_version();
should give you:
version | tag | hash | branch | boost --------+--------+-----------+--------+-------- 2.5.2 | v2.5.2 | 60585f1f7 | master | 1.58.0 (1 row)
Exit the psql console:
\q
This is how we can make database and create schema in command line .
Now let’s see how we can do it in pgAdmin :
PgAdmin – Install PostGIS and PostgreSQL
PostgreSQL has a number of administrative front-ends. The primary one is psql a command-line tool for entering SQL queries. Another popular PostgreSQL front-end is the free and open source graphical to pgAdmin. All queries done in pgAdmin can also be done on the command line with psql.
- Find pgAdmin and start it up.
Install PostGIS and PostgreSQL - If this is the first time you have run pgAdmin, you should have a server entry for PostGIS (localhost:5432) already configured in pgAdmin. Double click the entry, and enter anything you like at the password prompt to connect to the database.
The PostGIS database has been installed with unrestricted access for local users (users connecting from the same machine as the database is running). That means that it will accept any word you provide. If you need to connect from a remote computer, the password for the PostgreSQL user has been set to PostgreSQL.
Creating a Database
- Open the Databases tree item and have a look at the available databases. The PostgreSQL database is the user database for the default PostgreSQL user and is not too interesting to us.
- Right-click on the Databases item and select New Database.
- Fill in the New Database form as shown below and click OK.
Name | nyc | -------+----------+ Owner | postgres |
Install PostGIS and PostgreSQL - Select the new nyc database and open it up to display the tree of objects. You’ll see the public schema.
- Click on the SQL query button indicated below (or go to Tools > Query Tool).
- Enter the following query into the query text field to load the PostGIS spatial extension:
CREATE EXTENSION postgis;
- Click the Play button in the toolbar (or press F5) to “Execute the query.”
- Now confirm that PostGIS is installed by running a PostGIS function:
SELECT postgis_full_version();
You have successfully created a PostgreSQL database with PostGIS!! Let us know by commenting below if you have faced any problem in Install PostGIS and PostgreSQL. Or comment below with the trick you have applied to install postgis.