GeoHealth Mapping GIS Training

In some instances, data are already available, organized, and stored in a database. In order to understand what is contained in your database, a procedure known as querying is performed. A query is an inquiry into the database about any kind of information contained within that particular database, allowing you to extract variables of interest. For example, you may only be interested in seeing available data for females ages 15–49. Querying also allows you to

• Combine datasets into a bigger dataset
• Modify data—e.g., update or delete
• Aggregate data using some inbuilt functions—e.g., count
• Rearrange data for better viewing

Before you begin querying your data, you should

• Know your data well (what variables are collected, how many records exist, what are the allowed values for each variable, etc.)
• Understand what the coded values mean and how can they be decoded—e.g., is 1 equal to 'yes?'
• Understand the relationship between variables—e.g., last menstrual period (LMP) and gender
• Understand your database structure and what tables relate to one another
• Understand the database security options (i.e., What information is available to the public and what is only available to an administrator?)

2.4.1 Objectives

• Provide an overview of how to query in QGIS
• Provide some resources on more advanced querying
• Work with operators
• Understand the use of pivot tables

You will need the files for Section_2_4 to complete this module.

2.4.2 Practice simple querying

1. Data layers can be queried in a number of ways. The simple search allows you to search for individual text or numbers that appear in the attribute table of a layer.
2. Excel files have to be converted to .csv (comma separated values) format in order to be used in QGIS.
3. In Windows Explorer, browse your exercise data folder.
4. Open HIV National MIS Data Reporting Period July-December 2013.xls.
5. Click on File > Save As.
6. Change the Save As type from Excel Workbook to CSV by clicking on the down arrow and selecting CSV.
   
7. Navigate to your exercise data folder and save the file in the folder.
8. Click Yes on the next dialog box, which confirms you want to use the CSV format.
   
9. Close the file and exit Excel.
10. Open QGIS.
11. Click on Layer on the main menu.
12. Click on Add Layer > Add Delimited Text Layer.
    
13. In the dialog box that opens, browse to MyExercises and select the CSV file you just saved: HIV National MIS Data Reproting Period July-December 2013.csv.
14. For File format select CSV and for Geometry definition select No geometry (attribute only table).
    
15. Click Ok.
16. Your file will appear on the left panel in QGIS under layers. Right-click on the file and select open attribute table to view the contents of the file.
    
17. For a simple search by attributes on only one column, choose the Column filter > Implementing Organization from the menu. Only features matching your query are shown in the attribute table.
18. Look for an HIV implementing organization called "CARE."
    
19. Type "CARE" in the text box and click Apply.
    
20. The matching rows will be selected, and the total number of matching rows will appear in the title bar.
    

2.4.3 Advanced search

21. The Search Query Builder can conduct complex searches.
22. Results are displayed in the attribute table and show up as selected features on the map.
23. Select the layer to be queried and open the attribute table, then navigate to Show All Features before selecting Advanced Filter (Expression).
    
24. From the left window, Function list, open the tree menu fields and values and select all variables needed in the query.
25. To show all districts that received more than 20,000 condoms and didn't receive either a syringe or a needle type in > 20000 after "Condoms" and is null after "Need_Syrin".
26. Your query will now be in the following format:
    
27. Click Select, then click Close.

2.4.4 Single quote (') and double quote (")

28. Single quotes are used to enclose string literals. They are used to enclose information of Text Data Type (i.e., String, Varchar(), Text).
29. Double quotes are used to enclose identifiers (like table and column names).
30. Example: Country is a column name and Bangladesh is a data value found in the database under the column name Country
    .
31. You can directly access the query builder under Layer > Query.
    
32. The query builder is different from the query we used in Step 5 above. We used the column filter to query the data, but the query builder gives you advanced functionality.

2.4.5 Operators

33. Operators are the mathematical and equality symbols used to compare, evaluate, or calculate values.
34. There are three types of operators: mathematical, logical, and equality. Mathematical operators add (+), subtract (-), multiply (*), and divide (/) numbers. Logical operators include AND and OR.
35. Equality operators include the (<), (>), and (=) symbols, which are used to compare one value against another.

2.4.6 Logical AND, OR, and NOT

36. The AND operator displays a record if both the first condition AND the second condition are true.
37. The OR operator displays a record if either the first condition OR the second condition are true.
38. The OR operator should be used to search for multiple existences of records in one column.
39. The AND operator will only evaluate true when conditions are true in different columns.
    • Example: look for only FHI 360 and NASP implementing partners.
      
40. If you want to find rows that do not satisfy a condition, you can use the logical operator NOT. Use of this operator results in the reverse of a condition: that is, if a condition is satisfied, then the row is not returned.
    • Example: query for all information excluding Bogra Division.
      

2.4.7 Equality operators

41. Equality operators table
    
42. Using the Excel file GIS Requirement_Save the Children_Bangladesh.xls, practice the following queries:
    • How many districts received more than 50,000 free condoms for female sex workers (Condom (Free) > 50,000)?
    • In which districts were diagnosed STI cases less than or equal to 150 (STI <=150)?

2.4.8 Comparisons

43. Comparisons 'Key Words'
    
44. The LIKE operator is used to list all rows in a table whose column values match a specific pattern. It is used with a wildcard character: %.
    • For example, list all districts starting with "BA"; the expression will be written as "District" Like 'Ba%'.
    • This action searches for all districts where the first two letters are Ba and the rest of the letters can be any character.
45. The operators BETWEEN and AND are used to compare data for a range of values.
    • For example, how many people received condoms in a range of 10 to 50,000? The expression will be written as "Condoms" between 10 AND 50000.
46. The IN operator is used to compare a column with more than one value. It is similar to an OR condition.
    • For example, list all information in Barisal and Sylhet division. This will be written as "Division" IN ('Barisal','Sylhet').
47. A column value is NULL if it does not exist. The IS NULL operator is used to display all rows for columns that do not have a value.
    • For example, show all districts that had no STI cases reported. The expression on the query builder will be "Sti" is NULL.

2.4.9 Practice exercises

1. Query all districts in the Chittagong division supported by FHI 360 and Save the Children.
2. How many implementing organizations distributed between 200,000 and 280,100 condoms?
3. List all districts ending with the letter A.
4. List all districts that did not receive free condoms, but where more than 40,000 condoms were sold and where STI cases were between 50 and 200.
5. Show those districts beginning with the letter C where less than 35 STI cases were reported for injecting drug users, and where more than 3,000 needle syringes or 2,000 condoms were distributed.
6. Excluding Dhaka division, which other division had more than 500 reported STI cases for female sex workers (FSW)?

Top