Postal Code Master table: Difference between revisions
Ttenbergen (talk | contribs) |
Ttenbergen (talk | contribs) |
||
| (15 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
Postal_Code_Master table in [[ | {{DISPLAYTITLE: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]]. | 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]]. | ||
| Line 12: | Line 12: | ||
|| '''PCODE''' || text(6) || the postal code | || '''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) || | || latitude || number(single) || | ||
| Line 33: | Line 27: | ||
|} | |} | ||
== 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. | 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: | 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/ | * https://www.serviceobjects.com/blog/free-zip-code-and-postal-code-database-with-geocoordinates/ | ||
** This data has some errors... | ** 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\PostalCode}} | |||
The file '''''!ReadMe Postal Code Data.docx''''' in that directory has additional information. | |||
{{DJ | | |||
* Do we want a regular update process? See comments in that file. [[User:Ttenbergen|Ttenbergen]] 12:15, 4 August 2025 (CDT) | |||
}} | |||
== Log == | |||
* 2022-08-?? - made field sizes smaller / more appropriate recently, removed duplicating/unused fields | |||
== Related articles == | == Related articles == | ||
{{Related Articles}} | {{Related Articles}} | ||
[[Category:Data structure]] | [[Category:Data structure]] | ||
[[Category:Postal Codes]] | [[Category:Postal Codes]] | ||