How to Install PostGIS in PostgreSQL 12

Yusuf KAHVECİ March 17, 2020 PostgreSQL

What is PostGIS in PostgreSQL?

It would not be wrong to say that PostGIS is an open source software program that supports geometrical features for object relational database.

We use PostGIS together with postgresql. Thanks to PostGIS, we can store the polygon, linestring and point types of the data in the database. We can say that Polygon symbolizes a certain region and linestring is a particular line segment between two locations.

We describe the PostGIS installation on PostrgreSQL 12 step by step below.

How to Install PostGIS in PostgreSQL 12?

Step1: Download RPM Packages

In the first step, we download RPM packages.

sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

1	sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

sudo dnf -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm

1	sudo dnf -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm

Step2: Enable PowerTool

sudo dnf config-manager --set-enabled PowerTools

1	sudo dnf config-manager --set-enabled PowerTools

Step3: Disable default PostgreSQL AppStream Repository

sudo dnf -qy module disable postgresql

1	sudo dnf -qy module disable postgresql

Step4: Install PostGIS

sudo yum install postgis25_12

1	sudo yum install postgis25_12

This will install PostGIS v2.5 for PostgreSQL 12.

rpm -qi postgis25_12

1	rpm -qi postgis25_12

Step5: Create Extensions

After the necessary installations are completed, let’s create EXTENTION now.

su - postgres
createuser demo_usr
createdb demo_postgis -O demo_usr
 
-- connect psql
 
psql -d demo_postgis
 
CREATE EXTENSION postgis;

su - postgres

createuser demo_usr

createdb demo_postgis -O demo_usr

-- connect psql

psql -d demo_postgis

CREATE EXTENSION postgis;

Step5: Check PostGIS Version

SELECT PostGIS_version();

1	SELECT PostGIS_version();

Let’s query the library versions it uses.

select PostGIS_Full_Version();

1	select PostGIS_Full_Version();

Examples

We start our examples by creating a table with an Id and Geometry column below.

create table table_a
(
    id serial NOT NULL,
    geo geometry,
    CONSTRAINT tableA_pkey PRIMARY KEY (id)
);

create table table_a

(

id serial NOT NULL,

geo geometry,

CONSTRAINT tableA_pkey PRIMARY KEY (id)

);

Let’s add a column to the table_a table with the AddGeometryColumn function, which has a position of 4326 in the geographic coordinate named geo2 and will contain two-dimensional coordinates.

SELECT AddGeometryColumn ('public','table_a','geo2',4326,'POINT',2);

1	SELECT AddGeometryColumn ('public','table_a','geo2',4326,'POINT',2);

Create a point with the geomfromtext function

insert into table_a (geo)
select st_geomfromtext('POINT(30.8618697 36.9090847)',4326);

1 2	insert into table_a (geo) select st_geomfromtext('POINT(30.8618697 36.9090847)',4326);

Create a point with the makepoint function and define projection with setsrid

select st_setsrid(
            st_makepoint(32.8618697,39.9090847),4326
        );

select st_setsrid(

st_makepoint(32.8618697,39.9090847),4326

);

Creating geometry from geojson

insert into table_a (geo)
select st_geomfromgeojson('{"type":"Point","coordinates":[32.7703187,39.9449081]}');

1 2	insert into table_a (geo) select st_geomfromgeojson('{"type":"Point","coordinates":[32.7703187,39.9449081]}');

Creating geometry from kml

insert into table_a (geo)
select st_geomfromkml('<Point><coordinates>32.770318699999997,39.944908099999999</coordinates></Point>');

1 2	insert into table_a (geo) select st_geomfromkml('<Point><coordinates>32.770318699999997,39.944908099999999</coordinates></Point>');

Create a line with the makeline

insert into table_a (geo)
select st_makeline(
st_geomfromtext('POINT(32.8618697 39.9090847)',4326), st_geomfromtext('POINT(33.8618697 39.9090847)',4326)
);

insert into table_a (geo)

select st_makeline(

st_geomfromtext('POINT(32.8618697 39.9090847)',4326), st_geomfromtext('POINT(33.8618697 39.9090847)',4326)

);

Create a polygon with makepolygon

insert into table_a (geo)
    SELECT ST_MakePolygon(ST_GeomFromText('LINESTRING(75.15 29.53 1,77 29 1,77.6 29.5 1, 75.15 29.53 1)'));

1 2	insert into table_a (geo) SELECT ST_MakePolygon(ST_GeomFromText('LINESTRING(75.15 29.53 1,77 29 1,77.6 29.5 1, 75.15 29.53 1)'));

Outputs geometry in wkt format

select st_astext(geo) from table_a;

1	select st_astext(geo) from table_a;

Outputs geometry in geojson format

Output in kml format

Projection information must be defined for kml

For PostGIS documentation ; https://postgis.net/stuff/postgis-2.5.pdf

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux