Hudson QGIS

[jimrtex]

Part 26

We are now ready to perform a join.



(1) Right click on the layer you wish to join to, and Select Properties.
(2) Select Join.
(3) Click on Green + sign.
(4) Select the layer to be joined.
(5) Select the field in that layer for the join.
(6) Select the corresponding field in the target layer.

Foreshadow: That the types don't match is not good.

(7) Click OK.



(8) Click Apply.

We can check whether the join worked:



(1) Click on Vermont to Open Attribute Table

We scroll to the right and see that the joined fields have been added to the table, but not for all rows.



If we scroll back to the left, we notice that the towns with a leading zero were not joined. That is, values of '02099' and 2099 do not match.



We go back to our .xlsx file and change the COUSUB column to text, and do the same in our .csv copy. Notice the leading zero for the COUSUB for Ancram (02099)

You must be logged in to read this quote.

We make a corresponding change to the .csvt file.

You must be logged in to read this quote.

We load the .csv file again (we first remove the current layer, which also dissolves the join). And we do the join again, and verify that the join was done correctly.



We can now use the joined fields as part of our display:



Our label expression included the newline character  '
' to place the population on a second line. We also specified that the text be centered.

You must be logged in to read this quote.

Now that we have a numeric value to control the display we can use the Graduated option under style:



Or we can calculate the value to be displayed:



This displays the population density, POP/AREA. The string of constants converts the area in square meters to area in square centimeters, square inch, square feet, and finally square miles.

You must be logged in to read this quote.

[jimrtex]

Part 27

After word of the Toriemander reaches the Columbia County Board of Supervisors, we are tasked with preparing various options for reform of the board of supervisors.

We first decide to consider a 13-member board. We add two columns to our xlsx spreadsheet. RELPOP is the population of each town relative to the quota. To calculate the quota, we sum the town/city populations for the entire county and divide by 13.

E21: =SUM(E2:E20)/13

The RELPOP column is calculated as

I2: =E2/$E$21
...
I20 =E20/$E$21

We also add a DIST column for storing district numbers. We leave those cells empty for now. We also sort the rows by population. We decide this will help our analysis, and since the join is performed based on COUSUB, the two tables don't have to be in any particular order.

We copy the xlsx file to our csv file (paste values only), and save the csv file.

The .csv file looks like this.

You must be logged in to read this quote.

We then decide to strip the file xlsx and csv files to the bare essentials, removing the extraneous columns.

You must be logged in to read this quote.

We also remove the extraneous columns from the .csvt file:

You must be logged in to read this quote.

We are now ready to make changes in our spreadsheet, and then quickly move them into GQIS:

The process is quite straightforward:

(1) Remove the current .csv layer from the Project
     (a) right click on layer,
     (b) select Remove,
     (c) confirm.

(2) Add csv file:
     (a) click on comma icon.
     (b) browse to csv file
     (c) click on radio button indicating no coordinates
     (d) OK

(3) Join the csv file.
     (a) right click on layer to join too
     (b) select properties
     (c) select join
     (d) click on green +
     (e) browse to file to join
     (f) select field to join with
     (g) select field to join to
     (h) OK

We can now create an initial display. We have not assigned any districts yet, this is more of an analytical plan.



We notice that several towns have population enough for a single district, so we will start assigning district numbers to those towns. Kinderhook will be District 1. We also realize that when larger towns are assigned a single district, that the population per district for the remainder of the county declines.

Back to the  xlsx spreadsheet.

We create a minispreadsheet beginning in Column I

Column I is simply a series of district numbers 1...13
Columns J and K are filled with the following equations.

J1:    =SUMIF(E$2:E$20,I1,C$2:C$20)
K1:   =COUNTIF(E$2:E$20,I1)

J1 will contain the sum of the population of the towns assigned the district number in I1
K1 will contain the count of such towns.

J14:  =SUMIF(J1:J13)
K14   =SUMIF(K1:K13)

We can use those cells to keep track whether all towns have been assigned a district.

We assign districts 1 to 6 for the six largest towns.

We then update QGIS:

(1) Copy and paste from xlsx file to csv file
(2) Save csv file.
(3) Remove csv layer
(4) Add csv layer
(5) Join csv layer to shapefile layer

Now that some districts have been assigned, we can categorize our towns based on district number. Towns without a district are not categorized, but there is a color assigned to non-categorized towns. We change that color to a neutral beige.



We notice that if we assign a district to Livingston, then the remaining towns can be paired in an obvious fashion to form the final 6 districts of our total of 13.

We go back to the xlsx file and assign the district numbers, and then bring them back into QGIS. When we first do the join, the new districts are not colored. We need to go back in Style, and classify districts 7 to 13.

[jimrtex]

Part 28

We illustrated how to add district numbers to our town shapefile. While classifying and coloring towns by district is useful, it is not the same as displaying the districts as a whole.

To do that, we need to create a shapefile with districts. We use the dissolve function, to in effect dissolve the boundaries between the towns.



(1,2) A layer that has been joined, can not be directly dissolved. So we right click on the layer, and Save As: columbiatownsdistricts. Since this is just a temporary file, we don't bother with its display.
(3) In the menu select Vector
(4) Select Geoprocessing Tools
(5) Select Dissolve



(6) Select the layer to be dissolved.
(7) Select the field to be dissolved on. Unfortunately, when we saved the joined layer in step 1, the field names were truncated, with only a number. So you will have to figure out which field that you are dissolving on. In this case columbia_3 is the district number.
(8) Browse to enter the saved file.
(9) Click on OK. The progress bar will display. Once it is finished click on close.

With a little bit of cleanup we now have a district map:



The district layer is displayed over the town layer, which gives the background of town boundaries. We also added some attributes to the csv file. These were the (relative) population of each district, and a voting weight (calculated on the basis of 1000 total votes).

You must be logged in to read this quote.

These were stored for each town. When the shapefile was dissolved the attributes from one town were stored as the attributes of the district. By placing the same values for every town in a district, we ensure that the district would get its values from one of the towns. Alternatively, we could have done a join on the districts file.

If we want more detail in the Hudson area, we simply zoom and save an image:



Now imagine someone suggested that Kinderhook should have two legislators. We look at the map and figure out a way to recombine towns, and make the changes in our XLSX file, and update our CSV file.

We remove the three layers that we added and then simply go through the process of:

(1) Load CSV file.
(2) Join to MCD layer.
(3) Save a copy. We can overwrite our previous version.
(4) Perform the dissolve.

And you have a new map.