[Halloween Data Visualization Competition]
I wanted to see if there was a trend in the locations that UFO sightings were reported. I decided to use the date of sighting to see if there was a certain flight path taken by potential UFO's. This heatmap shows the locations where sightings most recently occurred.
The weight of the heatmap was set by calculating the number of days between the current date and the date of observation (shown in the code below).
SELECT latitude , longitude , shape , current_date , DATE(date_observed) , SUM(TRIM(TRAILING '`' FROM duration_seconds)) as "Total Time Observed" , DATEDIFF(day, current_date, DATE(date_observed)) as "DATE_DIFF" , COUNT(*) as "Occurances" FROM [kaggle_ufo_data] WHERE date_cmp(DATE(date_observed), to_date('1970-01-01', 'YYYY-mm-dd')) GROUP BY 1, 2, 3, 5