Changes

There are still geo errors in the db. Addresses within the US have incorrect geo-coordinates. To fix this problem we will just lookup all the addresses in the DB using the Geocode.py script. Also we need to pull a company level file from SDC because the addresses will be copied down or be null by the normalizer. Modify your round ssh sdc script to remove the round dates. Therefore only one line will be assigned to one company. There will be no normalization errors this way.Then copy into the db and copy out all the distinct coname, statecode, datefirstinv that have a value in addr1 or addr2. Then run this through the geocode script. Copy the result back into the db and redo the colevel output tables. DROP TABLE geoallcoords; CREATE TABLE geoallcoords ( coname varchar(100), statecode varchar(2), datefirstinv date, latitude real, longitude real ); \COPY geoallcoords FROM 'sdccompanygeolookup.txt_coords' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV --44999  --redo the colevel output tables DROP TABLE coleveloutput; CREATE TABLE coleveloutput AS SELECT companybasecore2.coname, companybasecore2.statecode, companybasecore2.datefirstinv, companybasecore2.city, companybasecore2.addr1, companybasecore2.addr2, companybasecore2.zip, g.latitude, g.longitude, d.deaddate, d.aliveyear, d.deadyear FROM companybasecore2 LEFT JOIN deadalive1 AS d ON d.coname=companybasecore2.coname AND d.statecode=companybasecore2.statecode AND d.datefirstinv=companybasecore2.datefirstinv LEFT JOIN geoallcoords AS g ON g.coname = companybasecore2.coname AND g.statecode = companybasecore2.statecode AND g.datefirstinv = companybasecore2.datefirstinv WHERE hadgrowthvc=1; --32575  DROP TABLE colevelsimple; CREATE TABLE colevelsimple AS SELECT coname, statecode, datefirstinv, city, addr1, addr2, zip, aliveyear, deadyear, latitude, longitude FROM coleveloutput WHERE aliveyear IS NOT NULL and deadyear IS NOT NULL AND latitude IS NOT NULL; --31523 \COPY colevelsimple TO 'colevelsimple.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV