user input categories, refactor, remove zombie code

8 files changed, 173 insertions, 134 deletions

diff --git a/data_extractor/data_extractor.lua b/data_extractor/data_extractor.lua
index 4732f3b..5f5cc9e 100644
--- a/data_extractor/data_extractor.lua
+++ b/data_extractor/data_extractor.lua
@@ -1,5 +1,8 @@
-local shop = osm2pgsql.define_table({
-    name = 'shop',
+-- Based on: https://osm2pgsql.org/examples/poi-db/
+-- Data:     https://download.geofabrik.de/europe/denmark-latest.osm.pbf
+
+local poi = osm2pgsql.define_table({
+    name = 'poi',
     ids = { type = 'any', type_column = 'osm_type', id_column = 'osm_id' },
     columns = {
         { column = 'name' },
@@ -19,11 +22,14 @@ function process_poi(object, geom)
         a.class = 'shop'
         a.subclass = object.tags.shop
         a.brand = object.tags.brand
+		elseif object.tags.amenity then
+        a.class = 'amenity'
+        a.subclass = object.tags.amenity
     else
         return
     end
 
-    shop:insert(a)
+    poi:insert(a)
 end
 
 function osm2pgsql.process_node(object)
diff --git a/src/db_handler.py b/src/db_handler.py
index 5a2ab73..b438b6c 100644
--- a/src/db_handler.py
+++ b/src/db_handler.py
@@ -3,9 +3,27 @@ import os
 import psycopg
 import yaml
 
+
+class IllegalCategoryException(Exception):
+    pass
+
+
 with open(os.path.join('configs', 'database.yml'), 'r') as file:
     db_con_params = yaml.safe_load(file.read())
 
+with psycopg.connect(**db_con_params) as conn:
+    with conn.cursor() as cur:
+        cur.execute("""
+        SELECT subclass
+        FROM poi
+        WHERE subclass NOT LIKE '%;%'
+        GROUP BY class, subclass
+        HAVING COUNT(*) > 1
+        ;
+        """)
+        categories = cur.fetchall()
+        categories = [category[0] for category in categories]
+
 
 def get_chains() -> (list[dict]):
     with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
@@ -16,79 +34,35 @@ def get_chains() -> (list[dict]):
             chains = cur.fetchall()
             return chains
 
-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,
+
+def get_all(category: str) -> (list[dict]):
+    if category not in categories:
+        raise IllegalCategoryException()
+    else:
+        with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
+            with conn.cursor() as cur:
+                cur.execute("""
+WITH filtered AS (
+SELECT osm_id, name, brand, geom, class, subclass
+FROM poi
+WHERE subclass = %(subclass)s
+)
+
+SELECT
+ filtered.osm_id,
+ filtered.name,
+ filtered.brand,
+ ST_Y(ST_Transform(filtered.geom, 4326)) AS lat,
+ ST_X(ST_Transform(filtered.geom, 4326)) AS long,
  ST_AsGeoJSON(ST_Transform(polygon.geom, 4326)) as polygon
- FROM shop
+ FROM filtered
  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:
-            cur.execute("""
-            SELECT name, 
-            CASE WHEN brand ILIKE '%365%' THEN '365discount' ELSE BRAND END,
-            ST_Y(ST_Transform(geom, 4326)) AS lat, ST_X(ST_Transform(geom, 4326)) AS long 
-            FROM shop WHERE subclass = 'supermarket';
-            """)
-
-            supermarkets = cur.fetchall()
-            return supermarkets
-
-
-def get_stores() -> (list[dict], list[str]):
-    with psycopg.connect(**db_con_params) as conn:
-        with conn.cursor() as cur:
-            cur.execute("""
-            SELECT
-            ST_Y(ST_Transform(geom, 4326)) AS lat, ST_X(ST_Transform(geom, 4326)) AS long 
-            FROM shop;
-            """)
-
-            stores = cur.fetchall()
-            return stores
-
-
-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 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()
+   FROM filtered
+ ) polygon ON ST_Contains(polygon.geom, filtered.geom)
+;
+            """, {'subclass': category})
 
-            return voronoi_polygons
+                all = cur.fetchall()
+                return all
diff --git a/src/shop_map_node5_net.py b/src/shop_map_node5_net.py
index 8ab97bc..3898e21 100644
--- a/src/shop_map_node5_net.py
+++ b/src/shop_map_node5_net.py
@@ -10,7 +10,7 @@ app = flask.Flask(__name__, template_folder='templates', static_folder='static',
 
 @app.route("/")
 def index():
-    return flask.redirect('/index.html')
+    return flask.render_template('/index.html')
 
 
 @app.route("/chains")
@@ -27,24 +27,15 @@ def chains():
     return json.dumps(color_by_name)
 
 
-@app.route("/stores.json")
-def stores():
-    return db_handler.get_stores()
-
-
-@app.route("/supermarkets.json")
-def supermarkets():
-    return db_handler.get_supermarkets()
-
-
-@app.route("/voronoi_polygons.json")
-def voronoi_polygons():
-    d = db_handler.voronoi_polygons()
-
+@app.route("/categories")
+def categories():
+    categories = db_handler.categories
+    return categories
 
 @app.route("/all.json")
 def all():
-    rows = db_handler.get_all()
+    category_RADIOACTIVE = flask.request.args.get('Category')  # User input is RADIOACTIVE
+    rows = db_handler.get_all(category_RADIOACTIVE)
     for row in rows:
         coordinates = []
         for coordinate in json.loads(row['polygon'])['coordinates'][0]:
diff --git a/src/static/index.html b/src/static/index.html
deleted file mode 100644
index 5bfcc1f..0000000
--- a/src/static/index.html
+++ /dev/null
@@ -1,18 +0,0 @@
-<!DOCTYPE html>
-<html lang="en">
-	<head>
-		<meta charset="UTF-8">
-		<meta name="viewport" content="width=device-width, initial-scale=1.0">
-		<meta http-equiv="X-UA-Compatible" content="ie=edge">
-		<title>Nearest shop map</title>
-		<link rel="stylesheet" href="leaflet.css"/>
-		<link rel="stylesheet" href="main.css"/>
-		<!-- Make sure you put this AFTER Leaflet's CSS -->
-		<script src="leaflet.js" defer></script>
-		<script src="leaflet-heat.js" defer></script>
-		<script src="main.js" defer></script>
-	</head>
-	<body>
-		 <div id="map"></div>
-	</body>
-</html>
diff --git a/src/static/main.js b/src/static/main.js
index a920476..be136a4 100644
--- a/src/static/main.js
+++ b/src/static/main.js
@@ -11,14 +11,40 @@ var markers =  L.featureGroup().addTo(map);
 var polygons =  L.featureGroup().addTo(map);
 var heatmap =  L.featureGroup().addTo(map);
 
+const CategoryField = document.getElementById('Category');
+
+const DefaultSearchValue = "supermarket";
+var Categories = []
+
 var overlayMaps = {
-    "Supermarket icons": markers,
-    "Nearest supermarket polygons": polygons,
-    "Stores heatmap": heatmap
+    "POI markers": markers,
+    "Nearest POI polygons": polygons,
+//    "POI heatmap": heatmap
 };
 
 var layerControl = L.control.layers({},overlayMaps).addTo(map);
 
+function runQueryAfterLoad() {
+    fetchAll(CategoryField.value);
+}
+
+async function fetchCategories() {
+    const response = await fetch("categories");
+    try {
+        categories = await response.json();
+        const CategoriesDataList = document.getElementById('CategoriesDataList');
+        categories.forEach(function(item){
+            var option = document.createElement('option');
+            option.value = item;
+            option.innerHTML = item;
+            CategoriesDataList.appendChild(option);
+    });
+    CategoryField.pattern = categories.join('|');
+    } catch (error) {
+        alert(`Failed to fetch categories, are you online?\n${error.message}`)
+    }
+}
+
 async function fetchChains() {
     const response = await fetch("chains");
     known_store_chains = await response.json();
@@ -33,7 +59,25 @@ async function fetchChains() {
             iconAnchor: [8, 8],
         });
     }
-    fetchAll()
+
+    const queryString = window.location.search;
+    const urlParams = new URLSearchParams(queryString);
+    const urlParam = urlParams.get('Category')
+    const fieldValue = CategoryField.value
+    // Retains saved value from input field on reload
+    if (urlParam)
+    {
+        fetchAll(urlParam);
+        CategoryField.value = urlParam;
+
+    } else if (fieldValue !== "")
+    {
+        fetchAll(fieldValue);
+    }  else
+    {
+        CategoryField.value = DefaultSearchValue;
+        fetchAll(DefaultSearchValue);
+    }
 }
 
 
@@ -64,8 +108,8 @@ function addShop(shop) {
 }
 
 
-async function fetchAll() {
-  const response = await fetch("all.json");
+async function fetchAll(Category) {
+  const response = await fetch(`all.json?Category=${Category}`)
   const shops = await response.json();
   shops.forEach((shop) => addShop(shop));
 }
@@ -76,23 +120,6 @@ async function fetchShopHeatmap() {
     var heat = L.heatLayer(shops, {radius: 30}).addTo(heatmap);
 }
 
-
-async function fetch_voronoi_polygons() {
-  const response = await fetch("voronoi_polygons.json");
-  const voronoi_polygons = await response.json();
-  voronoi_polygons.forEach((polygon) => addPolygon(polygon));
-}
-
-function update(){
-    if (map.getZoom() >= 14){
-        //console.log('update')
-    }
-}
-
-map.on('moveend', function() {
-    update()
-});
-
-fetchChains()
-
-fetchShopHeatmap();
+fetchChains();
+fetchCategories();
+//fetchShopHeatmap();
diff --git a/src/templates/base.html b/src/templates/base.html
new file mode 100644
index 0000000..2844f62
--- /dev/null
+++ b/src/templates/base.html
@@ -0,0 +1,24 @@
+<!DOCTYPE html>
+<html lang="en">
+<head>
+    {% block head %}
+    <meta charset="UTF-8">
+    <meta name="viewport" content="width=device-width, initial-scale=1.0">
+    <meta http-equiv="X-UA-Compatible" content="ie=edge">
+    <title>Nearest shop map</title>
+    <link rel="stylesheet" href="leaflet.css"/>
+    <link rel="stylesheet" href="main.css"/>
+    <link rel="stylesheet" href="bootstrap-5.3.3-dist/css/bootstrap.min.css">
+    <!-- Make sure you put this AFTER Leaflet's CSS -->
+    <script src="leaflet.js" defer></script>
+    <script src="leaflet-heat.js" defer></script>
+    <script src="main.js" defer></script>
+    {% endblock %}
+</head>
+<body>
+
+{% block body %}
+{% endblock %}
+
+</body>
+</html>
diff --git a/src/templates/index.html b/src/templates/index.html
new file mode 100644
index 0000000..f97ae53
--- /dev/null
+++ b/src/templates/index.html
@@ -0,0 +1,14 @@
+{% extends "base.html" %}
+
+{% block body %}
+<div class="container-fluid">
+    <div class="row">
+        <div class="col-2">
+            {% include 'options.html' %}
+        </div>
+        <div class="col">
+            <div id="map"></div>
+        </div>
+    </div>
+</div>
+{% endblock %}
\ No newline at end of file
diff --git a/src/templates/options.html b/src/templates/options.html
new file mode 100644
index 0000000..8f2d43a
--- /dev/null
+++ b/src/templates/options.html
@@ -0,0 +1,21 @@
+<h1>POI map</h1>
+<form>
+    <label for="Category" class="form-label"><h3>Category</h3></label>
+    <input class="form-control" list="CategoriesDataList" id="Category" name="Category" placeholder="Type to search..."
+           aria-describedby="CategoriesHelp" value=""> {# Value set to empty string is important for JS function #}
+    <datalist id="CategoriesDataList">
+
+    </datalist>
+    <div id="CategoriesHelp" class="form-text">Change the category shown on the map<br>
+        e.g. <code>supermarket</code>, <code>toilet</code>, or <code>swingerclub</code></div>
+
+    <br>
+
+    <button type="submit" class="btn btn-primary">Run query</button>
+
+    <br><br>
+
+    Read more, and find the source code here: <a href="https://git.node5.net/poi_map/about/">POI map - git.node5.net</a>
+
+</form>
+