Postal_Code_Master table

Postal_Code_Master table in Centralized data.accdb is used by query NDC_Bad_Postal_Code to detect by exclusion Postal Code entries that are likely invalid.

The table on its own would be about 71MB, (30MB with all extra info stripped out) which is why the check was not included in CCMDB.accdb.

Data Structure

field	type	description
PCODE	text(6)	the postal code
rhaname	text(17)	name of regional health authority that the postal code is in
CA_name	text(22)
NC_Name	text(27)
latitude	number(single)
longitude	number(single)
province	text(4)
town	text(40)

This is the table that postal codes get checked again. It includes the MB RHA that a postal code is affiliated with. For the MB component we imported a version provided by Marc Silva to p:Julie Mojica 2016-09-08. The country-wide component was based on an older table I think we go it from Stats Can.

The contents are a mix of what we have received from various sources, and of what Pagasa adds when she finds a code that hasn't been listed before.

This data is not available form the Government of Canada, but a regularly updated file is available from here:

https://www.serviceobjects.com/blog/free-zip-code-and-postal-code-database-with-geocoordinates/
- This data has some errors... there is no free and regularly updated source of this that we know of.

Data Integrity Checks

Management of this table has always been driven only by resolving inconsistencies query NDC_Bad_Postal_Code; our Data Processor updates Master when she finds that a previously unused code shows up in our data and is found to exist on the Canada Post website. See Query NDC_Bad_Postal_Code#CFE Data Integrity Checks Steps.

Maintenance and Validation of Master table

We maintain our own Postal_Code_Master table because access to a canonical record set has always been problematic. We have at times received copies of this data, but not found a source for regular updates.

	JALT We do not have a central process to update postal codes as new ones are added to the system. Do we need a formal process? Is the current ad-hoc process enough. Or do we need a decision on allowing these inconsistencies and just documenting them? Ttenbergen 15:55, 24 June 2025 (CDT)

Our files for this, with documentation and names, are on

\\ad.wrha.mb.ca\WRHA\HSC\shared\MED\MED_CCMED\QA
S:\MED\MED_CCMED\CCMDB_Special_Projects\PostalCode

	PWC emailed Chastity to see if we could get Postal Code data from them rather than maintaining our own. Never got a response. Emailed Chastity again. Ttenbergen 14:41, 2022 June 30 (CDT) I have since then found a listing of postal codes through stats can, we may need a process to maintain the master beyond what this page provides. Ttenbergen 15:23, 24 June 2025 (CDT)	added: 2022-03-24 action: 2022-07-15

	I think this is the process where you said you are having problems with copy/pasting. Copy pasting isn't even mentioned here, so maybe update the process to show how you actually do this, so that someone like Sheila Rusnak would be able to follow the instructions. Ttenbergen 15:45, 2022 March 17 (CDT) Do you create that query each time? Would we be able to update the NDC query that finds these in the first place to include the info you need? We can discuss at our next meeting. Ttenbergen 16:23, 2022 March 17 (CDT) Taking this off Pagasa's list for now, since if we can get this data from DSS we won't need to do this any more. Ttenbergen 15:36, 2022 March 24 (CDT)

File Locations

old master imports can be found at S:\MED\Med_CCMED\S:\MED\Med_CCMED\CCMDB\z_archive\PostalCode

Log

2022-08-?? - made field sizes smaller / more appropriate recently, removed duplicating/unused fields