Data loading part 2: Prepare the geocode data

by Kevin Garwood

Overview

Previous

Next

Purpose

The table also contains a simple flag has_valid_geocode, which may hide various data quality indicators that are specific to a cohort, or to software they use to match residential address histories to geographical map coordinates.

Location of Original Data File

original_geocode_data.csv

early_life/input_data

later_life/input_data

Example Original Data File

Suggested Approach

The geocode field is used to link original_geocode_data, original_address_history_data and original_exposure_data tables together. The map coordinates that are generated through the geocoding activity will be needed to produce exposure values for each geocode.

Step 1: Gather all cohort residential addresses

First, we try to isolate just the postal address data. In the example table, we would ignore phone number because ALGAE does not need it. For the purpose of geocoding, we need neither person_id nor time_stamp fields. However, we will want to preserve the link between geocodes and address periods.

Our geocoding scheme must accommodate cases where the same geocode is used by multiple people at different times, and when the same location may be represented in several slightly different addresses. In order to minimise the amount of extraneous information used for geocoding, we can add an identifier field for each address period.

Step 2: Express Addresses in a Canonical Format

Step 3: Use geocoding software to produce map coordinates

We expect that projects will use software to help automatically match residential addresses with map coordinates. Because the behaviour of geocoding applications can vary, we strongly recommend that you populate the required version field with the name and version of your software.

The software will generate x and y coordinates for each address. For the purposes of these examples, we will assume that you will generate geocode values by concatenating map coordinates together. However, some cohorts may be concerned about including sensitive location data in this field (See General Advice).

The software will likely produce data fields that indicate the quality of a match or reasons why the software had trouble processing it. Initially, you may want to preserve these fields in order to help trouble shoot cases where the software fails to produce a match.

Step 4: Allow for manual geocoding efforts

ALGAE expects that a comments field will be present in the original_geocode_data table. Although you can leave this field blank, you may want to include other useful information in it. For example, you could record how you manually fixed a canonical address that resulted in a failed match. You may also want to preserve diagnostic information about a geocode that was generated by the geocoding software you used. How you populate this field will depend on what you think is important to record or what information about locations you're comfortable to retain in the final version of the table.

Step 5: Decide how to derive values for has_valid_geocode field

For example, in the example data set, you may decide that if an address was manually geocoded or if the quality of an automated match is above 50%, then the result is "Y". Otherwise the value would be "N".

Step 6: Add geographical covariate data fields

Step 7: Link geocodes back to address period data

Step 8: Eliminate extraneous fields

Some projects may want to retain location information in the geocodes, whereas others may want to anonymise them to help minimise risks of identifying study members in data sets that may be maintained off-site from the cohort's facilities. Although anonymising geocodes can help minimise identifiability, it can also remove information that can be used to determine

General Advice

Decide whether you want to anonymise geocodes

Depending on how cohort facilities and off-site research partners divide the work of producing the original data tables, the project members may want the geocode values to be anonymised to minimise the identifiability of location data. Should you decide to anonymise geocode data, take special care to ensure that geocode values in corresponding tables are also anonymised in the same way.

Expect to do geocoding in multiple passes

In order to ensure that the most study members have exposure results, you may spend a lot of effort doing geocoding. Typically your work will either mean you correct addresses and re-run the software or that you interpret incorrect addresses and use your judgement to match them with appropriate map coordinates. We suggest you assume you will need to finish geocoding in multiple passes, and that in each pass you will make use of the version and comments fields to record how coordinates were derived.

Understand why geocoding may fail for some addressses

There are many reasons why an address may result in an invalid geocode. Some of these include:

typographical errors. If residential addresses are typed manually, they may contain mistakes that result in a non-existent address.

incomplete addresses. If an address is missing a street number, the software may either fail to provide a match, or provide a guess that is of such poor quality that it is not appropriate to use in a given study.

deprecated addresses. Over time, some street names may change and post codes may be retired, or reflect a change in the scope of area they cover. If the software is unable to recognise outdated addresses, it may fail to produce a match.

new addresses. The software may be referencing an outdated collection of addresses that may not include addresses from newly built residential areas.

incomplete address database. The geocoding software may be using a database that simply doesn't contain all the addresses it should have.

parsing errors. The software may interpret characters which it uses as delimiters. For example, the presence of a comma may cause the software to break an address into an incorrect number of address components. As another example, it may interpret the forward slash in 'c/o' as a signal to perform some kind of pre-processing.

abbreviations. An address may be correctly specified but contain abbreviations that are not understood by the software. For example, if the software is designed to suit US addresses, it may understand "ct." means court but may not understand the Canadian version which uses "crt".

centroid errors. The software, in its attempts to find the centre for a postal code area, may provide coordinates that would not make sense on a map. For example, the software could determine that the centre of a postal code area that covers a cove may actually be on water. Other calculations could place the centre of a post code in at the top of a mountain or in the middle of an industrial area that has no residents.

There are certain classes of errors that neither automated nor manual geocoding approaches would likely identify. For example, an address may contain a topographical error that still results in a valid geocode. Suppose that an address is written as 22 Pine Street when it should be 222 Pine Street, and the street is long enough to accommodate both addresses.

Table Properties