Spatial Analysis

Authors:

• Łukasz Jęcek
• Alicja Świercz
• Magdalena Synowiec

---

This repository contains materials for spatial data analysis using SQL/PostGIS and Python/GeoPandas, organized into:

• exercises (zadanie 1–zadanie 10),
• assignments (Zadanie domowe 1–Zadanie domowe 3),
• final project (projekt koncowy).

Repository Structure

1) Exercises (zadanie 1 - zadanie 10)

• zadanie 1 - basic SQL queries on NYC datasets (aggregation, grouping, filtering).
• zadanie 2 - geometry operations and PostGIS functions (WKT/WKB, geometry types, measurements, spatial relations).
• zadanie 3 - advanced spatial SQL/PostGIS queries on NYC data (spatial joins, distance analysis, intersections).
• zadanie 4 - Python notebook with introductory geometry operations.
• zadanie 5 - working with vector datasets (SHP/CSV), coordinate systems, and CRS transformations.
• zadanie 6 - loading and preparing spatial data from text files and shapefiles.
• zadanie 7 - travel-time analysis and spatial data classification/comparison.
• zadanie 8 - transport network and accessibility analysis with supporting layers (roads, metro).
• zadanie 9 - building interactive maps (Folium + address data).
• zadanie 10 - GeoAI-related tasks (including solar panel analysis with raster/vector masks).

Most folders include:

• a .sql script (SQL-based topics) or .ipynb notebook (Python-based topics),
• input datasets required to reproduce the analysis (e.g., .shp, .dbf, .geojson, .tif, .txt).

2) Assignments (Zadanie domowe 1 - Zadanie domowe 3)

• Zadanie domowe 1 - a sequence of SQL/PostGIS views (widoki.sql) built step-by-step using demographic and spatial criteria.
• Zadanie domowe 2 - notebook-based analysis of countries.geojson and population_density.geojson.
• Zadanie domowe 3 - analysis and visualization of bird observations (observations.shp) plus an interactive map (mapa.html).

3) Final Project (projekt koncowy)

Project topic:

Spatial analysis of 2025 high-school final exam (matura exam) results by school type and distance to urban centers.

This folder includes:

• projekt_koncowy.ipynb - the main analysis and visualization notebook,
• mapa.html - final interactive map output,
• data/ - source/intermediate data files and a data-preparation notebook (przygotowanie_danych.ipynb) (includes geocoding).

Requirements

Depending on the topic, the repository uses:

• Python and libraries such as pandas, geopandas, shapely, pyproj, folium, matplotlib, contextily, mapclassify, scikit-learn, seaborn, geoai,
• PostgreSQL + PostGIS for SQL-based work.