-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathet.sql
More file actions
131 lines (131 loc) · 11.1 KB
/
et.sql
File metadata and controls
131 lines (131 loc) · 11.1 KB
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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
select
distinct coalesce(nc.street_address, '')::text as civic_address,
case
when nc.region = 'Okanagan' then 'OKAR'
when nc.region = 'South Coast' then 'SOUR'
when nc.region = 'Kootenay' then 'KOOR'
when nc.region = 'Interior' then 'INTR'
when nc.region = 'Island' then 'ISLR'
when nc.region = 'North' then 'NORR'
end::text as region_code,
nc.latitude::numeric(10, 7) as latitude,
nc.longitude::numeric(10, 7) as longitude,
case
when nc.ownership_type = 'Fee Simple' then 'SMPL'
when nc.ownership_type = 'Crown' then 'CRWN'
end as ownership_type_code,
nc.primary_location_pid::text as pid,
nc.area::numeric(10, 2) as area_hectares,
nc.percent_of_alr::numeric(5, 2) as alr_percentage,
coalesce(nc.application_history_prior_c_e_issues, 'Migrated from NRIS, please verify using LAN folder')::text as alc_history,
ace.uuid as file_uuid,
case
when nc.local_government = 'Alberni-Clayoquot' then 'e4b0f43c-7c7d-47cd-af05-24b6313d80b0'
when nc.local_government = 'Bowen Island (Island Municipality)' then '6e5da7c6-9a72-44c5-8b8d-690439fb7cd1'
when nc.local_government = 'Bulkley-Nechako' then '0a0c9cad-ef11-4b85-a3d9-1d0d055d7e0f'
when nc.local_government = 'Capital' then 'beb93bee-6c60-404b-979b-2567a1a34492'
when nc.local_government = 'Cariboo' then '773a4edc-7af3-4dcb-b9ae-28bea98099a1'
when nc.local_government = 'Central Coast' then '51b0522b-b6e3-4d8f-904b-3413b33f936e'
when nc.local_government = 'Central Kootenay' then 'eb9a3858-b065-46cf-8bf7-73cdb5b6c2f7'
when nc.local_government = 'Central Okanagan' then 'c180bc79-c78b-4c58-91a6-aa3e92408d15'
when nc.local_government = 'Chilliwack' then 'cf727428-c2c7-4478-8924-6b8d41570a17'
when nc.local_government = 'City of Chilliwack' then 'cf727428-c2c7-4478-8924-6b8d41570a17'
when nc.local_government = 'City of Abbotsford' then 'b30fcb96-d5c5-498c-933c-22563f25b606'
when nc.local_government = 'City of Armstrong' then 'f9364f76-10bb-4036-9f0f-9e497e71c06f'
when nc.local_government = 'City of Burnaby' then '72a9c8c8-28cb-4b08-b79a-8afebabdd327'
when nc.local_government = 'City of Coquitlam' then '4754ed2c-100d-4bba-ac60-6a97232a9a31'
when nc.local_government = 'City of Courtenay' then 'b9195d80-81b4-42c5-9047-e1a1a00a04f0'
when nc.local_government = 'City of Dawson Creek' then '8d2378a8-edef-4b04-8b13-8aa67603f4f5'
when nc.local_government = 'City of Delta' then 'f1db2dca-61f5-46ac-819c-2c5575b007c9'
when nc.local_government = 'City of Kamloops' then 'ac411bf2-66b3-401d-9155-27f3026bbed3'
when nc.local_government = 'City of Kelowna' then 'cc7cebbb-d126-46bb-8a7a-6936f20ddce9'
when nc.local_government = 'City of Langford' then 'c438ec9e-8446-4b15-a794-629da2330bb8'
when nc.local_government = 'City of Langley' then '825f2ecf-fac1-467c-a96c-7b29473073b3'
when nc.local_government = 'City of Maple Ridge' then '8c834efa-9c33-4f46-a2c7-b0cfdedece04'
when nc.local_government = 'City of Merritt' then 'd57ff5bc-5715-4017-ab5c-fa8d00334bec'
when nc.local_government = 'City of Nanaimo' then 'afaa920f-7cd3-43c3-a7a1-bcd77b4a2af0'
when nc.local_government = 'City of Penticton' then '9fda6054-e4e5-4fcb-9752-174abc8ee9ea'
when nc.local_government = 'City of Pitt Meadows' then '9da89a5c-9a27-4371-8e60-d93a4c85e4a9'
when nc.local_government = 'City of Port Coquitlam' then '3115b2e5-b038-4292-b238-eadc032de2e7'
when nc.local_government = 'City of Powell River' then '7d2a7a18-2e2e-4a45-a3d3-0543652810e4'
when nc.local_government = 'City of Prince George' then '692b797a-d4a3-4c02-b60d-6f7c559665d8'
when nc.local_government = 'City of Richmond' then '961e2cf5-1508-4775-80c4-164f1199585d'
when nc.local_government = 'City of Salmon Arm' then 'b2883559-a22c-49dc-b6f7-20fe9117d085'
when nc.local_government = 'City of Surrey' then 'dd81e07c-499d-4416-bdfa-2a70f08302ce'
when nc.local_government = 'City of Vancouver' then 'de39716c-a867-4403-86da-bb6b05a34908'
when nc.local_government = 'City of Vernon' then 'f625fb10-c89e-49ac-afac-41b50e7ad3c3'
when nc.local_government = 'City of West Kelowna' then 'd5636832-75e1-4f64-ad25-046a3cf594a2'
when nc.local_government = 'Columbia Shuswap' then '614c63a6-599f-4c3e-bd51-43a0be5eaecd'
when nc.local_government = 'Comox Valley' then '693e3cfa-ce7f-43b4-8952-a4b02bb273f1'
when nc.local_government = 'Comox-Strathcona (Historical)' then 'ed2cfbb2-2139-4542-8063-212fd2654c9e'
when nc.local_government = 'Cowichan Valley' then 'abfe022f-64f2-472b-b312-afc0c9ae7e8f'
when nc.local_government = 'District of 100 Mile House' then '8eaa02e6-fd7d-4f51-89f7-b881b8137fa2'
when nc.local_government = 'District of Central Saanich' then 'c6109982-ec0c-4624-91d7-77c4405bbf71'
when nc.local_government = 'District of Coldstream' then '35906d62-8e9a-4170-9666-c21d2e16a506'
when nc.local_government = 'District of Delta' then 'f1db2dca-61f5-46ac-819c-2c5575b007c9'
when nc.local_government = 'District of Hope' then 'b17f2d87-2952-409d-a0d7-5cbbb76a11f5'
when nc.local_government = 'District of Hudson''s Hope' then 'df19457c-4511-4104-bc85-9675412a6359'
when nc.local_government = 'District of Kent' then 'bf6277e3-ad02-418f-bc0b-c61db4a3e469'
when nc.local_government = 'District of Lake Country' then '311704d0-8db8-4789-90f2-ac15c6437b3f'
when nc.local_government = 'District of Lantzville' then 'df1dea47-66e3-48ef-925f-d6f7ac40f688'
when nc.local_government = 'District of Lillooet' then '5ca6cd49-989a-430c-8805-a45a7c95bc71'
when nc.local_government = 'District of Metchosin' then 'b0d9e0fe-742e-4158-a9c5-3e06e9a42145'
when nc.local_government = 'District of Mission' then 'd9d9545b-748a-46da-85d4-fee20c0fb124'
when nc.local_government = 'District of North Cowichan' then '8db386a1-93a6-4848-8b49-68af04464bc8'
when nc.local_government = 'District of North Saanich' then 'a1457f58-7566-4222-acb1-8b7fdeb27810'
when nc.local_government = 'District of Peachland' then 'a1bdd9cd-fd63-46a8-a925-f38f81a3b5a1'
when nc.local_government = 'District of Saanich' then '8cc35f6b-bd3f-4985-8bca-7607889de964'
when nc.local_government = 'District of Sechelt' then '71dfa376-1bfc-4e0f-a753-b65e594be1e5'
when nc.local_government = 'District of Sooke' then '991b9a44-01bb-459b-8b92-115ca49af99f'
when nc.local_government = 'District of Summerland' then '919716db-c316-4ae5-92ab-74b0a4e5e3b6'
when nc.local_government = 'East Kootenay' then 'a3e3dd07-9efa-4a01-ac38-5dafa43911c3'
when nc.local_government = 'Fraser Fort George' then '5f144a91-b3f7-458e-872b-30eb8ddc06a8'
when nc.local_government = 'Fraser Valley' then '6b0d5e6b-69f7-4287-a941-a1abc99cdd04'
when nc.local_government = 'Islands Trust' then '597a183d-e958-468c-ac4d-b62affe1594a'
when nc.local_government = 'Islands Trust Gabriola Island' then '927ece9d-b4e3-4862-aa85-67539d9abb0d'
when nc.local_government = 'Islands Trust Hornby Island' then 'd325a7a4-fe68-49b0-9df2-599beb06766e'
when nc.local_government = 'Islands Trust Pender Island' then '13de89f7-1c6b-487c-87c8-d5089195f8c4'
when nc.local_government = 'Islands Trust Salt Spring Island' then 'dc0357b1-aacd-45ba-8fa9-0e62cbe88e9f'
when nc.local_government = 'Islands Trust- Comox Strathcona' then '88532d18-3059-4720-986a-363d9bce6be0'
when nc.local_government = 'Islands Trust- Nanaimo' then '4e721d39-09b3-4f9b-ad1f-601a69315808'
when nc.local_government = 'Islands Trust-Capital' then '9355bf78-280a-4695-adad-fafa06969c99'
when nc.local_government = 'Kitimat Stikine' then '35be0556-d281-4f7c-8b0a-8f6cdc202425'
when nc.local_government = 'Kootenay Boundary' then 'c4ddce75-e3bb-4b8d-87a2-a199fe31d665'
when nc.local_government = 'Metro Vancouver' then 'e30d6777-1345-4f63-9e63-bc7bbad5067a'
when nc.local_government = 'Multiple Jurisdictions for Capital' then 'a1ba516d-3637-42f6-9727-12b6e0d04d8a'
when nc.local_government = 'Multiple Jurisdictions for East Kootenay' then '3ef6f25c-8b5d-49f4-b6d1-5e167e9252ba'
when nc.local_government = 'Multiple Jurisdictions for Fraser Valley' then '8fde1050-463c-4226-acfb-b58263cb2387'
when nc.local_government = 'Multiple Jurisdictions for Fraser-Fort George' then '1777e5b5-5520-406d-8b05-05872478f112'
when nc.local_government = 'Multiple Jurisdictions for Kitimat-Stikine' then '2e485e96-81c6-4f4d-8b25-bb848da1a994'
when nc.local_government = 'Multiple Jurisdictions for Nanaimo' then '8a0002dd-5783-4822-8c29-6b60e421e5eb'
when nc.local_government = 'Multiple Jurisdictions for Okanagan-Similkameen' then '70960e85-252d-41ef-a1d5-6f4026d73fff'
when nc.local_government = 'Multiple Jurisdictions for Squamish-Lillooet' then 'fb2972f5-b5b7-435a-b667-492028f8beb6'
when nc.local_government = 'Multiple Jurisdictions for Thompson-Nicola' then 'f0bd3817-5825-4656-b685-d306ddd1fa70'
when nc.local_government = 'Nanaimo' then 'ba5bd974-ad17-48cf-ab1c-55abfa91c291'
when nc.local_government = 'North Okanagan' then '29a7e1ae-ba19-4dda-a7b8-7ae5a0a272d8'
when nc.local_government = 'Northern Rockies' then '33aa1f7d-3b65-4ed5-badf-11dafb0b2789'
when nc.local_government = 'Okanagan Similkameen' then '1aa6ba16-51e8-4789-8624-67ac30f25636'
when nc.local_government = 'Okanagan-Similkameen' then '1aa6ba16-51e8-4789-8624-67ac30f25636'
when nc.local_government = 'Peace River' then '001cfdad-bc6e-4d25-9294-1550603da980'
when nc.local_government = 'Peace River Regional District' then '001cfdad-bc6e-4d25-9294-1550603da980'
when nc.local_government = 'Powell River' then '437aa538-47ac-4b54-a512-a99ea976ced1'
when nc.local_government = 'qathet Regional District' then '437aa538-47ac-4b54-a512-a99ea976ced1'
when nc.local_government = 'Squamish Lillooet' then 'ba21a867-8a74-4364-95b9-4c3648591ba4'
when nc.local_government = 'Strathcona' then '2d778f4d-27a2-4f92-a561-f4b626f6b5d8'
when nc.local_government = 'Sunshine Coast' then '036e3d84-6ea4-4293-a44d-85a44bbad7c2'
when nc.local_government = 'Thompson Nicola' then '1549f188-b6a6-4401-92d5-d3a480c74a2d'
when nc.local_government = 'Town of Gibsons' then 'a2978d25-77e9-481b-9f84-726894b4570f'
when nc.local_government = 'Town of Lake Cowichan' then 'afb8feab-5604-4257-95cc-bd036251e387'
when nc.local_government = 'Town of Oliver' then 'a6ae4789-3fa5-463f-90a3-68295d4a83ab'
when nc.local_government = 'Town of Osoyoos' then 'd4a9a573-749b-45df-bef9-9e5435807144'
when nc.local_government = 'Town of Qualicum Beach' then '5a5b3fff-f341-40aa-9377-08c52d5e1def'
when nc.local_government = 'Township of Langley' then 'cea7033d-f36d-42c1-bc2a-77e25c189436'
when nc.local_government = 'Township of Spallumcheen' then '516876da-c350-4c4a-9379-224633972938'
when nc.local_government = 'Village of Midway' then 'c7e91940-0d7a-4cc8-8beb-c5471cc257c5'
when nc.local_government = 'Village of Nakusp' then '884bc103-5191-4c67-988b-5ebb074cf5d5'
when nc.local_government = 'Village of Pemberton' then '87b7e1f6-4946-4714-a7c9-c4777058d2a5'
end::uuid as local_government_uuid
from
nris.complaint nc
join alcs.compliance_and_enforcement ace on ace.file_number = nc.record_id
join alcs.compliance_and_enforcement_responsible_party acerp on acerp.file_uuid = ace.uuid;