Getting Started

Before you can work with SQL, you will need access to a Database Management System (DBMS). While SQL is a standard across many different DBMSs, we will focus on PostgreSQL, an open source, enterprise class DBMS with strong support for geospatial data.

Installing PostgreSQL

If you are installing the database on a different computer from the one you will be using as a client, you will need to modify some settings in pg_hba.conf before your can use your database. The configuration of pg_hba.conf is entirely dependent on your network and needs. If you are using PostgreSQL server and a client (pgAdmin, QGIS, ArcGIS) on the same computer, you should not need to change the default settings in PostgreSQL’s pg_hba.conf file.

Installing pgAdmin

pgAdmin installation is straightforward under both Windows and macOS. Download pgAdmin 3 for your operating system. If prompted, accept the defaults.

Connecting to a PostgreSQL Server

If a connection was not made to your local PostgreSQL database upon creation of pgAdmin, you will need to create the connection. Click on the “Add a Connection” button pgadmin-add_connection and then provide the following details.

pgadmin-new_server_registration

If you are going to connect to a remote database, you will need to provide that information through this same operation. pgAdmin will allow you connect to multiple servers and also supports SSH tunneling, if your server is behind a firewall or otherwise inaccessible.

Loading the Workshop Data

Download the Workshop Data File to your workstation. (If your database client and server are on separate computers, download to your client.)

Using pgAdmin to load data

Before you can load your data, you will need to create a new database for the workshop data to reside. To issue commands to a PostgreSQL database server, you will need to connect to a database hosted within. The postgres database serves as this special-purpose or maintenance database. It is good practice to leave this database empty. We will create a new database on the server to store the workshop data.

Right click on “Databases” under your database server and select New Database.

pgadmin-new_db_menu

While the databases can be customized, we will accept the defaults. Simply name this new database “workshop” and click OK. You can provide a comment, if desired. As your databases grow, comments will become helpful. You can comment on databases, schemas, tables, columns, and many more objects.

pgadmin-new_database_window

Next, click on your new workshop database to connect to it. Then, right click on the workshop database to create a new extension. PostGIS is included as an extension to the PostgreSQL database.

pgadmin-new_extension_menu

As the PostGIS extension is already installed in the database server, adding it to your specific “workshop” database is as simple as creating a new extension called “postgis”.

pgadmin-new_extension_window

Once the PostGIS extension is created, we can then load the Workshop Data file. Right-click on your workshop database and select Restore.

pgadmin-restore_menu

The Restore interface has several sections with multiple options. Under the first tab, specify the location of the Workshop Data file. The data file is in the custom data format.

pgadmin-restore_fileopts

On the “Restore Options #1” tab, make sure all the check boxes are selected. This will import all the pre-data (schema and table creation), the data itself, and the post-data (indexes, permissions, etc).

We also want to ignore the owner, privilege, and tablespace settings in the file. While database exports do not include the user account data, the exported objects may retain information about the account that created them. This information can be safely discarded – if ownership or permissions are set for a user that does not exist in this database system, the import will fail.

pgadmin-restore_opts1

On “Restore Options #2” make sure that “Verbose messages” is checked. In case of an error, this will help you identify issues with the import.

pgadmin-restore_opts2

Once those settings are defined, click the “Restore” button. You should then see new tables in your Public schema.

pgadmin-table_selected

You are now ready to work with these spatial tables.

Using the command line to load data

Navigate to the location of the downloaded data file. Provided pg_restore is in your path, you should be able to load the file like so:

$ psql
# create database workshop;
# \c workshop
# create extension postgis;
# \q
$ pg_restore -Fc -d workshop workshop.backup

If you are more familiar with shell, you might want to look into how the psql console works and how it can integrate into your shell environment.