329
edits
Changes
Jump to navigation
Jump to search
-- NOTE THAT THE DEFAULT BEHAVIOR OF THE SCRIPT IS TO FALL BACK TO THE EMPTY QUOTE-- AND NOT NULL AS BEFOREpatent=# select count(*) from june_2017_zipcode_hotfix june_2017_postcode_hotfix where postcode != ''is not null;
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>
=== 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.
→A Stopgap Measure
<nowiki>
DROP VIEW IF EXISTS june_2017_zipcode_hotfix;
CREATE VIEW june_2017_zipcode_hotfix june_2017_postcode_hotfix AS
SELECT
reelno,
frameno,
name,
city_cleaned as city,
GodHelpUsAll(postcode_cleaned, postcode) as postcode
FROM ptoassigneend_us_cleaned;</nowiki>
<nowiki>
patent=# select count(*) from june_2017_zipcode_hotfix june_2017_postcode_hotfix ;
count
---------
(1 row)
count
---------
3370613
(1 row)</nowiki>
