· 6 years ago · Apr 01, 2019, 01:52 PM
1\set table_name ign_bdadresse.address
2
3DROP TABLE IF EXISTS :table_name CASCADE;
4
5
6-- select and rename columns
7CREATE TEMPORARY VIEW temp0 AS
8(
9SELECT t1.id,
10 NULLIF(t1.numero, 0) AS road_number,
11 NULLIF(REGEXP_REPLACE(t1.rep, '\mNR\M', ''), '') AS repetition_index,
12 NULLIF(REGEXP_REPLACE(t1.compl, '\mNR\M', ''), '') AS entrance_location,
13 NULLIF(REGEXP_REPLACE(t1.nom_voie, '\mNR\M', ''), '') AS road_name,
14 NULLIF(REGEXP_REPLACE(t1.alias, '\mNR\M', ''), '') AS alternative_road_name,
15 NULLIF(REGEXP_REPLACE(t1.nom_ld, '\mNR\M', ''), '') AS locality_name,
16 NULLIF(t1.code_insee, '') AS insee_code,
17 NULLIF(t1.code_post, '') AS zipcode,
18 ST_Transform(t1.geom, 4326) AS geometry -- converting geometry to lat,lon
19 -- I have not included hexacle_1 and hexacle_2 because there are empty
20FROM ign_bdadresse.raw_address AS t1
21);
22
23-- in case there is a locality name we will put it as road_name to be able to look for it in fantoir and get its
24-- rivoli code, we also update the actual town name with the insee_code
25CREATE TEMPORARY VIEW update_town_name AS
26(
27SELECT
28 t1.id,
29 t1.road_number,
30 t1.repetition_index,
31 t1.entrance_location,
32 COALESCE(t1.locality_name, t1.road_name) AS road_name,
33 t1.alternative_road_name,
34 t2.town_name,
35 t1.insee_code,
36 t1.zipcode,
37 t1.geometry
38FROM temp0 AS t1
39LEFT JOIN ign_bdadresse.town AS t2
40ON t1.insee_code = t2.insee_code
41);
42
43-- now we update the insee code with the insee schema (cf #313)
44CREATE TEMPORARY VIEW update_insee_code AS
45(
46SELECT
47 t1.id,
48 t1.road_number,
49 t1.repetition_index,
50 t1.entrance_location,
51 t1.road_name,
52 t1.alternative_road_name,
53 t1.town_name,
54 t2.insee_code_2019 AS insee_code,
55 t1.zipcode,
56 t1.geometry
57FROM update_town_name AS t1
58LEFT JOIN insee.localities_match_table AS t2
59ON t1.insee_code = t2.insee_code
60);
61
62-- replace IGN abbreviation with their full name (road type, eg: r -> rue)
63CREATE TEMPORARY VIEW temp1 AS
64(
65SELECT id,
66 split_part(road_name, ' ', 1) AS road_name_p1,
67 SUBSTRING(road_name, LENGTH(split_part(road_name, ' ', 1)) + 2, LENGTH(road_name)) AS road_name_p2
68FROM update_insee_code
69);
70
71
72CREATE TEMPORARY VIEW temp2 AS
73(
74SELECT temp1.id,
75 ab.name AS road_type,
76 temp1.road_name_p2 AS road_name,
77 CONCAT_WS(' ', temp1.road_name_p1, temp1.road_name_p2) AS full_name
78FROM temp1
79LEFT JOIN public.road_type_abbreviation AS ab
80ON temp1.road_name_p1 = ab.ign_abbrev
81);
82
83CREATE TEMPORARY VIEW vr AS
84(
85SELECT
86 t1.id,
87 CASE
88 WHEN LOWER(t1.cote) = 'gauche' THEN t2.left_rivoli_code
89 WHEN LOWER(t1.cote) = 'droite' THEN t2.right_rivoli_code
90 ELSE NULL
91 END AS rivoli
92FROM ign_bdadresse.raw_address AS t1
93LEFT JOIN
94 (
95 SELECT
96 id,
97 left_rivoli_code,
98 right_rivoli_code
99 FROM ign_bdadresse.road_address
100 ) t2
101ON t1.id_tr = t2.id
102);
103
104CREATE TEMPORARY VIEW temp3 AS
105(
106SELECT
107 t2.id,
108 vr.rivoli AS rivoli_old,
109 t0.road_number,
110 CASE
111 WHEN t0.repetition_index IS NOT NULL THEN UPPER(LEFT(t0.repetition_index, 1))
112 ELSE NULL
113 END AS repetition_index, -- issue #261
114 NULLIF(t2.road_type, '') AS road_type,
115 CASE
116 WHEN t2.road_type IS NULL THEN NULLIF(t2.full_name, '')
117 ELSE NULLIF(t2.road_name, '')
118 END AS road_name,
119 t0.entrance_location,
120 t0.alternative_road_name,
121 t0.town_name,
122 t0.insee_code,
123 t0.zipcode,
124 t0.geometry
125FROM temp2 AS t2
126LEFT JOIN update_insee_code AS t0
127ON t2.id = t0.id
128LEFT JOIN vr AS vr
129ON vr.id = t0.id
130);
131
132
133CREATE TEMPORARY VIEW temp4 AS
134(
135SELECT
136 *,
137 COALESCE(infer_fantoir_rivoli(insee_code, road_type, road_name), rivoli_old) AS rivoli
138FROM temp3
139);
140
141
142CREATE TABLE :table_name AS
143(
144SELECT
145 *,
146 create_address_rivoli_id(road_number, repetition_index, insee_code, rivoli) AS address_rivoli_id
147FROM temp4
148);
149
150
151
152-- next step is to delete duplicated addresses with same id
153-- see: https://stackoverflow.com/questions/6583916/delete-duplicate-records-in-postgresql/12963112#12963112
154DELETE FROM :table_name a USING (
155 SELECT MIN(ctid) as ctid, id
156 FROM :table_name
157 GROUP BY id HAVING COUNT(*) > 1
158 ) b
159 WHERE a.id = b.id
160 AND a.ctid <> b.ctid;
161
162
163
164CREATE TEMPORARY VIEW ambiguous_address_rivoli_id AS --find duplicated address_rivoli_id, set them to NULL
165(
166SELECT address_rivoli_id
167FROM (SELECT address_rivoli_id, COUNT(1) AS n FROM :table_name GROUP BY address_rivoli_id) f
168WHERE n > 1
169);
170
171UPDATE :table_name
172 SET address_rivoli_id = NULL
173 WHERE address_rivoli_id IN (SELECT address_rivoli_id FROM ambiguous_address_rivoli_id);
174
175
176-- update the town name for Paris, Marseille and Lyon which have special insee codes like 75116 for Paris
177-- and are not referenced in the table
178UPDATE :table_name SET town_name = 'Paris' WHERE left(insee_code, 3) = '751';
179UPDATE :table_name SET town_name = 'Lyon' WHERE left(insee_code, 3) = '693';
180UPDATE :table_name SET town_name = 'Marseille' WHERE left(insee_code, 3) = '132';
181
182-- now add constraints
183ALTER TABLE :table_name ADD PRIMARY KEY (id);
184ALTER TABLE :table_name ADD CONSTRAINT ign_bdadresse_address_unique1 UNIQUE (address_rivoli_id);
185CREATE INDEX ign_bdadresse_address_idx1 ON :table_name (address_rivoli_id) WHERE (address_rivoli_id is not null);