Key Postgres and PostGIS steps from Weeks 2 and 3

Summary of some key steps from Weeks 1-4

We assume you have installed QGIS, pgAdmin, Postgres and PostGIS. Before proceeding make sure that the Postgres server is running.

1. Create a user account for your Postgres database

• In pgAdmin, under your postgres local server, right click and choose "New Object" and then "New Login Role"
• Under the "Properties" tab, Role Name: "user" (or whatever you want)
• Under the "Definition" tab, password "user" (or whatever you want as long as you will remember it!)
• Type the password in again

2. To create a new database in Postgres using pgAdmin

• In pgAdmin, right click on Databases
• Choose New Database
• Properties: name your database, and assign it an owner

3. To create a PostGIS database in Postgres using pgAdmin

• Right click on Databases under Postgresql, New Database, give a name to your database with owner postgres.
• Find your database under Postgres, Databases
• Under your new database, choose the “public” schema
• Use the SQL editor and run the command “CREATE EXTENSION postgis;” (use the play button to run that query)
• In the SQL Editor, run “SELECT postgis_full_version();” to verify that your postgis database is operational.
• In the Object Browser of pgAdmin, right click and refresh. You should see a new table (spatial-ref-sys) appear and also several new Views that are related to the PostGIS database structure.

4. To connect to the PostGIS/Postgres database in QGIS

• Open up the Browser in QGIS

• Choose “PostGIS”

• Right click and “create new connection”

  —— Name: Give it a name

  —— Host: localhost

  —— Port: 5432

  —— Database: Whatever you named it in Step 1 above

  —— Username: use a user login that you created following the directions under #1 above. Don't user Postgres since that is a superuser account.

  —— Password: use the password for that user login above

• Test the connection. Hopefully it works!

• Press OK. You should see the new PostGIS entry in your browser

5. Import shapefiles (or other vector formats) into your PostGIS database using DB Manager

In past material, we suggested using either pgShapeloader or SPIT. But we’ve found since then that SPIT is buggy. So below we are suggesting a different way, that seems more robust, using the “DB Manager” in the QGIS icon menu. Try these steps next time you want to import a shapefile into your PostGIS database:

• In the Browser Panel, right-click on PostGIS, and select New Connection….

• Fill in the parameters of the connection and test it.

• Menu Database > DB Manager > DB Manager.

• Open the list item PostGIS

• Click on the connection you created in step 2.

• Click on the button Import layer/file.

• In the dialog Import Vector Layer, choose the layer to import;

• Click the button Update options to copy the input name to the Table name.

• Click the button OK.

• Your imported shapefile should appear if you refresh in the QGIS browser.