1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
|
import os
import psycopg
import yaml
with open(os.path.join('configs', 'database.yml'), 'r') as file:
db_con_params = yaml.safe_load(file.read())
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() -> (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:
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 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()
return voronoi_polygons
|
