Changes

** Assignees {{Project|Has project output=|Has sponsor=McNair Center|Has title=Patent Data:Processing - SQL Steps|Has owner=Shelby Bice,|Has project status=Subsume|Has keywords=Tool}}CREATE TABLE assignees_temp2 Return to [[Patent Data (Wiki Page)]].

lastname varchar,firstname varchar,address varchar,postcode varchar,orgname varchar, --assignee varchar,city varchar, --city2 varchar,country varchar, --country2 varchar,== Objective ==patentnumber int, --The McNair Center owns two sets of patent integer,state varchar, data --state2 varcharone set that is inherited from Harvard,patentcountry varcharthe Harvard dataverse,nationality2 varcharwhich is stored in the database patentdata and another that is generated by crawlers pulling data from the USPTO website,residence varchar,asgseq int,asgtype integerwhich is stored in the database '''patent_2015'''.

PatentData: Column | Type | Modifiers -------------+-------------------+----------- patent | integer | asgtype | integer | assignee | character varying | city | character varying | state | character varying | country | character varying | nationality | character varying | residence | character varying | asgseq | integer |

INSERT INTO assignees_merge(SELECT'null','null','null','null',a.assignee,a.city,a.country,a.patent,a.state,'null',a.nationality,a.residence,a.asgseq,a.asgtypeFROM assignees a); /// Reference:CREATE TABLE assignees_temp2 ( lastname varchar,firstname varchar,address varchar,postcode varchar,orgname varchar, --assignee varchar,city varchar, --city2 varchar,country varchar, --country2 varchar,patentnumber int, --patent integer,state varchar, --state2 varchar,patentcountry varchar,nationality2 varchar,residence varchar,asgseq int,asgtype integer );//////   Patent_2015: Column | Type | Modifiers- --------------+---------+-----------

INSERT INTO assignees_merge patent_2015 | patentdata( --------------+--------------SELECTassignees.lastname,assignees.firstname,assignees.address,assignees.postcode,assignees. orgname, | assignee assignees. city | city,assignees. country | country,assignees. patentnumber,| patent assignees. state | state,assignees.patentcountry,'null','null',-1,-1FROM assignees);

\COPY assignees_merge TO '/tmp/assignees_merge_export.txt' DELIMITER AS E'\tFinal Schema' HEADER NULL AS '' CSV;\COPY assignees FROM '/tmp/assignees_merge_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;--1607724

\COPY assignees_merge TO '/tmp/assignees_merge_export1 Table "public.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;assignees"\COPY assignees FROM '/tmp/assignees_merge_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; Column | Type | Modifiers ---------------+-------------------+-----------3818842 lastname | character varying | firstname | character varying | address | character varying | postcode | character varying | orgname | character varying | city | character varying | country | character varying | patentnumber | integer | state | character varying | patentcountry | character varying | nationality2 | character varying | residence | character varying | asgseq | integer | asgtype | integer |

SELECT count(*), *from assignees ==== Index ====GROUP BY lastname, firstname, address, postcode, orgname, citySince the table is relatively large, countryand is likely to be searched often, patentnumber, state, patentcountry, nationality2, residence, asgseq, asgtypeHAVING COUNT(*) > 1;an index has been imposed on the table.

allpatent=# CREATE TABLE Assignees2 ASSELECT *from INDEX ON assignees GROUP BY lastname, firstname, address, postcode, (orgname, city, country, patentnumber, state, patentcountry, nationality2, residence, asgseq, asgtypeHAVING COUNT(*) = 1;; CREATE INDEX

DROP TABLE Assignees;====Sample insert and copy commands ====ALTER TABLE Assignees2 RENAME TO Assignees INSERT INTO assignees_merge ( SELECT 'null', 'null', 'null', 'null', a.assignee, a.city, a.country, a.patent, a.state, 'null', a.nationality, a.residence, a.asgseq, a.asgtype FROM assignees a );

INSERT INTO assignees_merge ( SELECT assignees.lastname, assignees.firstname, assignees.address, assignees.postcode, assignees.orgname, assignees.city, assignees.country, assignees.patentnumber, assignees.state, assignees.patentcountry, 'null', 'null', -1, - 1 FROM assignees );   \COPY assignees_merge TO '/tmp/assignees_merge_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; \COPY assignees FROM '/tmp/assignees_merge_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --1607724

Patentdata: Column | Type | Modifiers- --------+-------------------+-----------

Column Names:''' Overlapping Columns ''' patent_data patentnumber int,patent_2015 kind varchar, grantdate date, type varchar, applicationnumber varchar, filingdate date, prioritydate date, prioritycountry varchar, prioritypatentnumber varchar, ussubclass varchar, maingroup varchar, subgroup varchar, cpcsubclass varchar, cpcmaingroup varchar, cpcsubgroup varchar, classificationnationalcountry varchar, classificationnationalclass varchar, title varchar, numberofclaims int, primaryexaminerfirstname varchar, primaryexaminerlastname varchar, primaryexaminerdepartment varchar, pctpatentnumber varchar, filename varchar Combined Schema: Column Names: patent int, kind varchar, claims int, apptype int, appnum int, gdate date, gyear int, appdate date, appyear int   patentnumber int, --------------+--------- patent kind varchar, -- kind grantdate date, --gdate type varchar,patent | patentnumber applicationnumber varchar, filingdate date, prioritydate date, prioritycountry varchar, prioritypatentnumber varchar, ussubclass varchar, maingroup varchar, subgroup varchar, cpcsubclass varchar, cpcmaingroup varchar, cpcsubgroup varchar, classificationnationalcountry varchar, classificationnationalclass varchar, title varchar,kind | kind claims | numberofclaims int, primaryexaminerfirstname varchar,apptype primaryexaminerlastname varchar, primaryexaminerdepartment varchar, pctpatentnumber varchar, filename varchar claims int, apptype int,| type appnum int, | applicationnumber gyear int, appdate date, appyear int Output Schema: patents gdate CREATE TABLE patents_merged( patentnumber int, kind varchar, | grantdate date, type varchar, applicationnumber varchar, filingdate date, prioritydate date, prioritycountry varchar, prioritypatentnumber varchar, ussubclass varchar, maingroup varchar, subgroup varchar, cpcsubclass varchar, cpcmaingroup varchar, cpcsubgroup varchar, classificationnationalcountry varchar, classificationnationalclass varchar, title varchar, numberofclaims int, primaryexaminerfirstname varchar, primaryexaminerlastname varchar, primaryexaminerdepartment varchar, pctpatentnumber varchar, filename varchar, claims int, apptype int, appnum int, gyear int, appdate date, appyear int ); patentdata: INSERT INTO patents_merged ( SELECT patent, kind, gdate, 'NULL', 'NULL', NULL, NULL, 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', -1, 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', claims, apptype, appnum, gyear, appdate, appyear FROM patents ); -- RESULT : INSERT 0 3984771 patent_2015: INSERT INTO patents_merged ( SELECT patentnumber, kind, grantdate, type, applicationnumber, | filingdate, prioritydate, prioritycountry, prioritypatentnumber, ussubclass, maingroup, subgroup, cpcsubclass, cpcmaingroup, cpcsubgroup, classificationnationalcountry, classificationnationalclass, title, numberofclaims, primaryexaminerfirstname, primaryexaminerlastname, primaryexaminerdepartment, pctpatentnumber, filename, -1, -1, -1, -1, NULL, -1 FROM patents ); -- RESULT : INSERT 0 1646225

patent_2015:\COPY patents_merged TO '/tmp/merged_patents_export.txt' DELIMITER AS E'\tCombined Schema:' HEADER NULL AS '' CSV;--COPY 1646225

PATENTS TABLE\COPY The final schema of the patents FROM '/tmp/merged_patents_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;-- RESULT table is : COPY 3984771\COPY patents FROM '/tmp/merged_patents_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;-- RESULT : COPY 1646225

TESTING:select count(*) FROM (SELECT DISTINCT patentnumber FROM patents) AS t;Additionally, three columns --RESULT: 5411151EXPECTED: 5426566nber, uspc, uspc_sub have been added from the historicalpatentdata, a table built from data downloaded from the USPTO Bulk Data Storage. The join was executed on the patent number.

SELECT COUNT(*) Note : The addition, *FROM patentsGROUP BY patentnumber, kind, grantdate, type, applicationnumber, filingdate, prioritydate, prioritycountry, prioritypatentnumber, ussubclassdeletion of columns as through separate [[Patent Data Cleanup - June 2016 |scripts]],therefore the scripts below will be slightly discrepant. maingroup, subgroup,==== Index and Key Creation ==== cpcsubclassPatent numbers are distinct in this table, cpcmaingroupand are central to the rest of the fields in the table. A primary key can therefore be imposed on the column. Also, cpcsubgroupsince a number of searches are likely to be conducted on this table, classificationnationalcountry, classificationnationalclass, title, numberofclaims, primaryexaminerfirstname, primaryexaminerlastname, primaryexaminerdepartment, pctpatentnumber, filename, claims, apptype, appnum, gyear, appdate, appyearHAVING COUNT(*) > 1;an index has been imposed as well.

SELECT patentnumber, count(*)FROM patentsGROUP BY patentnumberHAVING count(*)>1;--7640598   SELECT * FROM patents opWHERE op.patentnumber IN ( SELECT ip.patentnumber FROM patents ip GROUP BY ip.patentnumber HAVING COUNT(*)>1 )ORDER BY op.patentnumber; (SELECT * INTO patentsCleanedFROM patents opWHERE op.patentnumber IN ( SELECT ip.patentnumber FROM patents ip GROUP BY ip.patentnumber HAVING COUNT(*)=1 )ORDER BY op.patentnumber)--SELECT 5191306  INSERT INTO patentsCleaned(SELECT * FROM patents opWHERE op.patentnumber IN ( SELECT ip.patentnumber FROM patents ip GROUP BY ip.patentnumber HAVING COUNT(*)>1 )AND op.applicationnumber NOT LIKE 'NULL'ORDER BY op.patentnumber); --219845 TESTINGCode:allpatent=# select count(*) from patentsCleaned; count--------- 5411151(1 row) allpatent=# select count(*), patentnumber FROM patentsCleaned group by patentnumber having count(*) > 1; count | patentnumber-------+--------------(0 rows)  ********** INDEX CREATION ************** ALTER TABLE patents ADD PRIMARY KEY (patentnumber); -- RESULT : ALTER TABLE allpatent=# CREATE UNIQUE INDEX patent_idx ON patents (patentnumber); allpatent=# CREATE INDEX ON assignees (orgname);CREATE INDEX

   **Patents Patentdata:Column | Type | Modifiers---------+-------------------+----------- patent | integer | kind | character varying | claims | integer | apptype | integer | appnum | integer | gdate | date | gyear | integer | appdate | date | appyear | integer | Column Names: patent int, kind varchar, claims int, apptype int, appnum int, gdate date, gyear int, appdate date, appyear int    Patent_2015: Column | Type | Modifiers -------------------------------+---------+----------- patentnumber | int | not null kind | varchar | grantdate | date | type | varchar | applicationnumber | varchar | filingdate | date | prioritydate | date | prioritycountry | varchar | prioritypatentnumber | varchar | ussubclass | varchar | maingroup | varchar | subgroup | varchar | cpcsubclass | varchar | cpcmaingroup | varchar | cpcsubgroup | varchar | classificationnationalcountry | varchar | classificationnationalclass | varchar | title | varchar | numberofclaims | int | primaryexaminerfirstname | varchar | primaryexaminerlastname | varchar | primaryexaminerdepartment | varchar | pctpatentnumber | varchar | filename | varchar | Column Names: patentnumber int, kind varchar, grantdate date, type varchar, applicationnumber varchar, filingdate date, prioritydate date, prioritycountry varchar, prioritypatentnumber varchar, ussubclass varchar, maingroup varchar, subgroup varchar, cpcsubclass varchar, cpcmaingroup varchar, cpcsubgroup varchar, classificationnationalcountry varchar, classificationnationalclass varchar, title varchar, numberofclaims int, primaryexaminerfirstname varchar, primaryexaminerlastname varchar, primaryexaminerdepartment varchar, pctpatentnumber varchar, filename varchar Combined Schema: Column Names: patent int, kind varchar, claims int, apptype int, appnum int, gdate date, gyear int, appdate date, appyear int   patentnumber int, -- patent kind varchar, -- kind grantdate date, --gdate type varchar, applicationnumber varchar, filingdate date, prioritydate date, prioritycountry varchar, prioritypatentnumber varchar, ussubclass varchar, maingroup varchar, subgroup varchar, cpcsubclass varchar, cpcmaingroup varchar, cpcsubgroup varchar, classificationnationalcountry varchar, classificationnationalclass varchar, title varchar, numberofclaims int, primaryexaminerfirstname varchar, primaryexaminerlastname varchar, primaryexaminerdepartment varchar, pctpatentnumber varchar, filename varchar claims int, apptype int, appnum int, gyear int, appdate date, appyear int Output Schema: patents CREATE TABLE patents_merged( patentnumber int, kind varchar, grantdate date, type varchar, applicationnumber varchar, filingdate date, prioritydate date, prioritycountry varchar, prioritypatentnumber varchar, ussubclass varchar, maingroup varchar, subgroup varchar, cpcsubclass varchar, cpcmaingroup varchar, cpcsubgroup varchar, classificationnationalcountry varchar, classificationnationalclass varchar, title varchar, numberofclaims int, primaryexaminerfirstname varchar, primaryexaminerlastname varchar, primaryexaminerdepartment varchar, pctpatentnumber varchar, filename varchar, claims int, apptype int, appnum int, gyear int, appdate date, appyear int ); ====Sample Insert and Copy Statements====

SELECT patent, kind, gdate, 'NULL', 'NULL', NULL, NULL, 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', -1, 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', claims, apptype, appnum, gyear, appdate, appyear FROM patents ); -- RESULT : INSERT 0 3984771

\COPY patents_merged TO '/tmp/merged_patents_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --COPY 3984771

\COPY patents_merged TO '/tmp/merged_patents_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --COPY 1646225

\COPY patents FROM '/tmp/merged_patents_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; -- RESULT : COPY 3984771 \COPY patents FROM '/tmp/merged_patents_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; -- RESULT : COPY 1646225

SELECT COUNT(*), * FROM patents GROUP BY patentnumber,

HAVING COUNT(*) > 1;

SELECT patentnumber, count(*) FROM patents GROUP BY patentnumber HAVING count(*)>1; --7640598

SELECT * FROM patents op WHERE op.patentnumber IN

ORDER BY op.patentnumber;

( SELECT * INTO patentsCleaned FROM patents op WHERE op.patentnumber IN

ORDER BY op.patentnumber ) --SELECT 5191306

INSERT INTO patentsCleaned( SELECT * FROM patents op WHERE op.patentnumber IN

AND op.applicationnumber NOT LIKE 'NULL' ORDER BY op.patentnumber); --219845 TESTING:allpatent=# select count(*) from patentsCleaned; count--------- 5411151(1 row) allpatent=# select count(*), patentnumber FROM patentsCleaned group by patentnumber having count(*) > 1; count | patentnumber-------+--------------(0 rows)  ********** INDEX CREATION ************** ALTER TABLE patents ADD PRIMARY KEY (patentnumber);-- RESULT : ALTER TABLEallpatent=# CREATE UNIQUE INDEX patent_idx ON patents (patentnumber); allpatent=# CREATE INDEX ON assignees (orgname);CREATE INDEX

CREATE OR REPLACE FUNCTION cleanpatno (text) RETURNS bigint AS $$

'''patentdata schema:'''  Column | Type | Modifiers ------------+-------------------+----------- patent | integer | cit_date | date | cit_name | character varying | cit_kind | character varying | cit_country | character varying | citation | integer | category | character varying | citseq | integer |   SELECT patent as citingpatentnumber, citation AS citedpatentnumber INTO citations_merged FROM citations; --SELECT 38452957 '''patent_2015 schema:''' Column | Type | Modifiers ---------------------+---------+----------- citingpatentnumber | integer | citingpatentcountry | text | citedpatentnumber | text | citedpatentcountry | text |

patent_2015: Column | Type | Modifiers---------------------+---------+----------- SELECT CAST(citingpatentnumber | integer |AS bigint), CAST(cleanpatno( citedpatentnumber) AS bigint) as citedpatentnumber citingpatentcountry | text | INTO citations_merged citedpatentnumber | text |FROM citations; citedpatentcountry | text | -- RESULT : SELECT 59227881

FINAL TABLE patent_2015 | patentdata | ---------------------+---------------+ citingpatentnumber | patent | citedpatentnumber | citation | ''' Combined Schema:'''

CREATE TABLE citations Column | Type | Modifiers(--------------------+--------+----------- citingpatentnumber | bigint,| citedpatentnumber | bigint);|

\COPY citations_merged TO '/tmp/merged_citations_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --COPY 38452957

\COPY citations_merged TO '/tmp/merged_citations_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; -- RESULT : COPY 59227881

\COPY citations FROM '/tmp/merged_citations_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --RESULT : COPY 59227881

\COPY citations FROM '/tmp/merged_citations_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --RESULT: COPY 38452957

ScriptsThe USPTO has a repository of patent data on their Bulk Data Storage system. We have this data downloaded and loaded into a table on the patent database. Here are the steps followed:* Download file from the BDS system - we have access to CSV files.* Create table with required specs* use the \COPY command to copy the data from the file into the table.  Script follows. '''Script:'''  /* creating patent data tables from : https://bulkdata.uspto.gov/data2/patent/maintenancefee/*/

CREATE TABLE \COPY PatentMaintenanceFee(FROM '/bulk/USPTO_Consolidated/MaintFeeEvents_20160613.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;patentnumber varchar,applicationnumber int,smallentity varchar,filingdate date,grantissuedate date,maintenancefeedate date,maintenancefeecode varchar); -- RESULT : COPY 14042059

\COPY PatentMaintenanceFee FROM ' /bulk/USPTO_Consolidated* creating tables for historical patent data - USPTO */MaintFeeEvents_20160613.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;-- RESULT : COPY 14042059

CREATE TABLE HistoricalPatentData(applicationid int \COPY historicalpatentdata FROM '/bulk/USPTO_Consolidated/HistoricalFiles/historical_masterfile.csv' DELIMITER AS ',pubno varchar,patentnumber varchar,NBER int,USPC varchar,USPC_sub varchar,applicationdate date,prioritydate date,pubdate date,displaydate date,disptype varchar,exp_dt date,exp_dt_max date,pta int)' HEADER NULL AS '' CSV;

\ --COPY historicalpatentdata FROM '/bulk/USPTO_Consolidated/HistoricalFiles/historical_masterfile.csv' DELIMITER AS ',' HEADER NULL AS '' CSV;11191813