US Address Verification - Revision history

Ed at 17:47, 21 September 2020

2020-09-21T17:47:42Z

2020-09-21T17:34:37Z

2020-09-20T17:20:18Z

2020-09-20T17:12:30Z

2017-06-29T21:42:20Z

A Stopgap Measure

2017-06-29T21:42:02Z

A Stopgap Measure

2017-06-23T21:55:39Z

A Stopgap Measure

2017-06-23T01:16:05Z

2017-06-23T01:14:17Z

A Stopgap Measure

2017-06-23T01:12:09Z

A Stopgap Measure

@@ Line 1: / Line 1: @@
 {{Project
-|Has project output=
+|Has project output=Tool
 |Has sponsor=McNair Center
 |Has title=US Address Verification

@@ Line 1: / Line 1: @@
 {{Project
 |Has sponsor=McNair Center
 |Has title=US Address Verification

@@ Line 1: / Line 1: @@
 {{Project
 |Has title=US Address Verification
 |Has owner=Oliver Chang,

@@ Line 1: / Line 1: @@
-{{McNair Projects
+{{Project
 |Has title=US Address Verification
 |Has owner=Oliver Chang,

@@ Line 418: / Line 418: @@
   <nowiki>
-DROP VIEW IF EXISTS june_2017_zipcode_hotfix;
+DROP VIEW IF EXISTS june_2017_postcodecode_hotfix;
 CREATE VIEW june_2017_postcode_hotfix AS
    SELECT

@@ Line 415: / Line 415: @@
 == A Stopgap Measure ==
-As a quick and dirty fix, walk through the cases and do the best we can. The script for that is located at <code>E:\McNair\Projects\SimplerPatentData\src\db\ZipCodeHotfix.sql</code> and it creates the following table:
+As a quick and dirty fix, walk through the cases and do the best we can. The script for that is located at <code>E:\McNair\Projects\SimplerPatentData\src\db\hacks\June2017PostcodeHotfix.sql</code> and it creates the following table:
   <nowiki>

@@ Line 419: / Line 419: @@
   <nowiki>
 DROP VIEW IF EXISTS june_2017_zipcode_hotfix;
-CREATE VIEW june_2017_zipcode_hotfix AS
+CREATE VIEW june_2017_postcode_hotfix AS
    SELECT
      reelno,
      frameno,
      name,
      GodHelpUsAll(postcode_cleaned, postcode) as postcode
    FROM ptoassigneend_us_cleaned;</nowiki>
   <nowiki>
-patent=# select count(*) from june_2017_zipcode_hotfix ;
+patent=# select count(*) from june_2017_postcode_hotfix ;
    count
 ---------
@@ Line 434: / Line 435: @@
 (1 row)
--- NOTE THAT THE DEFAULT BEHAVIOR OF THE SCRIPT IS TO FALL BACK TO THE EMPTY QUOTE
+patent=# select count(*) from june_2017_postcode_hotfix where postcode is not null;
    count
 ---------
   3370613
 (1 row)</nowiki>

@@ Line 4: / Line 4: @@
 |Has start date=June 2017
 |Has deadline=June 2017
-|Has project status=Active
+|Has project status=Complete
 }}

← Older revision		Revision as of 01:14, 23 June 2017
Line 473:		Line 473:
	ALTER TABLE june_2017_zipcode_join		ALTER TABLE june_2017_zipcode_join
	ADD UNIQUE (documentid);</nowiki>		ADD UNIQUE (documentid);</nowiki>
		+
		+	=== Analyzing the Stopgap ===
		+
		+	See <code>Z:\zipcodes-oliver\postcodes_ranked.tsv</code> where the first column is zipcode and the second column is number of patents in that zipcode, in descending order.

← Older revision		Revision as of 01:12, 23 June 2017
Line 441:		Line 441:
	3370613		3370613
	(1 row)</nowiki>		(1 row)</nowiki>
		+
		+	However, much of this data is ambiguous. Since f(reelno, frameno) = list_of_documentid where f is a mapping function and there are multiple postcodes for each documentid, we end up having to throw away a lot of information. This mapping takes place in <code>E:\McNair\Projects\SimplerPatentData\src\db\ZipcodeMapAssignmentToPatent.sql</code> and takes the form of
		+
		+	<nowiki>
		+	DROP TABLE IF EXISTS june_2017_documents_with_zipcodes;
		+	CREATE TABLE june_2017_documents_with_zipcodes AS
		+	SELECT DISTINCT
		+	p.documentid,
		+	a.postcode,
		+	max(p.filingdate) AS filingdate
		+	FROM
		+	ptoproperty_patent AS p,
		+	june_2017_zipcode_hotfix AS a
		+	WHERE
		+	p.reelno = a.reelno AND p.frameno = a.frameno AND a.postcode != ''
		+	GROUP BY documentid, postcode;
		+
		+	-- 1365408 rows
		+	DROP TABLE IF EXISTS june_2017_zipcode_join;
		+	CREATE TABLE june_2017_zipcode_join AS
		+	SELECT p.*
		+	FROM
		+	june_2017_documents_with_zipcodes AS p,
		+	(SELECT
		+	documentid,
		+	count(DISTINCT postcode) AS unique_postcodes
		+	FROM june_2017_documents_with_zipcodes
		+	GROUP BY documentid) AS z
		+	WHERE
		+	z.unique_postcodes = 1 AND z.documentid = p.documentid;
		+	ALTER TABLE june_2017_zipcode_join
		+	ADD UNIQUE (documentid);</nowiki>