Snowflake
Published in

Snowflake

Plotting F1 Circuit Locations into a Map using Folium

Plotting F1 Circuit Locations into a map using Folium

This Notebook is a continuation of a previous blogpost where I started loading F1 Data into Snowflake ❄️ using the Ergast API. Next up is plotting this data on a map using Folium. Folium is a Python library used for visualizing Geospatial Data. “It builds on the data wrangling strengths of the Python ecosystem and the mapping strengths of the Leaflet.js library. Manipulate your data in Python, then visualize it in a Leaflet map via folium.”

Follow the steps below to get things started.

  • Next install all the necessary library’s
  • Import necessary library’s
  • Create a function to get a Continent based on the Country and
  • Create a function to define the marker color based on the Continent
  • Setting up a Snowflake connection
  • After setting the connection to Snowflake it’s time to connect to the database, verify the connection and get the data out of the Circuits-table
  • Convert the Data output to a Pandas Data Frame so we can plot this Data on a Folium Map
  • Create the Folium Map. In this example 3 different variants:
  • World Map
  • Continent Map (Europe)
  • Country Map (France)

Importing necessary library’s

First install (if missing in the environment) and import all necessary library’s.

!pip install snowflake-connector-python # Connect to Snowflake
!pip install folium # Create the Geospatial Map
!pip install pycountry_convert # Get Continent based on Country
import pandas as pd # Creating Data Frames
import snowflake.connector # Connect to Snowflake
import json # Read the Snowflake Credential File
import folium # Create the Geospatial Map
import pycountry_convert as pc # Get Continent based on Country

Create functions

Create functions to get a Continent based on the Country and to define the marker color based on the Continent.

# Get Continent based on Country
def country_convert(country_name):

try:

country_code = pc.country_name_to_country_alpha2(country_name, cn_name_format='default')

continent_name = pc.country_alpha2_to_continent_code(country_code)

return pc.convert_continent_code_to_continent_name(continent_name)

except (KeyError, TypeError):

return (country_name)

# Base color on Continent
# 'darkblue', 'white', 'lightblue', 'pink', 'gray', 'green', 'orange', 'darkred', 'black', 'blue', 'cadetblue'
#, 'lightgreen', 'purple', 'darkgreen', 'red', 'beige', 'lightred', 'darkpurple', 'lightgray'

## 'Oceania', 'Asia', 'Europe', 'North America', 'UK', 'South America', 'UAE',
## 'Africa', 'Korea'

# Define the marker color based on the continent
def marker_color(continent_name):
if continent_name == 'Asia':
color = 'pink'
elif continent_name == 'Africa':
color = 'green'
elif continent_name == 'Europe':
color = 'blue'
elif continent_name == 'North America':
color = 'red'
elif continent_name == 'South America':
color = 'orange'
elif continent_name == 'Oceania':
color = 'purple'
elif continent_name == 'UK':
color = 'beige'
elif continent_name == 'UAE':
color = 'lightgreen'
elif continent_name == 'Korea':
color = 'cadetblue'
else:
color = 'grey'
return color

Connecting to Snowflake

Setup the connection to Snowflake and verify whether this connection works. After that, connect to the database and schema where the Circuits-table resides. Finally select the F1-circuits.

# Setting up Snowflake connection 
conn_location = '<CONN_LOC>'

connect = json.loads(open(str(conn_location + '/F1cred.json')).read())

username = connect['secrets']['username']
password = connect['secrets']['password']
account = connect['secrets']['account']
role = connect['secrets']['role']

# Connect to Snowflake

conn = snowflake.connector.connect(
user = username,
password = password,
account = account,
role = role
)
# Show connected user
conn.cursor().execute("SELECT CURRENT_USER()")
# Connect to DEMO_DB database
demo_db = connect['secrets']['database'].upper()
conn.cursor().execute("USE DATABASE " + demo_db)
# Connect to PRE_F1PY schema
f1_pre_schema = 'PRE_F1PY'

conn.cursor().execute("USE SCHEMA " + f1_pre_schema)

# Select Query F1 Circuits

sql_f1_circuits = """select replace(name, '"','') as name
, lat
, lng
, replace(country, '"','') as country
, replace(url, '"','') as url
from demo_db.pre_f1py.pre_f1py_circuits
"""
# Query F1 Circuits
cursor = conn.cursor()
cursor.execute(sql_f1_circuits)

Creating Folium Maps

Create the Folium Map. In this example 3 different variants:

  • Continent Map (Europe)
  • Country Map (France)
# Convert Query output to a DataFrame
df_f1_circuits = cursor.fetch_pandas_all()

# Add Continent to df_f1_circuits DataFrame
df_f1_circuits['CONTINENT'] = df_f1_circuits['COUNTRY'].apply(country_convert)

df_f1_circuits.head()
print(df_f1_circuits['CONTINENT'].unique())

World Map

# Creating the Folium Map 
CircuitsMap = folium.Map(location=[df_f1_circuits.LAT.mean(), df_f1_circuits.LNG.mean()], zoom_start=2, control_scale=True, tiles='openstreetmap')

# Adding Tile Layers
folium.TileLayer('openstreetmap').add_to(CircuitsMap)
folium.TileLayer('cartodb positron').add_to(CircuitsMap)
folium.TileLayer('stamenterrain').add_to(CircuitsMap)
folium.TileLayer('stamentoner').add_to(CircuitsMap)
folium.TileLayer('stamenwatercolor').add_to(CircuitsMap)
folium.TileLayer('cartodbdark_matter').add_to(CircuitsMap)

# Other mapping code (e.g. lines, markers etc.)
folium.LayerControl().add_to(CircuitsMap)

for index, location_info in df_f1_circuits.iterrows():
folium.Marker([location_info["LAT"], location_info["LNG"]], popup='<a href=' + location_info["URL"] + ' target="_blank">' + location_info["NAME"] + '</a>', icon=folium.Icon(icon_color='white', icon="car", prefix='fa', color=marker_color(location_info["CONTINENT"]))).add_to(CircuitsMap)
CircuitsMap

Continent Map (Europe)

# Filter df_f1_circuits DataFrame on 'Europe' Continent
df_f1_eur_circuits = df_f1_circuits.loc[df_f1_circuits['CONTINENT'] == 'Europe']

df_f1_eur_circuits.head()
# Creating the Continent Folium Map
CircuitsContinentMap = folium.Map(location=[df_f1_eur_circuits.LAT.mean(), df_f1_eur_circuits.LNG.mean()], zoom_start=5, control_scale=True, tiles='openstreetmap')

# Adding Tile Layers
folium.TileLayer('openstreetmap').add_to(CircuitsContinentMap)
folium.TileLayer('cartodb positron').add_to(CircuitsContinentMap)
folium.TileLayer('stamenterrain').add_to(CircuitsContinentMap)
folium.TileLayer('stamentoner').add_to(CircuitsContinentMap)
folium.TileLayer('stamenwatercolor').add_to(CircuitsContinentMap)
folium.TileLayer('cartodbdark_matter').add_to(CircuitsContinentMap)

# Other mapping code (e.g. lines, markers etc.)
folium.LayerControl().add_to(CircuitsContinentMap)

for index, location_info in df_f1_eur_circuits.iterrows():
folium.Marker([location_info["LAT"], location_info["LNG"]], popup='<a href=' + location_info["URL"] + ' target="_blank">' + location_info["NAME"] + '</a>', icon=folium.Icon(icon_color='white', icon="car", prefix='fa', color=marker_color(location_info["CONTINENT"]))).add_to(CircuitsContinentMap)
CircuitsContinentMap

Country Map (France)

# Filter df_f1_circuits DataFrame on Country
df_f1_country_circuits = df_f1_circuits.loc[df_f1_circuits['COUNTRY'] == 'France']

df_f1_country_circuits.head()
# Creating the Country Folium Map
CircuitsCountryMap = folium.Map(location=[df_f1_country_circuits.LAT.mean(), df_f1_country_circuits.LNG.mean()], zoom_start=5, control_scale=True, tiles='openstreetmap')

# Adding Tile Layers
folium.TileLayer('openstreetmap').add_to(CircuitsCountryMap)
folium.TileLayer('cartodb positron').add_to(CircuitsCountryMap)
folium.TileLayer('stamenterrain').add_to(CircuitsCountryMap)
folium.TileLayer('stamentoner').add_to(CircuitsCountryMap)
folium.TileLayer('stamenwatercolor').add_to(CircuitsCountryMap)
folium.TileLayer('cartodbdark_matter').add_to(CircuitsCountryMap)

# Other mapping code (e.g. lines, markers etc.)
folium.LayerControl().add_to(CircuitsCountryMap)

for index, location_info in df_f1_country_circuits.iterrows():
folium.Marker([location_info["LAT"], location_info["LNG"]], popup='<a href=' + location_info["URL"] + ' target="_blank">' + location_info["NAME"] + '</a>', icon=folium.Icon(icon_color='white', icon="car", prefix='fa', color='darkgreen')).add_to(CircuitsCountryMap)
CircuitsCountryMap

Closing Statements

This example provided the steps to plot F1 Circuit Locations into a map using Folium; a Python library used for visualizing Geospatial Data. Please check it out and let me know what you think. The code is on Github. In a next example I will covert this code into a Streamlit Application.

Till next time.

Daan Bakboord — DaAnalytics

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Daan Bakboord

Cloud ☁️ Data & Analytics 📊 Engineer @ DaAnalytics | Manager Data & Analytics @ Pong | Snowflake ❄️ Data Superhero | Modern Cloud ☁️ Data Stack enthusiast