ags_2_db_algorithm.py

# -*- coding: utf-8 -*-

"""
/***************************************************************************
 AGSTools
								 A QGIS plugin
 This plugin parses an AGS file and creates an SQlite database from it
 Generated by Plugin Builder: http://g-sherman.github.io/Qgis-Plugin-Builder/
							  -------------------
		begin                : 2023-04-19
		copyright            : (C) 2023 by Oliver Burdekin / burdGIS
		email                : info@burdgis.com
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/
"""

__author__ = 'Oliver Burdekin / burdGIS'
__date__ = '2023-04-19'
__copyright__ = '(C) 2024 by Oliver Burdekin / burdGIS'

# This will get replaced with a git SHA1 when you do a git archive

__revision__ = '$Format:%H$'

from qgis.PyQt.QtCore import QCoreApplication, QSettings
from qgis.core import (QgsProcessing,
					   QgsFeatureSink,
					   QgsApplication,
					   QgsMapLayer,
					   QgsProcessingAlgorithm,
					   QgsProcessingParameterFile,
					   QgsProcessingParameterFileDestination,
					   QgsProcessingParameterCrs,
					   QgsCoordinateReferenceSystem,
					   QgsDataSourceUri,
					   QgsVectorLayer,
					   QgsProject
						)
from qgis.utils import iface
from io import StringIO
import sqlite3
import os


class AGS2DBAlgorithm(QgsProcessingAlgorithm):
	"""
	This is an example algorithm that takes a vector layer and
	creates a new identical one.

	It is meant to be used as an example of how to create your own
	algorithms and explain methods and variables used to do it. An
	algorithm like this will be available in all elements, and there
	is not need for additional work.

	All Processing algorithms should extend the QgsProcessingAlgorithm
	class.
	"""

	# Constants used to refer to parameters and outputs. They will be
	# used when calling the algorithm from another algorithm, or when
	# calling from the QGIS console.

	OUTPUT = 'OUTPUT'
	INPUT = 'INPUT'
	CRS = 'CRS'

	def initAlgorithm(self, config):
		"""
		Here we define the inputs and output of the algorithm, along
		with some other properties.
		"""

		# We add the input vector features source. It can have any kind of
		# geometry.
		self.addParameter(
			QgsProcessingParameterFile(
				self.INPUT,
				self.tr('Input File'),
			)
		)

		# We add a feature sink in which to store our processed features (this
		# usually takes the form of a newly created vector layer when the
		# algorithm is run in QGIS).
		self.addParameter(
			QgsProcessingParameterFileDestination(
				self.OUTPUT,
				self.tr('Output Database'),
				'SpatiaLite Database (*.db)',
			)
		)

		self.addParameter(
			QgsProcessingParameterCrs(
				self.CRS,
				'Coordinate Reference System',
				defaultValue=QgsCoordinateReferenceSystem('EPSG:27700')
			)
		)

	def parse_ags_file(self, file_contents):
		lines = file_contents.split('\n')
		data = {}
		current_group = None
		headers = []

		# Takenn from AGS4 py
		for line in lines:
			if not line:
				continue

			temp = line.strip().split('","')
			temp = [item.strip('"') for item in temp]

			if temp[0] == 'GROUP':
				current_group = temp[1]
				data[current_group] = []
			elif temp[0] == 'HEADING':
				headers = temp[1:]
			elif temp[0] == 'UNIT':
				unit_values = temp[1:]
				if not any(unit_values):  # Skip empty UNIT rows
					unit_values = None
				else:
					data[f"{current_group}_units"] = dict(zip(headers, unit_values))
			elif temp[0] == 'DATA':
				record = dict(zip(headers, temp[1:]))
				data[current_group].append(record)

    	# Detect column types for each group independently
		column_types = {}
		for group_name, records in data.items():
			if group_name.endswith("_units"):
				continue

			if records:
				# Use the first record's keys as headers for this group
				group_headers = list(records[0].keys())
				column_types[group_name] = {}
				for header in group_headers:
					all_numeric = all(self.is_numeric(record.get(header)) for record in records if record.get(header))
					if all_numeric:
						# If all values are numeric, use REAL type
						column_types[group_name][header] = "REAL"
					else:
						# Otherwise, default to TEXT type
						column_types[group_name][header] = "TEXT"

		return data, column_types
	
	def is_numeric(self, value):
		"""Utility function to check if value can be converted to a number."""
		try:
			float(value)
			return True
		except ValueError:
			return False
	
	def read_ags_file(self, file_path):
		with open(file_path, 'r') as file:
			file_contents = file.read()

		return file_contents

	def create_spatial_table_from_loca(self, cursor, group_name, records, x_column, y_column, srid):
		columns = list(records[0].keys())
		group_name = group_name.lower()
		create_table_sql = f"CREATE TABLE {group_name}_spatial (id INTEGER PRIMARY KEY, geom GEOMETRY, {', '.join([f'{column} TEXT' for column in columns])})"
		cursor.execute(create_table_sql)

		register_sql = f"""
		INSERT INTO geometry_columns (f_table_name, f_geometry_column, geometry_type, coord_dimension, srid, spatial_index_enabled)
		VALUES ('{group_name}_spatial', 'geom', 1, 2, {srid}, 0)
		"""
		cursor.execute(register_sql)

		create_spatial_index_sql = f"SELECT CreateSpatialIndex('{group_name}_spatial', 'geom')"
		cursor.execute(create_spatial_index_sql)

		for record in records:
			if record[x_column] and record[y_column]:
				x = float(record[x_column])
				y = float(record[y_column])
				values = [record[column] for column in columns]
				insert_sql = f"INSERT INTO {group_name}_spatial ({', '.join(columns)}, geom) VALUES ({', '.join(['?' for _ in columns])}, MakePoint(?, ?, {srid}))"
				cursor.execute(insert_sql, (*values, x, y))
			else:
				values = [record[column] for column in columns]
				insert_sql = f"INSERT INTO {group_name}_spatial ({', '.join(columns)}, geom) VALUES ({', '.join(['?' for _ in columns])}, NULL)"
				cursor.execute(insert_sql, values)

	def processAlgorithm(self, parameters, context, feedback):
		input_path = self.parameterAsFile(parameters, self.INPUT, context)
		output_path = self.parameterAsFileOutput(parameters, self.OUTPUT, context)
		crs = self.parameterAsCrs(parameters, self.CRS, context)
		database_name = os.path.splitext(os.path.basename(output_path))[0]

		# Read and parse the .ags file
		file_contents = self.read_ags_file(input_path)
		parsed_data, column_type_map = self.parse_ags_file(file_contents)

		# Save the parsed data to an SQLite database
		if os.path.exists(output_path):
			os.remove(output_path)

		conn = sqlite3.connect(output_path)
		conn.enable_load_extension(True)
		# You may need to modify the path to the mod_spatialite library depending on your system
		conn.load_extension("mod_spatialite")
		# Initialize the SpatiaLite metadata tables
		cursor = conn.cursor()
		cursor.execute("SELECT InitSpatialMetadata(1)")

		# Process the parsed_data and create tables in the SQLite database
		for group_name, records in parsed_data.items():
			print(f"Processing group {group_name}: {len(records)} records")  # Add this print statement

			if group_name.endswith("_units"):
				columns = list(records.keys())
				column_types = ["TEXT"] * len(columns)
				create_table_sql = f"CREATE TABLE {group_name} ({', '.join([f'{column} {column_type}' for column, column_type in zip(columns, column_types)])})"
				cursor.execute(create_table_sql)

				values = list(records.values())
				insert_sql = f"INSERT INTO {group_name} ({', '.join(columns)}) VALUES ({', '.join(['?' for _ in columns])})"
				cursor.execute(insert_sql, values)
			else:
				columns = list(records[0].keys())
				# Use detected column types for table creation
				# _, column_types = self.parse_ags_file(file_contents)  # Get the column types
				create_table_sql = f"CREATE TABLE {group_name} ({', '.join([f'{column} {column_type_map[group_name].get(column, 'TEXT')}' for column in columns])})"
				cursor.execute(create_table_sql)

				for record in records:
					values = [float(record[column]) if column_type_map[group_name][column] == 'REAL' and record[column] else record[column] for column in columns]
					insert_sql = f"INSERT INTO {group_name} ({', '.join(columns)}) VALUES ({', '.join(['?' for _ in columns])})"
					cursor.execute(insert_sql, values)

			if group_name == 'LOCA':  # Assuming LOCA is the group name containing the spatial data
				x_column = "LOCA_NATE"
				y_column = "LOCA_NATN"
				# Replace 0 with the appropriate SRID (Spatial Reference ID) for your data
				crs = parameters['CRS']
				epsg_code = crs.authid().split(":")[1] # Extract the EPSG code
				srid = int(epsg_code) # Convert the code to an integer

				self.create_spatial_table_from_loca(cursor, group_name, records, x_column, y_column, srid)


		conn.commit()
		conn.close()



		
		# Get the absolute path to your SVG symbols
		svg_path = os.path.join(os.path.dirname(__file__), 'styles', 'svg')

		# Add this path to QGIS's SVG paths
		svg_paths = QgsApplication.svgPaths()
		if svg_path not in svg_paths:
			svg_paths.append(svg_path)
			QgsApplication.setDefaultSvgPaths(svg_paths)

		# Now, when you add your layer, apply the QML style
		qml_path = os.path.join(os.path.dirname(__file__), 'styles', 'loca_spatial.qml')

		# Add layer to the map
		uri = QgsDataSourceUri()
		uri.setDatabase(output_path)
		schema = ''
		table = 'loca_spatial'
		geom_column = 'geom'  # Assuming the geometry column in your spatial table is named 'geom'
		uri.setDataSource(schema, table, geom_column)

		layer = QgsVectorLayer(uri.uri(), table, "spatialite")
		if not layer.isValid():
			print("Layer failed to load!")
		else:
			QgsProject.instance().addMapLayer(layer)

		# Check if layer is a vector layer
		if layer.type() == QgsMapLayer.VectorLayer:
			layer.loadNamedStyle(qml_path)

		# Finally, trigger a refresh so QGIS knows to apply the new styles
		layer.triggerRepaint()
		iface.layerTreeView().refreshLayerSymbology(layer.id())


		# Add database connection to QGIS automatically
		settings = QSettings()
		settings.beginGroup('/SpatiaLite/connections/')
		settings.beginGroup(database_name)  # Replace 'MyConnection' with the name you want for the connection
		settings.setValue('sqlitepath', output_path)  # Replace 'output_path' with the path to your SQLite database
		settings.endGroup()
		settings.endGroup()

		# Refresh database connections in the Browser Panel
		iface.browserModel().refresh()

		# Save SQL query
		query_name = 'asbestos'  # Replace with the appropriate name for your query
		sql_query = '''
		
		SELECT
			ls.LOCA_ID,
			ls.geom,
			e.ERES_NAME,
			e.ERES_RTXT,
			CAST(e.SAMP_TOP AS FLOAT) AS SAMP_TOP,
			e.SAMP_ID,
			(
				SELECT
					CASE
						WHEN TRIM(g.GEOL_FORM) = '' THEN g.GEOL_TOP || '-' || g.GEOL_BASE
						ELSE g.GEOL_FORM
					END
				FROM
					GEOL g
				WHERE
					g.LOCA_ID = e.LOCA_ID
					AND CAST(e.SAMP_TOP AS FLOAT) >= CAST(g.GEOL_TOP AS FLOAT)
					AND CAST(e.SAMP_TOP AS FLOAT) < CAST(g.GEOL_BASE AS FLOAT)
				LIMIT 1
			) AS GEOL_FORM
		FROM
			ERES e
		JOIN
			loca_spatial ls ON e.LOCA_ID = ls.LOCA_ID
		WHERE
			(LOWER(e.ERES_NAME) LIKE '%asbestos%' OR e.ERES_CODE = '1332-21-4')
		ORDER BY ls.LOCA_ID; 
		
				'''  # Replace with your SQL query

		settings.beginGroup('/DB_Manager/query')
		query_name_with_prefix = f'ags_{query_name}'

		if not settings.contains(query_name_with_prefix):
			settings.setValue(query_name_with_prefix, sql_query)
		settings.endGroup()	

		return {self.OUTPUT: output_path}
	
	def processing_log(self, message):
		"""
		Logs a message to the Processing log.
		"""
		self.logMessage(message)

	def name(self):
		"""
		Returns the algorithm name, used for identifying the algorithm. This
		string should be fixed for the algorithm, and must not be localised.
		The name should be unique within each provider. Names should contain
		lowercase alphanumeric characters only and no spaces or other
		formatting characters.
		"""
		return 'AGS2DB'

	def displayName(self):
		"""
		Returns the translated algorithm name, which should be used for any
		user-visible display of the algorithm name.
		"""
		return self.tr(self.name())

	def group(self):
		"""
		Returns the name of the group this algorithm belongs to. This string
		should be localised.
		"""
		return self.tr(self.groupId())

	def groupId(self):
		"""
		Returns the unique ID of the group this algorithm belongs to. This
		string should be fixed for the algorithm, and must not be localised.
		The group id should be unique within each provider. Group id should
		contain lowercase alphanumeric characters only and no spaces or other
		formatting characters.
		"""
		return ''

	def tr(self, string):
		return QCoreApplication.translate('Processing', string)

	def createInstance(self):
		return AGS2DBAlgorithm()