7,612
edits
Changes
Jump to navigation
Jump to search
VC Database Rebuild (view source)
Revision as of 12:30, 27 September 2017
, 12:30, 27 September 2017→Creating Stage Flags Table
END AS excludeflag
FROM round;
'''NOTES FROM ED'''
*There wasn't a colevel, portco keyed master flag table, so I made one from '''roundplus''' (see below)
*I also included basic round summary data
DROP TABLE PortCoFlagMaster;
CREATE TABLE PortCoFlagMaster AS
SELECT coname, statecode, datefirstinv, city
max(growthflag) AS hadgrowth, sum(growthflag) as numgrowth,
max(seedflag) AS hadseed, sum(seedflag) as numseed,
max(earlyflag) AS hadearly, sum(earlyflag) as numearly,
max(laterflag) AS hadlater, sum(laterflag) as numlater,
max(transactionflag) AS hadtrans, sum(transactionflag) as numtrans,
max(excludeflag) AS hadexcl, sum(excludeflag) as numexcl,
max(dealflag) AS haddeal,
COUNT(rounddate) as numrounds, sum(numinvestors) as totalinvestors
FROM roundplus
GROUP BY coname, statecode, datefirstinv, city;
--44721
Code is in FixingVCDB2.sql in Z:\VentureCapitalData\SDCVCData\vcdb2
==Fixing erroneous geo-coordinates==
Some of the geocoordinates in the db are dirty and point to locations in India, Eastern Europe. However, the company addresses exist. Isolate the dirty geo-coordinates and do a lookup using Geocode.py script. To isolate place a box around the continental US and flag all points that fall outside the box. Add back the points that are located in Hawaii and Puerto Rico. Then import back into db.
