How to Create a Spatial Database With PostgreSQL and pgAdmin 4 on Windows 11 | Complete Guide | PostGIS | 2024

Introduction

In this post we’re going to create a spatial database using a tool called pgAdmin. pgAdmin allows us to write data manipulation queries and perform administrative tasks on PostgreSQL databases.

Prerequisites

Before we begin, make sure you have the following:

• PostgreSQL and pgAdmin installed on your machine. Link
• PostGIS installed on your machine. Link

Step 1: Getting Started with pgAdmin

First, let’s open up pgAdmin. Go to the start menu and search for pgAdmin:

Click on pgAdmin to start the application. The pgAgent will run in the background.

Next, click on the servers dropdown:

The password we set for everything would be “postgres”. Select the save checkbox so you don’t need to type it in every time:

Step 2: Creating a New Database

Next, we need to click on Databases:

You’ll see that our PostgreSQL example spatial database was created for us:

This means that PostGIS is installed and is working, but we want to create our own spatial database.

So we right-click on Databases and then select Create Database:

The name of our database will be cptwater, and then click on Save:

Step 3: Creating a Login Role

The next thing that we need to do is create a login role for our database. This will allow us to access the database from within our application, as well as be able to create tables.

Right-click on Login Roles, and then select Login or Group Role:

The Name of our role will be “waterwatch” in small caps:

Then, select Definition and give it a password. The password will also be “postgres”:

Then we click on Privileges. We want to make this a super user, so just select Superuser. We also want to be able to log in with this role. Then, click Save:

Step 4: Granting Privileges

Now that we have created our login role, the next thing that we need to do is grant all privileges on our database to our new user. Right-click cptwater and then select the Query Tool:

We’re going to execute some simple queries to do this. The first one is:

grant all privileges on database cptwater to waterwatch;

And then select the run button:

Step 5: Adding Spatial Extensions

The next thing we will do is add the spatial extensions to our database, to make it a spatial database. We’ll run two queries. The first one is:

create extension postgis;

And then click run:

The second query that we will run is:

create extension postgis_topology;

And then click run:

Now, we have created our spatial database. And our database is now capable of storing spatial data.

Conclusion

We have successfully created the spatial database and user for our application. So that’s it for this Tutorial. See you in the next one!