220
edits
Changes
Jump to navigation
Jump to search
→Name Based Matching geo keys to companybase keys
LEFT JOIN geocore AS g ON g.coname = m.geoconame AND m.geocity = g.city AND m.geodatefirstyear = g.startyear;
--45018
Okay so we need to fix this.Luckily I already had this data in another database so I copied it out and imported it into vcdb2. The raw data can be found in a text file in the folder on the Z drive called geolookupold.txt. CREATE TABLE geoimport ( coname varchar(100), statecode varchar(2), datefirstinv date, latitude real, longitude real ); \COPY geoimport FROM 'geolookupold.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV --42678 SELECT COUNT(*) FROM (SELECT DISTINCT coname, statecode, datefirstinv FROM geoimport)a; --42678 CREATE TABLE companybasegeomaster AS SELECT c.coname, c.statecode, c.datefirstinv, c.investedk, c.city, c.addr1, c.addr2, g.latitude, g.longitude FROM companybasecore AS c LEFT JOIN geoimport AS g ON c.coname = g.coname AND c.statecode = g.statecode AND c.datefirstinv = g.datefirstinv; --44740 \COPY companybasegeomaster TO 'companybasegeomaster.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
==Gathering geo data from company addresses==
