3: Spatially Enabled Relational Databases

3.1 Pre-Class Readings and Exercise: The Idea of Spatial Databases

Introduction:

Up until now we have introduced you to the QGIS desktop [http://qgis.osgeo.org], one of the leading open source desktop or “client” Geographic Information Systems software packages, and relational database theory and standard components such as the Structured Query Language (SQL). You have also become familiar with the use of open source relational database technologies: the relational database management system (RDBMS) PostgreSQL and the user interface to it, pgAdmin. What you may not fully appreciate is that PostgreSQL is a fully functional enterprise level relational database. That is, it can be used to process large amounts of data and can handle multiple users accessing the database at the same time.

The material we are covering in this set of readings and exercises focus on a special type of relational databases: spatial databases. If you are a reader who has experience with the proprietary GIS package ArcGIS, you may know — we actually mentioned this in last week’s material — that the Geodatabase is one example of a relational, spatially explicit database.

In this set of pre-class and in-class readings and exercises, you will get the opportunity to work with PostGIS — a special kind of Postgres relational database.

To begin we are again assigning a set of open access readings to do to help you understand PostGIS and how it works with Postgres. Like Week 2, there will be a 10-question Moodle quiz that will follow this material. We encourage you to take some notes on the ‘big ideas’ in these readings.

Acknowledgements:

We are grateful to the company Boundless (http://boundlessgeo.com) for providing sets of reading material on PostGIS under a Creative Commons license. The entire set of materials can be found at http://workshops.boundlessgeo.com/postgis-intro/. We are providing links to this material to respect their request that we retain Boundless branding, logos and style.

Assignment:

You should have already installed Postgresql, PostGIS, pgAdmin and the pgShapeloader utility in the first pre-class assignment. With that installed, go through the following Boundless Workshop Modules.

Before starting, download the Boundless data bundle available with their NYCity data from here: http://files.boundlessgeo.com/workshopmaterials/postgis-workshop-201401.zip

Now start going through these Boundless tutorials:

Introduction. http://workshops.boundlessgeo.com/postgis-intro/introduction.html

Chapter 4: Creating a Spatial Database. http://workshops.boundlessgeo.com/postgis-intro/creating_db.html

Chapter 5: Loading spatial data. http://workshops.boundlessgeo.com/postgis-intro/loading_data.html

IMPORTANT! Section 5.3. encourages the reader to try and view their data in QGIS, but doesn’t explain how to do this. To do this:

  1. Start up QGIS Desktop.
  2. Locate the Browser Panel; if it’s not open, menu View > Panels > Browser Panel.
  3. Right-click the item PostGIS, and in its contextual menu select New Connection….

  4. In the window Create a New PostGIS connection:

    1. In the field Name, enter a name that identifies the server, username, and database you want to connect to, e.g. localhost your_username nyc.
    2. In the field Host, enter the hostname, e.g. localhost.
    3. In the field Database, enter the name of the database, e.g. nyc.
    4. In the field Username, enter the username you are using to connect, e.g. your_username.
    5. In the field Password, type in the password you set for this account.
    6. Click the button Test Connection and you should see a dialog indicating that The connection to your database was successful. If you don’t, double-check your parameters.
    7. Click the button OK.

  5. Click the button / next to PostGIS in QGIS, and you should see your connection listed, e.g. localhost your_username nyc. Continue expanding. You should see a screen like below.

  6. If you double-click on the nyc data listed, you should see these layers appear in the map side of QGIS! See below.

Success! You’ve got a Postgres+PostGIS spatial database with four layers displayed in QGIS! For many of you — this will be the first time displaying data using an open source enterprise spatially enabled RDBMS!

If you are a bit of a geek, this should be exciting! (and even if you are not, but are interested in GIS…!)

OK, now continue doing the Boundless workshop modules below.

Chapter 6: About our data. http://workshops.boundlessgeo.com/postgis-intro/about_data.html

So before you begin reading the Boundless Chapter 7 on simple SQL, here is something to note on and reflect on. With the successful connection in QGIS, you now have two mechanisms for working with the data (attribute tables) associated with the PostGIS data layers.

Probably what you’d most likely want to do is do queries using QGIS or open up the attribute table and check out the data there.

Try that!

We’re assuming you have used GIS before, so try and figure out in QGIS how to open the attribute table for, say, the nyc_neighborhoods data layer to see the different neighborhood names.

Or how might you query the nye-neighborhood layer to select only the neighborhoods in Manhattan? If you don’t know, look to the bottom of this exercise for one way under “Answer Question 1”.

But the other way you could query this data is through the use of pgAdmin and the query tool there. pgAdmin is another interface to these postgres/postgis tables. In the exercises that follow, Boundless is providing you a little more information and experience using the relational database language SQL and the interaction with the Postgresql/PostGIS tables that make up these spatial data layers in their New York City dataset.

To learn more about SQL and the geometries of PostGIS GIS data layers, go though:

Chapter 7: Simple SQL. http://workshops.boundlessgeo.com/postgis-intro/simple_sql.html

Chapter 8: Simple SQL exercises. http://workshops.boundlessgeo.com/postgis-intro/simple_sql_exercises.html

Chapter 9: Geometries. http://workshops.boundlessgeo.com/postgis-intro/geometries.html

Chapter 10: Geometries Exercises. http://workshops.boundlessgeo.com/postgis-intro/geometries_exercises.html

What the exercises in Chapter 10 reveal is that through the use of the geometry in PostGIS coupled with functions and the table data, Postgres/PostGIS and SQL become powerful tools to answer complicated spatial analysis-related questions. This is one of the reasons the use of a powerful RDBMS is useful for storing geographic data.

You are now done with the Week 3, pre-class assignment!

Answer 1: One way to query only the neighborhoods in Manhattan using QGIS is to right click on the nyc_neighborhoods under the Layers pane, and choose ‘Properties.’ Under the General option, scroll down to the bottom and you will see the “Query Builder” button. Press that. This will open up that option, and you can choose “boroname” (double click on it to bring that down to the expression box), type “=“, and the under values select all, and then double-click on Manhattan. You will see at the bottom:

‘’boroname’ = ‘Manhattan’

Behind the scenes, QGIS is running a select query to select the polygons from nyc_neighborhoods where ‘boroname’ = ‘Manhattan’

Press OK and you should see only those neighborhoods of Manhattan on the QGIS map window.

results matching ""

    No results matching ""