{"id":2007,"date":"2022-10-10T00:15:52","date_gmt":"2022-10-10T07:15:52","guid":{"rendered":"https:\/\/murrowcourses.com\/graphics\/?p=2007"},"modified":"2022-10-09T22:32:46","modified_gmt":"2022-10-10T05:32:46","slug":"mapping-point-data","status":"publish","type":"post","link":"https:\/\/murrowcourses.com\/graphics\/mapping-point-data\/","title":{"rendered":"Mapping Point Data"},"content":{"rendered":"

Any establishment that sells alcohol in Washington state must have a license from the State Liquor Control Board. In this assignment, we\u2019ll use Google apps to map all the licensed businesses in Whitman County, which is home to WSU’s Pullman campus.<\/p>\n

Preparing the Data<\/span><\/p>\n

1. We\u2019re going to use data from the Washington State Liquor and Cannabis Board (WSLCB)<\/strong><\/a>. The state agency gets many requests for lists of all issued licenses, so commonly requested files are available to download from their website here: http:\/\/www.liq.wa.gov\/records\/frequently-requested-lists<\/a><\/p>\n

The WSLCB spreadsheets include data for the whole state and many columns of information for each license. For this assignment, you get to use a CSV file with information about licenses for Whitman County businesses. (Whitman County, home to WSU’s Pullman campus, is a good example for this assignment because it has a manageable number of businesses. But the process works the same way for any other Washington county, and you can complete this assignment with a different county if you are comfortable filtering the state’s raw data yourself.)<\/p>\n

Download and save the CSV file from this folder: WSLCB data downloads<\/strong><\/a><\/p>\n

2. In Google Sheets, create a new spreadsheet and give it an appropriate name. Go to File > Import > Upload<\/strong> and add the CSV file.<\/p>\n\"\"<\/a>\n\"\"\n

3. Before we can map things, you\u2019ll need to edit the data in a few places to make it appropriate for mapping. First, go through the License Number<\/strong> column and look for licenses that are listed more than once (in most cases the business name will also be the same). As long as the two rows show the same address, delete extra rows so each license number is listed only once. You should end up with just less than 100 rows. Here is a very short video about how to delete rows<\/a>.<\/p>\n\"\"\n

4. To map the addresses, the full address must be in one cell instead of having the street address, city and zip code in separate cells. At the far right of your spreadsheet, name a column Geocode Address<\/strong>.<\/p>\n

We\u2019re going to fill in that new Geocode Address column with a formula:<\/p>\n

=CONCATENATE(D2,\", \",F2,\" \",G2,\" \",H2)<\/code><\/p>\n

The concatenate function combines text from different cells. In the formula, the commas separate the cells you want to combine, and anything inside quotation marks also gets added as text. (In this case, the quotation marks encase a space, because otherwise the address information would get smushed together. We’re skipping Column E since it’s not important for the address.)<\/p>\n\"\"\n

Hit enter to apply the formula, and use the formula bar right above the spreadsheet if you need to edit it.<\/p>\n

Once you have the formula working in the first row, double-click the blue square in the bottom corner of that cell down to copy the formula down into the remaining empty cells. (You can also copy the formula down the column by dragging the little blue square.)<\/p>\n

5. On the far right end of your spreadsheet, add a new column labeled Category<\/strong>. This will differentiate the two main types of liquor licenses.<\/p>\n

Not all the locations on the list have the same type of WSLCB license. Locations have either an On Premise<\/strong> or Off Premise<\/strong> license, which indicates whether they\u2019re allowed to serve alcohol at that location or just sell it for consumption elsewhere. (Bars have on-premise licenses, for example; grocery stores have off-premise licenses.) There are also a variety of more specific license types, such as “beer\/wine restaurant” and “grocery store.”<\/p>\n

In our spreadsheet, the On-Premise locations are listed first alphabetically, then the Off-Premise locations are listed. You can also tell whether a location is on- or off-premise by looking at the “Privilege” column. <\/p>\n

In the first cell of this column, type On-Premise<\/strong> and drag down to copy this to all the other rows. Put “Off-Premise” for those locations in the lower half of the spreadsheet.<\/p>\n

Making the Map<\/span><\/p>\n

6. Now it\u2019s time to map the data with Google Maps. In Google Drive, click New > More > Google My Maps<\/strong>. (If you don’t see it in your list of apps, click the “Connect more apps” button at the bottom of the list.) You can also get to My Maps at google.com\/maps\/d\/<\/a><\/p>\n\"\"\n