Overlaying NYT and Geotweets

• Load the geotweet table to Omnisci (without the geometry field) using scripts /n/holyscratch01/cga/dkakkar/scripts/geotweets.py
• Load NYT data to Omnsici using /n/holyscratch01/cga/dkakkar/scripts/nytimes.py
• Add FIPS to all the geotweets by running the following SQL query in Immerse, this creates a new table geotweets_fips which has county FIPS added to all geotweeets:

Create table geotweets_fips AS (Select a.*,b.fips from geotweets a, omnisci_counties b WHERE ST_Intersects(b.omnisci_geo,ST_SetSRID(ST_Point(a.longitude,a.latitude),4326)));

• Create table geotweets_fips_date_cnt which calculates count of tweets by county by date

Create table geotweets_fips_date_cnt AS (Select date_trunc(day, tweet_date) as tweet_dat, fips, count(*) as tweet_count
from geotweets_fips group by date_trunc(day, tweet_date), fips);