Postal Code Master table: Difference between revisions
Ttenbergen (talk | contribs) No edit summary |
Ttenbergen (talk | contribs) |
||
| (4 intermediate revisions by the same user not shown) | |||
| Line 12: | Line 12: | ||
|| '''PCODE''' || text(6) || the postal code | || '''PCODE''' || text(6) || the postal code | ||
|- | |- | ||
|| rhaname || text(17) || name of | || rhaname || text(17) || name of [[Manitoba RHAs]] that the postal code is in | ||
|- | |- | ||
|| CA_name || text(22) || | || CA_name || text(22) || | ||
| Line 39: | Line 39: | ||
== Data Integrity Checks == | == 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. | 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. | ||
We have a directory with various postal code lists we have used for comparison over time at | |||
We | * {{S:\MED\MED CCMED|CCMDB_Special_Projects\PostalCode}} | ||
The file '''''!ReadMe Postal Code Data.docx''''' in that directory has additional information. | |||
* S:\MED\ | |||
{{DJ | | |||
{{ | * Do we want a regular update process? See comments in that file. [[User:Ttenbergen|Ttenbergen]] 12:15, 4 August 2025 (CDT) | ||
* | |||
}} | }} | ||
== Log == | == Log == | ||
Latest revision as of 11:33, 4 August 2025
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 Manitoba RHAs 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) |
Contents
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.
We have a directory with various postal code lists we have used for comparison over time at
- S:\MED\Med_CCMED\CCMDB_Special_Projects\PostalCodeS:\MED\Med_CCMED\CCMDB_Special_Projects\PostalCode
The file !ReadMe Postal Code Data.docx in that directory has additional information.
|
|
Log
- 2022-08-?? - made field sizes smaller / more appropriate recently, removed duplicating/unused fields