Basically, you will need to perform two steps. First, you need to add the landuse layer and the towns layer to the project. In the second step, you need to specify the subset(s) of data for the project.
To add PostGIS layers to the project, please, select "Layers > Add PostGIS Layer" menu. The dialog window will appear (Fig.2).
Figure 2. Ref.
The drop down menu consists of a list of PostGIS servers you can use data from. This list may be empty if you are using PostGIS servers for the first time. Lets add FossGIS PostGIS server to the list by clicking on the "New" button. The connection settings dialog window appears (Fig. 3).
Figure 3. Ref.
Use the following settings to configure the connection (the settings for the students, which take class online, are the same:
Name: FossGIS Class 2007 or UMASS (?r any another name you like)
Host: lambda.sbs.umass.edu
Database: testgis
Port: 5432 (use default)
Username: umstudent
Password: f0ss2007 (please note the zero f-zero-ss2007)
Click "OK" button to confirm settings. The new server will be added to the dropdown menu (Fig 4). Now select the added PostGIS server and click the "Connect" button.
Figure 4. Ref.
A list of spatial layers provided by the FossGIS server will appear in the field "Tables " (Fig. 5).
Figure 5. Ref.
To add the MA towns layer (public.matowns) select the layer from the list and click on the "Add" button (Fig. 6).
Figure 6. Ref.
You should now have the "matowns" layer in the QGIS content menu (left side bar).
Figure 7. Ref.
Add the Amherst landuse layer ("public.lu_amhest ") to the project from the FossGIS server.
Figure 8. Ref.
You have now added two layers from the FossGIS PostGIS server. You can add additional layers locally or from other SDE's. The next step is to specify subsets of data.
There are situations when you would like to show only a subset of data, separating data on some criteria. For example, your SDE server provides data for the whole state, but you would like to show only several counties. The other example is a situation when the data set is very detailed, and you need to show only a small sub category of data. In any case, it is more effective to specify a subset of data than to w?rk with the whole dataset. (Just as you order a subset of books from the library to solve problems, not ALL of the books from the library.)
For this example we will show ONLY landuse parcels where some residential development occured. (An alternative approach would be to make non-residential parcels transparent in your map. We will learn the more effective and elegant approach). Select the "amherst landuse" layer and open the "properties" window for the layer. Click on the "General" tab (Figure 9).
Figure 9. Ref.
As you can see, the option "Query Builder" is available now (by default this option is not available for shape files). We will select only parcels, which are zoned for residential development. Please remember that the land use layer has 4 types of residential developement, which are coded with values 10,11,12 and 13 in the field "lu21_yyyy" or "lu37_yyyy" of the attribute table (for details please refer to MassGIS metadata, metadata). To start making a query, press the "Query Builder" button. The new "PostgreSQL Query Builder " window will appear (Figure 10). The "Fields" menu consists of all fields of the attribute table. Select the field "lu21_1999" (landuse codes for the 1999 inventory), then click on the "Sample" button to obtain a list of unique values stored in this field. Finally, create an SQL query that selects only a subset of the data:
"lu21_1999" >= 10 AND "lu21_1999"<=13
you can click on the field names, values and logical operators (<,>,=...) to compose the query with the mouse (Figure 10).
Figure 10. Ref. Click on the "Test" button to ensure that the SQL query is written correctly. The query is correct, and 410 rows satisfy the specified criteria (Figure 11). Click the "OK" button to return to the "PostgreSQL Query Builder" window.
Figure 11. Ref.
Figure 12. Ref.
To perform the query click the "OK" button (Figure 12). You can see that only part of dataset is displayed. Let's use the "identity/info" tool to check the attribute data for the displayed parcels. The values are in the range from 10 to 13 (Figure 13), which means that all parcels are zoned for the residential development. Okay, query builder works!
Figure 13. Ref.
At this step we will display four counties which are part of Western Massachusetts (Right now, layer "ma_towns" displays all towns). Each county in the U.S. has an associated FIPS code. According to the metadata for the 'towns' layer, the field 'FIPS_COUNT' consists of coded values for the counties. For this small project we are interested in the counties with codes 3,11,13 and 15. Please open properties window for the 'ma_towns' layer, then (click on the "general" tab) start "Query Builder". Please form the following query (Figure 14):
Figure 14. Ref.
Why do we use the "OR" statement here? Would it be correct to use "AND" instead of "OR"? Please test the correctness of the statement before returning to the layer properties window. Perform the query. What happend? (Figure 15).
Figure 15. Ref.
As you can see we display only a subset of the 'ma_towns' layer now. Use the "identify" tool to check values of "FIPS_COUNT" field for the towns. The purpose was to show different counties, so let's change the symbology to differentiate counties based on the values of the "FIPS_COUNT" field. You know how to do this from the previous labs. The results should look similar to Figure 16.
Figure 16. Ref.
Ok, we have four counties of Western Massachusetts displayed in the QGIS project.
Figure 17. Ref.
Contratulations. You now know how to use remote data from a PostGIS database, as well as how to use a subset of data. In the next few labs we will explore the SQL world in more detail.