From 84f6db9d7b5070c2101cd0fb3971471df70e13f9 Mon Sep 17 00:00:00 2001 From: "user@node5.net" Date: Thu, 18 Jun 2026 22:41:33 +0200 Subject: nixify running needs databse, this is handled separately --- src/db_handler.py | 192 +++++++++++++------------- src/map_node5_net.py | 23 ++- src/static/icons_big/365discount.png | Bin 0 -> 1472 bytes src/static/icons_big/Dagli'Brugsen.png | Bin 0 -> 892 bytes src/static/icons_big/Lidl.png | Bin 0 -> 1683 bytes "src/static/icons_big/Min K\303\270bmand.png" | Bin 0 -> 536 bytes src/static/icons_big/Netto.png | Bin 0 -> 1624 bytes src/static/icons_big/Rema 1000.png | Bin 0 -> 1858 bytes src/static/icons_big/SuperBrugsen.png | Bin 0 -> 1100 bytes src/static/icons_big/Unknown.png | Bin 0 -> 4692 bytes 10 files changed, 114 insertions(+), 101 deletions(-) create mode 100644 src/static/icons_big/365discount.png create mode 100644 src/static/icons_big/Dagli'Brugsen.png create mode 100644 src/static/icons_big/Lidl.png create mode 100644 "src/static/icons_big/Min K\303\270bmand.png" create mode 100644 src/static/icons_big/Netto.png create mode 100644 src/static/icons_big/Rema 1000.png create mode 100644 src/static/icons_big/SuperBrugsen.png create mode 100644 src/static/icons_big/Unknown.png (limited to 'src') diff --git a/src/db_handler.py b/src/db_handler.py index 335e9e4..afc2852 100644 --- a/src/db_handler.py +++ b/src/db_handler.py @@ -9,105 +9,105 @@ logger = logging.getLogger(__name__) # Set the logger name, to the name of the m class IllegalInstructionException(Exception): pass +class DBHandler: + def __init__(self, conninfo: str): + self.conninfo = conninfo -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 class -FROM poi -GROUP BY class -HAVING COUNT(*) > 1 -ORDER BY class -; - """) - classes = cur.fetchall() - categories = [category[0] for category in classes] - - cur.execute(""" -SELECT class, subclass -FROM poi -WHERE subclass NOT LIKE '%;%' -GROUP BY class, subclass -HAVING COUNT(*) > 1 -ORDER BY class, subclass -; - """) - sub_cat = cur.fetchall() - categories += [(f"{category[0]}:{category[1]}") for category in sub_cat] - print(f"Loaded: {len(categories)} categories") - - cur.execute(""" - SELECT country + with psycopg.connect(conninfo=self.conninfo) as conn: + with conn.cursor() as cur: + + cur.execute(""" + SELECT class + FROM poi + GROUP BY class + HAVING COUNT(*) > 1 + ORDER BY class + ; + """) + classes = cur.fetchall() + self.categories = [category[0] for category in classes] + + cur.execute(""" + SELECT class, subclass FROM poi - WHERE country is not null GROUP BY country + WHERE subclass NOT LIKE '%;%' + GROUP BY class, subclass HAVING COUNT(*) > 1 - ;""") - countries = cur.fetchall() - countries = [country[0] for country in countries] - - print(f"Loaded countries: {countries}") - - -def get_chains() -> (list[dict]): - with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn: - with conn.cursor() as cur: - cur.execute(""" - SELECT name, color FROM chain - """) - chains = cur.fetchall() - return chains - - -def get_all(country: str, category: str) -> (list[dict]): - if category not in categories: - raise IllegalInstructionException("Category not found") - if country not in countries: - raise IllegalInstructionException("Country not found") - - query = """ -WITH filtered AS ( -SELECT osm_id, name, brand, geom, class, subclass -FROM poi -WHERE class = %(class)s -""" - - params = {'country': country} - - if ':' in category: - query += " AND subclass = %(subclass)s " - class_, sub_class = category.split(':') - params['class'] = class_ - params['subclass'] = sub_class - else: - params['class'] = category - - query += """AND country = %(country)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 filtered - JOIN - ( - SELECT (ST_DUMP(ST_VoronoiPolygons(ST_Collect(geom)))).geom as geom - FROM filtered - ) polygon ON ST_Contains(polygon.geom, filtered.geom) -; + ORDER BY class, subclass + ; + """) + sub_cat = cur.fetchall() + self.categories += [(f"{category[0]}:{category[1]}") for category in sub_cat] + print(f"Loaded: {len(self.categories)} categories") + + cur.execute(""" + SELECT country + FROM poi + WHERE country is not null GROUP BY country + HAVING COUNT(*) > 1 + ;""") + self.countries = cur.fetchall() + self.countries = [country[0] for country in self.countries] + + print(f"Loaded countries: {self.countries}") + + + def get_chains(self) -> (list[dict]): + with psycopg.connect(conninfo=self.conninfo, row_factory=psycopg.rows.dict_row) as conn: + with conn.cursor() as cur: + cur.execute(""" + SELECT name, color FROM chain + """) + chains = cur.fetchall() + return chains + + + def get_all(self, country: str, category: str) -> (list[dict]): + if category not in self.categories: + raise IllegalInstructionException("Category not found") + if country not in self.countries: + raise IllegalInstructionException("Country not found") + + query = """ + WITH filtered AS ( + SELECT osm_id, name, brand, geom, class, subclass + FROM poi + WHERE class = %(class)s """ - with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn: - with conn.cursor() as cur: - cur.execute(query, params) - - all = cur.fetchall() - return all + params = {'country': country} + + if ':' in category: + query += " AND subclass = %(subclass)s " + class_, sub_class = category.split(':') + params['class'] = class_ + params['subclass'] = sub_class + else: + params['class'] = category + + query += """AND country = %(country)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 filtered + JOIN + ( + SELECT (ST_DUMP(ST_VoronoiPolygons(ST_Collect(geom)))).geom as geom + FROM filtered + ) polygon ON ST_Contains(polygon.geom, filtered.geom) + ; + """ + + with psycopg.connect(conninfo=self.conninfo, row_factory=psycopg.rows.dict_row) as conn: + with conn.cursor() as cur: + cur.execute(query, params) + + all = cur.fetchall() + return all diff --git a/src/map_node5_net.py b/src/map_node5_net.py index fa8a425..be5f6f1 100644 --- a/src/map_node5_net.py +++ b/src/map_node5_net.py @@ -1,5 +1,6 @@ import json import os +import pathlib import flask @@ -30,7 +31,6 @@ class ColorFormatter(logging.Formatter): formatter = logging.Formatter(log_fmt) return formatter.format(record) - logger = logging.getLogger(__name__) # Instantiate a logger to be used in this module # Display every message Change this to INFO to see INFO and above (filter out DEBUG) @@ -41,8 +41,15 @@ stream_handler = logging.StreamHandler() # This catches and handles log message stream_handler.setFormatter(ColorFormatter()) logger.root.addHandler(stream_handler) + +db_conninfo = os.environ["DB_CONN"] +logger.debug(f'DB_CONN: {db_conninfo}') +db = db_handler.DBHandler(conninfo=db_conninfo) + + app = flask.Flask(__name__, template_folder='templates', static_folder='static', static_url_path='') + @app.route("/") def index(): return flask.render_template('/index.html') @@ -50,11 +57,11 @@ def index(): @app.route("/chains.json") def chains(): - files = os.listdir(os.path.join(os.getcwd(), 'src', 'static', 'icons')) + files = os.listdir(os.path.join(pathlib.Path(__file__).parent, 'static', 'icons')) files.pop(files.index('Unknown.png')) icon_names = set([os.path.splitext(a)[0] for a in files]) - chains = db_handler.get_chains() + chains = db.get_chains() chain_names = set((a['name'] for a in chains)) if icon_names != chain_names: print(f'WARNING ICON MISSING, OR DB ENTRY MISSING') @@ -64,7 +71,7 @@ def chains(): @app.route("/categories.json") def categories(): - categories = db_handler.categories + categories = db.categories return json.dumps(categories) @app.route("/all.json") @@ -72,7 +79,7 @@ def all(): category_RADIOACTIVE = flask.request.args.get('category') # User input is RADIOACTIVE country_RADIOACTIVE = flask.request.args.get('country') # User input is RADIOACTIVE #logger.debug(f'/all.json requested, input params: category: {category_RADIOACTIVE} country: {country_RADIOACTIVE}') - rows = db_handler.get_all(country_RADIOACTIVE, category_RADIOACTIVE) + rows = db.get_all(country_RADIOACTIVE, category_RADIOACTIVE) for row in rows: coordinates = [] for coordinate in json.loads(row['polygon'])['coordinates'][0]: @@ -80,3 +87,9 @@ def all(): row['polygon'] = coordinates return json.dumps(rows) +def main(): + global app + app.run() + +if __name__ == '__main__': + main() diff --git a/src/static/icons_big/365discount.png b/src/static/icons_big/365discount.png new file mode 100644 index 0000000..f8614d7 Binary files /dev/null and b/src/static/icons_big/365discount.png differ diff --git a/src/static/icons_big/Dagli'Brugsen.png b/src/static/icons_big/Dagli'Brugsen.png new file mode 100644 index 0000000..aea67c5 Binary files /dev/null and b/src/static/icons_big/Dagli'Brugsen.png differ diff --git a/src/static/icons_big/Lidl.png b/src/static/icons_big/Lidl.png new file mode 100644 index 0000000..6d06d06 Binary files /dev/null and b/src/static/icons_big/Lidl.png differ diff --git "a/src/static/icons_big/Min K\303\270bmand.png" "b/src/static/icons_big/Min K\303\270bmand.png" new file mode 100644 index 0000000..65d7f5f Binary files /dev/null and "b/src/static/icons_big/Min K\303\270bmand.png" differ diff --git a/src/static/icons_big/Netto.png b/src/static/icons_big/Netto.png new file mode 100644 index 0000000..7049046 Binary files /dev/null and b/src/static/icons_big/Netto.png differ diff --git a/src/static/icons_big/Rema 1000.png b/src/static/icons_big/Rema 1000.png new file mode 100644 index 0000000..c178545 Binary files /dev/null and b/src/static/icons_big/Rema 1000.png differ diff --git a/src/static/icons_big/SuperBrugsen.png b/src/static/icons_big/SuperBrugsen.png new file mode 100644 index 0000000..455c864 Binary files /dev/null and b/src/static/icons_big/SuperBrugsen.png differ diff --git a/src/static/icons_big/Unknown.png b/src/static/icons_big/Unknown.png new file mode 100644 index 0000000..d15e911 Binary files /dev/null and b/src/static/icons_big/Unknown.png differ -- cgit v1.2.3