7,612
edits
Changes
Jump to navigation
Jump to search
[[Category:Internal]]{{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}}Return to [[Internal Classification::Legacy| Patent Data (Wiki Page)]].
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 |
- --------------+---------+-----------
patent_2015 | patentdata --------------+-------------- orgname | assignee city | city country | country patentnumber | patent state | state
Column | Type | Modifiers ---------------+-------------------+----------- 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 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
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.
/* creating patent data tables from : https://bulkdataScript follows.uspto.gov/data2/patent/maintenancefee/*/
CREATE TABLE PatentMaintenanceFee(patentnumber varchar,applicationnumber int,smallentity varchar,filingdate date,grantissuedate date,maintenancefeedate date,maintenancefeecode varchar);'''Script:'''
\COPY PatentMaintenanceFee FROM ' /bulk* creating patent data tables from : https:/USPTO_Consolidated/MaintFeeEvents_20160613bulkdata.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;-- RESULT : COPY 14042059uspto.gov/data2/patent/maintenancefee/*/
/* creating tables for historical patent data - USPTO */ CREATE TABLE PatentMaintenanceFee( patentnumber varchar, applicationnumber int, smallentity varchar, filingdate date, grantissuedate date, maintenancefeedate date, maintenancefeecode varchar );
CREATE TABLE HistoricalPatentData(applicationid int,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 \COPY PatentMaintenanceFee FROM '/bulk/USPTO_Consolidated/MaintFeeEvents_20160613.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;); -- RESULT : COPY 14042059
\COPY historicalpatentdata FROM ' /bulk* creating tables for historical patent data - USPTO */USPTO_Consolidated/HistoricalFiles/historical_masterfile.csv' DELIMITER AS ',' HEADER NULL AS '' CSV;
Patent Data Processing - SQL Steps (view source)
Revision as of 13:34, 21 September 2020
, 13:34, 21 September 2020no edit summary
== Objective ==
The schema for the assignees table in '''patentdata''' database is:
The schema for the assignees table in patent_2015 is :
Column | Type | Modifiers
lastname | text |
firstname | text |
'''Overlapping Columns'''
These columns will have entries for most rows in the table, because they exist in both tables. The rest of the columns will be populated based on which table the row is coming from.
'''Final Schema'''
Table "public.assignees"
'''Non-overlapping Columns'''
Additionally, three columns - nber, 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.
Note : The addition, deletion of columns as through separate scripts [[Patent Data Cleanup - June 2016|scripts]], therefore the scripts below will be slightly discrepant.
==== Index and Key Creation ====
INSERT INTO patents_merged
(
);
-- RESULT : INSERT 0 3984771
== USPTO Consolidated Patent Data ==
CREATE TABLE HistoricalPatentData( applicationid int, 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 ); \COPY historicalpatentdata FROM '/bulk/USPTO_Consolidated/HistoricalFiles/historical_masterfile.csv' DELIMITER AS ',' HEADER NULL AS '' CSV; --COPY 11191813 [[Category:Internal]][[Internal Classification::Legacy| ]][[Category:Patent]]
