Difference between revisions of "Reproducible Patent Data"

From edegan.com
Jump to navigation Jump to search
 
(48 intermediate revisions by 3 users not shown)
Line 1: Line 1:
{{McNair Projects
+
{{Project
 +
|Has project output=Data,Tool,Content,How-to,Guide
 +
|Has sponsor=McNair Center
 
|Has Image=Uspto web logo.jpg
 
|Has Image=Uspto web logo.jpg
 
|Has title=Reproducible Patent Data
 
|Has title=Reproducible Patent Data
Line 8: Line 10:
 
}}
 
}}
  
A continuation of [[Redesigning Patent Database]] that aims to write faster, more centralized code to deal with data from the United States Patent and Trademark Office (USPTO). By having an end-to-end pipeline we can easily reproduce or update data without worrying about unintentional side effects or missing data.
+
<onlyinclude>The [[Reproducible Patent Data]] project is a continuation of the [[Redesigning Patent Database]] project. It aims to write faster, more centralized code to deal with data from the United States Patent and Trademark Office (USPTO). By having an end-to-end pipeline we can easily reproduce or update data without worrying about unintentional side effects or missing data. See also the [[Patent Data]] umbrella project. </onlyinclude>
 +
 
 +
== Quickstart ==
 +
 
 +
To get up and running with the code, do the following:
 +
 
 +
# Clone the git project (link at end of page) to your user directory
 +
# Launch IntelliJ with >= Java 8 and Maven configured (default version installed on the RDP is setup to do this)
 +
# Open project in IntelliJ
 +
# Create an empty database (see [[#Database]])
 +
# Run the table creation scripts in <code>src/db/schemas/</code> in your new database
 +
# Modify the constant <code>DATABASE_NAME</code> in <code>E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\postgres\DatabaseHelper.java</code>
 +
# Run the Driver scripts in IntelliJ with the correct value for <code>DATA_DIRECTORY</code> (or run <code>RunInitialImport.java</code> which will do all of the data directories for that patent item type)
 +
# [Take a really, really long lunch...in total should take no more than five hours to load data on RDP]
 +
# Run scripts in <code>src/db/constraints</code> to check data assumptions
 +
# That's it!
 +
 
 +
===Troubleshooting===
 +
 
 +
If you're new to IntelliJ (and even if you're not) you might run into problems with importing the project.
 +
 
 +
'''Setting Up Project as a Maven project'''
 +
It should be clear if the project is not set up as a Maven project - when you right click on RunInitialImport.java, for example, you won't see an option with
 +
a green triangle next to it that says "Run 'RunInitialImport.java'", and the green triangle in the top toolbar will be grayed out. If the project is not set up as a Maven project, you will not be able to run any of the code. To set up the project as a Maven project, when you import the project, follow the instructions at the following [https://www.jetbrains.com/help/idea/maven.html#maven_import_project_start link].
 +
 
 +
* Note that when you click "Import Project", you should select the "Simpler Patent Data" folder, not the "src" folder within Simpler Patent Data, otherwise you won't get the pom.xml file that you need to let IntelliJ know that this is a Maven project.
 +
* On the second window (there will be several options with check boxes next to them) make sure "import Maven projects automatically" is selected
 +
* On the "Please select project SDK" window, make sure it says "1.8" in the "Name" slot.
 +
* On the next window, enter a name for the project and enter a folder location.
 +
 
 +
This should ensure that the project is set up as a Maven project.
 +
 
 +
'''Setting Up Your Data Source'''
 +
 
 +
If you run into a message across the top that says something along the lines of "Configure Data Source", then you have not connected IntelliJ to a database. You will not be able to run the code located under src/db until you configure one. Start by clicking on the link in the message, or if it doesn't appear, follow the instructions  [https://www.jetbrains.com/help/idea/connecting-to-a-database.html here] to open up the "Data Sources and Drivers" pop-up to add a PostgreSQL database. When you get to the dialogue asking about the host, database, user, and password, do the following to connect to the database on the RDP:
 +
 
 +
host: localhost
 +
database: whatever the constant DATABASE_NAME is set to - the default is patentsj
 +
user: postgres
 +
password: tabspaceenter
 +
 
 +
Make sure to test the connection by clicking "Test Connection". Now you should be able to run the scripts under src/db.
 +
 
 +
If you're seeing issues such as "column [something] of relation [something] doesn't exist" but you've run the schema scripts, you probably have different database name under the data source than the one the constant DATABASE_NAME is set to. To change this, right click on the data source in the Database tab and select "Properties".
  
 
== Directory Layout ==
 
== Directory Layout ==
Line 49: Line 94:
 
The development environment is Java 8 JDK, IntelliJ Ultimate IDE, Maven build tools, and git VCS.
 
The development environment is Java 8 JDK, IntelliJ Ultimate IDE, Maven build tools, and git VCS.
  
The git repository can be found at https://rdp.mcnaircenter.org/codebase/Repository/ReproduciblePatent
+
The git repository can be found at http://rdp.mcnaircenter.org/codebase/Repository/ReproduciblePatent
  
 
==== Prior Art ====
 
==== Prior Art ====
Line 77: Line 122:
 
The code can also be run via the standard <code>javac</code> and <code>java</code> commands but since this project has a complicated structure you end up having to run commands like  
 
The code can also be run via the standard <code>javac</code> and <code>java</code> commands but since this project has a complicated structure you end up having to run commands like  
  
<code>"C:\Program Files\Java\jdk1.8.0_131\bin\java" "-javaagent:C:\Users\OliverC\IntelliJ IDEA 2017.1.3\lib\idea_rt.jar=62364:C:\Users\OliverC\IntelliJ IDEA 2017.1.3\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_131\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\rt.jar;E:\McNair\Projects\SimplerPatentData\target\classes;C:\Users\OliverC\.m2\repository\com\mashape\unirest\unirest-java\1.4.9\unirest-java-1.4.9.jar;C:\Users\OliverC\.m2\repository\org\apache\httpcomponents\httpclient\4.5.2\httpclient-4.5.2.jar;C:\Users\OliverC\.m2\repository\org\apache\httpcomponents\httpcore\4.4.4\httpcore-4.4.4.jar;C:\Users\OliverC\.m2\repository\commons-logging\commons-logging\1.2\commons-logging-1.2.jar;C:\Users\OliverC\.m2\repository\org\apache\httpcomponents\httpasyncclient\4.1.1\httpasyncclient-4.1.1.jar;C:\Users\OliverC\.m2\repository\org\apache\httpcomponents\httpcore-nio\4.4.4\httpcore-nio-4.4.4.jar;C:\Users\OliverC\.m2\repository\org\apache\httpcomponents\httpmime\4.5.2\httpmime-4.5.2.jar;C:\Users\OliverC\.m2\repository\org\json\json\20160212\json-20160212.jar;C:\Users\OliverC\.m2\repository\com\google\guava\guava\21.0\guava-21.0.jar;C:\Users\OliverC\.m2\repository\org\jsoup\jsoup\1.10.2\jsoup-1.10.2.jar;C:\Users\OliverC\.m2\repository\commons-codec\commons-codec\1.10\commons-codec-1.10.jar;C:\Users\OliverC\.m2\repository\org\jetbrains\annotations\15.0\annotations-15.0.jar;C:\Users\OliverC\.m2\repository\org\apache\commons\commons-lang3\3.5\commons-lang3-3.5.jar;C:\Users\OliverC\.m2\repository\org\postgresql\postgresql\42.1.1\postgresql-42.1.1.jar" org.bakerinstitute.mcnair.uspto_assignments.XmlDriver</code>
+
<code>"C:\Program Files\Java\jdk1.8.0_131\bin\java" "-javaagent:C:\Users\OliverC\IntelliJ IDEA 2017.1.3\lib\idea_rt.jar=62364:C:\Users\OliverC\IntelliJ IDEA 2017.1.3\bin" -Dfile.encoding=UTF-8 -classpath "[...contents truncated...];C:\Users\OliverC\.m2\repository\org\postgresql\postgresql\42.1.1\postgresql-42.1.1.jar" org.bakerinstitute.mcnair.uspto_assignments.XmlDriver</code>
  
 
to include all of the runtime dependencies and it's just not worth it.
 
to include all of the runtime dependencies and it's just not worth it.
Line 90: Line 135:
  
 
== Schema Reconciliation ==
 
== Schema Reconciliation ==
 +
 +
For the work by Joe, see the [[Patent Schema Reconciliation]] page
  
 
=== Patents (Granted) ===
 
=== Patents (Granted) ===
 +
 +
See <code>E:\McNair\Projects\SimplerPatentData\data\examples\granted</code> for extracted examples of what specific data is available for a sample of the data.
  
 
{| class="wikitable"
 
{| class="wikitable"
 
|+Granted Patent Data Formats
 
|+Granted Patent Data Formats
 
|-
 
|-
! scope="col" | Dates Used !! scope="col" | Format !! scope="col" | Location !! scope="col" | Supported by Parser? !! scope="col" | Utility !! scope="col" | Reissue !! scope="col" | Design !! scope="col" | Plant
+
! scope="col" | Dates Used !! scope="col" | Format !! scope="col" | Location !! scope="col" | Supported? !! scope="col" | Utility !! scope="col" | Reissue !! scope="col" | Design !! scope="col" | Plant
 
|-
 
|-
 
|January 1976 to December 2001
 
|January 1976 to December 2001
Line 105: Line 154:
 
|~
 
|~
 
|~
 
|~
|
+
|~
 
|-
 
|-
 
|<del>January 2001 to December 2001</del>
 
|<del>January 2001 to December 2001</del>
 
|<del>SGML</del>
 
|<del>SGML</del>
 
|Ignored; use concurrently recorded APS data
 
|Ignored; use concurrently recorded APS data
 +
|No
 
|N/A
 
|N/A
 
|N/A
 
|N/A
Line 122: Line 172:
 
|~
 
|~
 
|~
 
|~
|
+
|~
 
|-
 
|-
 
|January 2005 to December 2005
 
|January 2005 to December 2005
Line 131: Line 181:
 
|~
 
|~
 
|~
 
|~
|
+
|~
 
|-
 
|-
 
|January 2006 to December 2006
 
|January 2006 to December 2006
Line 140: Line 190:
 
|~
 
|~
 
|~
 
|~
|
+
|~
 
|-
 
|-
 
|January 2007 to December 2012
 
|January 2007 to December 2012
Line 149: Line 199:
 
|~
 
|~
 
|~
 
|~
|
+
|~
 
|-
 
|-
 
|January 2013 to September 24, 2013
 
|January 2013 to September 24, 2013
Line 158: Line 208:
 
|~
 
|~
 
|~
 
|~
|
+
|~
 
|-
 
|-
 
|October 8, 2013 to December 2014
 
|October 8, 2013 to December 2014
Line 167: Line 217:
 
|~
 
|~
 
|~
 
|~
|
+
|~
 
|-
 
|-
 
|January 2015 to December 2016
 
|January 2015 to December 2016
Line 176: Line 226:
 
|~
 
|~
 
|~
 
|~
|
+
|~
 
|}
 
|}
  
Line 184: Line 234:
  
 
It's worth mentioning that the APS contains an advanced text markup system for chemical formulae, basic text markup, tables, etc. that can lead to seemingly garbled text that is perfectly well-formed.
 
It's worth mentioning that the APS contains an advanced text markup system for chemical formulae, basic text markup, tables, etc. that can lead to seemingly garbled text that is perfectly well-formed.
 +
 +
==== APS Gotchas ====
 +
 +
* PATN.WKU is the granted patent number. It is 7 digits while the spec promises 6 digits. The rightmost digit is a check digit modulus 11. See [[File:Aps-wku-modulus11.pdf]] for the words from the horse's mouth.
  
 
=== Patents (Applications) ===
 
=== Patents (Applications) ===
Line 192: Line 246:
 
! scope="col" | Dates Used !! scope="col" | Format !! scope="col" | Location !! scope="col" | Supported by Parser?
 
! scope="col" | Dates Used !! scope="col" | Format !! scope="col" | Location !! scope="col" | Supported by Parser?
 
|-
 
|-
|January 2001 to December 2001
+
|March 15, 2001 to December 2001
 
|XML Version 1.5
 
|XML Version 1.5
 
|<code>data/extracts/applications/vintage</code>
 
|<code>data/extracts/applications/vintage</code>
|style="background: red; color: white;" | No
+
|style="background: yellow;" | Yes, for basic information, inventors, and correspondents
 
|-
 
|-
 
|January 2002 to December 2004
 
|January 2002 to December 2004
 
|XML Version 1.6
 
|XML Version 1.6
 
|<code>data/extracts/applications/vintage</code>
 
|<code>data/extracts/applications/vintage</code>
|style="background: red; color: white;" | No
+
|style="background: yellow;" | Ditto
 
|-
 
|-
 
|January 2005 to December 2005
 
|January 2005 to December 2005
 
|XML Version 4.0 ICE
 
|XML Version 4.0 ICE
 
|<code>data/extracts/applications/modern</code>
 
|<code>data/extracts/applications/modern</code>
|style="background: red; color: white;" | Maybe
+
|style="background: yellow;" | Ditto
 
|-
 
|-
 
|January 2006 to December 2006
 
|January 2006 to December 2006
 
|XML Version 4.1 ICE
 
|XML Version 4.1 ICE
 
|<code>data/extracts/applications/modern</code>
 
|<code>data/extracts/applications/modern</code>
|style="background: red; color: white;" | Maybe
+
|style="background: yellow;" | Ditto
 
|-
 
|-
 
|January 2007 to December 2012
 
|January 2007 to December 2012
 
|XML Version 4.2 ICE
 
|XML Version 4.2 ICE
 
|<code>data/extracts/applications/modern</code>
 
|<code>data/extracts/applications/modern</code>
|style="background: red; color: white;" | Maybe
+
|style="background: yellow;" | Ditto
 
|-
 
|-
|January 2013 to September 24, 2013
+
|January 2013 to December 2014
 
|XML Version 4.3 ICE
 
|XML Version 4.3 ICE
 
|<code>data/extracts/applications/modern</code>
 
|<code>data/extracts/applications/modern</code>
|style="background: green; color: white;" | Maybe
+
|style="background: yellow;" | Ditto
 
|-
 
|-
|October 8, 2013 to December 2014
+
|January 2015 to ''Present''
 
|XML Version 4.4 ICE
 
|XML Version 4.4 ICE
 
|<code>data/extracts/applications/modern</code>
 
|<code>data/extracts/applications/modern</code>
|style="background: green; color: white;" | Maybe
+
|style="background: yellow;" | Ditto
|-
 
|January 2015 to December 2016
 
|XML Version 4.5 ICE
 
|<code>data/extracts/applications/modern</code>
 
|style="background: green; color: white;" | Maybe
 
 
|}
 
|}
  
Line 239: Line 288:
 
This project uses the stock [https://jdbc.postgresql.org/ Postgres JDBC], version 42.1.1
 
This project uses the stock [https://jdbc.postgresql.org/ Postgres JDBC], version 42.1.1
  
# Create an empty database: <code>$ createdb --username=postgres patents_june_2017 # password is tabspaceenter</code>
+
=== Create an empty database on RDP ===
# Create tables via script at <code>E:\McNair\Projects\SimplerPatentData\src\db\NaiveSchema.sql</code>
 
#* Prior Example <code>E:\McNair\Software\Scripts\Patent\createTables.sql</code>
 
#* Aim to create a completely naive schema with as few constraints as possible--iteratively add more constraints in the future
 
  
Since writing raw SQL is a bit cumbersome and error-prone, I have added some abstraction layers that make it much easier to quickly add bulk data. By using Postgres's <code>CopyManager</code> class, we buffer SQL copy commands in memory (as many as possible) and then flush these rows. To understand how the abstraction layers work, see the code in <code>E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\postgres</code>. For a concrete example, see <code>E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\uspto_assignments\GeonamesZips.java</code> for a '''simple, self-contained example''' or <code>E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\models\GrantedPatent.java</code> for '''an example of how to extend''' the abstraction layer to deal with more complex scenarios.
+
To create an empty database, run this command: <code>$ createdb --username=postgres database-name-goes-here # password is tabspaceenter</code>
 +
 
 +
=== Abstraction Layer ===
 +
 
 +
Since writing raw SQL is a bit cumbersome and error-prone, I have added some abstraction layers that make it much easier to quickly add bulk data. By using Postgres's <code>CopyManager</code> class, we buffer SQL copy commands in memory (as many as possible) and then flush these rows. To understand how the abstraction layers work, see the code in <code>E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\postgres</code>. See <code>E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\models\GrantedPatent.java</code> for '''an example of how to extend''' the abstraction layer to deal with more complex scenarios.
 +
 
 +
=== New Table Checklist ===
 +
 
 +
* Create schema DDL SQL code for the new table in <code>E:\McNair\Projects\SimplerPatentData\src\db</code>
 +
* Run the schema creation
 +
* Create an enum with the same names for attributes as in the DDL (case-insensitive! prefer all-caps screaming snake case)
 +
* Create a class which subclasses <code>AbstractInsertableData</code>
 +
* Inside that class, create a static class which subclasses <code>AbstractTableMetadata</code> and has the proper values for getTableName(), getStringColumns(), getIntColumns()
 +
* (Optional) Implement builder pattern
 +
* (Optional) Create a custom databasehelper for complex extras (see PatentApplication and GrantedPatent for examples)
 +
* Write the data to the table (see DatabaseHelper for the pattern I use)
  
 
== Address Data ==
 
== Address Data ==
  
* Question: "In which zipcodes are the most patents granted?"
+
To get the most granular address data (street level, or at least postcode level) about who owns patents, the path is not so straightforward because off the complicated mapping of ownership to a granted patent.
** Hacky answer: <code>\COPY (select postcode, count(documentid) as c from june_2017_zipcode_join group by postcode order by c desc) TO '/bulk/zipcodes-oliver/postcodes_ranked.tsv' (format csv, delimiter E'\t')</code>
+
This is the final part of this project that I am working on and it is all at the level of SQL.
** <code>Z:/zipcodes-oliver/postcodes_ranked.tsv</code>
+
See <code>E:\McNair\Projects\SimplerPatentData\src\db\joins</code> for my attempts to create a clean mapping.
* Zipcode granularity--if possible, finer detail wanted
+
Optimistically speaking, the data generated here should be superset of the data present in the Patent Assignment Data Restructure project.
* Filter non-US, null addresses
+
 
* Need to cleanup addresses
+
Note that as of the beginning of August 2017, this part '''has not been completed.'''
* Extract zipcode or reverse locate to find zip
+
 
 +
 
 +
=== Intuition ===
  
*  "The 4-5 digit reel number refers to the microfilm reel number of the assignment entry in physical USPTO records; similarly the 1-4 digit frame number refers to the location of the assignment entry on the reel number in physical USPTO records. Thus, each assignment recorded with the USPTO has a unique reel number and frame number combination. While both reel number and frame number are sequential, there are missing values in the sequence because each only specifies the first page of the assignment records and records may have multiple pages." from https://www.uspto.gov/sites/default/files/documents/USPTO_Patents_Assignment_Dataset_WP.pdf, pg12, footnote 38
+
Use <code>assignments_longform.last_update_date</code> to find current/latest (or first/earliest) date of assignment. Then match with <code>properties.docid</code> on <code>reelno, frameno</code> to find patent application id. With this mapping to granted patents, we can discover the details of the original granted patent. And with the right date and reelno and frameno, we can match to the <code>assignees</code> table and get fine granularity addresses.
  
 
== Related Pages ==
 
== Related Pages ==
  
 +
* [[Redesign_Assignment_and_Patent_Database|Redesign Assignment and Patent Database, Fall 2017 by Shelby]]
 +
* [[Equivalent_XPath_and_APS_Queries|Equivalent XPath and APS Queries, Summer 2017 by Oliver & Joe]]
 
* [[US_Address_Verification|US Address Verification, Summer 2017 based on tables from Assignment Data Restructure]]
 
* [[US_Address_Verification|US Address Verification, Summer 2017 based on tables from Assignment Data Restructure]]
 
* [[Patent_Assignment_Data_Restructure|Assignment Data Restructure, Spring 2017 by Marcela and Sonia]]
 
* [[Patent_Assignment_Data_Restructure|Assignment Data Restructure, Spring 2017 by Marcela and Sonia]]
Line 267: Line 332:
 
* [[Lex_Machina|Lex Machina]]
 
* [[Lex_Machina|Lex Machina]]
 
* [[USPTO_Patent_Litigation_Data|USPTO Patent Litigation Research Dataset by Ed]]
 
* [[USPTO_Patent_Litigation_Data|USPTO Patent Litigation Research Dataset by Ed]]
* [[Patent_Litigation_and_Review|Patent Litigation and Review by Marcela]]
+
* [[Patent_Litigation_and_Review|Patent Litigation and Review by Marcela]]* [[Patent|Existing Database Schema]]
* [[Bag_of_Words_Analysis_of_Patent_Data|Bag of Words Analysis]]
 
* [[Patent|Existing Database Schema]]
 
 
* [[Oliver_Chang_(Work_Log)|My Work Log]]
 
* [[Oliver_Chang_(Work_Log)|My Work Log]]
  
 
== External Links ==
 
== External Links ==
  
 +
* Understanding Assignment Data: [https://www.uspto.gov/sites/default/files/documents/USPTO_Patents_Assignment_Dataset_WP.pdf USPTO Documentation on their cleanup of this data]
 +
* [https://bulkdata.uspto.gov/data2/patent/grant/redbook/fulltext/1976/PatentFullTextAPSDoc_GreenBook.pdf USPTO Green Book (APS) Documentation]
 
* [https://bulkdata.uspto.gov/ USPTO Bulk Data Storage System (BDSS)]
 
* [https://bulkdata.uspto.gov/ USPTO Bulk Data Storage System (BDSS)]
 
* [https://en.wikipedia.org/wiki/Builder_pattern Builder Pattern in Object-Oriented Programming]
 
* [https://en.wikipedia.org/wiki/Builder_pattern Builder Pattern in Object-Oriented Programming]
* [https://rdp.mcnaircenter.org/codebase/Repository/ReproduciblePatent Git Repository]
+
* [http://rdp.mcnaircenter.org/codebase/Repository/ReproduciblePatent Git Repository]

Latest revision as of 12:06, 6 October 2020


Project
Reproducible Patent Data
Uspto web logo.jpg
Project Information
Has title Reproducible Patent Data
Has owner Oliver Chang
Has start date May 17
Has deadline date
Has project status Active
Dependent(s): Patent Design Main Page, Redesign Assignment and Patent Database
Does subsume Redesigning Patent Database, Patent Assignment Data Restructure
Has sponsor McNair Center
Has project output Data, Tool, Content, How-to, Guide
Copyright © 2019 edegan.com. All Rights Reserved.


The Reproducible Patent Data project is a continuation of the Redesigning Patent Database project. It aims to write faster, more centralized code to deal with data from the United States Patent and Trademark Office (USPTO). By having an end-to-end pipeline we can easily reproduce or update data without worrying about unintentional side effects or missing data. See also the Patent Data umbrella project.

Quickstart

To get up and running with the code, do the following:

  1. Clone the git project (link at end of page) to your user directory
  2. Launch IntelliJ with >= Java 8 and Maven configured (default version installed on the RDP is setup to do this)
  3. Open project in IntelliJ
  4. Create an empty database (see #Database)
  5. Run the table creation scripts in src/db/schemas/ in your new database
  6. Modify the constant DATABASE_NAME in E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\postgres\DatabaseHelper.java
  7. Run the Driver scripts in IntelliJ with the correct value for DATA_DIRECTORY (or run RunInitialImport.java which will do all of the data directories for that patent item type)
  8. [Take a really, really long lunch...in total should take no more than five hours to load data on RDP]
  9. Run scripts in src/db/constraints to check data assumptions
  10. That's it!

Troubleshooting

If you're new to IntelliJ (and even if you're not) you might run into problems with importing the project.

Setting Up Project as a Maven project It should be clear if the project is not set up as a Maven project - when you right click on RunInitialImport.java, for example, you won't see an option with a green triangle next to it that says "Run 'RunInitialImport.java'", and the green triangle in the top toolbar will be grayed out. If the project is not set up as a Maven project, you will not be able to run any of the code. To set up the project as a Maven project, when you import the project, follow the instructions at the following link.

  • Note that when you click "Import Project", you should select the "Simpler Patent Data" folder, not the "src" folder within Simpler Patent Data, otherwise you won't get the pom.xml file that you need to let IntelliJ know that this is a Maven project.
  • On the second window (there will be several options with check boxes next to them) make sure "import Maven projects automatically" is selected
  • On the "Please select project SDK" window, make sure it says "1.8" in the "Name" slot.
  • On the next window, enter a name for the project and enter a folder location.

This should ensure that the project is set up as a Maven project.

Setting Up Your Data Source

If you run into a message across the top that says something along the lines of "Configure Data Source", then you have not connected IntelliJ to a database. You will not be able to run the code located under src/db until you configure one. Start by clicking on the link in the message, or if it doesn't appear, follow the instructions here to open up the "Data Sources and Drivers" pop-up to add a PostgreSQL database. When you get to the dialogue asking about the host, database, user, and password, do the following to connect to the database on the RDP:

host: localhost
database: whatever the constant DATABASE_NAME is set to - the default is patentsj
user: postgres
password: tabspaceenter

Make sure to test the connection by clicking "Test Connection". Now you should be able to run the scripts under src/db.

If you're seeing issues such as "column [something] of relation [something] doesn't exist" but you've run the schema scripts, you probably have different database name under the data source than the one the constant DATABASE_NAME is set to. To change this, right click on the data source in the Database tab and select "Properties".

Directory Layout

Where is the Data?

Directories

All of the information for this project is located at E:\McNair\Projects\SimplerPatentData

There are several interesting directories:

  • data/downloads/ is USPTO bulkdata, unmodified straight from the scraper
  • data/extracts/ is a directory of a strict subset of the information stored in data/downloads/. It is the result of running a bulk 7-zip job on that directory to get everything unzipped in a flat data structure. Note that these files have the USPTO modified-by time since that metadata is stored in the zipfiles. To extract files in this nice format, select all of the zipfiles and setup an extraction job like in this screenshot
  • data/backups/ is a 7zip'd backup of the corresponding directory in extracts
  • src/ is the main code repository for the java project

Input Files

All of the text-only Red Book files for granted patents from 1976 to 2016, inclusive. To find a specific year's XML file, find it in

E:\McNair\Projects\SimplerPatentData\data\extracts\granted\

To find application data from 2001 to 2016, inclusive, look in

E:\McNair\Projects\SimplerPatentData\data\extracts\applications\

To find assignment data, look in

E:\McNair\Projects\SimplerPatentData\data\extracts\granted\

To find maintenance fee data, look in

E:\McNair\Projects\SimplerPatentData\data\downloads\maintenance\

Where is the Code?

The code has the same parent directory as the data, so it is at E:\McNair\Projects\SimplerPatentData\src. You might notice a lot of single-entry directories; this is an idiomatic Java pattern that is used for package separation. If using IntelliJ or some other IDE, these directories are a bit less annoying.

The development environment is Java 8 JDK, IntelliJ Ultimate IDE, Maven build tools, and git VCS.

The git repository can be found at http://rdp.mcnaircenter.org/codebase/Repository/ReproduciblePatent

Prior Art

This tool is not so concerned with adding new functionality; rather, it aims to take a bunch of spread out Perl scripts and create a faster system that is easier to work with. As such, its functionality is largely stolen from those scripts:

  • Downloader: E:\McNair\Software\Scripts\Patent\USPTO_Parser.pl
  • XML Splitter: E:\McNair\PatentData\splitter.pl
  • XML Parsing: E:\McNair\PatentData\Processed\xmlparser_4.5_4.4_4.3.pl and E:\McNair\PatentData\Processed\*.pm

In addition, I used several non-standard Java libraries listed below:

If using maven, these dependencies are listed and should automatically be setup.

Using Code

Any file with a line that says public static void main(String[] args) { can be run as a standalone file. The easiest way to do this is to load the project and then the file in IntelliJ and click the little green play arrow next to this bit of code.

The code can also be run via the standard javac and java commands but since this project has a complicated structure you end up having to run commands like

"C:\Program Files\Java\jdk1.8.0_131\bin\java" "-javaagent:C:\Users\OliverC\IntelliJ IDEA 2017.1.3\lib\idea_rt.jar=62364:C:\Users\OliverC\IntelliJ IDEA 2017.1.3\bin" -Dfile.encoding=UTF-8 -classpath "[...contents truncated...];C:\Users\OliverC\.m2\repository\org\postgresql\postgresql\42.1.1\postgresql-42.1.1.jar" org.bakerinstitute.mcnair.uspto_assignments.XmlDriver

to include all of the runtime dependencies and it's just not worth it.

Altering Code

  • Use the IntelliJ command Reformat code (found in the menus at Code > Reformat Code
  • Use the optimize imports function found under the same menu
  • Use spaces for indentation
  • Loosely try to keep lines below 120 characters
  • Commit changes to the Git remote repository "bonobo"

Schema Reconciliation

For the work by Joe, see the Patent Schema Reconciliation page

Patents (Granted)

See E:\McNair\Projects\SimplerPatentData\data\examples\granted for extracted examples of what specific data is available for a sample of the data.

Granted Patent Data Formats
Dates Used Format Location Supported? Utility Reissue Design Plant
January 1976 to December 2001 APS data/extracts/granted/vintage Yes ~ ~ ~
January 2001 to December 2001 SGML Ignored; use concurrently recorded APS data No N/A N/A N/A N/A
January 2002 to December 2004 XML Version 2.5 data/extracts/granted/blunderyears Yes ~ ~ ~
January 2005 to December 2005 XML Version 4.0 ICE data/extracts/granted/modern Yes ~ ~ ~
January 2006 to December 2006 XML Version 4.1 ICE data/extracts/granted/modern Yes ~ ~ ~
January 2007 to December 2012 XML Version 4.2 ICE data/extracts/granted/modern Yes ~ ~ ~
January 2013 to September 24, 2013 XML Version 4.3 ICE data/extracts/granted/modern Yes ~ ~ ~
October 8, 2013 to December 2014 XML Version 4.4 ICE data/extracts/granted/modern Yes ~ ~ ~
January 2015 to December 2016 XML Version 4.5 ICE data/extracts/granted/modern Yes ~ ~ ~

APS Rosetta Stone

The Advanced Patent System (APS) is a fixed-width text format used to store historical patent grant data. The documentation for this sucks; there are pages missing at random. Luckily, we only care about the content contained here: File:PatentFullTextAPSDoc GreenBook pgs13-22.pdf.

It's worth mentioning that the APS contains an advanced text markup system for chemical formulae, basic text markup, tables, etc. that can lead to seemingly garbled text that is perfectly well-formed.

APS Gotchas

  • PATN.WKU is the granted patent number. It is 7 digits while the spec promises 6 digits. The rightmost digit is a check digit modulus 11. See File:Aps-wku-modulus11.pdf for the words from the horse's mouth.

Patents (Applications)

Patent Application Data Formats
Dates Used Format Location Supported by Parser?
March 15, 2001 to December 2001 XML Version 1.5 data/extracts/applications/vintage Yes, for basic information, inventors, and correspondents
January 2002 to December 2004 XML Version 1.6 data/extracts/applications/vintage Ditto
January 2005 to December 2005 XML Version 4.0 ICE data/extracts/applications/modern Ditto
January 2006 to December 2006 XML Version 4.1 ICE data/extracts/applications/modern Ditto
January 2007 to December 2012 XML Version 4.2 ICE data/extracts/applications/modern Ditto
January 2013 to December 2014 XML Version 4.3 ICE data/extracts/applications/modern Ditto
January 2015 to Present XML Version 4.4 ICE data/extracts/applications/modern Ditto

Database

Because there isn't a compelling reason not to, I used the existing PostgreSQL infrastructure on the RDP. The "Java Way" of interacting with databases is the Java Database Connectivity API (JDBC), an implementation-agnostic API for interacting with databases. This project uses the stock Postgres JDBC, version 42.1.1

Create an empty database on RDP

To create an empty database, run this command: $ createdb --username=postgres database-name-goes-here # password is tabspaceenter

Abstraction Layer

Since writing raw SQL is a bit cumbersome and error-prone, I have added some abstraction layers that make it much easier to quickly add bulk data. By using Postgres's CopyManager class, we buffer SQL copy commands in memory (as many as possible) and then flush these rows. To understand how the abstraction layers work, see the code in E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\postgres. See E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\models\GrantedPatent.java for an example of how to extend the abstraction layer to deal with more complex scenarios.

New Table Checklist

  • Create schema DDL SQL code for the new table in E:\McNair\Projects\SimplerPatentData\src\db
  • Run the schema creation
  • Create an enum with the same names for attributes as in the DDL (case-insensitive! prefer all-caps screaming snake case)
  • Create a class which subclasses AbstractInsertableData
  • Inside that class, create a static class which subclasses AbstractTableMetadata and has the proper values for getTableName(), getStringColumns(), getIntColumns()
  • (Optional) Implement builder pattern
  • (Optional) Create a custom databasehelper for complex extras (see PatentApplication and GrantedPatent for examples)
  • Write the data to the table (see DatabaseHelper for the pattern I use)

Address Data

To get the most granular address data (street level, or at least postcode level) about who owns patents, the path is not so straightforward because off the complicated mapping of ownership to a granted patent. This is the final part of this project that I am working on and it is all at the level of SQL. See E:\McNair\Projects\SimplerPatentData\src\db\joins for my attempts to create a clean mapping. Optimistically speaking, the data generated here should be superset of the data present in the Patent Assignment Data Restructure project.

Note that as of the beginning of August 2017, this part has not been completed.


Intuition

Use assignments_longform.last_update_date to find current/latest (or first/earliest) date of assignment. Then match with properties.docid on reelno, frameno to find patent application id. With this mapping to granted patents, we can discover the details of the original granted patent. And with the right date and reelno and frameno, we can match to the assignees table and get fine granularity addresses.

Related Pages

External Links