From fa360829bfe3f04f3e6d9af196dc9fd480864d4b Mon Sep 17 00:00:00 2001 From: user Date: Sat, 6 Apr 2024 22:23:06 +0200 Subject: initial commit - displays voronoi diagram --- src/db_handler.py | 35 +++++++++++++++++++++++++++++++++++ 1 file changed, 35 insertions(+) create mode 100644 src/db_handler.py (limited to 'src/db_handler.py') diff --git a/src/db_handler.py b/src/db_handler.py new file mode 100644 index 0000000..565819d --- /dev/null +++ b/src/db_handler.py @@ -0,0 +1,35 @@ +import os + +import psycopg +import yaml + +with open(os.path.join('configs', 'database.yml'), 'r') as file: + db_con_params = yaml.safe_load(file.read()) + + +def get_supermarkets() -> (list[dict], list[str]): + with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn: + with conn.cursor() as cur: + cur.execute(""" + SELECT osm_type, osm_id, name, + ST_Y(ST_Transform(geom, 4326)) AS lat, ST_X(ST_Transform(geom, 4326)) AS long + FROM shop WHERE subclass = 'supermarket' AND name='Løvbjerg'; + """) + + supermarkets = cur.fetchall() + return supermarkets + + +def voronoi_polygons() -> (list[dict], list[str]): + with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn: + with conn.cursor() as cur: + cur.execute(""" + SELECT ST_AsGeoJSON(ST_Transform(ST_VoronoiPolygons(ST_Collect(geom)), 4326)) as geojson + FROM shop + WHERE subclass = 'supermarket' + AND name = 'Løvbjerg'; + """) + + voronoi_polygons = cur.fetchone()['geojson'] + + return voronoi_polygons -- cgit v1.2.3