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

Recently, my employer upgraded to HolebaseSI Professional which has built in GIS capabilities and now our projects are all about location location location. One of the best features of HolebaseSI Professional is that you can select a Location ID then click a point on a map and the OSGB coordinates are automatically entered into the database. Using this data the software allows you to produce geological sections etc. But, it doesn't return the elevation above sea level which is quite important for a proper appraisal of the geology in a section.

ABOVE: Section without elevation data.

The above sectio looks okay however with some context of the site one would know that 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. However, after some research of the Bing Maps API (at least in the UK) I might have worked out why they didn't, the service only returns elevations as integers which makes for a messy and artifical looking section. From a previous experiment I knew that the Google Maps API returns elevation data as floats, which would make changes in elevation look more natural.

As a disclaimer in this case due to the software returning arguably inaccurate coordinates for the locations; when compared to a properly surveyed site, the use of potentially inaccurate elevations is acceptable.

What to do next?!?

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

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.