Changes

{{Project|Has project output=Tool|Has sponsor=McNair ProjectsCenter

|Has project status=ActiveComplete

! scope="col" | Table Name !! scope="col" | count(*) !! scope="col" | Descriptionof Tables in <code>patent</code>

=== <code>ptoassigneend_us_cleaned.postcode_f5_cleanedpostcode_*_cleaned</code> is total garbage === This garbage stretches to include * <code>ptoassigneend_us_cleaned.postcode_f5_cleaned</code>* <code>ptoassigneend_us_cleaned.postcode_cleaned</code> ==== only .15% of records have their postcodes extracted correctly ====  <nowiki>patent=# select count(*) from ptoassigneend_us_cleaned; count --------- 3572605 (1 row) patent=# select count(*) from ptoassigneend_us_cleaned where postcode_cleaned is not null; count --------- 3376480 (1 row) patent=# select count(*) from ptoassigneend_us_cleaned where postcode_f5_cleaned is not null; count ------- 5344 (1 row) patent=# select postcode_cleaned, postcode_f5_cleaned from ptoassigneend_us_cleaned where (postcode_cleaned is not null and postcode_f5_cleaned is null) limit 12; postcode_cleaned | postcode_f5_cleaned -------------------------------------------+--------------------- £\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082 | £\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082 | £\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082 | £\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082 | £\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082 | £\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082 | £\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082 | £\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082 | £\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082 | £\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082 | £\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082 | £\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082 | (12 rows)  patent=# select count(*) from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082'; count--------- 3371136(1 row) </nowiki> ==== the underlying data is fine though... ==== <nowiki>patent=# select postcode, postcode_addr1, postcode_addr2, postcode_city from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082' limit 5; postcode | postcode_addr1 | postcode_addr2 | postcode_city----------+----------------+----------------+--------------- 75024 | | | 55379 | | | 94538 | | | 23219 | | | 73114 | | |(5 rows)</nowiki> Moreover, selecting from the tables that <code>ptoassigneend_us_cleaned</code> is derived from did not yield this string. Therefore, there is likely an error in the SQL script, perhaps with some wonky copy-pasting from the internet. ==== ...this case is hit when postcode is present but none of the other fields are present ====  <nowiki>patent=# select count(*) from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u 0082'; count --------- 3371136 (1 row) patent=# select count(*) from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u 0082' and postcode is not null; count --------- 3371136 (1 row)  patent=# select count(*) from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u 0082' and postcode is not null and postcode_addr1 is null and postcode_addr2 is null and postcode_city is null; count--------- 3371136(1 row)</nowiki> ==== ...easy gains can be gotten as a quick fix ====  <nowiki>patent=# select count(*) from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082' and postcode is not null; count--------- 3371136(1 row) patent=# select count(*) from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082' and char_length(postcode) = 5; count--------- 2971542(1 row)</nowiki> ==== ...5 digit postcode works great ====  <nowiki>patent=# select postcode from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082' and char_length(postcode) = 5 limit 12; postcode---------- 75024 55379 94538 23219 73114 95134 33487 60603 84604 20191 97213 10504(12 rows)</nowiki> ==== ...9 digit postcode works ok (two forms) ====  <nowiki>patent=# select postcode from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082' and char_length(postcode) = 9 limit 12; postcode----------- USA 20585 USA 33458 4826-2766 181951501 772522463 USA 27601 772522463 USA 20310 USA 47202 174012991 913929221 USA 12345(12 rows)</nowiki> ==== ...10 digit postcode works great ====  <nowiki>patent=# select postcode from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082' and char_length(postcode) = 10 limit 12; postcode------------ 92121-1714 95134-1706 80527-2400 95066-4544 01862-2000 07962-2245 90245-5012 55133-3427 30332-0415 95134-1706 20892-7660 94080-4990(12 rows)</nowiki>

== Conclusion A Stopgap Measure == As a quick and dirty fix, walk through the cases and do the best we can. The script for that is located at <code>E:\McNair\Projects\SimplerPatentData\src\db\hacks\June2017PostcodeHotfix.sql</code> and it creates the following table:  <nowiki>DROP VIEW IF EXISTS june_2017_postcodecode_hotfix;CREATE VIEW june_2017_postcode_hotfix AS SELECT reelno, frameno, name, city_cleaned as city, GodHelpUsAll(postcode_cleaned, postcode) as postcode FROM ptoassigneend_us_cleaned;</nowiki>  <nowiki>patent=# select count(*) from june_2017_postcode_hotfix ; count--------- 3572605(1 row)

patent=# select count(* Consistent Rows:) from june_2017_postcode_hotfix where postcode is not null;* Inconsistent Rows: count--------- 3370613* Uncertain Rows:(1 row)</nowiki>