Step-by-Step Guide: Enable and Test PostGIS in pgAdmin

1. Connect to Your Database in pgAdmin

Ensure you have created or connected to the target database where you want to enable PostGIS. For example, a database named gis_portfolio.

2. Enable PostGIS Extensions

Open the Query Tool for your database in pgAdmin and run the following SQL commands:

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

If successful, you should see a message like: 

Query returned successfully in ...

3. Test the PostGIS Version

To confirm PostGIS is enabled, run this query:

SELECT PostGIS_Full_Version();

Expected output format:

POSTGIS="3.x.x" [Build info] GEOS="..." PROJ="..."

4. Bonus: Create a Spatial Table and Insert Data

This step confirms spatial functionality by creating a table with geometry data.

CREATE TABLE test_locations (
  id SERIAL PRIMARY KEY,
  name TEXT,
  geom GEOMETRY(Point, 4326)
);

Add a spatial point for Timau, Kenya:

INSERT INTO test_locations (name, geom)
VALUES ('Timau', ST_SetSRID(ST_MakePoint(37.0, -0.083), 4326));

Query the table with spatial output:

SELECT name, ST_AsText(geom) FROM test_locations;

If CREATE EXTENSION Fails

Error Example:

ERROR: could not open extension control file "postgis.control"

This means PostGIS is not installed on your PostgreSQL server.

You need to install it first. For example, on Ubuntu:

sudo apt update
sudo apt install postgis postgresql-14-postgis-3

Check your PostgreSQL version and adjust the package name accordingly.

Additional Tips