Introduction to PostgreSQL With Spatial Data

Accessing Databases from QGIS

For me, a database is a place to store and filter large vector data sets. QGIS makes it very easy to access PostGIS database tables.

Adding Layers to QGIS from Existing Connections

The layers that are provided with OSGeo can be loaded into QGIS with preexisting connections.

1. Launch QGIS
2. On the left side you'll see a icon with an elephant on it. Click it.
3. From "Connections", select the "Natural Earth" database and click "Connect". This will load the tables from the database that are spatially enabled.
4. Click the arrow next to "public" below "Schema" in the table and you should see the table that contain spatial data.
5. Click on the "ne_10m_admin_0_countries" and click "Add"
6. You should see the entire table added to QGIS!
7. If you right click on the layer and select "Open Attribute Table", you'll see the entire contents of the table has been loaded. This is fine for small tables but will be a problem for larger tables.

Adding Layers for new tables

To load a layer from a table that you have added to PostgreSQL, you'll need to create a "New" connection.

1. In QGIS, click on the elephant icon.
2. Click on the "New" button.
3. You'll need to setup the connection information similar to one of the existing connections like the one below. If you use "user" as the "Username", you should not have to specify a password.

Filtering Table Data

Now we'll learn to filter data from PostgreSQL.

1. Click on the elephant icon again and click "Connect"
2. Select the "ne_10m_populated_places" table.
3. Click on "Filter".
4. On the left you'll see the fields in the table and on the right the available values.
5. Click on some tables and then click on "All" below "Values" to see the distinct values in each table.
6. Select the "pop_max" field and you'll see that the maximum population varies widely.
7. Double click on "pop_max" and you'll see it appear in the "Provider specific filter expression". Then click on the ">" button and add the value "50000" to select cities greater than 50,000 people.
8. Click "OK" and you should see cities appear over the countries but only those larger than 50,000 in population.

Note: I have had varying success using the Filters (i.e. sometimes the filter does not appear to do anything).

Importing Vector Data Into the Database

The easiest way to bring vector data into the database is to use the "Shape to PostgreSQL" (shp2pgsql) program to import a shapefile. This will create a new table with all the data from the shapefile.

Some versions of PostgreSQL with PostGIS installed, have sph2pgsql installed as a "plug-in". To try this:

1. Select the database you want to bring the shapefile into.
2. From the "Plugins" menu, select "PostGIS Shapefile and DBF loader...".

If this is not available, you'll need to launch the plugiin from the command line. Try one of the following:

1. First, you need to find the "shp2pgsql" program. There are two versions of this file, a command line and a GUI version. You can use either but I prefer GUIs.
2. On Linux, the file is actually called "shp2pgsql_gui" and is stored in the "File System/usr/bin" folder. Double-click on this file.
3. On Windows, the file is tyipcally stored in: "C:/Program Files/PostgreSQL/<version>/bin. You'll typically need to "cd" into this folder from the command line to run the program.

Once you see the GUI appear:

1. Click on "View connection details...". You'll need to enter the Username, Password, and Database. The default user name is "user" without a password.
2. When you click "OK" you will see an error if the program cannot log into the database.
3. Click on "Add File" to specify a file to load.
4. Click on "Options..." and set the "DBF file character encoding to "LATIN1". This may not be needed with all shapefiles.
5. Click on "Mode" and change it to "Delete" to have the existing data deleted and replaced by the new file. You can also select "Append" to have the data appended to the current table contents.
6. When you click "Import" the file will be inserted into a table that has the same name as the file.

Importing CSV files

Editing Vector Data

You can use QGIS to directly edit data in a PostgreSQL database and it is very easy (maybe too easy!).

First, load a layer into QGIS from the database as we did above. Then, edit the vector data as you normally would (click on the pencil to start, use the editing tools, then click on the pencil to stop). When you click on the pencil to stop editing, you'll be asked if you want to save the changes. This will save the changes directly to the database!