US Address Verification
Revision as of 17:57, 21 June 2017 by OliverC (talk | contribs) (→ptoassigneend_us_cleaned.postcode_f5_cleaned is total garbage)
| US Address Verification | |
|---|---|
| Project Information | |
| Project Title | US Address Verification |
| Owner | Oliver Chang |
| Start Date | June 2017 |
| Deadline | June 2017 |
| Primary Billing | |
| Notes | |
| Has project status | Active |
| Copyright © 2016 edegan.com. All Rights Reserved. | |
This project is composed of SQL queries against patents.ptoassigneend_allus to gain confidence in the data. The scripts and rationale behind the design decisions in this table are described in its wiki page here and all original work was done by Sonia Zhang.
Total Row Counts
| Table Name | count(*) | Description |
|---|---|---|
| ptoassigneend | 7,234,001 | Base data with duplicates removed |
| ptoassigneend_allus | 3,572,605 | Sonia's subset of only US addresses; 49.4% of ptoassigneend |
| ptoassigneend_us_cleaned | 3,572,605 | "cleaned postcode, city, and state" of ptoassigneend_allus; this is the one we want to work with |
| ptoassigneend_us_extracted | 5,343 | cleaned up complete addresses; too small to be worthwhile |
Note that the suffix nd stands for "no duplicates". Assume this method was done correctly.
The Good
country column is reliable
It appears that the country field is consistent across all of the entries, and that the country names are taken from a small lookup table.
Proof:
patent=# select country, count(country) as c from ptoassigneend group by country order by c desc limit 50;
country | c
----------------------------------------+---------
UNITED STATES | 3570216
JAPAN | 1334774
GERMANY | 429429
KOREA, REPUBLIC OF | 284830
TAIWAN | 231493
FRANCE | 172995
CANADA | 132387
SWITZERLAND | 99281
UNITED KINGDOM | 90634
CHINA | 88954
NETHERLANDS | 84573
SWEDEN | 70538
ITALY | 61416
FINLAND | 44116
AUSTRALIA | 43314
ISRAEL | 41857
ENGLAND | 31341
DENMARK | 24742
BELGIUM | 24147
AUSTRIA | 19487
SINGAPORE | 17518
HONG KONG | 17091
SPAIN | 13288
INDIA | 12589
IRELAND | 11754
NORWAY | 11290
STATELESS | 10917
GREAT BRITAIN | 10692
BERMUDA | 8780
CAYMAN ISLANDS | 8206
NOT PROVIDED | 6658
VIRGIN ISLANDS, BRITISH | 6510
NEW ZEALAND | 6364
ONTARIO | 6047
LUXEMBOURG | 5856
KOREA, DEMOCRATIC PEOPLE'S REPUBLIC OF | 5625
LIECHTENSTEIN | 4711
BRAZIL | 4368
SOUTH AFRICA | 3699
RUSSIAN FEDERATION | 3375
SAUDI ARABIA | 3335
BARBADOS | 3115
HUNGARY | 2642
GERMAN DEMOCRATIC REPUBLIC | 2401
MEXICO | 2083
QUEBEC | 1777
NETHERLANDS ANTILLES | 1690
POLAND | 1607
MALAYSIA | 1606
BRITISH COLUMBIA | 1279
(50 rows)
territories, etc are categorized differently
There are single-digit edge cases that in the strictest interpretation of country are correct.
patent=# select count(*) from ptoassigneend; count --------- 7234001 (1 row) patent=# select count(*) from ptoassigneend where country = 'UNITED STATES'; count --------- 3570216 (1 row) patent=# select count(*) from ptoassigneend where country like '%UNITED STATES%'; count --------- 3570217 (1 row) patent=# select * from ptoassigneend where (country like '%UNITED STATES%' and country != 'UNITED STATES'); reelno | frameno | name | addrline1 | addrline2 | --------+---------+------------------------------+---------------+----------------------+-------- 29344 | 970 | WARNER CHILCOTT COMPANY, LLC | P.O. BOX 1005 | UNION STREET, KM 1.1 | FAJARDO(1 row)
all zipcodes are valid US zipcodes
Using the data from geonames for the United States, put into the table via psql < Z:\zipcodes-oliver\dump.sql where dump.sql is generated via E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\uspto_assignments\GeonamesZips.java, all of the values for postcodes are valid US postcodes.
patent=# select count(distinct c.postcode_f5_cleaned) from ptoassigneend_us_cleaned as c left join geonames_us_zipcodes
as z on c.postcode_f5_cleaned = z.zipcode;
count
-------
1116
(1 row)
patent=# select count(distinct postcode_f5_cleaned) from ptoassigneend_us_cleaned;
count
-------
1116
(1 row)
The Bad
state column is ill-constrained
# returns 57 rows (District of Columbia is counted as distinct state)
# select distinct state, count(state) from ptoassigneend_us_cleaned group by state limit 100;
...
Armed Forces in Europe, the Middle East, Africa, and Canada | 2
UNITED STATES ARMY | 2
Armed Forces in the Pacific | 3
| 1
NATIONAL AERONAUTICS AND SPACE ADMINISTRATION | 25
| 0
...
ptoassigneend_us_cleaned.postcode_*_cleaned is total garbage
This garbage stretches to include
ptoassigneend_us_cleaned.postcode_f5_cleanedptoassigneend_us_cleaned.postcode_cleaned
only .15% of records have their postcodes extracted correctly
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)
the underlying data is fine though...
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)
Moreover, selecting from the tables that ptoassigneend_us_cleaned 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.
The Ugly
fields directly contradict each other
Possible Heuristics:
- Presence of Ln, St, Ave, Blvd, Cir, etc...
approximate data is available for stateless assignees
only on the order of 500 stateless assignees have postcodes
patent=# select count(*) from ptoassigneend where (country = 'NOT PROVIDED' or country = 'STATELESS'); count ------- 17575 (1 row) patent=# select count(*) from ptoassigneend where (country = 'NOT PROVIDED' or country = 'STATELESS') and (postcode is not null); count ------- 329 (1 row) patent=# select count(*) from ptoassigneend where (country = 'NOT PROVIDED' or country = 'STATELESS') and (postcode is not null or addrline1 is not null); count ------- 1572 (1 row)
approximate location data is available (but unused)
patent=# select name, addrline1, addrline2, postcode from ptoassigneend where (country = 'NOT PROVIDED' or country = 'S
TATELESS') and (addrline1 is not null or addrline2 is not null) limit 30;
name | addrline1 | addrline2 | postcode
--------------------------------------------------------------------------------------+----------------------------------+----------------------------------+----------
REGAL KING COMMERCIAL OFFSHORE DE MACAU LIMITED | ALAMEDA DR. CARLOS D'S ASSUMPCAO | DYNASTY PLAZA, 19 AND. (H) MACAO |
ECODYNE CORPORATION | | A CORP. OF DE |
SHELL OIL COMPANY | | A CORP. OF DE |
RCA CORPORATION | | A CORP. OF DE |
BACHEM FEINCHEMIKALIEN A.G. | | A SWISS CORP. |
AMERICAN STERILIZER COMPANY | | A PA. CORP. |
CREATIVE TECHNOLOGY LTD. | CREATIVE RESOURCE | 31 INTERNATIONAL BUSINESS PARK | 60922
FUJITSU LIMITED | NAKAHARA-KU, KAWASAKI-SHI | 1-1, KAMIKODANAKA 4-CHOME | 211-8
SIEMENS AKTIENGESELLSCHAFT | | A GERMAN CORP |
ISABERG AB, HESTRA | | A CORP. OF SWEDEN |
MOELLER MANUFACTURING CO., INC. | | A DE CORP. |
MOBIL OIL CORPORATION | | A CORP OF NY |
GENERAL ELECTRIC COMPANY | | A NY CORP. |
GENERAL ELECTRIC COMPANY | A CORP. OF NY | |
BTR INDUSTRIES LIMITED | CARLISLE PLACE | BTR HOUSE |
DIATRON CORPORATION | | A CORP. OF CA |
CLOSURES AND PACKAGING SERVICES LIMITED | P. O. BOX 119 | | GY1 3HB
DR. JOHANNES HEIDENHAIN GMBH A CORPORATION OF GERMANY | | TRAUNREUT CITY |
AMERICAN SAFETY RAZOR COMPANY | | A DE CORP. |
ETHICON, INC. | | A NJ CORP. |
GENERAL ELECTRIC COMPANY | | A NEW YORK CORP. |
MOBIL OIL CORPORATION | | A CORP. OF NY |
RICHARD LANGLECHNER GMBH | MAUERBERGERSTR. 15 | |
KONISHIROKU PHOTO INDUSTRY CO., LTD., | | A CORP OF JAPAN |
SHELL OIL COMPANY | | A CORP. OF DE |
SIEMENS AKTIENGESELLSCHAFT A GERMAN CORPORATION | | MUNICH |
C.A. BRIGGS COMPANY | | A CORP OF PA |
GTE PRODUCTS CORPORATION | | A DE CORP. |
TRAMEX ENGINEERING LIMITED | 8 CLARE STREET, | A IRISH COMPANY |
MOBIL OIL CORPORATION | | A CORPORATION OF NEW YORK |
Conclusion
- Consistent Rows:
- Inconsistent Rows:
- Uncertain Rows:
