stride-db/stride_database_update_queries.sql at main · BeatsBySak/stride-db · GitHub

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
-- SELECT *
-- FROM edge_walkability_scores
-- WHERE edge_id = 24395;

-- SELECT *
-- FROM edge_walkability_scores
-- LIMIT 100;

-- SELECT COUNT(*)
-- FROM ways;
-- SELECT w.id, w.name, s.base_safety_cost, w.geom
-- FROM ways w
-- JOIN edge_walkability_scores s ON w.id = s.edge_id
-- WHERE w.geom && ST_MakeEnvelope(-84.3895, 33.7710, -84.3815, 33.7820, 4326);

-- CREATE TABLE manual_edges(edge_id);

-- INSERT INTO manual_edges (edge_id)
-- SELECT CAST(value AS BIGINT)
-- FROM regexp_split_to_table(
-- $$
-- 18039
-- 4307
-- 10516
-- 12058
-- 22827
-- 4053
-- 14524
-- 10515
-- 9414
-- 19134
-- $$,
-- E'\n'
-- ) AS value
-- WHERE value <> '';

-- UPDATE edge_walkability_scores s
-- SET
--     sidewalk_quality = vals.sidewalk,
--     lighting_density = vals.lighting,
--     business_activity = vals.business
-- FROM ways w,
-- LATERAL (
--     SELECT
--         FLOOR(2 + RANDOM() * 3) AS sidewalk,
--         FLOOR(RANDOM() * 3) AS lighting,
--         FLOOR(RANDOM() * 3) AS business
-- ) vals
-- WHERE w.id = s.edge_id
--   AND w.geom && ST_MakeEnvelope(-84.3895, 33.7710, -84.3815, 33.7820, 4326)
--   AND s.edge_id NOT IN (
--       SELECT edge_id FROM manual_edges
--   );

-- SELECT
--     w.id,
--     w.name,
--     s.sidewalk_quality,
--     s.lighting_density,
--     s.business_activity,
--     s.base_safety_cost,
-- 	s.base_night_safety_cost
-- FROM ways w
-- JOIN edge_walkability_scores s
--   ON w.id = s.edge_id
-- WHERE w.geom && ST_MakeEnvelope(-84.3895, 33.7710, -84.3815, 33.7820, 4326);

-- UPDATE edge_walkability_scores s
-- SET base_safety_cost = GREATEST(6 * (5 - s.sidewalk_quality) + 1 * (2 - s.lighting_density) + 3 * (2 - s.business_activity))
-- FROM ways w
-- WHERE w.id = s.edge_id
--   AND w.geom && ST_MakeEnvelope(-84.3895, 33.7710, -84.3815, 33.7820, 4326);

-- ALTER TABLE edge_walkability_scores
-- ADD COLUMN base_night_safety_cost DOUBLE PRECISION;

-- UPDATE edge_walkability_scores s
-- SET base_night_safety_cost = GREATEST(2 * (5 - s.sidewalk_quality) + 5 * (2 - s.lighting_density) + 3 * (2 - s.business_activity))
-- FROM ways w
-- WHERE w.id = s.edge_id
--   AND w.geom && ST_MakeEnvelope(-84.3895, 33.7710, -84.3815, 33.7820, 4326);