353
edits
Changes
Jump to navigation
Jump to search
Patent Assignment Data Restructure (view source)
Revision as of 17:26, 5 April 2017
, 17:26, 5 April 2017→5. Output (Tables)
city_city is the city name extracted from 'city'; city_addr1 is the city name extracted from 'addrline1'; city_addr2 is the city name extracted from 'addrline2'.
*'''ptoassigneend_us_extracted'''
Contain all the original features as well as city, state and postcode info extracted from features addrline1, addrline2 and city.
Table "public.ptoassigneend_us_extracted"
Column | Type | Modifiers
reelno | integer |
frameno | integer |
name | character varying(500) |
addrline1 | character varying(500) |
addrline2 | character varying(500) |
city | character varying(500) |
state | character varying(500) |
country | character varying(500) |
postcode | character varying(80) |
postqcode_city | text |
postcode_addr1 | text |
postcode_addr2 | text |
state_city | text |
state_addr1 | text |
state_addr2 | text |
city_addr1 | text |
city_addr2 | text |
city_city | text |
Merge ptoassigneend_missus_final and ptoassigneend_missus_city_final with:
CREATE TABLE ptoassigneend_us_extracted AS
SELECT a.*, b.city_addr1, b.city_addr2, b.city_city
FROM ptoassigneend_missus_final a
LEFT JOIN ptoassigneend_missus_city_final b
ON a.reelno = b.reelno AND a.frameno = b.frameno
AND a.name = b.name AND a.addrline1 = b.addrline1 AND
a.addrline2 = b.addrline2 AND
a.city = b.city;
====Master Table====
