Patent Assignment Data Restructure

From edegan.com
Jump to navigation Jump to search



In order to restructure the current patent dataset, the data requires rigorous cleaning. The primary areas for improvement are:

McNair Project
Patent Assignment Data Restructure
Project logo 02.png
Project Information
Project Title Patent Data Restructure
Owner Marcela Interiano, Sonia Zhang
Start Date 201701
Deadline 201705
Keywords Patent, Data
Primary Billing
Notes
Has project status Active
Subsumes: Patent Data (Wiki Page), Patent Data Cleanup - June 2016, Patent Data Extraction Scripts (Tool), USPTO Bulk Data Processing
Copyright © 2016 edegan.com. All Rights Reserved.
1. Clean ptoassignment table to unique keys.
2. Clean ptoproperties to remove nonutility patents. The patent numbers currently include:
  • 7 digit patent numbers
  • application numbers
  • unknown numbers that cannot be matched to patent numbers in the patent table
20090108066
20100007288
20090108066
20100110022
  • Design and Reissue patents ('%D%' or '%RE%')
  • alphanumeric character strings
3. Restructure address information in ptoassignee table to extract meaningful information
4. Verify that cleaned patent documentids correspond to patent numbers or application numbers in the patent table
5. Restructure address information in ptoassignment table
6. Transform structure of the dataset

Semester Plan

The final deliverable for the semester is a table with the following structure: 

Reel No | Frame No | Invention Title | Filing Date | Patent No | Application No | Publication No | Match By

The schema of the table will be: 

 Column          |          Type         | Modifiers
-----------------+-----------------------+-----------
Reel No          |        integer        |
Frame No         |        integer        |
Invention Title  | character varying(500)|
Filing Date      |          date         |
Patent No        |        integer        |
Application No   |        integer        |
Publication No   |        integer        |
Match By         |        integer        |

Currently the pto tables contain varying identifiers for one invention title that is involved in a reassignment. The purpose of the table is to have each identifier for an invention title listed in a single row, making it easy to track the invention through various transactions. The Match By column will inform the user which identifier should be used to match to other tables in the patent database.

Data Cleanup Progress

Patent Number Cleanup

The goal is to only have assignment records on utility patents. The patents in ptoproperty include alphanumerics which represent reissue and design patents as well as mistakes in the data input. Additionally, the documentids include application numbers or ids and publication numbers. The ptoproperty table stores the patent ids as character strings.

First the duplicates were dropped from the ptoproperty table creating ptoproperty_cleaned. 

SELECT COUNT(*) FROM (SELECT DISTINCT * FROM ptoproperty) As T; 
--27266638
 
SELECT COUNT(*) FROM ptoproperty_cleaned;
--27266638

Next, the Reissue and Design patents were removed. 

SELECT COUNT(*) FROM ptoproperty WHERE documentid LIKE 'RE%';
--38512

SELECT COUNT(*) FROM ptoproperty WHERE documentid LIKE 'D%';
--1128247

Restructure Address Information

The dbase is patent.

The table is ptoassigneend.

SQL code and other things are in: 

E:/McNair/Projects/PatentAddress

IO files are on the dbase server in: 

Z:/PatentAddress

Extract Address Information

1. Introduction
  • Five features (addrline1, addrline2, city, country, postcode) in the table contain address information.
  • Features addrline1, addrline2 and city are not cleaned. They have city, country and postcode information.
  • The object of this project is to extract city, country and postcode information from the three features above.
  • By now, we only focus on cleaning American patents.
2. Postcode(U.S.)

U.S. post code follows the pattern [five digits - four digits]. In this way, U.S. patents can be extracted by searching for post code with regular expression 

'(^|\s)\d{5}-\d{4}($|\s)'

For example, 

                city                  | postcode_city
NEW YORK, NY 10022-3201               | 10022-3201
BEAVERTON, OREGON 97005-6453          | 97005-6453
SANTA BARBARA, CA 93130-3003          | 93130-3003
NEW YORK NY 10022-3201                | 10022-3201
SUNNYVALE, CA 94088-3453              | 94088-3453
94088-3470                            | 94088-3470
CS 46510-35065 RENNES CEDEX           | 46510-3506
NEW YORK, NY 10013-2412               | 10013-2412
OALKLAND, CA 94612-3550               | 94612-3550
OXFORD CT 06483-1011                  | 06483-1011

The extracted post code records are stored in table ptoassigneend_missus_final.

SQL code is in: 

E:/McNair/Projects/PatentAddress/RxPostcode.sql

Simplified code is in: 

E:/McNair/Projects/PatentAddress/Functions.sql
3. State (U.S.)

Some patterns can be used to extract state information.

a. '[,] State Postcode'

The state and post code are always together, separated by a space. We can extract state information with regular expression 

'([,]|[.])\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'

SQL Code: 

WHEN city ~* '([,]|[.])\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'
THEN LTRIM(RTRIM(replace(regexp_replace(SUBSTRING(city, '[,]\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'), '\d{5}-\d{4}', ),',',)))

Examples: 

                city                  |  state_city
NEW YORK, NY 10022-3201               | NY
BEAVERTON, OREGON 97005-6453          | OREGON
SANTA BARBARA, CA 93130-3003          | CA
SUNNYVALE, CA 94088-3453              | CA
NEW YORK, NY 10013-2412               | NY
OALKLAND, CA 94612-3550               | CA
SANTA CLARA, CA 95052-8090            | CA
PEORIA, IL  61629-6490                | IL
MIDVALE, UTAH 84047-1408              | UTAH
OAKLAND, CA 94612-3550                | CA
HARRISBURG, PA 17105-3608             | PA
ROCHESTER, NY  14650-2201             | NY
NEW YORK, NY  10013-2412              | NY
HOUSTON, TEXAS 77256-6571             | TEXAS
BROOKINGS, SOUTH DAKOTA 57006-0128    | SOUTH DAKOTA

b. '\s State(abbreviation) Postcode' 

'(^|\s)\w{2}\s{1}\d{5}[-]\d{4}'

SQL code: 

WHEN city ~* '(^|\s)\w{2}\s{1}\d{5}[-]\d{4}' 
THEN regexp_replace(SUBSTRING(city, '\w{2}\s{1,}\d{5}[-]\d{4}'), '\d{5}-\d{4}', )

Examples: 

NEW YORK NY 10022-3201    |NY
WAUKEGAN IL 60085-2195    |IL

c. 'D.C.' 

'D[.]C[.]\s\d{5}-\d{4}'

SQL code: 

WHEN city ~* 'D[.]C[.]\s\d{5}-\d{4}'
THEN 'D.C.'

d. 'A CORP.* OF [State]'

This pattern is not reliable. When addrline1 looks this way, addrline2 always provide more detailed address information than addrline2. Besides, a great part of state info extracted from 'A CORP.* OF [State]' doesn't match the state extracted from detailed addrline2. In this way, we discard this pattern.

Examples: 

A CORP. OF NEW YORK                                                                      | ONE LINCOLN FIRST SQUARE, ROCHESTER, NEW YORK, NY 14601-0054
A CORPORATION OF NY                                                                      | 550 MADISON AVENUE, NEW YORK, NY 10022-3201
A NON-PROFIT CORP. OF WASHINGTON                                                         | 4225 ROOSEVELT WAY NE, STE. 303 SEATTLE, WA 98105-6099
A CORP. OF MN                                                                            | 2611 NORTH SECOND ST.,                                      MINNEAPOLIS, MN 55411-1633
A CORPORATION OF DE                                                                      | 612 WHEELER'S FARM RD.                                      MILFORD, CT 06460-8719
A CORP. OF WA.                                                                           | 411 FIRST AVENUE SOUTH                                      SEATTLE, WASHINGTON 98104-2871
A CORPORATION OF OREGON                                                                  | ONE BOWERMAN DRIVE, BEAVERTON, OREGON 97005-6453
A CORPORATION OF                                                                         | ONE BOWERMAN DRIVE BEAVERTON, OR 97005-6453
A CORP. OF INDIANA                                                                       | 4810 TECUMSEH LANE, POST OFFICE BOX 15190                   EVANSVILLE, INDIANA 47716-0190
A CORPORATION OF PA                                                                      | 7200 SUTER ROAD, COOPERSBURG, PA 18036-1299
A CORP. OF DE                                                                            | SCARBORO & BEAR CREEK ROAD, P.O. BOX 2009                   OAK RIDGE, TENNESSEE 37831-8014

If you're interested, the SQL code is 

WHEN addrline1 ~* 'CORP.*OF(\s|$)'
THEN LTRIM(RTRIM(SUBSTRING(regexp_replace(SUBSTRING(addrline1, 'CORP.*OF.*'),'CORP.*OF', ), 1, 18)))
  • Summary

The extracted state records are stored in the table ptoassigneend_missus_final.

SQL code is in: 

E:/McNair/Projects/PatentAddress/RxPostcode.sql

Simplified code is in: 

E:/McNair/Projects/PatentAddress/Functions.sql
4. City (U.S.)

Some patterns can be used to extract city information.

Three lists of samples extracted from addrline1, addrline2 and city are used to summarize the patterns. They are in 

Z:/PatentAddress/

a. '\s{2,} CityName [,] State Postcode'

SQL code: 

CASE WHEN addrline1 ~* '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}' 
THEN LTRIM(RTRIM(REPLACE(SUBSTRING(SUBSTRING(addrline1, '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5} [-]\d{4}'),'.*[,]'),',',)))

Examples: 

800 CHRYSLER DR. EAST                                       AUBURN HILLS, MICHIGAN  48326-2757  |AUBURN HILLS
550 MADISON AVENUE                                          NEW YORK, NEW YORK 10022-3201       |NEW YORK
P.O. BOX 15439                                              WILMINGTON, DE  19850-5439          |WILMINGTON

Some noise exists (just a little). 

1313 N. MARKET STREET                                       HERCULES PLAZAWILMINGTON, DE  19894-0001

b. '[,]\s{1,} CityName [,] State Postcode'

SQL code: 

CASE WHEN addrline1 ~* '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}' 
THEN LTRIM(RTRIM(REPLACE(SUBSTRING(SUBSTRING(addrline1, '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'),'.*[,]'),',',)))

Example: 

920 DISC DRIVE, SCOTTS VALLEY, CA 95067-0360                               |SCOTTS VALLEY
550 MADISON AVENUE, NEW YORK, NY 10022-3201                                |NEW YORK
BALLSTON TOWER ONE 800 NORTH QUINCY STREET, ARLINGTON, VA   22217-5660     |ARLINGTON

c. 'CityName [,] State Postcode' (no leading spaces)

SQL code: 

WHEN addrline1 ~* '^\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'
THEN REPLACE(SUBSTRING(SUBSTRING(addrline1, '^\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'), '.*[,]'),',', )

Examples: 

PHILADELPHIA, PA 19104-3147   |PHILADELPHIA
ROCHESTER, NY 14650-2201      |ROCHESTER

d. 'CityName State(abbreviation) Postcode' (no leading spaces)

SQL code: 

WHEN addrline1 ~* '^\w{1,}\s{0,}\w{0,}\s{0,}\w{2}\s{0,}\d{5}[-]\d{4}'
THEN regexp_replace(SUBSTRING(addrline1, '^\w{1,}\s{0,1}\w{0,}\s{1,}\w{2}\s{0,}\d{5}[-]\d{4}'), '\w{2}\s\d{5}[-]\d{4}', )

Examples: 

TARRYTOWN NY 10591-6706       |TARRYTOWN

e. Special cases

Examples: 

	BATON, ROUGE, LA 70809-4562 ('BATON ROUGH' is separated by a comma)          
        ST. PAUL, MIN 55133-3427 ('ST. PAUL' contains a dot)
 QUINCY STREETARLINGTON, VA 22217-5660 (no space between street and city name :(

SQL code: 

CASE WHEN addrline1 ~* 'BATON[,] ROUGE[,]\s{1}LA'
THEN 'BATON ROUGE'
WHEN addrline1 ~* 'ST[.]\s{1}PAUL'
THEN 'ST. PAUL'
WHEN addrline1 ~* 'ARLINGTON[,]\s{1}VA'
THEN 'ARLINGTON'

Noise:

  • CityName State (full name) Postcode' (no leading spaces)
NEW YORK NEW YORK 10022-3201

This pattern can't be identified because of much noise.

  • 'CityName Postcode' (no leading spaces)
LITTLE ELM  75068-3787    
OAK RIDGE  37831-6498

This pattern can't be identified because of the noise: 

MASSACHUSETTS 02780-7319   ('State Postcode')
  • no space between street and city name :( 
BOX 87703CHICAGO, IL  60680-0703

SQL code is in: 

E:/McNair/Projects/PatentAddress/RxCity.sql

Simplified code is in: 

E:/McNair/Projects/PatentAddress/Functions.sql
5. Output (Tables)
  • ptoassigneend_allus

This table contains all the U.S. patents extracted from ptoassigneend table. The rule to generate this table is 'country = 'UNITED STATES' ' or any of the features contains U.S. postcode.

Noise exists because of postcode errors.

The ptoassigneend_allus table may miss some U.S. patents which lose postcode records.

SQL code: 

CREATE TABLE ptoassigneend_allus AS
SELECT * FROM ptoassigneend
WHERE city ~* '.*\d{5}[-]\d{4}.*' OR addrline1 ~* '.*\d{5}[-]\d{4}.*' OR
addrline2 ~* '.*\d{5}[-]\d{4}.*' OR postcode ~* '.*\d{5}[-]\d{4}.*' OR
country ~* 'UNITED STATES';

SELECT 3572682

      Table "public.ptoassigneend_allus"
 Column   |          Type          | Modifiers
reelno    | integer                |
frameno   | integer                |
name      | character varying(500) |
addrline1 | character varying(500) |
addrline2 | character varying(500) |
city      | character varying(500) |
state     | character varying(500) |
country   | character varying(500) |
postcode  | character varying(80)  |
  • ptoassigneend_us_extracted

Contain all the original features as well as city, state and postcode info extracted from features addrline1, addrline2 and city. See Section 2, 3, 4 for extraction process. 

     Table "public.ptoassigneend_us_extracted"
    Column     |          Type          | Modifiers
reelno         | integer                |
frameno        | integer                |
name           | character varying(500) |
addrline1      | character varying(500) |
addrline2      | character varying(500) |
city           | character varying(500) |
state          | character varying(500) |
country        | character varying(500) |
postcode       | character varying(80)  |
postqcode_city | text                   |
postcode_addr1 | text                   |
postcode_addr2 | text                   |
state_city     | text                   |
state_addr1    | text                   |
state_addr2    | text                   |
city_addr1     | text                   |
city_addr2     | text                   |
city_city      | text                   |

postcode_city is the postcode extracted from 'city'; postcode_addr1 is the postcode extracted from 'addrline1'; postcode_addr2 is the postcode extracted from 'addrline2'.

state_city is the state name extracted from 'city'; state_addr1 is the state name extracted from 'addrline1'; state_addr2 is the state name extracted from 'addrline2'.

city_city is the city name extracted from 'city'; city_addr1 is the city name extracted from 'addrline1'; city_addr2 is the city name extracted from 'addrline2'.

SQL code is in: 

E:/McNair/Projects/PatentAddress/Functions.sql

Master Table

Obj:

Create a Master Table with original features and cleaned postcode, city, and state.

Postcode

Reminder: postcode_city is the postcodes extracted from 'city'; postcode_addr1 is the postcodes extracted from 'addrline1'; postcode_addr2 is the postcodes extracted from 'addrline2'.

The postcode_city, postcode_addr1 and postcode_addr2 are consistent.

Examples: 

postcode_addr1 | postcode_addr2
90401-1708     | 90401-1708
37831-8243     | 37831-8243

The issue is the inconsistency between postcode and (postcode_city, postcode_addr1 and postcode_addr2).

  • Inconsistency between postcode and postcode_addr1

postcode_addr1 beats postcode because addrline1 is detailed. For example: 

                                                                        addrline1                                                                          | postcode_addr1 | postcode_new
P.O. BOX 6 / 83707-0006                                                                                                                                    | 83707-0006     | 83716
BLDG. C01, M.S. A126 P.O. BOX 80028 LOS  ANGELES, CA 90080-0028                                                                                            | 90080-0028     | 90045
P. O. BOX 1407, HOUSTON, TEXAS 77251-1407                                                                                                                  | 77251-1407     | 77042
841 3RD, LONGVIEW, WASHINGTON 98632-0189                                                                                                                   | 98632-0189     | 95404
2210 W. OAKLAWN, 72762-6999                                                                                                                                | 72762-6999     | 72765-2020
BLDG. CO1, M.S. A126, P.O. BOX 80028 LOS ANGELES, CA  90080-0028                                                                                           | 90080-0028     | 90045
P.O. BOX 6 / 83707-0006                                                                                                                                    | 83707-0006     | 83716
  • Inconsistency between postcode and postcode_addr2

postcode_addr2 beats postcode because addrline2 is detailed.

Example: 

                                              addrline2                                               | postcode_addr2 | postcode_new
P.O. BOX 6 / 83707-0006                                                                               | 83707-0006     | 83716-9632
P.O. BOX 5800 - MS0161, ALBUQUERQUE, NEW MEXICO 87185-0161                                            | 87185-0161     | 87123-0161
P.O. BOX 674412, HOUSTON, TEXAS 77267-4412                                                            | 77267-4412     | 77002
WASHINGTON, DC  20250-1400                                                                            | 20250-1400     | 61604
BOX 10076, HATTIESBURGH, MISSISSIPPI  39406-0076                                                      | 39406-0076     | 39402
P.O. BOX 674412, HOUSTON, TEXAS 77267-4412                                                            | 77267-4412     | 77032
P.O. BOX 6 / 83707-0006                                                                               | 83707-0006     | 83716-9632
P.O. BOX 6 / 83707-0006                                                                               | 83707-0006     | 83716-9632
P.O. BOX 27115-4191                                                                                   | 27115-4191     | 27105-4191
P.O. BOX 674412, HOUSTON, TX 77267-4412                                                               | 77267-4412     | 77002

Besides, I randomly picked some records and googled address and postcode. These records support postcode_addr2.

Examples: 

Micron Technology, Inc.
8000 South Federal Way
Post Office Box 6
Boise 83707-0006
USA
Tel: Ph: 208-368-4000

WebsterBank, NA
50 Kennedy Plaza
11th Floor Suite 110 (Mail Stop: PR-105)
Providence, RI 02903
  • Inconsistency between postcode and postcode_city

postcode_city beats postcode. 

        city          |  state   | postcode_city | postcode
HAUPPAUGE, 11788-8847 | NEW YORK | 11788-8847    | 78759
  • Output

SQL code is in: 

E:/McNair/Projects/PatentAddress/Functions.sql

For records of which addrline1, addrline2 and city don't contain postcode info, just clean the feature postcode as the postcode_cleaned

All the cleaned postcodes for U.S. patents are stored in ptoassigneend_us_cleaned (see feature postcode_cleaned).

State

Reminder: state_city is the states extracted from 'city'; state_addr1 is the states extracted from 'addrline1'; state_addr2 is the states extracted from 'addrline2'.

The state_city, state_addr1 and state_addr2 are consistent.

Examples: 

state_addr2 | state_city
ILLINOIS    | ILLINOIS
IL          | IL
IL          | IL
CT          | CT
MN          | MN
CA          | CA

One reason for the inconsistency between state and (state_city, state_addr1 and state_addr2) is the co-existence of abbreviation and full form. So they are the same actually. Otherwise, state_city, state_addr1 and state_addr2 beat feature state because they are detailed.

For records of which addrline1, addrline2 and city don't contain state info, just take feature state as the state_cleaned.

  • Output

SQL code is in: 

E:/McNair/Projects/PatentAddress/Functions.sql

All the cleaned states for U.S. patents are stored in ptoassigneend_us_cleaned (see feature state_cleaned).

City

Reminder: city_city is the cities extracted from 'city'; city_addr1 is the cities extracted from 'addrline1'; city_addr2 is the cities extracted from 'addrline2'.

The city_city, city_addr1 and city_addr2 are consistent.

Examples: 

  city_addr2  |  city_city
BOISE        | BOISE
THOMASVILLE  | THOMASVILLE
CARROLLTON   | CARROLLTON
CINCINNATI   | CINCINNATI
CINCINNATI   | CINCINNATI
PEORIA       | PEORIA
OAK RIDGE    | OAK RIDGE
CARROLLTON   | CARROLLTON

city_city, city_addr1 and city_addr2 beat feature city because they are detailed.

For records of which addrline1, addrline2 and city don't contain city info, just keep feature city as the city_cleaned.

  • Output

SQL code is in: 

E:/McNair/Projects/PatentAddress/Functions.sql

All the cleaned cities for U.S. patents are stored in ptoassigneend_us_citycleaned. (# 3572605)

Functions to Simplify SQL Code

Extraction
  • Extract Postcode

SQL function: 

CREATE OR REPLACE FUNCTION ExtractPostcode(adr text) RETURNS text AS $$ 
	BEGIN
		RETURN SUBSTRING(adr, '\d{5}[-]\d{4}');
	END;
$$ LANGUAGE plpgsql;
  • Extract State

SQL function: 

CREATE OR REPLACE FUNCTION ExtractState(adr text) RETURNS text AS $$
    SELECT 
CASE WHEN adr ~* '([,]|[.])\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'
THEN LTRIM(RTRIM(replace(regexp_replace(SUBSTRING(adr, '[,]\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'), '\d{5}-\d{4}', ),',',)))
WHEN adr ~* '(^|\s)\w{2}\s{1}\d{5}[-]\d{4}' 
THEN regexp_replace(SUBSTRING(adr, '\w{2}\s{1,}\d{5}[-]\d{4}'), '\d{5}-\d{4}', ) 
WHEN adr ~* 'D[.]C[.]\s\d{5}-\d{4}'
THEN 'D.C.'
ELSE NULL END AS result;
$$ LANGUAGE SQL;
  • Extract City

SQL function: 

CREATE OR REPLACE FUNCTION ExtractCity(adr text) RETURNS text AS $$
   SELECT 
CASE WHEN adr ~* '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}' 
THEN LTRIM(RTRIM(REPLACE(SUBSTRING(SUBSTRING(adr, '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'),'.*[,]'),',',)))
WHEN adr ~* '[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'
THEN LTRIM(RTRIM(REPLACE(SUBSTRING(SUBSTRING(adr, '[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'),'[,].*[,]'),',',)))
WHEN adr ~* '^\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'
THEN REPLACE(SUBSTRING(SUBSTRING(adr, '^\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'), '.*[,]'),',', )
WHEN adr ~* '^\w{1,}\s{0,}\w{0,}\s{0,}\w{2}\s{0,}\d{5}[-]\d{4}'
THEN regexp_replace(SUBSTRING(adr, '^\w{1,}\s{0,1}\w{0,}\s{1,}\w{2}\s{0,}\d{5}[-]\d{4}'), '\w{2}\s\d{5}[-]\d{4}', )
WHEN adr ~* 'BATON[,] ROUGE[,]\s{1}LA'
THEN 'BATON ROUGE'
WHEN adr ~* 'ST[.]\s{1}PAUL'
THEN 'ST. PAUL'
WHEN adr ~* 'ARLINGTON[,]\s{1}VA'
THEN 'ARLINGTON'
ELSE NULL END AS result;
$$ LANGUAGE SQL;

With these functions, we can generate ptoassigneend_us_extract, which is the final output in Section 2.2.1, with the following code: 

CREATE TABLE ptoassigneend_us_ AS
SELECT *, ExtractPostcode(city) postcode_city,
ExtractPostcode(addrline1) postcode_addr1,
ExtractPostcode(addrline2) postcode_addr2,
ExtractState(city) state_city,
ExtractState(addrline1) state_addr1,
ExtractState(addrline2) state_addr2
ExtractCity(city) city_city,
ExtractCity(addrline1) city_addr1,
ExtractCity(addrline2) city_addr2
FROM ptoassigneend_missus;

All the SQL code can be found:

Cleaning
  • Postcode Cleaning

SQL function: 

CREATE OR REPLACE FUNCTION PostcodeClean (text,text,text,text) RETURNS text AS $$
    $pri1=$_[0];
    $pri2=$_[1];
    $pri3=$_[2];
    $postcode=$_[3];
    if ($pri1) {return $pri1;}
    if ($pri2) {return $pri2;}
    if ($pri3) {return $pri3;}
    if ($postcode) {
    	if ($postcode = ~ '\d{5}-\d{4}') {return $postcode}
    	if ($postcode = ~ '\d{5}') {return $postcode}
    }
    return undef;
$$ LANGUAGE plperl;
  • State Cleaning

SQL function: 

CREATE OR REPLACE FUNCTION StateClean (text,text,text,text) RETURNS text AS $$
    $pri1=$_[0];
    $pri2=$_[1];
    $pri3=$_[2];
    $state=$_[3];
    if ($pri1) {return $pri1;}
    if ($pri2) {return $pri2;}
    if ($pri3) {return $pri3;}
    if ($state) {return $state;}
    return undef;
$$ LANGUAGE plperl;
  • City Cleaning

SQL function: 

CREATE OR REPLACE FUNCTION CityClean (text,text,text,text) RETURNS text AS $$
    $pri1=$_[0];
    $pri2=$_[1];
    $pri3=$_[2];
    $city=$_[3];
    if ($pri1) {return $pri1;}
    if ($pri2) {return $pri2;}
    if ($pri3) {return $pri3;}
    if ($city) {return $city;}
    return undef;
$$ LANGUAGE plperl;

Issues

  • Write functions to simplify SQL code.
  • Condense Address Information

The address information extracted from addrline1, addrline2 and city is not consistent. For example, the postcode extracted from addrline1 may be different from that extracted from city.

Examples: 

|  postcode  | postcode_addr1 | postcode_addr2 | postcode_city
| 77042      | 77251-1407     |                |
| 83716-9632 |                | 83707-0006     |
| 90045      | 90080-0028     |                |

(postcode_city is the postcode extracted from 'city'; postcode_addr1 is the postcode extracted from 'addrline1'; postcode_addr2 is the postcode extracted from 'addrline2'.)

  • Inconsistency between 'addrline' and 'country'

Example: 

                                          addrline2                                           |            city             | country
2882 SAND HILL ROAD                                         MENLO PARK, CALIFORNIA 94025-7022 | TOKYO                       | JAPAN
2801 CENTERVILLE ROAD, P.O. BOX 15439                       WILMINGTON, DE 19850-5439         | TOKYO                       | JAPAN
1-6, UCHISAIWAI-CHO 1-CHOME                                                                   | CHIYODA-KU, TOKYO           | JAPAN
MENLO PARK, CA 94025-7022                                                                     | TOKYO                       | JAPAN
                                                                                              | MINATO-KU, TOKYO 10585-8518 | JAPAN
1225 NORTH HIGHWAY 169,                                     MINNEAPOLIS, MINNESOTA 55441-5058 | TOKYO                       | JAPAN
2882 SAND HILL ROAD                                         MENLO PARK, CA 94025-7022         | TOKYO                       | JAPAN
3001 ORCHARD PARKWAY                                        SAN JOSE, CALIFORNIA 95134-2088   | TOKYO 107                   | JAPAN
3001 ORCHARD PARKWAY                                        SAN JOSE, CA  95134-2088          | MINATO-KU, TOKYO 107        | JAPAN
3001 ORCHARD PARKWAY                                        SAN JOSE, CALIFORNIA 95134-2088   | TOKYO 107                   | JAPAN
3001 ORCHARD PARKWAY                                        SAN JOSE, CA 95134-2088           | MINATO-KU, TOKYO 107        | JAPAN


  • The post code and zip regex for other countries besides U.S. can be found here:
http://stackoverflow.com/questions/578406/what-is-the-ultimate-postal-code-and-zip-regex

Example: 

"US", "\d{5}([ \-]\d{4})?"
"CA", "[ABCEGHJKLMNPRSTVXY]\d[ABCEGHJ-NPRSTV-Z][ ]?\d[ABCEGHJ-NPRSTV-Z]\d"
"DE", "\d{5}"
"JP", "\d{3}-\d{4}"
"FR", "\d{2}[ ]?\d{3}"
  • The city feature needs to be standardized. For example, 'GRAND CAYMAN, CAYMAN ISLAND' and 'GRAND CAYMAN' indicate the same city.
  • Both state name and its abbreviation exist.
Retrieved from "http://www.edegan.com/mediawiki/index.php?title=Patent_Assignment_Data_Restructure&oldid=17609"