[Halloween Data Visualization Competition] Wiggly SQL Maps

I thought a per-capita view of UFO sightings in the US might be interesting to see over time. It might just reflect random noise over this sample set (2010-2013), but thought it made for a cool looking wiggly map of UFOs flying all over the sky. Over a longer time period, perhaps we could see a geographic shift in UFO reporting preferences?

Population indexed against a census table for 2010.

with
  ufo_sighting_counts_by_lat_long as (
    select
      latitude
      , longitude
      , state
      , count(*) as instances
    from
      [kaggle_ufo_data]
    where
      country = 'us'
      and state not in (
        'ak'
        , 'hi'
        , 'pr'
      )
      and [date_observed:year] = '2010-01-01'
    group by
      1
      , 2
      , 3
  )
, state_populations as (
select 'al' as state, '4779736'::float as population, '2010' as year
union select 'ak', '710231', '2010'
union select 'az', '6392017', '2010'
union select 'ar', '2915918', '2010'
union select 'ca', '37253956', '2010'
union select 'co', '5029196', '2010'
union select 'ct', '3574097', '2010'
union select 'de', '897934', '2010'
union select 'dc', '601723', '2010'
union select 'fl', '18801310', '2010'
union select 'ga', '9687653', '2010'
union select 'hi', '1360301', '2010'
union select 'id', '1567582', '2010'
union select 'il', '12830632', '2010'
union select 'in', '6483802', '2010'
union select 'ia', '3046355', '2010'
union select 'ks', '2853118', '2010'
union select 'ky', '4339367', '2010'
union select 'la', '4533372', '2010'
union select 'me', '1328361', '2010'
union select 'md', '5773552', '2010'
union select 'ma', '6547629', '2010'
union select 'mi', '9883640', '2010'
union select 'mn', '5303925', '2010'
union select 'mi', '2967297', '2010'
union select 'ms', '5988927', '2010'
union select 'mo', '989415', '2010'
union select 'ne', '1826341', '2010'
union select 'nv', '2700551', '2010'
union select 'nh', '1316470', '2010'
union select 'nj', '8791894', '2010'
union select 'nm', '2059179', '2010'
union select 'ny', '19378102', '2010'
union select 'nc', '9535483', '2010'
union select 'nd', '672591', '2010'
union select 'oh', '11536504', '2010'
union select 'ok', '3751351', '2010'
union select 'or', '3831074', '2010'
union select 'pa', '12702379', '2010'
union select 'ri', '1052567', '2010'
union select 'sc', '4625364', '2010'
union select 'sd', '814180', '2010'
union select 'tn', '6346105', '2010'
union select 'tx', '25145561', '2010'
union select 'ut', '2763885', '2010'
union select 'vt', '625741', '2010'
union select 'va', '8001024', '2010'
union select 'wa', '6724540', '2010'
union select 'wv', '1852994', '2010'
union select 'wi', '5686986', '2010'
union select 'wy', '563626', '2010'
  )
select
  latitude
  , longitude
  , instances::float / population as per_capita_instances
from
  ufo_sighting_counts_by_lat_long
  join state_populations using (state)
Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like2 Follow
  • 2 Likes
  • 3 mths agoLast active
  • 118Views
  • 1 Following