get_measurements.py

import json
from datetime import datetime
import random
import geopandas as gpd
from server.map.add_lake import write_json_to_file


# get_markers requests all marker data or valid markers, converts the data to json, and writes
# the data to the response object
def get_all_markers(self, cursor, waterBodyName):
    try:
        sql_query = '''
            SELECT m.MeasurementID, m.SensorID, m.TimeMeasured, m.CenterLat, m.CenterLon,
                   s.SensorType, s.Active, 
                   b.Name,
                   d.SubDivisionID, d.GroupID, d.MinimumThickness, 
                   d.AverageThickness, d.CenterLatitude, d.CenterLongitude, 
                   d.Accuracy
            FROM Measurement m
            INNER JOIN Sensor s ON m.SensorID = s.SensorID
            INNER JOIN BodyOfWater b ON m.WaterBodyName = b.Name
            LEFT JOIN SubDivision d ON m.MeasurementID = d.MeasurementID
            WHERE b.Name = 'Mjosa'
        '''

        cursor.execute(sql_query)

        rows = cursor.fetchall()

        # Container for all fetched measurement objects
        measurement_data = {}

        # Iterate over all fetched rows
        for row in rows:
            measurement_id = row[0]

            # Create subdivision new object
            sub_division = {
                'SubdivID': row[8],
                'GroupID': row[9],
                'MinThickness': row[10],
                'AvgThickness': row[11],
                'CenLatitude': row[12],
                'CenLongitude': row[13],
                'Accuracy': row[14],
                'Color': calculateColor(row[11])  # NB color calculated based on average thickness, should be minimum
            }

            # Check if measurement ID already exists in measurement_data
            if measurement_id in measurement_data:
                # Create new subdivision within measurement if it does not already exist
                if sub_division not in measurement_data[measurement_id]['Subdivisions']:
                    measurement_data[measurement_id]['Subdivisions'].append(sub_division)

            else:
                # Create a new entry for measurement_id if it does not already exist in the list
                measurement_data[measurement_id] = {
                    'MeasurementID': measurement_id,
                    'TimeMeasured': row[2],
                    'CenterLat': row[3],
                    'CenterLon': row[4],
                    'Sensor': {  # Each measurement only has one related sensor
                        'SensorID': row[1],
                        'SensorType': row[5],
                        'Active': bool(row[6])
                    },
                    'Subdivisions': [sub_division],  # Array of sub_division objects
                }

        ##################################### TEST DATA ###########################################
        # Temporary test data
        test_measurements = []
        subdiv_id = 17

        for i in range(3, 10):
            sub_divisions = []

            for j in range(0, 30):
                min_thickness = random.uniform(0, 10)
                avg_thickness = random.uniform(0, 15) + min_thickness

                subdivision = {
                    'SubdivID': subdiv_id,
                    'GroupID': 1,
                    'MinThickness': min_thickness,
                    'AvgThickness': avg_thickness,
                    'CenLatitude': 7.0,
                    'CenLongitude': 8.0,
                    'Accuracy': 1.0,
                    'Color': calculateColor(avg_thickness)
                }

                sub_divisions.append(subdivision)
                subdiv_id += 1

            measurement = {
                'MeasurementID': i,
                'TimeMeasured': str(datetime.now()),
                'CenterLat': 10.0,
                'CenterLon': 8.0,
                'Sensor': {
                    'SensorID': 1,
                    'SensorType': "test data",
                    'Active': True
                },
                'Subdivisions': sub_divisions
            }

            test_measurements.append(measurement)
        ##################################### TEST DATA ###########################################

        # Convert dictionary values to list of measurements
        data = list(measurement_data.values()) + test_measurements

        ######################### ADD GROUP_IDS TO RELATION DATA ##################################

        # Read lake relation from json file
        geo_data = gpd.read_file("server/lake_relations/mjosa_div.json")
        relation_data = geo_data[geo_data['geometry'].geom_type == 'Polygon']

        # Add group IDs to lake relation
        for measurement in data:
            measurement_id = str(measurement['MeasurementID'])  # Extract measurement ID
            for subdivision in measurement['Subdivisions']:
                subDivID = str(subdivision['SubdivID'])  # Convert to string to match format in feature

                group_id = subdivision['GroupID']  # Extract group ID
                new_group_id = str(measurement_id) + "-" + str(group_id)  # Create concatenated group ID

                # Find the matching subdivision in relation_data
                for index, feature in relation_data.iterrows():
                    # Add the new group ID to the correct subdivision
                    if feature['sub_div_id'] == subDivID:
                        # Update group_id and measurement_id within the properties
                        relation_data.at[index, 'group_id'] = new_group_id
                        relation_data.at[index, 'measurement_id'] = measurement_id
                        # relation_data.at[index, 'sub_div_center'] = feature['sub_div_center']

        # Convert GeoDataFrame to JSON and update json file
        relation_data_json = json.loads(relation_data.to_json())

        write_json_to_file("server/lake_relations", "mjosa", relation_data_json)

        ####################################################################################

        if len(rows) == 0 or len(data) == 0:  # Return 500 and empty list if no data is found
            print(f"No data which meets the condition found")
            marker_data = '[]'
        else:
            # Convert list of dictionaries to JSON
            marker_data = json.dumps(data, indent=4)

    except Exception as e:
        print(f"Error get_measurements(): {e}")
        marker_data = '[]'

    # Set headers
    self.send_response(200)
    self.send_header("Content-type", "application/json")
    self.end_headers()

    # Write both measurement data and relation data to the response object
    self.wfile.write(marker_data.encode('utf-8'))


def calculateColor(thickness: float):  # NB not final colors nor ranges
    if 0 < thickness <= 4:
        return 0xFFff0000  # Red
    elif 4 < thickness <= 6:
        return 0xffff6a00  # Orange
    elif 6 < thickness <= 8:
        return 0xFFb1ff00  # Green
    elif thickness > 8:
        return 0xFF00d6ff  # Blue
    else:
        return 0xFF939393  # Gray