Difference between revisions of "Reproducible Patent Data"
Line 271: | Line 271: | ||
* Run the schema creation | * 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 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> | + | * 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() | * 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) Implement builder pattern |
Revision as of 12:47, 14 November 2017
Reproducible Patent Data | |
---|---|
Project Information | |
Project Title | Reproducible Patent Data |
Owner | Oliver Chang |
Start Date | May 17 |
Deadline | |
Primary Billing | |
Notes | |
Has project status | Active |
Subsumes: | Redesigning Patent Database, Patent Assignment Data Restructure |
Copyright © 2016 edegan.com. All Rights Reserved. |
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.
Contents
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
src/db/schemas/
in your new database - Modify the constant
DATABASE_NAME
inE:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\postgres\DatabaseHelper.java
- Run the Driver scripts in IntelliJ with the correct value for
DATA_DIRECTORY
(or runRunInitialImport.java
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
src/db/constraints
to check data assumptions - That's it!
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 scraperdata/extracts/
is a directory of a strict subset of the information stored indata/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 screenshotdata/backups/
is a 7zip'd backup of the corresponding directory in extractssrc/
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
andE:\McNair\PatentData\Processed\*.pm
In addition, I used several non-standard Java libraries listed below:
- Unirest for easy HTTP requests (MIT License)
- Google Guava for immutable collections and Stream utilities (Apache v2.0 License)
- jsoup for HTML parsing (MIT License)
- Apache Commons Codec (Apache v2.0 License)
- Apache Commons Lang v3 (Apache v2.0 License)
- Jetbrains Annotations for enhanced null checks (Apache v2.0 License)
- PostgreSQL JDBC (BSD 3-clause per https://github.com/pgjdbc/pgjdbc-jre7/blob/master/LICENSE)
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.
Dates Used | Format | Location | Supported? | Utility | Reissue | Design | Plant |
---|---|---|---|---|---|---|---|
January 1976 to December 2001 | APS | data/extracts/granted/vintage
|
Yes | ✓ | ~ | ~ | ~ |
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)
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
- Redesign Assignment and Patent Database, Fall 2017 by Shelby
- Equivalent XPath and APS Queries, Summer 2017 by Oliver & Joe
- US Address Verification, Summer 2017 based on tables from Assignment Data Restructure
- Assignment Data Restructure, Spring 2017 by Marcela and Sonia
- Redesigning Patent Database, Spring 2017 by Shelby
- Patent Data Cleanup, June 2016 by Marcela
- Patent Data, Spring 2016 by Marcela
- Lex Machina
- USPTO Patent Litigation Research Dataset by Ed
- Patent Litigation and Review by Marcela
- Bag of Words Analysis
- Existing Database Schema
- My Work Log