7,612
edits
Changes
Jump to navigation
Jump to search
VC Database Rebuild (view source)
Revision as of 16:23, 25 September 2017
, 16:23, 25 September 2017→Fixing erroneous geo-coordinates
--41718
Then redo coleveloutput and colevelsimple using the geoimportfix as your geo table instead of geoimport.
==Re-Fixing erroneous geo-coordinates==
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.
NOTE FROM ED: The lat/long data has to be stored as decimal or numeric to prevent precision problems. The code below has been rerun.
DROP TABLE geoallcoords;
CREATE TABLE geoallcoords (
statecode varchar(2),
datefirstinv date,
latitude realnumeric, longitude realnumeric
);
\COPY geoallcoords FROM 'sdccompanygeolookup.txt_coords' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--44999
--select latitude, longitude from geoallcoords;
--redo the colevel output tables
