color polygons

3 files changed, 76 insertions, 22 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
diff --git a/src/shop_map_node5_net.py b/src/shop_map_node5_net.py
index eafad5c..44c6cb5 100644
--- a/src/shop_map_node5_net.py
+++ b/src/shop_map_node5_net.py
@@ -29,10 +29,16 @@ def supermarkets():
 @app.route("/voronoi_polygons.json")
 def voronoi_polygons():
     d = db_handler.voronoi_polygons()
-    b = []
-    for a in json.loads(d)['geometries']:
-        c = []
-        for g in a['coordinates'][0]:
-            c.append([g[1], g[0]])
-        b.append(c)
-    return json.dumps(b)
+
+
+@app.route("/all.json")
+def all():
+    rows = db_handler.get_all()
+    for row in rows:
+        print(row['polygon'])
+        coordinates = []
+        for coordinate in json.loads(row['polygon'])['coordinates'][0]:
+            coordinates.append([coordinate[1], coordinate[0]])
+        row['polygon'] = coordinates
+        print(row['polygon'])
+    return json.dumps(rows)
diff --git a/src/static/main.js b/src/static/main.js
index 405f455..c37c92b 100644
--- a/src/static/main.js
+++ b/src/static/main.js
@@ -23,11 +23,26 @@ async function fetchChains() {
             iconAnchor: [8, 8],
         });
     }
+    fetchAll()
+}
+
 
-    fetchShops();
+function addPolygon(store) {
+    console.log(store)
+    let color;
+    if (store.brand === 'Rema 1000'){
+        color = 'blue';
+    } else if (store.brand === 'Netto'){
+        color = 'yellow'
+    }
+    else if (store.brand === 'Coop 365discount'){
+        color = 'green';
+    }else{
+        color = 'red'}
+    L.polygon(store.polygon, {color: color}).addTo(map);
 }
 
-function addMarker(shop) {
+function addShop(shop) {
     let icon;
 
     if (known_store_chains.includes(shop.brand)){
@@ -40,17 +55,14 @@ function addMarker(shop) {
         title: shop.name,
         icon: icon,
     }).addTo(map);
-}
-
-function addPolygon(polygon) {
-    L.polygon(polygon, {color: 'red'}).addTo(map);
+    addPolygon(shop)
 }
 
 
-async function fetchShops() {
-  const response = await fetch("supermarkets.json");
+async function fetchAll() {
+  const response = await fetch("all.json");
   const shops = await response.json();
-  shops.forEach((shop) => addMarker(shop));
+  shops.forEach((shop) => addShop(shop));
 }
 
 
@@ -70,5 +82,4 @@ map.on('moveend', function() {
     update()
 });
 
-fetchChains()
-fetch_voronoi_polygons()
\ No newline at end of file
+fetchChains()
\ No newline at end of file