UFO Sightings in the Hudson Valley with BigQuery Geo Functions

UFO Sightings Dataset

This dataset, which can be found on Kaggle, contains over 80,000 reports of UFO sightings over the last century up until the year 2014. It contains a few categorical columns and the date of the reported sighting. The data although it has been cleaned is still pretty messy which we wll have to tackle first before we get into the geospatial analysis.

Find it here: https://www.kaggle.com/datasets/NUFORC/ufo-sightings/data

BigQuery Public Data Sets

Like many datasets that contain geo information, you'll find that this data only contains some geo components such as the City, State, Latitude and Longitude, but not all. What if we are interested in the County or Zip Code? Not to fear, BigQuery hosts some great geo datasets from the US Census Bureau each at a different geo hierarchy found in their public data set project bigquery-public-data and in their geo_us_boundaries dataset, seen below. They have MSAs and CBSAs to match up to Census Bureau data, Zip Codes, Counties, etc. Each one has various codes which can make joining them together very easy. There are latitudes, longitues, names, and even boundary points for the geographies. The only thing to understand here is that they aren't updated that often. The last update was in 2022, so newer zip codes for instance might not be available.

You can find more about them here:
https://console.cloud.google.com/marketplace/product/united-states-census-bureau/us-geographic-boundaries?hl=en&inv=1&invt=Ab0Hvw&project=gemini-sandbox-446215

Data Cleaning

I'm not going to run through all the code to clean the data. There wasn't anything too complex I had to do to get it into a useable form; datatypes, column names, nulls, etc. I did however filter this to the US only which is about 70,000 sightings, since the BigQuery geo data is for the US only. You can follow along in my GitHub repo if you are interested. 

GitHub Repo: https://github.com/sam-tritto/ufo-sightings/blob/main/UFO_Sightings.ipynb

BigQuery Geo Functions

Now we can explore Google's fantastic suite of geo functions for BigQuery. They have everything from functions that measure distance, boundaries, intersections, to even clustering. I'm just going to stick to some of the most common/useful ones for this project but encourage you to read through them all here:

https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions

We can take a quick look at some that I'll use later on straight from their documentation. 

Within County

The first thing we will explore is fltering for all UFO sighting reports in Dutchess County. This is in the heart of the Hudson Valley and will allow us to use the ST_WITHIN() function. It would be nice to be able to simply join the two tables together, but our UFO sightings data only has city and state. Also the public county geography table does not have a record for each city in each county. With third party data, or any data for that matter, there is no garuntee that the spellings, case, spaces match between textual columns. It will be a much safer bet to use the latitude and longitude of each UFO sighting report and see if it is contained in the geo boundary for Dutchess County. 

Utilizing a cte can help both with readability and optimization. Since we already know we'd like to focus on Dutchess County we can pre filter that data ahead of the join which will greatly reduce time and cost. We could have also used another cte for the UFO data if there were any filtering or manipulations we would have needed to do there, such as year or type of craft. Filtering in the WHERE clause of the join is always an option too, but since the JOIN clause comes before the WHERE clause in the SQL order of operations we really should pre filter our data.  You'll notice this is a CROSS JOIN between the two tables so thiking about filtering and optimizations is crucial since the number of records can spin out of control pretty fast. 

The ST_WTHIN() function takes a geo point coordinate and a geo boundary as inputs. The us boundary table already contains a MULTIPOLYGON column named county_geom, this is what ST_WITHIN() accepts. The UFO sightings data however only has coordinate points, so we will us ST_GEOPOINT() to convert this into a useable geography. ST_WITHIN() then returns a boolean True or False for each record so when used in the WHERE clause, becomes an efficient filter. 

Within Zip Code

Here's a similar query but using the zip code boundary table. Zip Codes are interesting since they can span multiple cities. Since our UFO dataset only has City and State this query might have returned multiple cities for this zip code, but it did not for our select zip code.

Distance Between

Now we can try out a different function to find the distance between Dutchess County and any other County where a chevron or V-shaped craft was seen. We'll use the ST_DISTANCE() function which returns the distance in meters which we can convert into miles. This isn't street driving distance, but rather "as the bird flies" distance or the distance on top of a sphere which is perfect for this UFO case study. The Google Maps API is a great choice if you need driving distance, but it is costly. There's also geopy a free and open sourced alternative if you are comfortable with python.

I'll start building some CTEs to split up the query. First I'll isolate Dutchess County so we can measure the distance from here. You'll notice that while we've previously used the geo border of the County now we are relying on the County's internal coordinate points which are also available in these boundary datasets. These latitudes and longitudes represent the central most point within each County's border.

Clustering Chevron Sightings

Now we can explore some ML in BigQuery with the DBSCAN algorithm and the ST_CLUSTERDBSCAN() function. We'll also use plotly, an interactive python visualization library, to show these clusters. 

DBSCAN, which stands for Density-Based Spatial Clustering of Applications with Noise, is a powerful clustering algorithm that groups together points that are closely packed together, marking as outliers points that lie alone in low-density regions. Unlike algorithms like K-Means, you don't need to specify the number of clusters beforehand, and it can find arbitrarily shaped clusters. 

DBSCAN defines clusters based on the concept of "density." A cluster is a region of high data point density, separated by regions of lower density. There are just two parameters...

Two Key Parameters:

• epsilon: This is the maximum distance between two points for them to be considered "neighbors." Imagine drawing a circle of radius epsilon around a point; any other points within this circle are its neighbors.

• min_geographies: This is the minimum number of points required within the epsilon radius for a point to be considered a "core point."

Point Types: DBSCAN classifies each data point into one of three types:

• Core Point: A point is a core point if it has at least min_geographies (including itself) within its epsilon neighborhood. These are the "heart" of a dense region.

• Border Point: A point is a border point if it has fewer than min_geographies within its epsilon neighborhood, but it is within the epsilon neighborhood of a core point. These points are on the "edge" of a cluster.

• Noise Point (Outlier): A point is a noise point if it is neither a core point nor a border point. These points are considered outliers or noise and don't belong to any cluster.