get_markers.py

import json


# 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]
            }

            # 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
                    'Corners': [], # NB remove value if not used
                }

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

        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 in querying database: {e}")
        marker_data = '[]'

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

    # Write marker data to response object
    self.wfile.write(marker_data.encode('utf-8'))