Home / How To / How to install PostGIS PostgreSQL Geographic Information Systems on Debian 10

How to install PostGIS PostgreSQL Geographic Information Systems on Debian 10



PostGIS is an open source software that allows "Geographic Information Systems" objects to the PostgreSQL object relational database. PostGIS is specifically designed to use lightweight geometries that help servers increase the amount of data migrated from physical disk storage to RAM. It is an extension to PostgreSQL that is used to store room data using geometry and geography data types. It is also used to perform some special queries to determine area, distance, length, circumference and index your data.

In this tutorial we will teach you how to install PostGIS on Debian 10.

Prerequisites

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

Getting Started

Before you begin, it is a good idea to update the system package to the latest version. You can update them with the following command:

  apt-get update -y 
apt-get upgrade -y

After all packages have been updated, restart the system to apply the changes.

Installing PostgreSQL

Before you begin, you must add the PostgreSQL archive to your system.

Download and add the GPG key first with the following command:

  apt-get install gnupg2 -y 
wget - silent -O - https://www.postgresql.org /media/keys/ACCC4CF8.asc | apt-key add -

Then add to 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.listebrit19659009??Next, update the repository and install the PostgreSQL server with the following command: 

  apt-get update -y 
apt-get install postgresql -11- year

When the installation is complete, you can check the status of the 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 setting: enabled)
Active: active (closed) since Sun 2020-04-12 04:58:43 UTC; 15 years ago
Main PID: 2665 (code = terminated, status = 0 / SUCCESS)
Details: 0 (limit: 2359)
Memory: 0B
CGroup: /system.slice/postgresql.service

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

Installing PostGIS

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

You can install it by running only the following command:

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

Once installed, you can proceed to the next step.

Enable PostGIS on PostgreSQL

Next, you must create a PostgreSQL database and users and enable PostGIS functions in a database before you can store spatial data.

First log on to PostgreSQL with the following command:

  su - postgres 

Create then a user and database with the following command:

  [email protected]: ~ $ createuser postgisuser 
[email protected]: ~ $ createb postgis_db -O postgisuser

Then connect to the database you just created:

  [email protected]: ~ $ psql -d postgis_db [19659009] 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.

Then enable the PostGIS extension in the database:

  postgis_db = # CREATE EXTENSION postgis; 

Next, verify PostGIS whether or not it works:

  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

Optimize 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 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 Chapter19659009? ? Change the following parameters depending on your system RAM:   Ads     

  #This value should be about 75% of the server's RAM.
shared_buffers = 1500M
# This allows PostgreSQL to cache more data in memory while performing its sorting
work_mem = 64 MB
# A checkpoint is a periodic action that stores information about your system
checkpoint_segments = 6
#This setting is essentially how long your optimizer should read the read memory before reaching your disk.
random_page_cost = 2.0

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

  systemctl restart postgresql 

Conclusion

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


Source link