How to install PostGIS on EDB Postgres Advanced Server

January 20, 2023

In this blog, I’ll describe what PostGIS is, how you can set this up quickly and easily using EnterpriseDB’s Advanced Server and scratch the surface of what’s possible using PostGIS.


First of all, what is PostGIS?

PostGIS is a spatial extender for Postgres and adds more geometric types and spatial functionality to Postgres. You can use PostGIS to easily calculate distances between geometric objects, examples of which are points, lines, and polygons; you can easily calculate areas, whether one or more points sit within any geometric shapes (e.g. which town does a house sit within), whether lines cross shape boundaries and so on. One example of current enterprise use of PostGIS is by local administrative organizations (e.g. city councils) who use PostGIS to calculate business rates/land taxes for land ownership/use.


What PostGIS is not...

is something that looks like google maps, although you can set up a tile server application that can present PostGIS data in exactly that way. A future blog will show you how to do that using OpenStreetMap data as your data source and PostGIS as your database.


This blog will show you how easy it is to set up PostGIS on your database server and will finish off by showing you how to use just a few of the spatial functions that are available to you.


So Let’s get started with the installation.

I’ll assume that you have just installed your minimal copy of Centos 7 and your Centos 7 has access to the Internet.

Once we’re all set, let’s log in as the root user.


Let's install a few packages.

yum -y install epel-release
yum -y install openssh-server openssh-clients
yum -y install ntp
yum -y update


Now I’ll set up the time correctly for my location - Sydney, Australia.

timedatectl set-timezone Australia/Sydney

systemctl start ntpd
systemctl enable ntpd


You’ll need an EDB account to complete the following installations. If you don’t already have an EDB account, you can create one (which will give you your access credentials) here:


Next, we’ll install the EDB REPO and set up our EDB YUM repo username and password.

yum -y install


sed -i "s/<username>:<password>/$YUMUSERNAME:$YUMPASSWORD/g" /etc/yum.repos.d/edb.repo
sed -i "\/edbas11/,/gpgcheck/ s/enabled=0/enabled=1/" /etc/yum.repos.d/edb.repo
sed -i "\/enterprisedb-tools]/,/gpgcheck/ s/enabled=0/enabled=1/" /etc/yum.repos.d/edb.repo
sed -i "\/edb-repos/,/gpgcheck/ s/enabled=0/enabled=1/" /etc/yum.repos.d/edb.repo
sed -i "\/enterprisedb-dependencies/,/gpgcheck/ s/enabled=0/enabled=1/" /etc/yum.repos.d/edb.repo


Install EDB Advanced server v 11 and PostGIS

setenforce 0
yum -y install edb-as11
yum -y install edb-as11-postgis
setenforce 1

mkdir /usr/edb/as11/data

chown enterprisedb /usr/edb/as11/data
chgrp enterprisedb /usr/edb/as11/data
chmod 700 /usr/edb/as11/data


We’ll create a file with your environment variables that we will use globally; you can do the same in a bash.rc file if you prefer.

vi /etc/profile.d/


export PGDATA
export PATH
export MANPATH


Save the file, then type:

source /etc/profile.d/


Initialize the Database cluster

su enterprisedb

initdb -D /usr/edb/as11/data/


And startup Advanced Server

cd /usr/edb/as11/data/

pg_ctl -D /usr/edb/as11/data/ -W start


Login to the database using the psql client

psql -U enterprisedb -p 5444 -d template1


At this point you would normally configure your firewall and/or security groups, secure your database, remove default access to the public group and public schema, add a password to the enterprisedb user account, set up pg_hba.conf to tighten up access control and so on.

I’m going to skip all of those steps here as our goal is to just install and run a demo of PostGIS.

Note: if you want advice on setting up, fully securing and tuning EnterpriseDB’s Advanced Server, you can contact EDB using one of the contact methods on the EDB website.


Set up a new database template where we'll add our PostGIS extensions

CREATE DATABASE template_postgis WITH template = template1;

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis';

\c template_postgis

CREATE EXTENSION postgis_topology;
CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;


We now have PostGIS database template setup, PostGIS installed, as well as some relevant supporting extensions.  So let’s go ahead and create a database called test, create a single table and then insert the EDB office names, longitudes and latitudes so that we can use this data to calculate the distances in Km between the different EDB offices.


We'll set up a test database using the template database we just created and then create the table we need.

CREATE DATABASE test WITH template = 'template_postgis';

\c test

CREATE TABLE edb_offices
   office_name VARCHAR(128),
   lng numeric(9,6),
   lat numeric (9,6)


Insert the basic data we need; the data consists of the EDB office names along with their longitude and latitudes.

INSERT INTO edb_offices(office_name , lng , lat) VALUES ('Australia HQ' , 151.206940 , -33.839632);
INSERT INTO edb_offices(office_name , lng , lat) VALUES ('USA HQ' , -71.248010 , 42.510452);
INSERT INTO edb_offices(office_name , lng , lat) VALUES ('EMEA HQ' , -0.757880 , 51.415287);
INSERT INTO edb_offices(office_name , lng , lat) VALUES ('Japan HQ' , 139.713593 , 35.711357);
INSERT INTO edb_offices(office_name , lng , lat) VALUES ('Korea HQ' , 127.077235 , 37.391944);
INSERT INTO edb_offices(office_name , lng , lat) VALUES ('Pakistan HQ' , 73.019291 , 33.671297);
INSERT INTO edb_offices(office_name , lng , lat) VALUES ('Singapore HQ' , 103.859465 , 1.295488);
INSERT INTO edb_offices(office_name , lng , lat) VALUES ('India HQ' , 73.877699 , 18.533235);
INSERT INTO edb_offices(office_name , lng , lat) VALUES ('Netherlands HQ' , 4.846080 , 52.345114);



Now for the interesting part.

Let’s write a query that calculates the distance between the EDB offices using the PostGIS ST_DistanceSpheroid and ST_DistanceSphere functions.

I won’t go into too much detail about how this works here other than to say that the ST_DistanceSphere function calculates distances over the surface of a sphere, whereas ST_DistanceSpheroid calculates the distance over an oblate spheroid which is a little more accurate but takes a little longer to calculate.


Calculate km distances between EDB offices

            'SPHEROID["WGS 84",6378137,298.257223563]'
         ) /1000 As numeric
   ) AS spheroid_distance_in_km,
         ) /1000 As numeric
   ) AS sphere_distance_in_km
   edb_offices a
   edb_offices b

and the result is:

  office_name  |   office_name   | spheroid_distance_in_km | sphere_distance_in_km
 Australia HQ  | EMEA HQ         |                17029.74 |              17034.35
 Australia HQ  | Netherlands HQ  |                16639.74 |              16644.99
 Australia HQ  | USA HQ          |                16222.13 |              16223.09
 USA HQ        | Singapore HQ    |                15127.78 |              15119.45
 USA HQ        | India HQ        |                12362.90 |              12344.39
 Australia HQ  | Pakistan HQ     |                11063.97 |              11078.08
 Singapore HQ  | India HQ        |                 3788.17 |               3789.71
 Pakistan HQ   | India HQ        |                 1679.37 |               1685.44
 Japan HQ      | Korea HQ        |                 1145.71 |               1143.20
 EMEA HQ       | Netherlands HQ  |                  399.41 |                398.20


And that’s it, it’s that easy.


It should be noted that if you just want to run the distance calculations shown above you could just set up the table data, create a small stored procedure which used the haversine formula, Vincenty’s algorithm or another algorithm of your choice to do the calculations although PostGIS has much more functionality than just distance calculations between lng/lat points.


I’ll blog more on the subject of PostGIS over the coming months so that you can get a better idea of the full capabilities it has to offer.

Share this

Relevant Blogs

Why you should use Docker Compose

SUMMARY: This article explains the benefits of using Docker Compose for creating multiple container applications. It reviews the steps for...
January 24, 2023

Quickstart guide on using pgPool

Steps (as root user)   #!/bin/bash   # Setup YUM repository for installing EPAS as the PEM # repository rpm -Uvh   # Set YUM username/password in edb.repo export YUM_USER=&lt;yum...
January 24, 2023

More Blogs

Using auth_method=hba in PgBouncer

Introduction PgBouncer is a great tool for improving database performance with connection pooling.  I've been using it for many years, since it first became available in 2007.  Since then, several...
January 23, 2023

Debugging Your PostgreSQL Database Binaries

Why ? Knowing how to debug is a critical aspect of every application development life cycle. Debugging database bianaries allow you to not only recognize that an exception has occurred...
January 23, 2023