From f0626b175d21230dc68ecd83e2726ec7e7daae5a Mon Sep 17 00:00:00 2001 From: user Date: Mon, 20 May 2024 19:51:41 +0200 Subject: user input categories, refactor, remove zombie code --- data_extractor/data_extractor.lua | 12 +++- src/db_handler.py | 116 +++++++++++++++----------------------- src/shop_map_node5_net.py | 23 +++----- src/static/index.html | 18 ------ src/static/main.js | 79 +++++++++++++++++--------- src/templates/base.html | 24 ++++++++ src/templates/index.html | 14 +++++ src/templates/options.html | 21 +++++++ 8 files changed, 173 insertions(+), 134 deletions(-) delete mode 100644 src/static/index.html create mode 100644 src/templates/base.html create mode 100644 src/templates/index.html create mode 100644 src/templates/options.html 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(, 0, 1) OVER() AS _clst - FROM ; - """ - 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 @@ - - - - - - - Nearest shop map - - - - - - - - -
- - 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 @@ + + + + {% block head %} + + + + Nearest shop map + + + + + + + + {% endblock %} + + + +{% block body %} +{% endblock %} + + + 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 %} +
+
+
+ {% include 'options.html' %} +
+
+
+
+
+
+{% 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 @@ +

POI map

+
+ + {# Value set to empty string is important for JS function #} + + + +
Change the category shown on the map
+ e.g. supermarket, toilet, or swingerclub
+ +
+ + + +

+ + Read more, and find the source code here: POI map - git.node5.net + +
+ -- cgit v1.2.3