At the April meeting, Collaborative members expressed interest in developing a map of their patients by Census Tract. This type of map could be used for strategic planning at the organization- and Collaborative-level. Community Heath Solutions will work with clinics to develop these data, following the steps below.
Part 1. Clinic staff compile a spreadsheet of patient addresses (street address, city, state, zip). This can be based on active patients at the clinic at a point in time.
Part 2. Proceed with geocoding, based on the CHS video tutorial (see video below). The video will guide you through the following steps:
- Sanitize your patient address file of Patient Names and MRNs. *Note: You may want to save a copy of the file with patient names on your server, for future reference, but you will remove PHI from the file you upload for geocoding)
- Save as a CSV (comma separated values) file type and make sure your column headings read “Street Address”, “City”, “State”, and “Zip”. *Note: Make sure the patient addresses are on only one worksheet/tab in your spreadsheet. The CSV file type will only allow one worksheet.
- Visit this page: https://geoservices.tamu.edu/Services/Geocode/BatchProcess/
- Sign up for a free account.
- In the Batch Geocoding section, upload your CSV file to the website.
- Proceed through the steps to geocode your file. *Note: Make sure to click the “CensusValues” checkbox at the appropriate point as noted in the video.
- Download the geocoded file when finished, and save to your computer.
- In the geocoded file, delete all columns EXCEPT the Unique ID and any columns that start with “Census”. Note: You will delete the original patient addresses along with the other extraneous columns.
- Send that sanitized file to CHS (email@example.com). We will aggregate the data so you have a total number of patients by census tract.