diff options
Diffstat (limited to 'src/db_handler.py')
| -rw-r--r-- | src/db_handler.py | 45 |
1 files changed, 41 insertions, 4 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 |