Setting up a database

Now that we have installed the necessary software, let's see how we can use PostGIS to create and set up a spatial database. We will start by creating a Postgres user account, creating a database, and setting up the user to access that database, and then we will enable the PostGIS spatial extension for our database.

Creating a Postgres user account

Our first task is to set up a Postgres user, who will own the database we create. While you might have a user account on your computer that you use for logging in and out, the PostgreSQL user is completely separate from this account and is used only within Postgres. You can set up a PostgreSQL user with the same name as your computer username, or you can give it a different name if you prefer.

Note

Note that a user is sometimes referred to as a "role" in the Postgres manual.

To create a new PostgreSQL user, type the following command:

% createuser -P <username>

Tip

Obviously, replace <username> with whatever name you want to use for your new user. You may also need to add the -U postgres option or use sudo for these commands if you need to run Postgres under a different user account.

The -P command-line option tells Postgres that you want to enter a password for this new user. Don't forget the password that you enter, as you will need it when you try to access your database.

Creating a database

You next need to create the database you want to use for storing your spatial data. Do this using the createdb command:

% createdb <dbname>

Tip

Make sure you replace <dbname> with the name of the database you wish to create. Once again, add -U postgres or use sudo if required.

Allowing the user to access the database

To let the user access this new database, type the following command into a terminal window, adding -U postgres if necessary:

% psql -c "GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <user>;"

Tip

Obviously, replace <dbname> with the name of your database and <user> with the name of your new user. Also, remember to add -U postgres or use sudo if you need to.

Spatially enable the database

So far, we have created a Postgres database and an associated user. Our database is just a regular database; to turn it into a spatial database, we have to enable the PostGIS extension for it. To do this, type the following into a terminal window, replacing <dbname> with the name of your new database:

% psql -d <dbname> -c "CREATE EXTENSION postgis;"

Table of Contents for Python Geospatial Development - Third Edition