Ken Tong

How to Setup a PostGIS Database on Windows from Scratch

March 31, 2021

This is a guide to setting up a PostGIS database on your Windows computer. This is not a deep dive into the database.

This guide will show you how to:

No-installation PostgreSQL Setup

The first thing we need to do is install the PostgreSQL and PostGIS.

We’ll setup our database with the no-installation method which will work whether you have Administrator privileges or not.

Let’s say you want C:/PostgreSQL/pgsql to be your PostgreSQL installation directory.

  1. Create the installation directory on your computer.

  2. Download the zipped binaries for PostgreSQL and unzip the contents to C:/PostgreSQL/pgsql.

  3. Download the zipped binaries for PostGIS and unzip the files into C:/PostgreSQL/pgsql.

If you have Administrator privileges or if you’re installing PostGIS in a production server, you should use the PostgreSQL installer by EDB.

The installer includes StackBuilder, a package manager that is used to install additional PostgreSQL tools and extensions like PostGIS.

Follow the EDB guide on how to install PostgreSQL and extensions.

PostgreSQL and PostGIS are now installed.

Initialize the PostgreSQL Storage Area

Before we create a database we have to tell PostgreSQL where to store our data on disk. This is called a database cluster.

  1. To create the cluster, open Notepad and paste the following lines into into the file.
@ECHO ON
REM The script sets environment variables helpful for PostgreSQL
@SET PATH="%~dp0\bin";%PATH%
@SET PGDATA=%~dp0\data
@SET PGDATABASE=postgres
@SET PGUSER=postgres
@SET PGPORT=5432
@SET PGLOCALEDIR=%~dp0\share\locale
REM Run the line below un-remarked for the first time to 
REM initalize database cluster
"%~dp0\bin\initdb" -U postgres -E UTF8 -A trust
"%~dp0\bin\pg_ctl" -D "%~dp0/data" -l logfile start
ECHO "Click enter to stop"
pause
"%~dp0\bin\pg_ctl" -D "%~dp0/data" stop

  1. Save the file as start_pgsql.bat in C:/PostgreSQL/pgsql.

  2. Double-click to run start_pgsql.bat to initalize the cluster and start the PostgreSQL database server. You will see a Command Window pop up, and stay up.

  3. Click Enter to close the Command Window to stop the PostgreSQL database server.

  4. The database cluster is now initialized. We can comment out the line that initializes the cluster when we start the PostgreSQL server. Open start_pgsql.bat in Notepad and comment out the line that initalizes the cluster by putting REM in front of that line.

REM "%~dp0\bin\initdb" -U postgres -E UTF8 -A trust
  1. Save the file.

We now have a .bat file that will start up the PostgeSQL server.

Remember to start the PostgreSQL database server first by running start_pgsql.bat before accessing your database.

Connect to PostgreSQL with pgAdmin

We will use pgAdmin, a management tool for PostgreSQL, to connect to our server and finish the setup of our spatial database.

Start your PostgreSQL server with start_pqsql.bat if your it isn’t started.

Start the pgAdmin4 web application by double-clicking C:/PostgreSQL/pgsql/bin/pgAdmin 4/bin/pgAdmin4.exe and wait for the web interface to come up.

Create a new connection with:

Maintanence database refers to the name of the initial databse you will this connection will connect to.

Save the connection information. Double-click the connection you created and connect to the PostgreSQL database.

Create Roles

Users, groups, and roles are the same thing in PostgreSQL. You can think of a role that can login to the database as a user.

We will create a role that can login and create new roles. This role will also own the spatial database and own the schema that we will store our data in.

We will name the role gis_layers_admin with the password admin.

  1. Open a Query Editor by right-clicking on the postgres database and selecting Query Tool....

  2. Copy and paste the SQL statement in the query editor and click Execute.

-- create login role
create role gis_layers_admin with
    login
    createrole
    password 'admin';

Create the PostGIS database

We will create the spatial database and set gis_layers_admin as the owner.

Run the SQL below to in query windows to create the database.

-- create a database called gis with owner gis_layers_admin
create database gis with
    owner = gis_layers_admin
    encoding = 'UTF8'
    connection limit = -1;

The newly created database should npw be visible under the localhost - postgres connection we created earlier. If the database is not there, right click on the connection and select Refresh...

Close the Query Editor window for the postgres database.

Open a new query editor window for the gis database by right-clicking gis database and selecting Query Tool....

Create a schema for our data

We will store our data in a schema called gis_layers. The schema will be owned by our gis_layers_admin role.

Copy the following lines into the editor and executing the SQL statements. Make sure you are using a query editor window for the gis database.

-- create the schema and set the owner
create schema gis_layers authorization gis_layers_admin;

Enable the PostGIS extension

The last step in setting up our spatial databse is to enable the PostGIS extension.

Run the create extension statement into the query window.

-- enable the postgis extension
create extension postgis;

Check to make sure PostGIS is enabled by checking the PostGIS verion in the query window.

-- check PostGIS version
select postgis_full_version();

Create a connection to the spatial database

It’s best to use the postgres superuser only when necessary. With your spatial database created, you’ll load and mange your database with the gis_layers_admin role.

Let’s create a connection to the PostGIS database in pgAdmin.

Create a new connection with:

You can save your password for convenience since this on your local machine for development purposes.

Next steps

That’s it! Your spatial database is read to go!

You can connect to the gis database as the gis_layers_admin login role and load your data into the gis_layers schema.