After over a year of slowly learning python, making a few scripts here and there, I'm always looking for ways to Automate the Boring Stuff with Python.

Recently, my employer upgraded to HolebaseSI Professional and all our projects are all about location location location. One great feature with Holebase is that you can click a point on a map and the OSGB coordinates are automatically entered into the database. Then you can produce geological sections etc. with just a few clicks, but elevation is quite important for a proper appraisal of the geology in a section.

As a disclaimer in this case, due to the arguably inaccurate method of obtaining coodinates for the locations in Holebase when compared to the properly surveyed site. The use of potentially inaccurate elevations is balanced against the need for data.

ABOVE: Section without elevation data.

However with some context of the site, BH1 is actually at least 2 or 3 metres higher than BH6, so using the above section would not be very useful. Holebase gets its coordinates from the Bing Maps API which can return elevations, but for some reason Keynetix don't use it. Maybe because Bing Maps API (at least in the UK) returns elevations as integers which makes for a messy, artifical looking section. From a previous experiment I knew Google Maps API returns elevations as floats, which would make changes in elevation look more natural.

What to do next?!?

So now I had an idea of what work my script would need to do:

Location ID's - These are in Holebase which I can grab from the database using SQLAlchemy.
Elevations - I can then pass the coordinates to the Google Maps API which in turn will return the elevations.
Importable CSV file - Holebase very helpfully accepts CSV imports (making a valid AGS file would be too much work!), I just need to write my Location ID's along with elevations using the correct headers.

Fast forward a couple of hours primarily on google or stackexchange I come up with the code below:

Imports

Fairly self explanatory.



In [ ]:

    
import csv
import re
import googlemaps
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Setting initial variables

dp_password is the MSSQL db password not a user account password.

gapi_key is the API key google would provide, google's documentation is great.

My two empty lists.



In [ ]:

    
db_password = 'somestring'
gapi_key = 'anotherstring'

points = []
positions = []

User input for Project ID

Takes the users input and validates that the entry is compliant with our 'S' project number style.



In [ ]:

    
project_no = False

while project_no is False:
    try:
        string = input("Enter Project Number: ")
        pattern = re.compile(r"S\d{6}\Z")
        if pattern.match(string):
            project_no = string
        else:
            raise ValueError('ERROR: Not a correct project number')
    except ValueError as e:
        print(e)

SQLAlchemy code

For the create engine method I had to:

pip install pyodbc

And setup the Holebase database as a User DSN, search "ODBC data sources" in Windows and it should show up and make sure your on the User DSN tab and if you can setup the initial connection on Holebase SI this is pretty much the same thing.

I had to use a raw SQL query rather than use the SQLAlchemy querybuilder, this was due to Microsoft SQL Server encoding the longitude and latitude in a special format. The only documentation I could find on how to decode the data was to query using the .STAsText() method which I couldn't seem to get to work in a SQLAlchemy query.



In [ ]:

    
engine = create_engine('mssql+pyodbc://HolebaseSIUser:{password}@Holebase'.format(password=db_password))
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

result = session.execute("""SELECT COR_LocationDetails.LocationID, COR_LocationDetails.LongLatCentroid.STAsText() 
                            FROM COR_Project, COR_LocationDetails 
                            WHERE COR_Project.ProjectID = '{project_no}' 
                            AND COR_Project.Id = COR_LocationDetails.Project""".format(project_no=project_no))

session.close()

Looping through the data of each Location ID

I used a regular expression to match the part of the string I wanted between parentheses. Next splitting the match and assigning it long and lat, converting these to floats and then appending to my lists.

With hindsight, I see a lot wrong with this piece of code. I used two lists to make sending my request to google easier, but I could probably use a dictionary and a list comprehension which would read better. And the if statement is a mess.



In [ ]:

    
for i in result:
    if i[1] != None:
        match = re.findall(r'\((.*?)\)', i[1])
        long, lat = match[0].split(' ')
        points.append([float(lat), float(long)])
        positions.append([i[0]])
    else:
        pass

Making a request to Google Maps API

The googlemaps package makes this process so easy.



In [ ]:

    
gmaps = googlemaps.Client(key=gapi_key)

gmaps_elevation = gmaps.elevation(points)

I love enumerate

Previously, I passed in the points list to the elevation method, things could go wrong if google passed my data back out of order. Right now I rely; which I have checked with test data, on the correct order being returned and use enumerate to give me the right index to append the elevation data to the positions list.

I should probably setup a check of the coordinates sent against that returned, which would remove the worry of incorrect order and using a dictionary or a class data structure would make it easier too.



In [ ]:

    
for i, d in enumerate(gmaps_elevation):
    positions[i].append(round(d['elevation'], 3))

Creates a Holebase importable csv file

I checked the default data import settings which Holebase expects of a csv file and formatted accordingly. I found the correct column headers to use from the HoleBASE SI Data Entry Feedback Tool which is on Keynetix Assist, the download has a headers csv file which lists table names and valid table headers. In this case I name the file "Location Details" to match the table the data is to go into and "Location ID" and "Ground Level" are pretty self explanatory, though if we didn't include the Location ID obviously Holebase wouldn't know which "Ground Level" or elevation went with which Location ID.



In [ ]:

    
with open('Location Details.csv', 'w', newline='') as f:
    writer = csv.writer(f, quoting=csv.QUOTE_ALL)
    writer.writerow(['Location ID',
                    'Ground Level'])
    writer.writerows(positions)

Very messy and probably has a lot of improvable code, I'm hoping to come back to it when I have some spare time, but right now it works and I've hit no bugs ... yet.

ABOVE: Section with elevation data.

Now the end result once imported into Holebase is a nice section with a much more true elevation profile.