329
edits
Changes
Jump to navigation
Jump to search
→A Stopgap Measure
3370613
(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>
