Postal Code Master table: Difference between revisions

(22 intermediate revisions by the same user not shown)

Line 1:

Postal_Code_Master ~~is a~~ table in [[~~Centralized_data~~.~~mdb~~]] ~~that~~ is used by [[query NDC_Bad_Postal_Code]] to detect ~~postal codes~~ that are likely invalid.

{{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.~~mdb~~]].

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 ==

{| class="wikitable sortable"

|-

!| 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\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 ==

[[Category:Data structure]]

[[Category:Postal Codes]]

@@ Line 1: / Line 1: @@
-Postal_Code_Master is a table in [[Centralized_data.mdb]] that is used by [[query NDC_Bad_Postal_Code]] to detect postal codes that are likely invalid.
+{{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.mdb]].
+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 ==
+{| class="wikitable sortable"
+|-
+!| 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\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}}
 [[Category:Data structure]]
 [[Category:Postal Codes]]