PostGIS is an open-source software that allows “Geographic Information Systems” objects to the PostgreSQL object-relational database. The PostGIS is specially designed to use light-weight geometries that help servers to increase the amount of data migrated up from physical disk storage into RAM. It is an extension to the PostgreSQL used to store spatial data using geometry and geography data types. It is also used to perform some special queries to determine area, distance, length, perimeter and create indexes on your data.

In this tutorial, we will learn how to install PostGIS on Debian 10.

Prerequisites

  • A server running Debian 10.
  • A root password is setup on your server.

Getting Started

Before starting, it is a good idea to update your system’s package to the latest version. You can update them using the following command:

apt-get update -y

 apt-get upgrade -y

Once all the packages are updated, restart your system to apply the changes.

Install PostgreSQL

Before starting, you will need to add the PostgreSQL repository to your system.

First, download and add the GPG key with the following command:

apt-get install gnupg2 -y

 wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

Next, add the PostgreSQL repository with the following command:

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | tee /etc/apt/sources.list.d/pgdg.list

Next, update the repository and install the PostgreSQL server with the following command:

apt-get update -y

 apt-get install postgresql-11 -y

Once the installation has been completed, you can check the status of PostgreSQL server with the following command:

systemctl status postgresql

You should get the following output:

? postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Sun 2020-04-12 04:58:43 UTC; 15s ago
 Main PID: 2665 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 2359)
   Memory: 0B
   CGroup: /system.slice/postgresql.service

Apr 12 04:58:43 debian10 systemd[1]: Starting PostgreSQL RDBMS...
Apr 12 04:58:43 debian10 systemd[1]: Started PostgreSQL RDBMS.

Install PostGIS

At this point, the PostgreSQL server is installed and running. Now, it’s time to install PostGIS.

You can install it by simply running the following command:

apt-get install postgis postgresql-11-postgis-2.5 -y

Once installed, you can proceed to the next step.

Activate PostGIS on PostgreSQL

Next, you will need to create a PostgreSQL database and user, and activate PostGIS features on a database before you can store spatial data.

First, login to PostgreSQL with the following command:

su - postgres

Next, create a user and database with the following command:

[email protected]:~$ createuser postgisuser

[email protected]:~$ createdb postgis_db -O postgisuser

Next, connect to the database you just created:

[email protected]:~$ psql -d postgis_db

You should get the following output:

psql (12.2 (Debian 12.2-2.pgdg100 1), server 11.7 (Debian 11.7-0 deb10u1))
Type "help" for help.

Next, enable the PostGIS extension on the database:

postgis_db=# CREATE EXTENSION postgis;

Next, verify the PostGIS whether it is working or not:

postgis_db=# SELECT PostGIS_version();

You should get the following output:

            postgis_version            
---------------------------------------
 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

Finally, exit from the PostgreSQL shell with the following command:

postgis_db=# q

[email protected]:~$ exit

Optimizing PostgreSQL for PostGIS

PostgreSQL is designed to run with small databases to large databases. PostGIS database objects are large compared to text data. So you will need to optimize PostgreSQL to work better with PostGIS objects.

You can optimize the PostgreSQL server by editing the file /etc/postgresql/11/main/postgresql.conf:

nano /etc/postgresql/11/main/postgresql.conf

Change the following parameter depending on your systems RAM:

#This value should be around 75% of your server’s RAM.
shared_buffers = 1500M
#This allows PostgreSQL to cache more data in memory while it performs its sorting
work_mem = 64MB
#A checkpoint is a periodic action that stores information about your system
checkpoint_segments = 6
#This setting essentially is the amount of time that your optimizer should spend reading memory before reaching out to your disk.
random_page_cost = 2.0

Save and close the file when you are finished. Then, restart the PostgreSQL service to implement the changes.

systemctl restart postgresql

Conclusion

Congratulations! you have successfully installed PostGIS on the PostgreSQL server. You have now database configured for spatial queries. For more information, visit the PostGIS documentation at PostGIS.

Show Comments