Familiarity with QGIS DB Manager to Write and Run SQL Queries

GGRA30: Geographic Information Systems (GIS) and Empirical Reasoning Assignment #3 Intro to PostgreSQL Queries and Joining Data in QGIS Instructor: Glenn Brauen Department of Human Geography, University of Toronto Scarborough Winter 2020 Due Date: Feb. 26, at start of your tutorial section. Total marks possible: 15 All answers must be typed/printed. 1 Learning Outcomes When this assignment is completed you will have achieved the following learning outcomes: • Demonstrated familiarity with using the QGIS DB Manager to write and run SQL Queries on a PostgreSQL/PostGIS database. • Demonstrated the joining of summary tabular data from PostgreSQL to a QGIS data layer to produce a simple map of the combined data layer. 2 Assignment Submission Submit a paper copy of your assignment (neatly typed) to your Teaching Assistant at the beginning of your tutorial section. There is no digital submission for this assignment. Late assignments should be placed in the instructor’s Essay Drop Box (see Syllabus for location). Answer all questions in Section 5 and complete the map described in Section 6. Question values are given in those sections. 1 Assignment #3 GGRA30 Winter 2020 3 Data Provided The data you are to use for this assignment has been provided in the form of an online PostgreSQL/PostGIS database: • server: ggrgis.utsc.utoronto.ca • database: ggra30_a3 • login: ggra30 • password: ggra30 Tutorial #4 includes instructions for how to connect to the database and load vector layers. Tutorial #5 contains instructions for using the QGIS DB Manager to run SQL select queries on the tables in the database and for creating a QGIS query layer to join a database query result to a previously loaded vector layer for mapping. You need to understand those procedures to complete this assignment. The database includes the following tables, all of which are derived from the Transportation Tomorrow Surveys (TTS) provided by the Data Management Group (DMG), Department of Civil Engineering, University of Toronto. • tts_trip_counts: estimates of daily commute trips by full- and part-time students. This table is more fully explained below. • tts_planning_districts: geographic data (vector) layer for the TTS planning districts (PD) within the City of Toronto. This table contains the PD boundaries and the following attributes for each PD: – name: a text string describing the PD. – pid: a numeric PD identifier. – pop_unknown: the planning district’s population of persons whose status — student or non-student — is unknown, estimated during the 2016 survey. – pop_nonstudent: the planning district’s population of non-students, estimated during the 2016 survey. – pop_pt_student: the planning district’s population of part-time students, estimated during the 2016 survey. – pop_ft_student: the planning district’s population of full-time students, estimated during the 2016 survey. The tts_trip_counts table is designed to contain daily commute information for full- and part-time students, based on surveys and aggregated to show trip counts in which commuters travelled between identified Toronto planning districts (PD) using the same travel mode and travelled the same straight line distance (measured to the nearest kilometre). The actual start and end of the journey was not recorded for each trip but trips have been categorized according to which PDs the trip started and ended in, the distance travelled and the travel mode used. The table contains the following fields: 2 of 6 Assignment #3 GGRA30 Winter 2020 • cid: unique record identifier. Primary key. • sur_year: each record is tagged with the survey year in which the data was collected. Surveys have been completed for 1986, 1991, 1996, 2001, 2006, 2011 and 2016. Summary records from all of those surveys is included in the table. • from_pid: Numeric identifier of the PD from which the trip began. These values match the values in the pid field of the tts_planning_districts table. • to_pid: Numeric identifier of the PD where the trip ended. • mode: the travel mode used by the commuters represented by the trip count in this record. See the list of mode codes (below). • ntrips: estimate of the number of daily trips meeting the criteria of the record (survey_year, from_pid, to_pid, mode). For example, the record shown in Table 1 estimates that in 1986 there were 1537 daily commutes by full- or part-time students from PD 1 to PD 6 completed as automobile passengers (see the list of mode categories below) in which the student travelled a distance of 8 kilometres (straight line distance, start to finish). The cid value shown is generated by the database to be an unique identifier within the table and has no meaning for the commuting trips summarized by the record. cid sur_year from_pid to_pid mode dist_km ntrips 1213 1986 1 6 P 8 1537 Table 1: Example record from the commute data. The table could also contain other records showing the number of trips between those same zones in 1986 that used different modes of travel or covered longer or shorter straight line distances. You should not interpret this record as showing the total number of daily trips from PD 1 to PD 6. Similarly, this only shows the number of trips in one direction. There may be another record showing the number of trips in the opposite direction (from_pid=6, to_pid=1). If a record for a specific set of criteria (sur_- year, from_pid, to_pid, mode, dist_km) does not exist in the table, the number of commutes fitting that criteria set was zero. The following travel mode codes are used in the tts_trip_counts table. Note that if any part of the trip uses public transit, the primary mode will be designated as B, G, or J. B Transit excluding GO rail C Cycle D Auto driver G GO rail only J Joint GO rail and local transit M Motorcycle O Other P Auto passenger S School bus T Taxi passenger U Paid rideshare (2016) W Walk 9 Unknown 4 Procedures All of the procedures required to answer the questions in this assignment are described in tutorial instructions #4 and #5. They are not repeated here. 3 of 6 Assignment #3 GGRA30 Winter 2020 5 Questions When answering the questions and creating your map for this assignment, you are to focus on the commuting trips that originate or end in a single TTS Planning District (PD). This will be your assigned PD. Table 2 lists the assigned PDs based on the last digit of your student number. Last Digit (Student ID) Assigned PD 0, 1 3 2, 3 2 4, 5 1 6, 7 6 8, 9 13 Table 2: Planning District assignments for your work on this assignment. For each question in this section you are to create a Structured Query Language (SQL) SELECT statement that will produce the required result. Your answer must include both the SQL statement for the query that produced the result and an image of the DB Manager result table on your computer screen. • When you have a query working properly in the QGIS DB Manager SQL panel, you can copy and paste that query text back into your write-up. Ensure that the query text is correct and fully copied. If the query string does not make sense with the query result you show, you will lose some or all marks for that question. Do not rewrite the query in your write-up — it is much too easy to make a mistake in transcribing it from your work in the DB Manager. • Your query result should be shown in your write-up as a screen shot of the DB Manager SQL result panel showing the table returned by your query. Please include only the portion of the screen that contains the query result table and ensure that you include that legibly in your write-up. If the result table can not be read in your printed document, we will not be able to mark it.1 • For each screen shot, ensure that the DB Manager result window is arranged so that the information can all be read. Widen columns so that the column name and all values in the result are visible. Your TAs and I will deduct marks if you submit a screenshot that does not show the requested information clearly. Note that you can use the DB Manager table preview for each table to examine the columns included in the table and the information each field contains. This will help you to understand how to create the SQL query to fetch the information needed to answer the questions below. But your final answer must be both the query (SQL statement) and the result screen captured as described above. Each question below is to be answered by a single SQL query. 1 Instructions for capturing images of portions of your screen can be found online: For Windows, see https://www. wikihow.com/Take-a-Screenshot-in-Microsoft-Windows, especially the instructions for using the Snipping Tool. For Mac, see https://www.wikihow.com/Take-a-Screenshot-on-a-Mac, especially the section on capturing only a portion of your screen. 4 of 6 Assignment #3 GGRA30 Winter 2020 1. (1 mark) List the pid, name, part-time student population and full-time student population for all PD with identifiers less than or equal to 10. 2. (1 mark) The tts_trip_counts record shown in Table 1 is incorrect — this does not show real data from the surveys. Write a query to display only the real record matching the criteria shown in the Table and showing the real number of commutes that meet those criteria. 3. (4 marks) How many trip count records (tts_trip_counts table) are there from the 2016 survey, for each travel mode, for trips that both originated from (from_pid) and ended in (to_pid) your assigned planning district? Your query must group the record counts according to travel mode. You do not need to provide the grand total of records for all travel modes. Answer the following questions — these do not require another query and instead just require you to examine the query result produced. (a) For which travel mode(s) are there the most records for trips from your assigned planning district (give the travel mode meaning not just the letter code)? (b) Why are there multiple trip records for at least some travel modes that begin and end in the same planning district? 4. (2 marks) How many trips (tts_trip_counts table) are there from the 2016 survey, for each travel mode, for trips originating from (from_pid) your assigned planning district? Your query must group the trip totals according to travel mode. You do not need to provide the grand total of trips for all travel modes. 5. (1 mark) How many trips (tts_trip_counts table) are there from the 2016 survey, for each recorded straight line distance (dist_km), for trips originating from (from_pid) your assigned planning district? Your query must group the trip totals according to the distance travelled and list them in order by descending trip totals. • This table may be slightly longer than will fit on one screen. You do not need to show all of the result table as a screenshot in your write-up. Show at least the top 10 lines. • You do not need to provide the grand total of trips for all distances (here). 6. (1 mark) Your answers to Questions 4 and 5 (above) show subtotals for the number of trips originating in your assigned PD from the 2016 survey year with the subtotal groupings being based on different categories for the two questions. Copy result and check that grand totals are equal. When you have a result table in the QGIS DB Manager window, you can left-click and drag the cursor across the contents of the result table to select all cells in the result. Once selected, you can copy the table structure and contents (controlC on Windows). Copy the contents of the result table for these two questions, paste them into an Excel sheet, calculate a grand total for the number of trips originating from your assigned PD. • What is the grand total? • Are the grand totals for Questions 4 and 5 equal? (Hint: the only correct answer to this question is “yes.” If you can’t give that answer figure out where you have gone wrong). 7. (2 marks) Create a query that shows the total number of trips that ended in your assigned planning district from each planning district (tts_trip_counts, sums grouped by originating planning district) according to the 2016 survey data. Include all trip counts in your totals (i.e., your query 5 of 6 Assignment #3 GGRA30 Winter 2020 totals should include all modes and distances). You will need the originating planning district (from_pid) value for each record along with the total number of trips for that planning district. It is probably a good idea for you to keep the to_pid and sur_year values as well so you can check your result table to make sure that it only contains records appropriate to this question. 6 Create and Join a QueryLayer to Create a Simple Map (3 marks) Create a basic map showing a layer combining the tts_planning_districts combined with the query result from Question 7 in Section 5. 1. Create a QGIS QueryLayer from the query by loading the query as a new layer (see Tutorial #5 instructions). 2. If you have not yet added the tts_planning_districts layer to your QGIS project, add it now. 3. Join the QueryLayer just created to the tts_planning_districts layer. 4. Create a very simple map, as described below: (a) The only layer you need to show in your map is the tts_planning_districts. (b) Label each district using an expression that shows the name of the planning district and the total number of commuting trips originating in that planning district and ending in your assigned planning district. Note that even your assigned planning district will show only one count of the number of trips that started and ended in your assigned planning district. You will need to have joined the QueryLayer described above for this to work. As an example, the label for PD 1 might look like: PD 1 of Toronto: 68767 (this total is wrong – I made the number up!) (c) Add a title to the map. (d) Add a text box with your name and student number. 6 of 6