color polygons

1 files changed, 41 insertions, 4 deletions

diff --git a/src/db_handler.py b/src/db_handler.py
index 3f77366..5798c66 100644
--- a/src/db_handler.py
+++ b/src/db_handler.py
@@ -7,6 +7,32 @@ with open(os.path.join('configs', 'database.yml'), 'r') as file:
     db_con_params = yaml.safe_load(file.read())
 
 
+def get_all() -> (list[dict]):
+    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
+        with conn.cursor() as cur:
+            cur.execute("""
+ SELECT
+ shop.osm_id,
+ shop.name,
+ shop.brand,
+ ST_Y(ST_Transform(shop.geom, 4326)) AS lat,
+ ST_X(ST_Transform(shop.geom, 4326)) AS long,
+ ST_AsGeoJSON(ST_Transform(polygon.geom, 4326)) as polygon
+ FROM shop
+ JOIN
+ (
+   SELECT (ST_DUMP(ST_VoronoiPolygons(ST_Collect(geom)))).geom as geom
+   FROM shop
+   WHERE subclass = 'supermarket'
+ ) polygon ON ST_Contains(polygon.geom, shop.geom)
+ WHERE subclass = 'supermarket'
+ ;
+            """)
+
+            all = cur.fetchall()
+            return all
+
+
 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:
@@ -24,12 +50,23 @@ def get_supermarkets() -> (list[dict], list[str]):
 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:
+            """
+            SELECT ST_AsGeoJSON(ST_Transform(ST_VoronoiPolygons(ST_Collect(geom)), 4326)) as geojson
+            FROM shop 
+            WHERE subclass = 'supermarket';
+            """
+            """
+            SELECT *,
+            ST_ClusterDBSCAN(<geom>, 0, 1) OVER() AS _clst
+            FROM   <your_table>;
+            """
             cur.execute("""
-                SELECT ST_AsGeoJSON(ST_Transform(ST_VoronoiPolygons(ST_Collect(geom)), 4326)) as geojson
-                FROM shop 
-                WHERE subclass = 'supermarket';
+                SELECT ARRAY_AGG(name) as names, ST_AsGeoJSON(ST_Transform(ST_VoronoiPolygons(ST_Collect(geom)), 4326)) as geojson
+                FROM shop
+                WHERE subclass = 'supermarket' AND osm_id IN (7688736659, 7682568726, 1085855265, 4517989992)
+                ;
             """)
 
-            voronoi_polygons = cur.fetchone()['geojson']
+            voronoi_polygons = cur.fetchone()
 
             return voronoi_polygons