Scripting, and more Scripting!

From adding latitude and longitude to my initial datasets, to importing my datasets into my database and data retrieval into my Power Bi reports, I relied heavily on Python scripting.

I utilized three libraries for my script Coordinates.py: the Nominatim library from Geopy.Geocoders, Openpyxl, and Time. Openpyxl is the library I used to open, read, and write coordinates to spreadsheet files which were later imported into my database. Nominatim is a library that can be called and returns latitude and longitude coordinates if provided information about a location including an address. For this project I used an aircraft’s city, state, and country as parameters. I created separate functions to return latitude and longitude to keep the process separate as I was learning. Looking back now, keeping the function as one, rather than breaking it up, may have the ability to save on overall run time which would be beneficial. Especially when working with a large dataset.

Error Catching:

Because the Nominatim library from geopy relies on returning coordinates over a port, I learned early on that time outs can occur. In addition to this, if a row of information was not complete, my script would throw an error. I created exception handling for this in order to remove data that was not complete and usable from my dataset. The image above contains Exception logic to remove a row if an aircraft’s tail number is missing.

Creating Database Tables and Importing Data

For fun, I decided to try using PHP for my table creation, and Python for importing data into my database. After using both, I thought Python was the better language to use. One thing I really enjoy about Python is that it can be used for so many different types of tasks, the libraries provide so many useful features, and it is fairly easy to learn and write.

When adding new data into my database, my script first checks to see if the information has already present in the table to prevent the creation of duplicate rows, if it is not present, then the new row is added.