-
Notifications
You must be signed in to change notification settings - Fork 0
/
floodzonemap.sql
57 lines (57 loc) · 1.22 KB
/
floodzonemap.sql
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
-- Create views mapping rentfaster and mls listings to flood maps
DROP VIEW IF EXISTS rfaster_flood;
CREATE OR REPLACE VIEW rfaster_flood AS
WITH
cte_100_flood
AS
(
SELECT geom
FROM floodmap
WHERE scenario = '1 in 100 chance flood'
),
cte_20_flood
AS
(
SELECT geom
FROM floodmap
WHERE scenario = '1 in 20 chance flood'
)
SELECT
rfaster.rfaster_id AS rfaster_flood_id,
CASE
WHEN ST_Contains(cte_20_flood.geom, rfaster.geom)
THEN '1 in 20 flood risk'
WHEN ST_Contains(cte_100_flood.geom, rfaster.geom)
THEN '1 in 100 flood risk'
ELSE 'Outside flood map'
END AS flood_risk
FROM rfaster, cte_20_flood, cte_100_flood
;
DROP VIEW IF EXISTS mls_flood;
CREATE OR REPLACE VIEW mls_flood AS
WITH
cte_100_flood
AS
(
SELECT geom
FROM floodmap
WHERE scenario = '1 in 100 chance flood'
),
cte_20_flood
AS
(
SELECT geom
FROM floodmap
WHERE scenario = '1 in 20 chance flood'
)
SELECT
mls.mls_id AS mls_flood_id,
CASE
WHEN ST_Contains(cte_20_flood.geom, mls.geom)
THEN '1 in 20 flood risk'
WHEN ST_Contains(cte_100_flood.geom, mls.geom)
THEN '1 in 100 flood risk'
ELSE 'Outside flood map'
END AS flood_risk
FROM mls, cte_20_flood, cte_100_flood
;