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 --- data_extractor/Makefile | 32 ++--- flake.lock | 46 ++++++ flake.nix | 52 +++++++ pyproject.toml | 16 +++ shell.nix | 18 +++ 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 15 files changed, 261 insertions(+), 118 deletions(-) create mode 100644 flake.lock create mode 100644 flake.nix create mode 100644 pyproject.toml create mode 100644 shell.nix 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 diff --git a/data_extractor/Makefile b/data_extractor/Makefile index b9bbb1d..3506e68 100644 --- a/data_extractor/Makefile +++ b/data_extractor/Makefile @@ -1,35 +1,33 @@ -.PHONY: process_data -SHELL := /bin/bash +.PHONY: process_data hamburt denmark permission count_per_country db_shell notify_desktop process_data -pbfs/hamburg-latest.osm.pbf: +pbfs: + mkdir pbfs + +pbfs/hamburg-latest.osm.pbf: pbfs cd pbfs && wget https://download.geofabrik.de/europe/germany/hamburg-latest.osm.pbf -pbfs/denmark-latest.osm.pbf: +pbfs/denmark-latest.osm.pbf: pbfs cd pbfs && wget https://download.geofabrik.de/europe/denmark-latest.osm.pbf hamburg: pbfs/hamburg-latest.osm.pbf # Load Hamburg - # sudo -u postgres osm2pgsql -d map.node5.net -O flex -S data_extractor.lua pbfs/hamburg-latest.osm.pbf --slim --append - sudo -u postgres psql -d map.node5.net -c "update poi set region = 'Hamburg' where region IS NULL;" - sudo -u postgres psql -d map.node5.net -c "update poi set country = 'Germany' where country IS NULL;" + # sudo -u postgres osm2pgsql -d map -O flex -S data_extractor.lua pbfs/hamburg-latest.osm.pbf --slim --append + sudo -u postgres psql -d map -c "update poi set region = 'Hamburg' where region IS NULL;" + sudo -u postgres psql -d map -c "update poi set country = 'Germany' where country IS NULL;" -denmark: +denmark: pbfs/denmark-latest.osm.pbf # Load Denmark, this resets DB - sudo -u postgres osm2pgsql -d map.node5.net -O flex -S data_extractor.lua pbfs/denmark-latest.osm.pbf --slim - sudo -u postgres psql -d map.node5.net -c "update poi set country = 'Denmark' where country IS NULL;" - -permission: - # Set code user permission - sudo -u postgres psql -d map.node5.net -c 'grant select on table poi to "map.node5.net";' + sudo -u postgres osm2pgsql -d map -O flex -S data_extractor.lua pbfs/denmark-latest.osm.pbf --slim + sudo -u postgres psql -d map -c "update poi set country = 'Denmark' where country IS NULL;" count_per_country: - sudo -u postgres psql -d map.node5.net -c "select country, count(*) from poi group by country;" + sudo -u postgres psql -d map -c "select country, count(*) from poi group by country;" db_shell: - sudo -u postgres psql -d map.node5.net + sudo -u postgres psql -d map notify_desktop: - notify-send "OSM processing" "done" + notify-send "OSM processing" "done" || true process_data: denmark hamburg count_per_country notify_desktop diff --git a/flake.lock b/flake.lock new file mode 100644 index 0000000..31150c6 --- /dev/null +++ b/flake.lock @@ -0,0 +1,46 @@ +{ + "nodes": { + "nixpkgs": { + "locked": { + "lastModified": 1781607440, + "narHash": "sha256-rxO+uc/KFbSJp+pgyXRuAX6QlG9hJdnt0BXpEQRXY+U=", + "owner": "NixOS", + "repo": "nixpkgs", + "rev": "3e41b24abd260e8f71dbe2f5737d24122f972158", + "type": "github" + }, + "original": { + "id": "nixpkgs", + "type": "indirect" + } + }, + "pyproject-nix": { + "inputs": { + "nixpkgs": [ + "nixpkgs" + ] + }, + "locked": { + "lastModified": 1781585874, + "narHash": "sha256-T4e8kqZ/fsuqYWbcXDcr0EBLClh3VRKVV1XcL/ibf3A=", + "owner": "nix-community", + "repo": "pyproject.nix", + "rev": "112aebcc00ecf20d48a5c08e80660a6852a4707a", + "type": "github" + }, + "original": { + "owner": "nix-community", + "repo": "pyproject.nix", + "type": "github" + } + }, + "root": { + "inputs": { + "nixpkgs": "nixpkgs", + "pyproject-nix": "pyproject-nix" + } + } + }, + "root": "root", + "version": 7 +} diff --git a/flake.nix b/flake.nix new file mode 100644 index 0000000..d197227 --- /dev/null +++ b/flake.nix @@ -0,0 +1,52 @@ +{ + description = "A basic flake using pyproject.toml project metadata"; + + inputs = { + pyproject-nix = { + url = "github:nix-community/pyproject.nix"; + inputs.nixpkgs.follows = "nixpkgs"; + }; + }; + + outputs = { nixpkgs, pyproject-nix, ... }: + let + inherit (nixpkgs) lib; + + project = pyproject-nix.lib.project.loadPyproject { + # Read & unmarshal pyproject.toml relative to this project root. + # projectRoot is also used to set `src` for renderers such as buildPythonPackage. + projectRoot = ./.; + }; + + # This example is only using x86_64-linux + pkgs = nixpkgs.legacyPackages.x86_64-linux; + + python = pkgs.python3; + + # Returns an attribute set that can be passed to `buildPythonPackage`. + attrs = project.renderers.buildPythonPackage { inherit python; }; + + pkg = python.pkgs.buildPythonPackage (attrs // { + meta = { + description = "Webinterface for map.node5.net"; + homepage = "https://map.node5.net/"; + changelog = "https://git.node5.net/map.node5.net/log/"; + mainProgram = "node5-map"; + }; + propagatedBuildInputs = (attrs.propagatedBuildInputs or []) ++ [ pkgs.git ]; # Make git binary available + postInstall = '' + echo $src + echo $out + cp -r $src/src/static/ $out/${python.sitePackages} + cp -r $src/src/templates/ $out/${python.sitePackages} + ''; + }); + + # Add missing templates and static content, that wasn't auto discovered by the python build process + in + { + packages.x86_64-linux.default = pkg; + pythonPath = "${python.pkgs.makePythonPath attrs.dependencies}:${pkg}/${python.sitePackages}"; + }; +} + diff --git a/pyproject.toml b/pyproject.toml new file mode 100644 index 0000000..37d0fc5 --- /dev/null +++ b/pyproject.toml @@ -0,0 +1,16 @@ +[project] +name = "map.node5.net" +version = "0.1.0" +description = "Flask project to host map.node5.net map" + +# define any Python dependencies +dependencies = [ + "Flask~=3.0", + "PyYAML~=6.0", + "psycopg~=3.1", +] + +# define the CLI executable +# Here, we define the entry point to be the 'main()' function in the module 'app/main.py' +[project.scripts] +node5-map = "map_node5_net:main" diff --git a/shell.nix b/shell.nix new file mode 100644 index 0000000..266b449 --- /dev/null +++ b/shell.nix @@ -0,0 +1,18 @@ +{ pkgs ? import {} }: + +pkgs.mkShell { + buildInputs = with pkgs; [ + gnumake + python313 # Specify the Python version (adjust if needed) + python313Packages.flask + python313Packages.pyyaml + python313Packages.psycopg + osm2pgsql + wget + ]; + + shellHook = '' + # Runs when starting shell + ''; +} + 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