asantefes7/ETL-Pipeline-for-Analyzing-Illegal-Mining

This project developed an ETL pipeline to analyze the environmental impact of galamsey (illegal gold mining) in Ghana’s Pra River Basin. # ETL-Pipeline-for-Analyzing-Illegal-Mining This project developed an ETL pipeline to analyze the environmental impact of galamsey (illegal gold mining) in Ghana’s Pra River Basin. The pipeline extracts geospatial data from OpenStreetMap (OSM) and mocked water quality data, transforms it to map affected areas and quantify pollution (focusing on mercury), and loads it into a PostgreSQL/PostGIS database for analysis. The goal was to provide actionable insights into galamsey’s toll on regions like Ashanti (Kumasi) and Western (Sekondi), supporting regulatory efforts and public awareness through visualizations. ## Technology and Tool Stacks o Python: You used pandas, geopandas, and osmnx as planned (e.g., extract_osm.py, clean_pollution.py). o Airflow: Used to orchestrate the pipeline (localhost). o PostgreSQL/PostGIS: Used for storage, with screenshots (water_quality_count.png, etc.) as proof. o Visualization: matplotlib for galamsey_map.png, Looker Studio for the dashboard (Bubble Map, Bar Chart). o Monitoring: Not implemented (Prometheus/Grafana were optional). ## Approach & Methodology 2.1 Data Import o Extracted OSM geospatial data (rivers, land use like construction and industrial) for the Pra River Basin using osmnx in extract_osm.py. o Used mocked water quality data (pra_water_quality.csv) with metrics like mercury, arsenic, lead, and turbidity for locations including Kumasi, Sekondi, and Twifo Praso, due to challenges accessing OpenAQ and Ghana EPA data. 2.2 Data Transformation o Cleaned the water quality data using clean_pollution.py, producing pra_water_quality_cleaned.csv. o Calculated distances between water quality points and rivers (dist_river) and land use (dist_landuse) using geopandas. Created galamsey_analysis.csv with enriched data, including a GeoLocation field (CONCAT(Latitude, ", ", Longitude)) for mapping 2.3 Data Loading o Loaded the data into PostgreSQL/PostGIS with tables for water quality and geospatial …