A perennially popular request is the creation of global "conflict maps" summarizing the state of world events over time and the intensity of conflict activity at any given location on earth. To explore the potential of Google BigQuery to make possible the creation of whole-of-earth global views onto conflict, we created the map below, which displays all locations from which GDELT monitored events on at least 16 distinct days and colors that location by the percent of events from that location that were deemed "conflict" in nature.
Click on the image below to launch the interactive zoomable map.
TECHNICAL DETAILS
The final query to generate the map aggregates the 2015 event dataset by unique location (using the Geographic FeatureID to normalize for different spellings and transliterations of a given location name), filters for the number of unique days with events from that location, and the percent of events from that location that were conflict in nature. It also uses the NumArticles field to filter for events with higher confidence and requires that both Actor1 and Actor2 fields be populated. This eliminates events with unknown victims or perpetrators (such as "unknown assailants killed 50 civilians yesterday"), but further reduces ambiguity, ensuring that the final events are well-formed.
select location, REGEXP_REPLACE(location, r',.*', '') locationname, REGEXP_REPLACE(location, r'^.*,', '') locationcountry, featureid, latitude, longitude, cnt, conflictcnt, (conflictcnt / cnt * 100) conflictperc, numdays, goldstein, tone, numsources from ( SELECT max(location) location, featureid, max(latitude) latitude, max(longitude) longitude, count(1) cnt, sum( QuadClass = 4) conflictcnt, COUNT(DISTINCT(SQLDATE)) numdays, COUNT(DISTINCT(domain)) numsources, avg( GoldsteinScale) goldstein, avg(AvgTone) tone FROM ( SELECT min(SQLDATE) SQLDATE, min(ActionGeo_FullName) location, ActionGeo_FeatureID featureid, min(ActionGeo_Lat) latitude, min(ActionGeo_Long) longitude, max(QuadClass) QuadClass, max(GoldsteinScale) GoldsteinScale, max(AvgTone) AvgTone, DOMAIN(SOURCEURL) domain FROM [gdelt-bq:gdeltv2.events] where ActionGeo_Type>1 and SQLDATE>20150300 and NumArticles >= 3 and Actor1Code is not null and Actor2Code is not null group by featureid, domain ) group by featureid order by cnt desc ) where numdays > 15