Creating a Drilling Campaign Overview in QGIS

'''Import libraries'''
import requests

'''define CRS and file paths'''
project_crs = "EPSG:3067"
drillholes_path = "C:/Users/petri/OneDrive/Documents/LOGBOARD/Testing/Database/drillholes_export.csv"
daily_drillings_path ="C:/Users/petri/OneDrive/Documents/LOGBOARD/Testing/Database/daily_drillings_export.csv"
campaign_style_path = "C:/Users/petri/OneDrive/Documents/QGIS/Logboard/Styles/Logboard_campaign_overview.qml"
statistics_style_path = "C:/Users/petri/OneDrive/Documents/QGIS/Logboard/Styles/Logboard_campaign_statistics.qml"

'''Add basemap layer'''
# Define the Google Satellite Tile URL
service_url = "mt1.google.com/vt/lyrs=s&x={x}&y={y}&z={z}" 
service_uri = f"type=xyz&zmin=0&zmax=21&url=https://{requests.utils.quote(service_url)}"

# Add the layer to QGIS
tms_layer = iface.addRasterLayer(service_uri, "Google Satellite", "wms")

# Set Opacity (if needed)
if tms_layer.isValid():
    tms_layer.setOpacity(0.7)
    print("Google Satellite layer added successfully with 70% opacity.")
    
    # Set Project CRS
    project = QgsProject.instance()
    project.setCrs(QgsCoordinateReferenceSystem(project_crs))
    

'''Add drillholes and daily drilling data'''
# Function to Add CSV Layer
def add_csv_layers():

    # Construct CSV URIs
    uri_drillholes = (
        f"file:///{drillholes_path}"
        f"?delimiter=,&xField=PLN_E&yField=PLN_N&crs={project_crs}"
    )
    
    uri_drilling_stats = (
        f"file:///{daily_drillings_path}"
        f"?delimiter=,"
    )
    
    
    # Create Layer
    drillholes = QgsVectorLayer(uri_drillholes, "Drillholes", "delimitedtext")
    drilling_stats = QgsVectorLayer(uri_drilling_stats, "Daily Drilling", "delimitedtext")
    
    if not drillholes.isValid():
        print("Failed to load CSV layer!")
        print(drillholes.errorString())
    elif not drilling_stats.isValid():
        print("Failed to load CSV layer!")
        print(drilling_stats.errorString())
    else:
        # Add to project
        QgsProject.instance().addMapLayer(drillholes)
        QgsProject.instance().addMapLayer(drilling_stats)
        print("CSVs layer added successfully with CRS ", project_crs)
        
        # Zoom to Layer Extent
        iface.mapCanvas().setExtent(drillholes.extent())
        iface.mapCanvas().refresh()
        print("Map canvas refreshed and zoomed to CSV layer extent.")
        

'''Apply styles to Drillholes layer'''
# Function to Apply Style to a Layer
def apply_style_to_layer(layer_name, style_file_path):

    # Find the layer by name
    layer = QgsProject.instance().mapLayersByName(layer_name)
    
    layer = layer[0]  # Get the first matching layer
    
    # Apply the QML style
    layer.loadNamedStyle(style_file_path)
    layer.triggerRepaint()


add_csv_layers()
apply_style_to_layer("Drillholes", campaign_style_path)

'''Access the layers'''
drillholes_layer = QgsProject.instance().mapLayersByName('Drillholes')[0]
daily_drillings_layer = QgsProject.instance().mapLayersByName('Daily Drilling')[0]

provider_drillholes = drillholes_layer.dataProvider()
provider_daily_drillings = daily_drillings_layer.dataProvider()

'''Calculate total drillholes'''
drillholes = provider_drillholes.uniqueValues(drillholes_layer.fields().indexFromName('HOLEID'))
drillholes_count = len(drillholes)

'''Initialize counters'''
drillholes_meters = 0
drilled_meters = 0
drillholes_drilled = []
non_ongoing_sum = 0
ongoing_sum = 0

'''Loop through drillholes for combined calculations'''
for feature in drillholes_layer.getFeatures():
    status = feature['STATUS']
    pln_depth = feature['PLN_DEPTH']
    depth = feature['DEPTH']

    # Total planned meters
    drillholes_meters += pln_depth

    # Total drilled meters
    drilled_meters += depth

    # Count finished or aborted holes
    if status in ['Finished', 'Aborted']:
        drillholes_drilled.append(feature['HOLEID'])
    
    # Calculate non-ongoing depth
    if status not in ['Ongoing', 'Finished', 'Aborted']:
        non_ongoing_sum += pln_depth
    
    # Calculate ongoing depth
    elif status == 'Ongoing':
        remaining_depth = max(pln_depth - depth, 0)
        ongoing_sum += remaining_depth

# Final drilled holes count
drilled_count = len(drillholes_drilled)

'''Calculate days drilled'''
days = provider_daily_drillings.uniqueValues(daily_drillings_layer.fields().indexFromName('DRILLING_DATE'))
days_drilled = len(days)

'''Calculate drilling rate (with zero division protection)'''
drilling_rate = drilled_meters / days_drilled if days_drilled > 0 else 0

'''Calculate meters left to drill'''
left_to_drill = non_ongoing_sum + ongoing_sum

'''Calculate days left to drill (with zero division protection)'''
days_left = left_to_drill / drilling_rate if drilling_rate > 0 else 0

'''Create add point for visualizing statistics'''
# Find the northwestern drillhole
northwest_x = float('inf')
northwest_y = float('-inf')

for feature in drillholes_layer.getFeatures():
    geom = feature.geometry()
    if geom and geom.type() == 0:  # Check if it's a point geometry
        point = geom.asPoint()
        x, y = point.x(), point.y()
        
        # Check if this point is more northwestern
        if x < northwest_x or (x == northwest_x and y > northwest_y):
            northwest_x = x
            northwest_y = y

# Offset the point 50m north
northwest_y += 50  # Add 50m to the Y-coordinate

'''Create a vector layer for displaying statistics'''
layer = QgsVectorLayer('Point?crs='+project_crs, 'Logboard Campaign Statistics', 'memory')
provider = layer.dataProvider()

# Define the fields (attributes) for the layer
provider.addAttributes([
    QgsField('Total_Drillholes', QVariant.Int),
    QgsField('Total_Meters', QVariant.Double),
    QgsField('Drilled_Holes', QVariant.Int),
    QgsField('Drilled_Meters', QVariant.Double),
    QgsField('Days_Drilled', QVariant.Int),
    QgsField('Drilling_Rate', QVariant.Double),
    QgsField('Left_to_Drill', QVariant.Double),
    QgsField('Days_Left', QVariant.Double)
])

layer.updateFields()

# Create a point feature to represent the statistics
feature = QgsFeature()
feature.setGeometry(QgsGeometry.fromPointXY(QgsPointXY(northwest_x, northwest_y)))

# Set attribute values
feature.setAttributes([
    drillholes_count,    # Total Drillholes
    round(drillholes_meters, 1),  # Total Meters
    drilled_count,       # Drilled Holes
    round(drilled_meters, 1),  # Drilled Meters
    days_drilled,        # Days Drilled
    round(drilling_rate, 1),  # Drilling Rate
    round(left_to_drill, 1),  # Left to Drill
    round(days_left, 1)  # Days Left
])

# Add the feature to the layer
provider.addFeatures([feature])
layer.updateExtents()

# Add the layer to the project
project.addMapLayer(layer)

'''Apply styles to Logboard Campaign Statistics layer and refresh'''
apply_style_to_layer("Logboard Campaign Statistics", statistics_style_path)

# Map Refresh
iface.mapCanvas().refreshAllLayers()  # Refresh all layers