Difference between revisions of "HistPatent Table"
Jump to navigation
Jump to search
| Line 48: | Line 48: | ||
==Table Purpose== | ==Table Purpose== | ||
==Current Problems== | ==Current Problems== | ||
| − | {{#section: | + | {{#section:Patent_Data_Issues|histpatent}} |
Revision as of 12:59, 18 July 2016
Return to Patent Data Specifications.
Contents
Table Structure
Table "public.histpatent"
Column | Type | Modifiers
----------------+-------------------+-----------
applicationid | integer |
pubno | character varying |
patentnumber | character varying |
nber | integer |
uspc | character varying |
uspc_sub | character varying |
applicationdate | date |
prioritydate | date |
pubdate | date |
displaydate | date |
disptype | character varying |
exp_dt | date |
exp_dt_max | date |
pta | integer |
Example of table entries:
applicationid | pubno | patentnumber | nber | uspc | uspc_sub | applicationdate | prioritydate | pubdate | displaydate | disptype | exp_dt | exp_dt_max | pta
--------------+-----------------+--------------+------+------+----------+-----------------+--------------+------------+-------------+----------+------------+------------+-----
9728079 | US20010000377A1 | 6301920 | 69 | 62 | 374 | 2000-12-04 | 1998-12-04 | 2001-04-26 | 2001-10-16 | ISS | 2009-10-16 | | 0
9738354 | US20010000378A1 | 6327947 | 51 | 82 | 1 | 2000-12-18 | 1996-09-04 | 2001-04-26 | 2001-12-11 | ISS | 2016-09-03 | | 0
9730148 | US20010000379A1 | -2938081 | 51 | 83 | 013 | 2000-12-04 | 2000-12-04 | 2001-04-26 | 2003-01-28 | ABN | | | 0
9730180 | US20010000380A1 | 6461408 | 13 | 95 | 55 | 2000-12-05 | 1997-09-24 | 2001-04-26 | 2002-10-08 | ISS | 2017-09-23 | | 0
9734424 | US20010000381A1 | 6491741 | 13 | 95 | 90 | 2000-12-11 | 1998-10-08 | 2001-04-26 | 2002-12-10 | ISS | 2018-10-07 | | 0
9732670 | US20010000382A1 | 6393980 | 69 | 101 | 128 | 2000-12-08 | 1998-10-16 | 2001-04-26 | 2002-05-28 | ISS | 2014-05-28 | | 0
9739809 | US20010000383A1 | 6497774 | 19 | 149 | 45 | 2000-12-20 | 1997-03-31 | 2001-04-26 | 2002-12-24 | ISS | 2017-03-30 | | 0
9739203 | US20010000384A1 | 6247415 | 69 | 109 | 33 | 2000-12-19 | 2000-12-19 | 2001-04-26 | 2001-06-19 | ISS | 2009-06-19 | | 0
9729711 | US20010000385A1 | 6550416 | 69 | 116 | 142 | 2000-12-06 | 1998-04-10 | 2001-04-26 | 2003-04-22 | ISS | 2007-04-22 | | 0
9735177 | US20010000386A1 | 6805134 | 61 | 131 | 299 | 2000-12-12 | 1999-04-26 | 2001-04-26 | 2004-10-19 | ISS | 2019-04-25 | | 0
9741335 | US20010000387A1 | -3349739 | 19 | 149 | 019 | 2000-12-21 | 2000-12-21 | 2001-04-26 | 2001-12-10 | ABN | | | 0
9730265 | US20010000388A1 | 6491078 | 55 | 152 | 539 | 2000-12-05 | 1999-05-25 | 2001-04-26 | 2002-12-10 | ISS | 2019-05-24 | | 0
9738655 | US20010000389A1 | 6521075 | 19 | 156 | 251 | 2000-12-15 | 1999-04-08 | 2001-04-26 | 2003-02-18 | ISS | 2019-04-07 | | 0
9728280 | US20010000390A1 | -1342275 | 19 | 156 | 472 | 2000-12-01 | 2000-12-01 | 2001-04-26 | 2003-01-06 | ABN | | | 0
9736673 | US20010000391A1 | 6877544 | 59 | 157 | 1 | 2000-12-13 | 1998-06-01 | 2001-04-26 | 2005-04-12 | ISS | 2019-08-01 | | 427
9737195 | US20010000392A1 | 6362417 | 41 | 174 | 384 | 2000-12-15 | 1999-02-03 | 2001-04-26 | 2002-03-26 | ISS | 2019-02-02 | | 0
9737877 | US20010000393A1 | 6283228 | 64 | 175 | 58 | 2000-12-15 | 1997-01-08 | 2001-04-26 | 2001-09-04 | ISS | 2017-01-07 | | 0
9728070 | US20010000394A1 | -2976897 | 55 | 180 | 002 | 2000-11-30 | 2000-11-30 | 2001-04-26 | 2002-01-11 | ABN | | | 0
9727786 | US20010000395A1 | 6325178 | 55 | 187 | 382 | 2000-12-04 | 1999-08-03 | 2001-04-26 | 2001-12-04 | ISS | 2019-08-02 | | 0
9730968 | US20010000396A1 | 6613214 | 19 | 205 | 118 | 2000-12-05 | 1998-11-30 | 2001-04-26 | 2003-09-02 | ISS | 2019-03-05 | | 96
Table Variables
Table Purpose
Current Problems
The USPTO bulk data contains negative patent numbers.
applicationid | pubno | patent | nber | uspc | uspc_sub | applicationdate | prioritydate | pubdate | displaydate | disptype | exp_dt | exp_dt_max | pta
--------------+-------+----------+------+------+----------+-----------------+--------------+---------+-------------+----------+------------+------------+------
8466602 | | -2037052 | 23 | 347 | 259 | 1995-06-06 | 1995-06-06 | | 1997-06-19 | ABN | | | 0
8605804 | | -2962913 | 70 | 395 | 500 | 1996-02-23 | 1996-02-23 | | 1998-08-22 | ABN | | | 0
| | 1332054 | 63 | 112 | 153 | | | | 1920-02-24 | ISS | 1937-02-23 | | 0
Application numbers are 8 digits long (99/999999). The first two digits are a series code and the last six represent a serial code assigned by the USPTO. The histpatent table has over 3 million application numbers with 7 digits, since leading zeros were dropped. Leading zeros were also dropped for patent numbers.
patent=# SELECT COUNT(*) FROM histpatent WHERE applicationid > 9999999; count --------- 4048050 (1 row)
patent=# SELECT COUNT(*) FROM histpatent WHERE applicationid <9999999; count --------- 3028846 (1 row)
LexJudge
There are duplicate entries for certain judges that may be dropped once data on patent litigation has been added.
name | court | count -----------------------------+-------+------- Malcolm Jones Howard | EDNC | 2 Richard Leroy Williams | EDVa | 2 Peter Jo Messitte | DMd | 2 Andre Maurice Davis | DMd | 2 Paula Xinis | DMd | 2 Mary Hannah Lauck | EDVa | 2 Julie E. Carnes | NDGa | 2 Claude M. Hilton | EDVa | 2 William D. Quarles Jr. | DMd | 2 James C. Dever III | EDNC | 2 Catherine C. Blake | DMd | 2 Gerald Bruce Lee | EDVa | 2 Leonie M. Brinkema | EDVa | 2
PatentId
The patentid table has many entries that do not consist of only integers.
SELECT COUNT(*) FROM patentid WHERE patentid ~ '[A-Z]'; count ------- 775982
SELECT COUNT(*) FROM patentid WHERE patentid ~ '[^A-Z]'; count -------- 27294418
