Running a marathon is a monumental undertaking. Even though I had been preparing for 1.8 years, it wasn’t until a few weeks ago that I truly grasped the significance of this feat. The turning point came when I made the decision to run for a charity organization in Brazil, specifically Casa da Crianca Paralítica de Campinas (https://www.ccp.org.br/web/). This organization provides vital medical, dental, and pedagogical support to children with physical and mental disabilities in Campinas, Sao Paulo State, Brazil.
Moreover, during my preparations, I delved into the origin of the marathon. It is rooted in the inspiring story of Phidippides, a herald who ran the grueling 26 miles from Marathon to Athens to deliver the news of Greek victory and tragically perished on the spot.
This newfound understanding has deepened my appreciation for the immense challenge and purpose behind running a marathon. It has brought me closer to the cause I am running for and has made the journey even more meaningful.
In addition to the incredible experience itself, I leveraged IoT devices to stay updated on various aspects of my body and speed. These insights were invaluable, and I diligently recorded them in the Strava application. Here are a few highlights of the information I captured:
By capturing the running information, I had the opportunity to download the GPX (Global Positioning XML) files that meticulously recorded GPS waypoints throughout my marathon journey. Leveraging this valuable data, I developed an application capable of visualizing the route on a map, plotting the elevation variation over distance, and providing detailed information at each kilometer. This included data such as latitude, longitude, elevation, and the corresponding time reached. With these features, I was able to gain comprehensive insights into my performance and accurately track my progress throughout the marathon:
One kilometer reached - Kilometer 1 Latitude: 59.346874 Longitude: 18.06763 Elevation: 36.4 Time: 2023-06-03 10:17:09+00:00 <<<-- the marathon starts at 12:00 pm not 10:00 am. One kilometer reached - Kilometer 2 Latitude: 59.342318 Longitude: 18.057401 Elevation: 23.4 Time: 2023-06-03 10:23:22+00:00 One kilometer reached - Kilometer 3 Latitude: 59.334876 Longitude: 18.060889 Elevation: 24.4 Time: 2023-06-03 10:29:20+00:00 One kilometer reached - Kilometer 4 Latitude: 59.336397 Longitude: 18.046855 Elevation: 18.8 Time: 2023-06-03 10:35:31+00:00 One kilometer reached - Kilometer 5 Latitude: 59.337395 Longitude: 18.035086 Elevation: 31.4 Time: 2023-06-03 10:41:54+00:00 One kilometer reached - Kilometer 6 Latitude: 59.333199 Longitude: 18.043528 Elevation: 24.3 Time: 2023-06-03 10:48:04+00:00 One kilometer reached - Kilometer 7 Latitude: 59.326784 Longitude: 18.045059 Elevation: 15.0 Time: 2023-06-03 10:53:49+00:00 One kilometer reached - Kilometer 8 Latitude: 59.328353 Longitude: 18.06119 Elevation: 12.9 Time: 2023-06-03 11:00:01+00:00 One kilometer reached - Kilometer 9 Latitude: 59.322674 Longitude: 18.070649 Elevation: 12.9 Time: 2023-06-03 11:06:16+00:00 One kilometer reached - Kilometer 10 Latitude: 59.328373 Longitude: 18.072838 Elevation: 16.5 Time: 2023-06-03 11:12:27+00:00 One kilometer reached - Kilometer 11 Latitude: 59.331817 Longitude: 18.083543 Elevation: 15.9 Time: 2023-06-03 11:18:42+00:00 One kilometer reached - Kilometer 12 Latitude: 59.336051 Longitude: 18.090822 Elevation: 25.4 Time: 2023-06-03 11:25:09+00:00 One kilometer reached - Kilometer 13 Latitude: 59.340122 Longitude: 18.078659 Elevation: 29.2 Time: 2023-06-03 11:31:55+00:00 One kilometer reached - Kilometer 14 Latitude: 59.341578 Longitude: 18.086899 Elevation: 29.6 Time: 2023-06-03 11:38:38+00:00 One kilometer reached - Kilometer 15 Latitude: 59.337827 Longitude: 18.102701 Elevation: 21.2 Time: 2023-06-03 11:44:44+00:00 One kilometer reached - Kilometer 16 Latitude: 59.33263 Longitude: 18.105322 Elevation: 12.8 Time: 2023-06-03 11:50:54+00:00 One kilometer reached - Kilometer 17 Latitude: 59.333129 Longitude: 18.122436 Elevation: 18.4 Time: 2023-06-03 11:57:20+00:00 One kilometer reached - Kilometer 18 Latitude: 59.328229 Longitude: 18.132758 Elevation: 13.6 Time: 2023-06-03 12:03:34+00:00 One kilometer reached - Kilometer 19 Latitude: 59.323245 Longitude: 18.127401 Elevation: 17.5 Time: 2023-06-03 12:10:17+00:00 One kilometer reached - Kilometer 20 Latitude: 59.322588 Longitude: 18.111348 Elevation: 13.1 Time: 2023-06-03 12:16:22+00:00 One kilometer reached - Kilometer 21 Latitude: 59.325796 Longitude: 18.096353 Elevation: 14.9 Time: 2023-06-03 12:22:42+00:00 One kilometer reached - Kilometer 22 Latitude: 59.331535 Longitude: 18.088168 Elevation: 14.2 Time: 2023-06-03 12:29:39+00:00 One kilometer reached - Kilometer 23 Latitude: 59.330183 Longitude: 18.078932 Elevation: 14.1 Time: 2023-06-03 12:35:48+00:00 One kilometer reached - Kilometer 24 Latitude: 59.324627 Longitude: 18.075818 Elevation: 14.2 Time: 2023-06-03 12:42:14+00:00 One kilometer reached - Kilometer 25 Latitude: 59.318594 Longitude: 18.078732 Elevation: 34.5 Time: 2023-06-03 12:48:42+00:00 One kilometer reached - Kilometer 26 Latitude: 59.314479 Longitude: 18.07504 Elevation: 38.1 Time: 2023-06-03 12:55:00+00:00 One kilometer reached - Kilometer 27 Latitude: 59.312194 Longitude: 18.062026 Elevation: 33.9 Time: 2023-06-03 13:01:16+00:00 One kilometer reached - Kilometer 28 Latitude: 59.31788 Longitude: 18.05432 Elevation: 34.3 Time: 2023-06-03 13:07:36+00:00 One kilometer reached - Kilometer 29 Latitude: 59.318504 Longitude: 18.039661 Elevation: 37.1 Time: 2023-06-03 13:14:52+00:00 One kilometer reached - Kilometer 30 Latitude: 59.323143 Longitude: 18.028996 Elevation: 37.5 Time: 2023-06-03 13:21:26+00:00 One kilometer reached - Kilometer 31 Latitude: 59.330603 Longitude: 18.021492 Elevation: 18.1 Time: 2023-06-03 13:28:05+00:00 One kilometer reached - Kilometer 32 Latitude: 59.327308 Longitude: 18.037927 Elevation: 14.6 Time: 2023-06-03 13:34:36+00:00 One kilometer reached - Kilometer 33 Latitude: 59.332945 Longitude: 18.043762 Elevation: 23.5 Time: 2023-06-03 13:41:15+00:00 One kilometer reached - Kilometer 34 Latitude: 59.337331 Longitude: 18.034871 Elevation: 30.4 Time: 2023-06-03 13:48:42+00:00 One kilometer reached - Kilometer 35 Latitude: 59.336522 Longitude: 18.046515 Elevation: 19.4 Time: 2023-06-03 13:55:52+00:00 One kilometer reached - Kilometer 36 Latitude: 59.331736 Longitude: 18.058298 Elevation: 16.7 Time: 2023-06-03 14:02:25+00:00 One kilometer reached - Kilometer 37 Latitude: 59.324959 Longitude: 18.066619 Elevation: 14.4 Time: 2023-06-03 14:09:49+00:00 One kilometer reached - Kilometer 38 Latitude: 59.325161 Longitude: 18.075494 Elevation: 15.4 Time: 2023-06-03 14:17:22+00:00 One kilometer reached - Kilometer 39 Latitude: 59.332495 Longitude: 18.076794 Elevation: 14.8 Time: 2023-06-03 14:25:01+00:00 One kilometer reached - Kilometer 40 Latitude: 59.332289 Longitude: 18.092964 Elevation: 16.9 Time: 2023-06-03 14:32:28+00:00 One kilometer reached - Kilometer 41 Latitude: 59.338685 Longitude: 18.086357 Elevation: 29.6 Time: 2023-06-03 14:40:18+00:00 One kilometer reached - Kilometer 42 Latitude: 59.343693 Longitude: 18.077884 Elevation: 34.4 Time: 2023-06-03 14:47:47+00:00
The code that generated the aforementioned information and visualizations can be found in its entirety on my GitHub repository. You can access it at the following link: GitHub – Stockholm Marathon 2023. Feel free to explore the code and use it as a reference for your own projects or further analysis of marathon data.
But how can these details be implemented in cloud computing? To address this question, I made the decision to store the data in databases provided by different cloud providers. Specifically, I chose to utilize Oracle Database (or MySQL) on Oracle Cloud Infrastructure (OCI), Amazon RDS (Relational Database Service) on Amazon Web Services (AWS), Cloud SQL (MySQL) on Google Cloud Platform (GCP), and Azure SQL Database on Microsoft Azure.
By leveraging these cloud database solutions, I ensured efficient data storage and management for my marathon-related information. Oracle Database and MySQL, both offering robust capabilities, served as excellent choices for OCI, including Arm Ampere A1 Compute. Additionally, Amazon RDS provided a wide range of database engine options on AWS, GCP’s Cloud SQL supported MySQL and PostgreSQL, and Azure SQL Database emerged as a fully managed service on Microsoft Azure. With these diverse offerings, I could effectively meet my data storage needs across multiple cloud platforms.
With the database services offered by each cloud provider, I was able to securely store and access my marathon data in a scalable and reliable manner. This approach allowed me to fully leverage the unique features and benefits provided by each cloud provider. Below, you can see a visual representation of the usage of OCI through the Autonomous Database Service, as well as the information required to establish connections to the other providers in the code.
To connect to Oracle Cloud Infrastructure (OCI) and create a table named marathon_data2023, I used the following code, which is also available on my GitHub:
# Establish a connection
import cx_Oracle
import os
# Set the environment variable for the wallet location
os.environ["TNS_ADMIN"] = "/Users/brunotechdatabasket/Downloads/Wallet_marathondb"
# Set the TNS entry in tnsnames.ora
with open(os.path.join(os.environ["TNS_ADMIN"], "tnsnames.ora"), "w") as tns_file:
tns_file.write("ORACLEDB =\n"
"(DESCRIPTION =\n"
" (ADDRESS = (PROTOCOL = TCPS)(HOST = adb.eu-stockholm-1.oraclecloud.com)(PORT = 1522))\n"
" (CONNECT_DATA =\n"
" (SERVER = DEDICATED)\n"
" (SERVICE_NAME = g114b7e420dfb56_marathondb_tpurgent.adb.oraclecloud.com)\n"
" )\n"
" (SECURITY =\n"
" (SSL_SERVER_DN_MATCH = YES)\n"
" )\n"
")\n")
# Set the SSL options in sqlnet.ora
with open(os.path.join(os.environ["TNS_ADMIN"], "sqlnet.ora"), "w") as sqlnet_file:
sqlnet_file.write("WALLET_LOCATION =\n"
" (SOURCE =\n"
" (METHOD = FILE)\n"
" (METHOD_DATA =\n"
" (DIRECTORY = /Users/brunotechdatabasket/Downloads/Wallet_marathondb)\n"
" )\n"
" )\n")
# Test the connection using Oracle Wallet Manager
try:
cx_Oracle.connect("/", mode=cx_Oracle.SYSDBA)
print("Wallet connection test successful.")
except cx_Oracle.DatabaseError as e:
print("Error occurred while testing wallet connection:", e)
# Establish a connection
connection = cx_Oracle.connect(
"ADMIN",
"yourpasswordhere",
"ORACLEDB"
)
# Create a cursor
cursor = connection.cursor()
# Create a table (if necessary)
cursor.execute("CREATE TABLE marathon_data2023 (latitude NUMBER, longitude NUMBER, elevation NUMBER, time TIMESTAMP)")
# Insert data into the table
for point in points:
cursor.execute("INSERT INTO marathon_data2023 VALUES (:1, :2, :3, :4)",
(point.latitude, point.longitude, point.elevation, point.time))
# Commit the changes
connection.commit()
# Select data from the table
cursor.execute("SELECT * FROM marathon_data2023")
result = cursor.fetchall()
# Print the selected data
for row in result:
print(row)
# Close the cursor and connection
cursor.close()
connection.close()
As expected, the message ‘Error occurred while testing wallet connection: ORA-12162: TNS:net service name is incorrectly specified’ was displayed. I included this verification step solely for educational purposes to check the utilization of the wallet. In this implementation, I have been connecting via Transport Layer Security (TLS) connections. The output is shown below:
The same implementation is also available for Amazon Web Services (AWS) using Amazon RDS (Relational Database Service), Google Cloud Platform (GCP) using Cloud SQL (MySQL), and Microsoft Azure using Azure SQL Database in the Python file included in the repository. Adopting a multi-cloud strategy offers increased flexibility, scalability, and reliability, enabling organizations to leverage the unique strengths of multiple cloud providers. As mentioned earlier, you can find the implementation codes for each cloud platform in the GitHub repository [2]. I hope this blog post has provided valuable insights into the journey from Stockholm Marathon to a multi-cloud strategy, demonstrating the potential of storing IoT data in cloud computing environments.
References:
[1] Oracle Documentation: “Preparing to Connect to Autonomous Database” – Available at: https://aws.amazon.com/rds/
[2] GitHub Repository: “stockholm_marathon2023” – Available at: https://github.com/brunorsreis/stockholm_marathon2023
The full code content in the file Stockholm_marathon_2023.py follows below:
import gpxpy
import matplotlib.pyplot as plt
# Read the GPX file
gpx_file = open('Stockholm_Marathon_2023.gpx', 'r')
gpx = gpxpy.parse(gpx_file)
# Extract track data
tracks = gpx.tracks
if len(tracks) > 0:
track = tracks[0] # Assuming only one track in the GPX file
segments = track.segments
if len(segments) > 0:
segment = segments[0] # Assuming only one segment in the track
points = segment.points
# Extract latitude, longitude, elevation, and time data
lats = [point.latitude for point in points]
lngs = [point.longitude for point in points]
elevations = [point.elevation for point in points]
times = [point.time for point in points]
# Visualize the data in different ways
# Plotting the elevation profile
#plt.figure(figsize=(10, 5))
#plt.plot(times, elevations)
#plt.xlabel('Time')
#plt.ylabel('Elevation (m)')
#plt.title('Elevation Profile')
#plt.show()
# Plotting the route on a map
plt.figure(figsize=(8, 8))
plt.plot(lngs, lats, color='blue')
plt.xlabel('Longitude')
plt.ylabel('Latitude')
plt.title('Route on Map')
plt.axis('equal')
plt.show()
# Scatter plot of elevation over distance
distances = [point.distance_2d(points[i-1]) if i > 0 else 0 for i, point in enumerate(points)]
plt.figure(figsize=(10, 5))
plt.scatter(distances, elevations, s=5)
plt.xlabel('Distance (m)')
plt.ylabel('Elevation (m)')
plt.title('Elevation Variation over Distance')
plt.show()
else:
print('No segments found in the GPX file.')
else:
print('No tracks found in the GPX file.')
# Display information at each kilometer
total_distance = 0
prev_point = None
kilometer_count = 1
for point in points:
if prev_point is not None:
distance = point.distance_2d(prev_point)
total_distance += distance
if total_distance >= 1000: # Check if the total distance is equal to or greater than 1 kilometer
print("One kilometer reached - Kilometer", kilometer_count)
print("Latitude:", point.latitude)
print("Longitude:", point.longitude)
print("Elevation:", point.elevation)
print("Time:", point.time)
print() # Empty line for separation
total_distance = 0 # Reset the total distance
kilometer_count += 1 # Increment the kilometer count
prev_point = point
import gpxpy
import matplotlib.pyplot as plt
from sklearn.cluster import KMeans
# Plot the GPS points
plt.scatter(lngs, lats, c='blue', alpha=0.5)
# Perform clustering for visualization
kmeans = KMeans(n_clusters=5) # Adjust the number of clusters as desired
kmeans.fit(list(zip(lats, lngs)))
# Plot the cluster centers
plt.scatter(kmeans.cluster_centers_[:, 1], kmeans.cluster_centers_[:, 0], c='red', marker='x')
# Display the plot
plt.xlabel('Longitude')
plt.ylabel('Latitude')
plt.title('Visualization of GPS Points with Cluster Centers')
plt.show()
Establish a connection
import cx_Oracle
import os
# Set the environment variable for the wallet location
os.environ["TNS_ADMIN"] = "/Users/brunotechdatabasket/Downloads/Wallet_marathondb"
# Set the TNS entry in tnsnames.ora
with open(os.path.join(os.environ["TNS_ADMIN"], "tnsnames.ora"), "w") as tns_file:
tns_file.write("ORACLEDB =\n"
"(DESCRIPTION =\n"
" (ADDRESS = (PROTOCOL = TCPS)(HOST = adb.eu-stockholm-1.oraclecloud.com)(PORT = 1522))\n"
" (CONNECT_DATA =\n"
" (SERVER = DEDICATED)\n"
" (SERVICE_NAME = g114b7e420dfb56_marathondb_tpurgent.adb.oraclecloud.com)\n"
" )\n"
" (SECURITY =\n"
" (SSL_SERVER_DN_MATCH = YES)\n"
" )\n"
")\n")
# Set the SSL options in sqlnet.ora
with open(os.path.join(os.environ["TNS_ADMIN"], "sqlnet.ora"), "w") as sqlnet_file:
sqlnet_file.write("WALLET_LOCATION =\n"
" (SOURCE =\n"
" (METHOD = FILE)\n"
" (METHOD_DATA =\n"
" (DIRECTORY = /Users/brunotechdatabasket/Downloads/Wallet_marathondb)\n"
" )\n"
" )\n")
# Test the connection using Oracle Wallet Manager
try:
cx_Oracle.connect("/", mode=cx_Oracle.SYSDBA)
print("Wallet connection test successful.")
except cx_Oracle.DatabaseError as e:
print("Error occurred while testing wallet connection:", e)
# Establish a connection
connection = cx_Oracle.connect(
"ADMIN",
"yourpasswordhere",
"ORACLEDB"
)
# Create a cursor
cursor = connection.cursor()
# Create a table (if necessary)
cursor.execute("CREATE TABLE marathon_data2023 (latitude NUMBER, longitude NUMBER, elevation NUMBER, time TIMESTAMP)")
# Insert data into the table
for point in points:
cursor.execute("INSERT INTO marathon_data2023 VALUES (:1, :2, :3, :4)",
(point.latitude, point.longitude, point.elevation, point.time))
# Commit the changes
connection.commit()
# Select data from the table
cursor.execute("SELECT * FROM marathon_data2023")
result = cursor.fetchall()
# Print the selected data
for row in result:
print(row)
# Close the cursor and connection
cursor.close()
connection.close()
#Connection to Amazon Web Services (AWS) using Amazon RDS (Relational Database Service):
import pymysql
# Establish a connection
connection = pymysql.connect(host='your_host', user='your_user', password='your_password', db='your_database')
# Create a cursor
cursor = connection.cursor()
# Create a table (if necessary)
cursor.execute("CREATE TABLE marathon_data (latitude FLOAT, longitude FLOAT, elevation FLOAT, time TIMESTAMP)")
# Insert data into the table
for point in points:
cursor.execute("INSERT INTO marathon_data VALUES (%s, %s, %s, %s)",
(point.latitude, point.longitude, point.elevation, point.time))
# Commit the changes
connection.commit()
# Close the cursor and connection
cursor.close()
connection.close()
#Connecting to Google Cloud Platform (GCP) using Cloud SQL (MySQL):
from google.cloud import bigquery
# Set up authentication credentials (if required)
# ...
# Establish a connection
client = bigquery.Client()
# Create a dataset (if necessary)
dataset_ref = client.create_dataset('my_dataset')
# Create a table
table_ref = dataset_ref.table('marathon_data')
schema = [
bigquery.SchemaField('latitude', 'FLOAT'),
bigquery.SchemaField('longitude', 'FLOAT'),
bigquery.SchemaField('elevation', 'FLOAT'),
bigquery.SchemaField('time', 'TIMESTAMP'),
]
table = bigquery.Table(table_ref, schema=schema)
table = client.create_table(table)
# Insert data into the table
rows_to_insert = []
for point in points:
rows_to_insert.append({'latitude': point.latitude, 'longitude': point.longitude,
'elevation': point.elevation, 'time': point.time})
client.insert_rows(table, rows_to_insert)
#Microsoft Azure using Azure SQL Database:
import pyodbc
# Establish a connection
connection = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};Server=your_server;Database=your_database;UID=your_username;PWD=your_password')
# Create a cursor
cursor = connection.cursor()
# Create a table (if necessary)
cursor.execute("CREATE TABLE marathon_data (latitude FLOAT, longitude FLOAT, elevation FLOAT, time DATETIME)")
# Insert data into the table
for point in points:
cursor.execute("INSERT INTO marathon_data VALUES (?, ?, ?, ?)",
(point.latitude, point.longitude, point.elevation, point.time))
# Commit the changes
connection.commit()
# Close the cursor and connection
cursor.close()
connection.close()
[3] Amazon Web Services (AWS) Documentation: “Amazon RDS” – Available at: https://aws.amazon.com/rds/
[4] Microsoft Azure Documentation: “Azure SQL Database” – Available at: https://azure.microsoft.com/services/sql-database/
[5] Google Cloud Platform (GCP) Documentation: “Cloud SQL” – Available at: https://cloud.google.com/sql/
[6] Oracle Cloud Infrastructure (OCI) Sign-In – Available at: https://www.oracle.com/cloud/sign-in.html
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)