source: subversion/applications/utils/export/osm2pgsql/gazetteer/gazetteer-functions.sql @ 18873

Last change on this file since 18873 was 18873, checked in by twain, 6 years ago

add test in the associatedStreet code to ensure associatedStreet is actually a road

File size: 106.8 KB
Line 
1--DROP TRIGGER IF EXISTS place_before_insert on placex;
2--DROP TRIGGER IF EXISTS place_before_update on placex;
3--CREATE TYPE addresscalculationtype AS (
4--  word text,
5--  score integer
6--);
7
8
9CREATE OR REPLACE FUNCTION isbrokengeometry(place geometry) RETURNS BOOLEAN
10  AS $$
11DECLARE
12  NEWgeometry geometry;
13BEGIN
14  NEWgeometry := place;
15  IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') THEN 
16    RETURN true;
17  END IF;
18  RETURN false;
19END;
20$$
21LANGUAGE plpgsql IMMUTABLE;
22
23CREATE OR REPLACE FUNCTION clean_geometry(place geometry) RETURNS geometry
24  AS $$
25DECLARE
26  NEWgeometry geometry;
27BEGIN
28  NEWgeometry := place;
29  IF ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') THEN 
30    NEWgeometry := ST_buffer(NEWgeometry,0);
31    IF ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') THEN 
32      RETURN ST_SetSRID(ST_Point(0,0),4326);
33    END IF;
34  END IF;
35  RETURN NEWgeometry;
36END;
37$$
38LANGUAGE plpgsql IMMUTABLE;
39
40CREATE OR REPLACE FUNCTION geometry_sector(place geometry) RETURNS INTEGER
41  AS $$
42DECLARE
43  NEWgeometry geometry;
44BEGIN
45--  RAISE WARNING '%',place;
46  NEWgeometry := place;
47  IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') THEN 
48    NEWgeometry := ST_buffer(NEWgeometry,0);
49    IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') THEN 
50      RETURN NULL;
51    END IF;
52  END IF;
53  RETURN (500-ST_X(ST_Centroid(NEWgeometry))::integer)*1000 + (500-ST_Y(ST_Centroid(NEWgeometry))::integer);
54END;
55$$
56LANGUAGE plpgsql IMMUTABLE;
57
58CREATE OR REPLACE FUNCTION debug_geometry_sector(osmid integer, place geometry) RETURNS INTEGER
59  AS $$
60DECLARE
61  NEWgeometry geometry;
62BEGIN
63--  RAISE WARNING '%',osmid;
64  IF osmid = 61315 THEN
65    return null;
66  END IF;
67  NEWgeometry := place;
68  IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') THEN 
69    NEWgeometry := ST_buffer(NEWgeometry,0);
70    IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') THEN 
71      RETURN NULL;
72    END IF;
73  END IF;
74  RETURN (500-ST_X(ST_Centroid(NEWgeometry))::integer)*1000 + (500-ST_Y(ST_Centroid(NEWgeometry))::integer);
75END;
76$$
77LANGUAGE plpgsql IMMUTABLE;
78
79CREATE OR REPLACE FUNCTION geometry_index(place geometry,indexed BOOLEAN,name keyvalue[]) RETURNS INTEGER
80  AS $$
81BEGIN
82IF indexed THEN RETURN NULL; END IF;
83IF name is null THEN RETURN NULL; END IF;
84RETURN geometry_sector(place);
85END;
86$$
87LANGUAGE plpgsql IMMUTABLE;
88
89CREATE OR REPLACE FUNCTION create_tables_location_point() RETURNS BOOLEAN
90  AS $$
91DECLARE
92  i INTEGER;
93  postfix TEXT;
94BEGIN
95
96  FOR i in 0..26 LOOP
97
98    postfix := '_'||i;
99
100    EXECUTE 'drop table IF EXISTS location_point'||postfix;
101    EXECUTE 'CREATE TABLE location_point'||postfix||' ('||
102      'place_id bigint,'||
103      'country_code varchar(2),'||
104      'name keyvalue[],'||
105      'keywords TEXT[],'||
106      'rank_search INTEGER NOT NULL,'||
107      'rank_address INTEGER NOT NULL,'||
108      'is_area BOOLEAN NOT NULL'||
109      ')';
110   EXECUTE 'SELECT AddGeometryColumn(''location_point'||postfix||''', ''centroid'', 4326, ''POINT'', 2)';
111   EXECUTE 'CREATE INDEX idx_location_point'||postfix||'_centroid ON location_point'||postfix||' USING GIST (centroid)';
112   EXECUTE 'CREATE INDEX idx_location_point'||postfix||'_sector ON location_point'||postfix||' USING BTREE (geometry_sector(centroid))';
113   EXECUTE 'CREATE INDEX idx_location_point'||postfix||'_place_id ON location_point'||postfix||' USING BTREE (place_id)';
114   EXECUTE 'CLUSTER location_point'||postfix||' USING idx_location_point'||postfix||'_sector';
115  END LOOP;
116
117  RETURN true;
118END;
119$$
120LANGUAGE plpgsql;
121
122CREATE OR REPLACE FUNCTION transliteration(text) RETURNS text
123  AS '/home/twain/osm2pgsql/gazetteer/gazetteer.so', 'transliteration'
124LANGUAGE c IMMUTABLE STRICT;
125
126CREATE OR REPLACE FUNCTION make_standard_name(name TEXT) RETURNS TEXT
127  AS $$
128DECLARE
129  out varchar;
130BEGIN
131out := transliteration(name);
132out := ' '||regexp_replace(out,' +',' ','g')||' ';
133
134-- postgresql regex replace seems very inefficient so avoid
135--out := replace(out, ' rd ',' road ');
136--out := replace(out, ' street ',' st ');
137--out := replace(out, ' saint ',' st ');
138--out := replace(out, ' dr ',' drive ');
139--out := replace(out, ' drv ',' drive ');
140--out := replace(out, ' av ',' avenue ');
141--out := replace(out, ' ave ',' avenue ');
142--out := replace(out, ' ln ',' lane ');
143--out := replace(out, ' ct ',' court ');
144--out := replace(out, ' pl ',' place ');
145
146--out := replace(out, ' bwy ',' broadway ');
147--out := replace(out, ' blvd ',' boulevard ');
148--out := replace(out, ' bvd ',' boulevard ');
149--out := replace(out, ' bld ',' boulevard ');
150--out := replace(out, ' bd ',' boulevard ');
151
152--out := replace(out, ' cl ',' close ');
153--out := replace(out, ' cres ',' crescent ');
154--out := replace(out, ' est ',' estate ');
155--out := replace(out, ' gdn ',' garden ');
156--out := replace(out, ' gdns ',' gardens ');
157--out := replace(out, ' gro ',' grove ');
158--out := replace(out, ' hwy ',' highway ');
159--out := replace(out, ' sq ',' square ');
160
161--out := replace(out, ' n ',' north ');
162--out := replace(out, ' s ',' south ');
163--out := replace(out, ' e ',' east ');
164--out := replace(out, ' w ',' west ');
165
166--out := replace(out, ' ne ',' north east ');
167--out := replace(out, ' nw ',' north west ');
168--out := replace(out, ' se ',' south east ');
169--out := replace(out, ' sw ',' south west ');
170
171out := replace(out, ' north east ',' ne ');
172out := replace(out, ' north west ',' nw ');
173out := replace(out, ' south east ',' se ');
174out := replace(out, ' south west ',' sw ');
175out := replace(out, ' north-east ',' ne ');
176out := replace(out, ' north-west ',' nw ');
177out := replace(out, ' south-east ',' se ');
178out := replace(out, ' south-west ',' sw ');
179
180--out := replace(out, ' northeast ',' north east ');
181--out := replace(out, ' northwest ',' north west ');
182--out := replace(out, ' southeast ',' south east ');
183--out := replace(out, ' southwest ',' south west ');
184
185out := replace(out, ' acceso ',' acces ');
186out := replace(out, ' acequia ',' aceq ');
187out := replace(out, ' air force base ',' afb ');
188out := replace(out, ' air national guard base ',' angb ');
189out := replace(out, ' al ',' ale ');
190out := replace(out, ' alameda ',' alam ');
191out := replace(out, ' alea ',' ale ');
192out := replace(out, ' aleea ',' ale ');
193out := replace(out, ' aleja ',' al ');
194out := replace(out, ' alejach ',' al ');
195out := replace(out, ' aleje ',' al ');
196out := replace(out, ' aleji ',' al ');
197out := replace(out, ' all ',' al ');
198out := replace(out, ' allee ',' all ');
199out := replace(out, ' alley ',' al ');
200out := replace(out, ' alqueria ',' alque ');
201out := replace(out, ' alue ',' al ');
202out := replace(out, ' aly ',' al ');
203out := replace(out, ' am ',' a ');
204out := replace(out, ' an der ',' a d ');
205out := replace(out, ' andador ',' andad ');
206out := replace(out, ' angosta ',' angta ');
207out := replace(out, ' apartamentos ',' aptos ');
208out := replace(out, ' apartments ',' apts ');
209out := replace(out, ' apeadero ',' apdro ');
210out := replace(out, ' app ',' apch ');
211out := replace(out, ' approach ',' apch ');
212out := replace(out, ' arboleda ',' arb ');
213out := replace(out, ' arrabal ',' arral ');
214out := replace(out, ' arroyo ',' arry ');
215out := replace(out, ' auf der ',' a d ');
216out := replace(out, ' aukio ',' auk ');
217out := replace(out, ' autopista ',' auto ');
218out := replace(out, ' autovia ',' autov ');
219out := replace(out, ' avd ',' av ');
220out := replace(out, ' avda ',' av ');
221out := replace(out, ' ave ',' av ');
222out := replace(out, ' avenida ',' av ');
223out := replace(out, ' avenue ',' av ');
224out := replace(out, ' avinguda ',' av ');
225out := replace(out, ' b dul ',' bd ');
226out := replace(out, ' back ',' bk ');
227out := replace(out, ' bad ',' b ');
228out := replace(out, ' bahnhof ',' bhf ');
229out := replace(out, ' bajada ',' bjada ');
230out := replace(out, ' balneario ',' balnr ');
231out := replace(out, ' banda ',' b ');
232out := replace(out, ' barranco ',' branc ');
233out := replace(out, ' barranquil ',' bqllo ');
234out := replace(out, ' barriada ',' barda ');
235out := replace(out, ' barrio ',' bo ');
236out := replace(out, ' barro ',' bo ');
237out := replace(out, ' bda ',' b ');
238out := replace(out, ' bdul ',' bd ');
239out := replace(out, ' berg ',' bg ');
240out := replace(out, ' bf ',' bhf ');
241out := replace(out, ' bldngs ',' bldgs ');
242out := replace(out, ' blok ',' bl ');
243out := replace(out, ' bloque ',' blque ');
244out := replace(out, ' blv ',' bd ');
245out := replace(out, ' blvd ',' bd ');
246out := replace(out, ' boulevard ',' blvd ');
247out := replace(out, ' boundary ',' bdy ');
248out := replace(out, ' brazal ',' brzal ');
249out := replace(out, ' bri ',' brdg ');
250out := replace(out, ' bridge ',' brdg ');
251out := replace(out, ' broadway ',' bway ');
252out := replace(out, ' broeder ',' br ');
253out := replace(out, ' brucke ',' br ');
254out := replace(out, ' buildings ',' bldgs ');
255out := replace(out, ' bul ',' bd ');
256out := replace(out, ' bulev ',' bd ');
257out := replace(out, ' bulevar ',' bulev ');
258out := replace(out, ' bulevard ',' bd ');
259out := replace(out, ' bulevardu ',' bd ');
260out := replace(out, ' bulevardul ',' bd ');
261out := replace(out, ' bulievard ',' bul ');
262out := replace(out, ' bulvar ',' bl ');
263out := replace(out, ' bulvari ',' bl ');
264out := replace(out, ' burg ',' bg ');
265out := replace(out, ' burgemeester ',' burg ');
266out := replace(out, ' business ',' bus ');
267out := replace(out, ' buu dien ',' bd ');
268out := replace(out, ' bvd ',' blvd ');
269out := replace(out, ' bypass ',' byp ');
270out := replace(out, ' c le ',' c ');
271out := replace(out, ' cadde ',' cd ');
272out := replace(out, ' caddesi ',' cd ');
273out := replace(out, ' calle ',' c ');
274out := replace(out, ' calleja ',' cllja ');
275out := replace(out, ' callejon ',' callej ');
276out := replace(out, ' callejuela ',' cjla ');
277out := replace(out, ' callizo ',' cllzo ');
278out := replace(out, ' calzada ',' czada ');
279out := replace(out, ' camino ',' cno ');
280out := replace(out, ' camino hondo ',' c h ');
281out := replace(out, ' camino nuevo ',' c n ');
282out := replace(out, ' camino viejo ',' c v ');
283out := replace(out, ' camping ',' campg ');
284out := replace(out, ' campo ',' c po ');
285out := replace(out, ' can cu khong quan ',' cckq ');
286out := replace(out, ' cantera ',' cantr ');
287out := replace(out, ' cantina ',' canti ');
288out := replace(out, ' canton ',' cant ');
289out := replace(out, ' cao dang ',' cd ');
290out := replace(out, ' caravan ',' cvn ');
291out := replace(out, ' carrer ',' c ');
292out := replace(out, ' carrera ',' cra ');
293out := replace(out, ' carrero ',' cro ');
294out := replace(out, ' carretera ',' ctra ');
295out := replace(out, ' carreterin ',' ctrin ');
296out := replace(out, ' carretil ',' crtil ');
297out := replace(out, ' carril ',' crril ');
298out := replace(out, ' caserio ',' csrio ');
299out := replace(out, ' cau ldhc bo ',' clb ');
300out := replace(out, ' causeway ',' cswy ');
301out := replace(out, ' center ',' cen ');
302out := replace(out, ' centre ',' cen ');
303out := replace(out, ' cesta ',' c ');
304out := replace(out, ' chalet ',' chlet ');
305out := replace(out, ' che ',' ch ');
306out := replace(out, ' chemin ',' ch ');
307out := replace(out, ' cinturon ',' cint ');
308out := replace(out, ' circle ',' cir ');
309out := replace(out, ' circuit ',' cct ');
310out := replace(out, ' circunvalacion ',' ccvcn ');
311out := replace(out, ' city ',' cty ');
312out := replace(out, ' cjon ',' callej ');
313out := replace(out, ' cl ',' c ');
314out := replace(out, ' cllon ',' callej ');
315out := replace(out, ' close ',' cl ');
316out := replace(out, ' cmno ',' cno ');
317out := replace(out, ' cobertizo ',' cbtiz ');
318out := replace(out, ' colonia ',' col ');
319out := replace(out, ' commandant ',' cmdt ');
320out := replace(out, ' common ',' comm ');
321out := replace(out, ' community ',' comm ');
322out := replace(out, ' complejo ',' compj ');
323out := replace(out, ' cong truong ',' ct ');
324out := replace(out, ' cong ty ',' cty ');
325out := replace(out, ' cong ty co phyn ',' ctcp ');
326out := replace(out, ' cong vien ',' cv ');
327out := replace(out, ' cong vien van hoa ',' cvvh ');
328out := replace(out, ' conjunto ',' cjto ');
329out := replace(out, ' convento ',' cnvto ');
330out := replace(out, ' cooperativa ',' coop ');
331out := replace(out, ' corral ',' crral ');
332out := replace(out, ' corralillo ',' crrlo ');
333out := replace(out, ' corredor ',' crrdo ');
334out := replace(out, ' corso ',' c so ');
335out := replace(out, ' corte ',' c te ');
336out := replace(out, ' cortijo ',' crtjo ');
337out := replace(out, ' costanilla ',' cstan ');
338out := replace(out, ' costera ',' coste ');
339out := replace(out, ' cottages ',' cotts ');
340out := replace(out, ' county ',' co ');
341out := replace(out, ' county route ',' cr ');
342out := replace(out, ' cours ',' crs ');
343out := replace(out, ' court ',' crt ');
344out := replace(out, ' creek ',' cr ');
345out := replace(out, ' crescent ',' cres ');
346out := replace(out, ' crk ',' cr ');
347out := replace(out, ' croft ',' cft ');
348out := replace(out, ' crossing ',' xing ');
349out := replace(out, ' ct ',' crt ');
350out := replace(out, ' ctr ',' cen ');
351out := replace(out, ' cty cp ',' ctcp ');
352out := replace(out, ' cuadra ',' cuadr ');
353out := replace(out, ' cuesta ',' custa ');
354out := replace(out, ' cway ',' cswy ');
355out := replace(out, ' ddhi hoc ',' dh ');
356out := replace(out, ' ddhi lo ',' dl ');
357out := replace(out, ' dehesa ',' dhsa ');
358out := replace(out, ' demarcacion ',' demar ');
359out := replace(out, ' diagonal ',' diag ');
360out := replace(out, ' diseminado ',' disem ');
361out := replace(out, ' doctor ',' dr ');
362out := replace(out, ' dokter ',' dr ');
363out := replace(out, ' doktor ',' d r ');
364out := replace(out, ' dolna ',' dln ');
365out := replace(out, ' dolne ',' dln ');
366out := replace(out, ' dolny ',' dln ');
367out := replace(out, ' dominee ',' ds ');
368out := replace(out, ' dorf ',' df ');
369out := replace(out, ' dotsient ',' dots ');
370out := replace(out, ' drive ',' dr ');
371out := replace(out, ' druga ',' 2 ');
372out := replace(out, ' drugi ',' 2 ');
373out := replace(out, ' drugie ',' 2 ');
374out := replace(out, ' duong ',' d ');
375out := replace(out, ' duong sat ',' ds ');
376out := replace(out, ' duza ',' dz ');
377out := replace(out, ' duze ',' dz ');
378out := replace(out, ' duzy ',' dz ');
379out := replace(out, ' east ',' e ');
380out := replace(out, ' edificio ',' edifc ');
381out := replace(out, ' empresa ',' empr ');
382out := replace(out, ' entrada ',' entd ');
383out := replace(out, ' escalera ',' esca ');
384out := replace(out, ' escalinata ',' escal ');
385out := replace(out, ' espalda ',' eslda ');
386out := replace(out, ' esplanade ','  ');
387out := replace(out, ' estacion ',' estcn ');
388out := replace(out, ' estate ',' est ');
389out := replace(out, ' estrada ',' estda ');
390out := replace(out, ' explanada ',' expla ');
391out := replace(out, ' expressway ',' expy ');
392out := replace(out, ' extramuros ',' extrm ');
393out := replace(out, ' extrarradio ',' extrr ');
394out := replace(out, ' fabrica ',' fca ');
395out := replace(out, ' faubourg ',' fg ');
396out := replace(out, ' fbrca ',' fca ');
397out := replace(out, ' ferry ',' fy ');
398out := replace(out, ' fondamenta ',' f ta ');
399out := replace(out, ' fort ',' ft ');
400out := replace(out, ' freeway ',' frwy ');
401out := replace(out, ' fundacul ',' fdc ');
402out := replace(out, ' fundatura ',' fnd ');
403out := replace(out, ' fwy ',' frwy ');
404out := replace(out, ' g ',' gt ');
405out := replace(out, ' galeria ',' gale ');
406out := replace(out, ' gamla ',' gla ');
407out := replace(out, ' gardens ',' gdn ');
408out := replace(out, ' gata ',' gt ');
409out := replace(out, ' gatan ',' g ');
410out := replace(out, ' gate ',' ga ');
411out := replace(out, ' gaten ',' gt ');
412out := replace(out, ' gebroeders ',' gebr ');
413out := replace(out, ' generaal ',' gen ');
414out := replace(out, ' gienieral ',' ghien ');
415out := replace(out, ' glorieta ',' gta ');
416out := replace(out, ' gorna ',' grn ');
417out := replace(out, ' gorne ',' grn ');
418out := replace(out, ' gorny ',' grn ');
419out := replace(out, ' gracht ',' gr ');
420out := replace(out, ' grad ',' ghr ');
421out := replace(out, ' gran via ',' g v ');
422out := replace(out, ' grand ',' gr ');
423out := replace(out, ' granden ',' gr ');
424out := replace(out, ' granja ',' granj ');
425out := replace(out, ' grosse ',' gr ');
426out := replace(out, ' grosser ',' gr ');
427out := replace(out, ' grosses ',' gr ');
428out := replace(out, ' grove ',' gro ');
429out := replace(out, ' gt ',' ga ');
430out := replace(out, ' hauptbahnhof ',' hbf ');
431out := replace(out, ' heights ',' hgts ');
432out := replace(out, ' heiligen ',' hl ');
433out := replace(out, ' high school ',' hs ');
434out := replace(out, ' highway ',' hwy ');
435out := replace(out, ' hipodromo ',' hipod ');
436out := replace(out, ' hospital ',' hosp ');
437out := replace(out, ' house ',' ho ');
438out := replace(out, ' hse ',' ho ');
439out := replace(out, ' hts ',' hgts ');
440out := replace(out, ' im ',' i ');
441out := replace(out, ' impasse ',' imp ');
442out := replace(out, ' in ',' i ');
443out := replace(out, ' in der ',' i d ');
444out := replace(out, ' industrial ',' ind ');
445out := replace(out, ' ingenieur ',' ir ');
446out := replace(out, ' international ',' intl ');
447out := replace(out, ' intr ',' int ');
448out := replace(out, ' intrarea ',' int ');
449out := replace(out, ' island ',' is ');
450out := replace(out, ' jardin ',' jdin ');
451out := replace(out, ' jonkheer ',' jhr ');
452out := replace(out, ' k s ',' ks ');
453out := replace(out, ' kaari ',' kri ');
454out := replace(out, ' kanunnik ',' kan ');
455out := replace(out, ' kapitan ',' kap ');
456out := replace(out, ' kardinaal ',' kard ');
457out := replace(out, ' katu ',' k ');
458out := replace(out, ' khach sdhn ',' ks ');
459out := replace(out, ' khu cong nghiep ',' kcn ');
460out := replace(out, ' khu du lich ',' kdl ');
461out := replace(out, ' khu nghi mat ',' knm ');
462out := replace(out, ' kleine ',' kl ');
463out := replace(out, ' kleiner ',' kl ');
464out := replace(out, ' kleines ',' kl ');
465out := replace(out, ' kolo ',' k ');
466out := replace(out, ' kolonel ',' kol ');
467out := replace(out, ' kolonia ',' kol ');
468out := replace(out, ' koning ',' kon ');
469out := replace(out, ' koningin ',' kon ');
470out := replace(out, ' kort e ',' kte ');
471out := replace(out, ' kuja ',' kj ');
472out := replace(out, ' kvartal ',' kv ');
473out := replace(out, ' kyla ',' kl ');
474out := replace(out, ' laan ',' ln ');
475out := replace(out, ' ladera ',' ldera ');
476out := replace(out, ' lane ',' ln ');
477out := replace(out, ' lange ',' l ');
478out := replace(out, ' largo ',' l go ');
479out := replace(out, ' lille ',' ll ');
480out := replace(out, ' line ',' ln ');
481out := replace(out, ' little ',' lit ');
482out := replace(out, ' llanura ',' llnra ');
483out := replace(out, ' lower ',' low ');
484out := replace(out, ' luitenant ',' luit ');
485out := replace(out, ' lwr ',' low ');
486out := replace(out, ' m te ',' m tele ');
487out := replace(out, ' maantee ',' mnt ');
488out := replace(out, ' mala ',' ml ');
489out := replace(out, ' male ',' ml ');
490out := replace(out, ' malecon ',' malec ');
491out := replace(out, ' maly ',' ml ');
492out := replace(out, ' manor ',' mnr ');
493out := replace(out, ' mansions ',' mans ');
494out := replace(out, ' market ',' mkt ');
495out := replace(out, ' markt ',' mkt ');
496out := replace(out, ' mazowiecka ',' maz ');
497out := replace(out, ' mazowiecki ',' maz ');
498out := replace(out, ' mazowieckie ',' maz ');
499out := replace(out, ' meadows ',' mdws ');
500out := replace(out, ' medical ',' med ');
501out := replace(out, ' meester ',' mr ');
502out := replace(out, ' mercado ',' merc ');
503out := replace(out, ' mevrouw ',' mevr ');
504out := replace(out, ' mews ',' m ');
505out := replace(out, ' miasto ',' m ');
506out := replace(out, ' middle ',' mid ');
507out := replace(out, ' middle school ',' ms ');
508out := replace(out, ' mile ',' mi ');
509out := replace(out, ' military ',' mil ');
510out := replace(out, ' mirador ',' mrdor ');
511out := replace(out, ' mitropolit ',' mit ');
512out := replace(out, ' mnt ',' m tele ');
513out := replace(out, ' monasterio ',' mtrio ');
514out := replace(out, ' monseigneur ',' mgr ');
515out := replace(out, ' mount ',' mt ');
516out := replace(out, ' mountain ',' mtn ');
517out := replace(out, ' mt ',' m tele ');
518out := replace(out, ' muelle ',' muell ');
519out := replace(out, ' municipal ',' mun ');
520out := replace(out, ' muntele ',' m tele ');
521out := replace(out, ' museum ',' mus ');
522out := replace(out, ' na ',' n ');
523out := replace(out, ' namesti ',' nam ');
524out := replace(out, ' namestie ',' nam ');
525out := replace(out, ' national park ',' np ');
526out := replace(out, ' national recreation area ',' nra ');
527out := replace(out, ' national wildlife refuge area ',' nwra ');
528out := replace(out, ' nha hat ',' nh ');
529out := replace(out, ' nha thi dzu ',' ntd ');
530out := replace(out, ' nha tho ',' nt ');
531out := replace(out, ' nordre ',' ndr ');
532out := replace(out, ' norra ',' n ');
533out := replace(out, ' north ',' n ');
534out := replace(out, ' northeast ',' ne ');
535out := replace(out, ' northwest ',' nw ');
536out := replace(out, ' nowa ',' nw ');
537out := replace(out, ' nowe ',' nw ');
538out := replace(out, ' nowy ',' nw ');
539out := replace(out, ' nucleo ',' ncleo ');
540out := replace(out, ' oa ',' o ');
541out := replace(out, ' ob ',' o ');
542out := replace(out, ' obere ',' ob ');
543out := replace(out, ' oberer ',' ob ');
544out := replace(out, ' oberes ',' ob ');
545out := replace(out, ' olv ',' o l v ');
546out := replace(out, ' onze lieve vrouw e ',' o l v ');
547out := replace(out, ' osiedle ',' os ');
548out := replace(out, ' osiedlu ',' os ');
549out := replace(out, ' ostra ',' o ');
550out := replace(out, ' p k ',' pk ');
551out := replace(out, ' p ta ',' pta ');
552out := replace(out, ' p zza ',' p za ');
553out := replace(out, ' palacio ',' palac ');
554out := replace(out, ' pantano ',' pant ');
555out := replace(out, ' paraje ',' praje ');
556out := replace(out, ' park ',' pk ');
557out := replace(out, ' parkway ',' pkwy ');
558out := replace(out, ' parque ',' pque ');
559out := replace(out, ' particular ',' parti ');
560out := replace(out, ' partida ',' ptda ');
561out := replace(out, ' pasadizo ',' pzo ');
562out := replace(out, ' pasaje ',' psaje ');
563out := replace(out, ' paseo ',' po ');
564out := replace(out, ' paseo maritimo ',' psmar ');
565out := replace(out, ' pasillo ',' psllo ');
566out := replace(out, ' pass ',' pas ');
567out := replace(out, ' passage ',' pas ');
568out := replace(out, ' passatge ',' ptge ');
569out := replace(out, ' passeig ',' pg ');
570out := replace(out, ' pastoor ',' past ');
571out := replace(out, ' penger ',' pgr ');
572out := replace(out, ' pfad ',' p ');
573out := replace(out, ' ph ',' p ');
574out := replace(out, ' phi truong ',' pt ');
575out := replace(out, ' phuong ',' p ');
576out := replace(out, ' piata ',' pta ');
577out := replace(out, ' piazza ',' p za ');
578out := replace(out, ' piazzale ',' p le ');
579out := replace(out, ' piazzetta ',' p ta ');
580out := replace(out, ' pierwsza ',' 1 ');
581out := replace(out, ' pierwsze ',' 1 ');
582out := replace(out, ' pierwszy ',' 1 ');
583out := replace(out, ' pike ',' pk ');
584out := replace(out, ' pky ',' pkwy ');
585out := replace(out, ' plac ',' pl ');
586out := replace(out, ' placa ',' pl ');
587out := replace(out, ' place ',' pl ');
588out := replace(out, ' placem ',' pl ');
589out := replace(out, ' placu ',' pl ');
590out := replace(out, ' plass ',' pl ');
591out := replace(out, ' plassen ',' pl ');
592out := replace(out, ' plats ',' pl ');
593out := replace(out, ' platsen ',' pl ');
594out := replace(out, ' platz ',' pl ');
595out := replace(out, ' plaza ',' pl ');
596out := replace(out, ' plazoleta ',' pzta ');
597out := replace(out, ' plazuela ',' plzla ');
598out := replace(out, ' plein ',' pln ');
599out := replace(out, ' ploshchad ',' pl ');
600out := replace(out, ' plz ',' pl ');
601out := replace(out, ' plza ',' pl ');
602out := replace(out, ' poblado ',' pbdo ');
603out := replace(out, ' point ',' pt ');
604out := replace(out, ' poligono ',' polig ');
605out := replace(out, ' poligono industrial ',' pgind ');
606out := replace(out, ' polku ',' p ');
607out := replace(out, ' ponte ',' p te ');
608out := replace(out, ' porta ',' p ta ');
609out := replace(out, ' portal ',' prtal ');
610out := replace(out, ' portico ',' prtco ');
611out := replace(out, ' portillo ',' ptilo ');
612out := replace(out, ' prazuela ',' przla ');
613out := replace(out, ' precinct ',' pct ');
614out := replace(out, ' president ',' pres ');
615out := replace(out, ' prins ',' pr ');
616out := replace(out, ' prinses ',' pr ');
617out := replace(out, ' professor ',' prof ');
618out := replace(out, ' profiesor ',' prof ');
619out := replace(out, ' prolongacion ',' prol ');
620out := replace(out, ' promenade ',' prom ');
621out := replace(out, ' pueblo ',' pblo ');
622out := replace(out, ' puente ',' pnte ');
623out := replace(out, ' puerta ',' pta ');
624out := replace(out, ' puerto ',' pto ');
625out := replace(out, ' puistikko ',' pko ');
626out := replace(out, ' puisto ',' ps ');
627out := replace(out, ' punto kilometrico ',' pk ');
628out := replace(out, ' pza ',' pl ');
629out := replace(out, ' quai ',' qu ');
630out := replace(out, ' quan ',' q ');
631out := replace(out, ' qucyng truong ',' qt ');
632out := replace(out, ' quelle ',' qu ');
633out := replace(out, ' quoc lo ',' ql ');
634out := replace(out, ' raitti ',' r ');
635out := replace(out, ' rambla ',' rbla ');
636out := replace(out, ' rampla ',' rampa ');
637out := replace(out, ' ranta ',' rt ');
638out := replace(out, ' rdhp hat ',' rh ');
639out := replace(out, ' reservation ',' res ');
640out := replace(out, ' reservoir ',' res ');
641out := replace(out, ' residencial ',' resid ');
642out := replace(out, ' rhein ',' rh ');
643out := replace(out, ' ribera ',' rbra ');
644out := replace(out, ' rincon ',' rcon ');
645out := replace(out, ' rinconada ',' rcda ');
646out := replace(out, ' rinne ',' rn ');
647out := replace(out, ' rise ',' ri ');
648out := replace(out, ' riv ',' r ');
649out := replace(out, ' river ',' r ');
650out := replace(out, ' road ',' rd ');
651out := replace(out, ' rotonda ',' rtda ');
652out := replace(out, ' route ',' rt ');
653out := replace(out, ' rte ',' rt ');
654out := replace(out, ' rue ',' r ');
655out := replace(out, ' sa ',' s ');
656out := replace(out, ' saint ',' st ');
657out := replace(out, ' sainte ',' ste ');
658out := replace(out, ' salizada ',' s da ');
659out := replace(out, ' san bay ',' sb ');
660out := replace(out, ' san bay quoc te ',' sbqt ');
661out := replace(out, ' san van dong ',' svd ');
662out := replace(out, ' sanatorio ',' sanat ');
663out := replace(out, ' sankt ',' st ');
664out := replace(out, ' santuario ',' santu ');
665out := replace(out, ' sdla ',' str la ');
666out := replace(out, ' sector ',' sect ');
667out := replace(out, ' sendera ',' sedra ');
668out := replace(out, ' sendero ',' send ');
669out := replace(out, ' sielo ',' s ');
670out := replace(out, ' sint ',' st ');
671out := replace(out, ' sodra ',' s ');
672out := replace(out, ' sok ',' sk ');
673out := replace(out, ' sokagi ',' sk ');
674out := replace(out, ' sokak ',' sk ');
675out := replace(out, ' sondre ',' sdr ');
676out := replace(out, ' soseaua ',' sos ');
677out := replace(out, ' south ',' s ');
678out := replace(out, ' southeast ',' se ');
679out := replace(out, ' southwest ',' sw ');
680out := replace(out, ' spl ',' sp ');
681out := replace(out, ' splaiul ',' sp ');
682out := replace(out, ' spodnja ',' sp ');
683out := replace(out, ' spodnje ',' sp ');
684out := replace(out, ' spodnji ',' sp ');
685out := replace(out, ' square ',' sq ');
686out := replace(out, ' srednja ',' sr ');
687out := replace(out, ' srednje ',' sr ');
688out := replace(out, ' srednji ',' sr ');
689out := replace(out, ' stara ',' st ');
690out := replace(out, ' stare ',' st ');
691out := replace(out, ' stary ',' st ');
692out := replace(out, ' state highway ',' sh ');
693out := replace(out, ' state route ',' sr ');
694out := replace(out, ' station ',' stn ');
695out := replace(out, ' stazione ',' staz ');
696out := replace(out, ' steenweg ',' stwg ');
697out := replace(out, ' sth ',' s ');
698out := replace(out, ' stig ',' st ');
699out := replace(out, ' stigen ',' st ');
700out := replace(out, ' store ',' st ');
701--out := replace(out, ' str ',' strada ');
702out := replace(out, ' stra ',' strada ');
703out := replace(out, ' straat ',' str ');
704out := replace(out, ' strada comunale ',' sc ');
705out := replace(out, ' strada provinciale ',' sp ');
706out := replace(out, ' strada regionale ',' sr ');
707out := replace(out, ' strada statale ',' ss ');
708out := replace(out, ' stradela ',' str la ');
709out := replace(out, ' strasse ',' str ');
710out := replace(out, ' street ',' st ');
711out := replace(out, ' subida ',' sbida ');
712out := replace(out, ' sveta ',' sv ');
713out := replace(out, ' sveti ',' sv ');
714out := replace(out, ' svieti ',' sv ');
715out := replace(out, ' taival ',' tvl ');
716out := replace(out, ' tanav ',' tn ');
717out := replace(out, ' tct ',' tcty ');
718out := replace(out, ' terr ',' ter ');
719out := replace(out, ' terrace ',' ter ');
720out := replace(out, ' thanh pho ',' tp ');
721out := replace(out, ' thi trzn ',' tt ');
722out := replace(out, ' thi xa ',' tx ');
723out := replace(out, ' tie ',' t ');
724out := replace(out, ' tieu hoc ',' th ');
725out := replace(out, ' tinh lo ',' tl ');
726out := replace(out, ' tong cong ty ',' tcty ');
727out := replace(out, ' tori ',' tr ');
728out := replace(out, ' torrente ',' trrnt ');
729out := replace(out, ' towers ',' twrs ');
730out := replace(out, ' township ',' twp ');
731out := replace(out, ' tpke ',' tpk ');
732out := replace(out, ' trail ',' trl ');
733out := replace(out, ' transito ',' trans ');
734out := replace(out, ' transversal ',' trval ');
735out := replace(out, ' trasera ',' tras ');
736out := replace(out, ' travesia ',' trva ');
737out := replace(out, ' trung hoc co so ',' thcs ');
738out := replace(out, ' trung hoc pho thong ',' thpt ');
739out := replace(out, ' trung tam ',' tt ');
740out := replace(out, ' trung tam thuong mdhi ',' tttm ');
741out := replace(out, ' trzeci ',' 3 ');
742out := replace(out, ' trzecia ',' 3 ');
743out := replace(out, ' trzecie ',' 3 ');
744out := replace(out, ' tunnel ',' tun ');
745out := replace(out, ' turnpike ',' tpk ');
746out := replace(out, ' ulica ',' ul ');
747out := replace(out, ' ulice ',' ul ');
748out := replace(out, ' ulicy ',' ul ');
749out := replace(out, ' ulitsa ',' ul ');
750out := replace(out, ' university ',' univ ');
751out := replace(out, ' untere ',' u ');
752out := replace(out, ' unterer ',' u ');
753out := replace(out, ' unteres ',' u ');
754out := replace(out, ' upper ',' up ');
755out := replace(out, ' upr ',' up ');
756out := replace(out, ' urbanizacion ',' urb ');
757out := replace(out, ' utca ',' u ');
758out := replace(out, ' va ',' v ');
759out := replace(out, ' vag ',' v ');
760out := replace(out, ' vagen ',' v ');
761out := replace(out, ' vale ',' va ');
762out := replace(out, ' van ',' v ');
763out := replace(out, ' van de ',' v d ');
764out := replace(out, ' varf ',' vf ');
765out := replace(out, ' varful ',' vf ');
766out := replace(out, ' vastra ',' v ');
767out := replace(out, ' vayla ',' vla ');
768out := replace(out, ' vd ',' v d ');
769out := replace(out, ' vecindario ',' vecin ');
770out := replace(out, ' vei ',' v ');
771out := replace(out, ' veien ',' v ');
772out := replace(out, ' velika ',' v ');
773out := replace(out, ' velike ',' v ');
774out := replace(out, ' veliki ',' v ');
775out := replace(out, ' veliko ',' v ');
776out := replace(out, ' vereda ',' vreda ');
777out := replace(out, ' via ',' v ');
778out := replace(out, ' viaduct ',' via ');
779out := replace(out, ' viaducto ',' vcto ');
780out := replace(out, ' viale ',' v le ');
781out := replace(out, ' vien bcyo tang ',' vbt ');
782out := replace(out, ' view ',' vw ');
783out := replace(out, ' virf ',' vf ');
784out := replace(out, ' virful ',' vf ');
785out := replace(out, ' viviendas ',' vvdas ');
786out := replace(out, ' vkhod ',' vkh ');
787out := replace(out, ' vliet ',' vlt ');
788out := replace(out, ' vuon quoc gia ',' vqg ');
789out := replace(out, ' walk ',' wlk ');
790out := replace(out, ' way ',' wy ');
791out := replace(out, ' west ',' w ');
792out := replace(out, ' wielka ',' wlk ');
793out := replace(out, ' wielki ',' wlk ');
794out := replace(out, ' wielkie ',' wlk ');
795out := replace(out, ' wielkopolska ',' wlkp ');
796out := replace(out, ' wielkopolski ',' wlkp ');
797out := replace(out, ' wielkopolskie ',' wlkp ');
798out := replace(out, ' wojewodztwie ',' woj ');
799out := replace(out, ' wojewodztwo ',' woj ');
800out := replace(out, ' yard ',' yd ');
801out := replace(out, ' zgornja ',' zg ');
802out := replace(out, ' zgornje ',' zg ');
803out := replace(out, ' zgornji ',' zg ');
804out := replace(out, ' zhilishchien komplieks ',' zh k ');
805out := replace(out, ' zum ',' z ');
806
807out := replace(out, ' and ',' ');
808out := replace(out, ' und ',' ');
809out := replace(out, ' en ',' ');
810out := replace(out, ' et ',' ');
811out := replace(out, ' e ',' ');
812out := replace(out, ' y ',' ');
813
814out := replace(out, ' the ',' ');
815out := replace(out, ' der ',' ');
816out := replace(out, ' den ',' ');
817out := replace(out, ' die ',' ');
818out := replace(out, ' das ',' ');
819
820out := replace(out, ' la ',' ');
821out := replace(out, ' le ',' ');
822out := replace(out, ' el ',' ');
823out := replace(out, ' il ',' ');
824out := replace(out, ' ال ',' ');
825
826out := replace(out, 'ae','a');
827out := replace(out, 'oe','o');
828out := replace(out, 'ue','u');
829
830-- out := regexp_replace(out,E'([^0-9])\\1+',E'\\1','g');
831-- out := regexp_replace(out,E'\\mcir\\M','circle','g');
832
833return trim(out);
834END;
835$$
836LANGUAGE 'plpgsql' IMMUTABLE;
837
838CREATE OR REPLACE FUNCTION getorcreate_word_id(lookup_word TEXT) 
839  RETURNS INTEGER
840  AS $$
841DECLARE
842  lookup_token TEXT;
843  return_word_id INTEGER;
844BEGIN
845  lookup_token := trim(lookup_word);
846  SELECT word_id FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id;
847  IF return_word_id IS NULL THEN
848    return_word_id := nextval('seq_word');
849    INSERT INTO word VALUES (return_word_id, lookup_token, regexp_replace(lookup_token,E'([^0-9])\\1+',E'\\1','g'), null, null, null, null, 0, null);
850  END IF;
851  RETURN return_word_id;
852END;
853$$
854LANGUAGE plpgsql;
855
856CREATE OR REPLACE FUNCTION getorcreate_housenumber_id(lookup_word TEXT)
857  RETURNS INTEGER
858  AS $$
859DECLARE
860  lookup_token TEXT;
861  return_word_id INTEGER;
862BEGIN
863  lookup_token := ' '||trim(lookup_word);
864  SELECT word_id FROM word WHERE word_token = lookup_token and class='place' and type='house' into return_word_id;
865  IF return_word_id IS NULL THEN
866    return_word_id := nextval('seq_word');
867    INSERT INTO word VALUES (return_word_id, lookup_token, null, null, 'place', 'house', null, 0, null);
868  END IF;
869  RETURN return_word_id;
870END;
871$$
872LANGUAGE plpgsql;
873
874CREATE OR REPLACE FUNCTION getorcreate_country(lookup_word TEXT, lookup_country_code varchar(2))
875  RETURNS INTEGER
876  AS $$
877DECLARE
878  lookup_token TEXT;
879  return_word_id INTEGER;
880BEGIN
881  lookup_token := ' '||trim(lookup_word);
882  SELECT word_id FROM word WHERE word_token = lookup_token and country_code=lookup_country_code into return_word_id;
883  IF return_word_id IS NULL THEN
884    return_word_id := nextval('seq_word');
885    INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, lookup_country_code, 0, null);
886  END IF;
887  RETURN return_word_id;
888END;
889$$
890LANGUAGE plpgsql;
891
892CREATE OR REPLACE FUNCTION getorcreate_amenity(lookup_word TEXT, lookup_class text, lookup_type text)
893  RETURNS INTEGER
894  AS $$
895DECLARE
896  lookup_token TEXT;
897  return_word_id INTEGER;
898BEGIN
899  lookup_token := ' '||trim(lookup_word);
900  SELECT word_id FROM word WHERE word_token = lookup_token and class=lookup_class and type = lookup_type into return_word_id;
901  IF return_word_id IS NULL THEN
902    return_word_id := nextval('seq_word');
903    INSERT INTO word VALUES (return_word_id, lookup_token, null, null, lookup_class, lookup_type, null, 0, null);
904  END IF;
905  RETURN return_word_id;
906END;
907$$
908LANGUAGE plpgsql;
909
910CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT, src_word TEXT) 
911  RETURNS INTEGER
912  AS $$
913DECLARE
914  lookup_token TEXT;
915  nospace_lookup_token TEXT;
916  return_word_id INTEGER;
917BEGIN
918  lookup_token := ' '||trim(lookup_word);
919  SELECT word_id FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id;
920  IF return_word_id IS NULL THEN
921    return_word_id := nextval('seq_word');
922    INSERT INTO word VALUES (return_word_id, lookup_token, regexp_replace(lookup_token,E'([^0-9])\\1+',E'\\1','g'), src_word, null, null, null, 0, null);
923    nospace_lookup_token := replace(replace(lookup_token, '-',''), ' ','');
924    IF ' '||nospace_lookup_token != lookup_token THEN
925      INSERT INTO word VALUES (return_word_id, '-'||nospace_lookup_token, null, src_word, null, null, null, 0, null);
926    END IF;
927  END IF;
928  RETURN return_word_id;
929END;
930$$
931LANGUAGE plpgsql;
932
933CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT) 
934  RETURNS INTEGER
935  AS $$
936DECLARE
937BEGIN
938  RETURN getorcreate_name_id(lookup_word, '');
939END;
940$$
941LANGUAGE plpgsql;
942
943CREATE OR REPLACE FUNCTION get_word_id(lookup_word TEXT) 
944  RETURNS INTEGER
945  AS $$
946DECLARE
947  lookup_token TEXT;
948  return_word_id INTEGER;
949BEGIN
950  lookup_token := trim(lookup_word);
951  SELECT word_id FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id;
952  RETURN return_word_id;
953END;
954$$
955LANGUAGE plpgsql IMMUTABLE;
956
957CREATE OR REPLACE FUNCTION get_name_id(lookup_word TEXT) 
958  RETURNS INTEGER
959  AS $$
960DECLARE
961  lookup_token TEXT;
962  return_word_id INTEGER;
963BEGIN
964  lookup_token := ' '||trim(lookup_word);
965  SELECT word_id FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id;
966  RETURN return_word_id;
967END;
968$$
969LANGUAGE plpgsql IMMUTABLE;
970
971CREATE OR REPLACE FUNCTION array_merge(a INTEGER[], b INTEGER[])
972  RETURNS INTEGER[]
973  AS $$
974DECLARE
975  i INTEGER;
976  r INTEGER[];
977BEGIN
978  IF array_upper(a, 1) IS NULL THEN
979    RETURN b;
980  END IF;
981  IF array_upper(b, 1) IS NULL THEN
982    RETURN a;
983  END IF;
984  r := a;
985  FOR i IN 1..array_upper(b, 1) LOOP 
986    IF NOT (ARRAY[b[i]] && r) THEN
987      r := r || b[i];
988    END IF;
989  END LOOP;
990  RETURN r;
991END;
992$$
993LANGUAGE plpgsql IMMUTABLE;
994
995CREATE OR REPLACE FUNCTION add_keywords(a keyvalue[], b keyvalue[]) RETURNS keyvalue[]
996  AS $$
997DECLARE
998  i INTEGER;
999  j INTEGER;
1000  f BOOLEAN;
1001  r keyvalue[];
1002BEGIN
1003  IF array_upper(a, 1) IS NULL THEN
1004    RETURN b;
1005  END IF;
1006  IF array_upper(b, 1) IS NULL THEN
1007    RETURN a;
1008  END IF;
1009  r := a;
1010  FOR i IN 1..array_upper(b, 1) LOOP 
1011    f := false;
1012    FOR j IN 1..array_upper(a, 1) LOOP 
1013      IF (a[j].key = b[i].key) THEN
1014        f := true;
1015      END IF; 
1016    END LOOP;
1017    IF NOT f THEN
1018      r := r || b[i];
1019    END IF;
1020  END LOOP;
1021  RETURN r;
1022END;
1023$$
1024LANGUAGE plpgsql IMMUTABLE;
1025
1026CREATE OR REPLACE FUNCTION make_keywords(src keyvalue[]) RETURNS INTEGER[]
1027  AS $$
1028DECLARE
1029  result INTEGER[];
1030  s TEXT;
1031  w INTEGER;
1032  words TEXT[];
1033  i INTEGER;
1034  j INTEGER;
1035BEGIN
1036  result := '{}'::INTEGER[];
1037
1038  IF NOT array_upper(src, 1) IS NULL THEN
1039
1040    FOR i IN 1..array_upper(src, 1) LOOP
1041
1042      s := make_standard_name(src[i].value);
1043
1044      w := getorcreate_name_id(s, src[i].value);
1045      IF NOT (ARRAY[w] && result) THEN
1046        result := result || w;
1047      END IF;
1048
1049      words := string_to_array(s, ' ');
1050      IF array_upper(words, 1) IS NOT NULL THEN
1051        FOR j IN 1..array_upper(words, 1) LOOP
1052          IF (words[j] != '') THEN
1053            w = getorcreate_word_id(words[j]);
1054            IF NOT (ARRAY[w] && result) THEN
1055              result := result || w;
1056            END IF;
1057          END IF;
1058        END LOOP;
1059      END IF;
1060
1061    END LOOP;
1062  END IF;
1063  RETURN result;
1064END;
1065$$
1066LANGUAGE plpgsql IMMUTABLE;
1067
1068CREATE OR REPLACE FUNCTION make_keywords(src TEXT) RETURNS INTEGER[]
1069  AS $$
1070DECLARE
1071  result INTEGER[];
1072  s TEXT;
1073  w INTEGER;
1074  words TEXT[];
1075  i INTEGER;
1076  j INTEGER;
1077BEGIN
1078  result := '{}'::INTEGER[];
1079
1080  s := make_standard_name(src);
1081  w := getorcreate_name_id(s);
1082
1083  IF NOT (ARRAY[w] && result) THEN
1084    result := result || w;
1085  END IF;
1086
1087  words := string_to_array(s, ' ');
1088  IF array_upper(words, 1) IS NOT NULL THEN
1089    FOR j IN 1..array_upper(words, 1) LOOP
1090      IF (words[j] != '') THEN
1091        w = getorcreate_word_id(words[j]);
1092        IF NOT (ARRAY[w] && result) THEN
1093          result := result || w;
1094        END IF;
1095      END IF;
1096    END LOOP;
1097  END IF;
1098
1099  RETURN result;
1100END;
1101$$
1102LANGUAGE plpgsql IMMUTABLE;
1103
1104CREATE OR REPLACE FUNCTION get_word_score(wordscores wordscore[], words text[]) RETURNS integer
1105  AS $$
1106DECLARE
1107  idxword integer;
1108  idxscores integer;
1109  result integer;
1110BEGIN
1111  IF (wordscores is null OR words is null) THEN
1112    RETURN 0;
1113  END IF;
1114
1115  result := 0;
1116  FOR idxword in 1 .. array_upper(words, 1) LOOP
1117    FOR idxscores in 1 .. array_upper(wordscores, 1) LOOP
1118      IF wordscores[idxscores].word = words[idxword] THEN
1119        result := result + wordscores[idxscores].score;
1120      END IF;
1121    END LOOP;
1122  END LOOP;
1123
1124  RETURN result;
1125END;
1126$$
1127LANGUAGE plpgsql IMMUTABLE;
1128
1129CREATE OR REPLACE FUNCTION get_country_code(place geometry) RETURNS TEXT
1130  AS $$
1131DECLARE
1132  nearcountry RECORD;
1133BEGIN
1134  FOR nearcountry IN select country_code from location_area where ST_Contains(area, ST_Centroid(place)) and country_code is not null order by rank_address asc limit 1
1135  LOOP
1136    RETURN lower(nearcountry.country_code);
1137  END LOOP;
1138  -- Are we in a country? Ignore the error condition of being in multiple countries
1139  FOR nearcountry IN select distinct country_code from country where ST_Contains(country.geometry, ST_Centroid(place)) limit 1
1140  LOOP
1141    RETURN lower(nearcountry.country_code);
1142  END LOOP;
1143  -- Not in a country - try nearest withing 12 miles of a country
1144  FOR nearcountry IN select country_code from country where st_distance(country.geometry, ST_Centroid(place)) < 0.5 order by st_distance(country.geometry, place) asc limit 1
1145  LOOP
1146    RETURN lower(nearcountry.country_code);
1147  END LOOP;
1148  RETURN NULL;
1149END;
1150$$
1151LANGUAGE plpgsql IMMUTABLE;
1152
1153CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR(2)) RETURNS TEXT
1154  AS $$
1155DECLARE
1156  nearcountry RECORD;
1157BEGIN
1158  FOR nearcountry IN select distinct country_default_language_code from country where country_code = search_country_code limit 1
1159  LOOP
1160    RETURN lower(nearcountry.country_default_language_code);
1161  END LOOP;
1162  RETURN NULL;
1163END;
1164$$
1165LANGUAGE plpgsql IMMUTABLE;
1166
1167CREATE OR REPLACE FUNCTION add_location(
1168    place_id BIGINT,
1169    place_country_code varchar(2),
1170    name keyvalue[],
1171    rank_search INTEGER,
1172    rank_address INTEGER,
1173    geometry GEOMETRY
1174  ) 
1175  RETURNS BOOLEAN
1176  AS $$
1177DECLARE
1178  keywords INTEGER[];
1179  country_code VARCHAR(2);
1180  locationid INTEGER;
1181  isarea BOOLEAN;
1182BEGIN
1183  -- 26 = street/highway
1184  IF rank_search < 26 THEN
1185    keywords := make_keywords(name);
1186    IF place_country_code IS NULL THEN
1187      country_code := get_country_code(geometry);
1188    END IF;
1189    country_code := lower(place_country_code);
1190
1191    isarea := false;
1192    IF (ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(geometry)) THEN
1193      INSERT INTO location_area values (place_id,country_code,name,keywords,
1194        rank_search,rank_address,ST_Centroid(geometry),geometry);
1195      isarea := true;
1196    END IF;
1197
1198    INSERT INTO location_point values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1199    IF not isarea THEN
1200    IF rank_search < 26 THEN
1201      INSERT INTO location_point_26 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1202    IF rank_search < 25 THEN
1203      INSERT INTO location_point_25 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1204    IF rank_search < 24 THEN
1205      INSERT INTO location_point_24 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1206    IF rank_search < 23 THEN
1207      INSERT INTO location_point_23 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1208    IF rank_search < 22 THEN
1209      INSERT INTO location_point_22 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1210    IF rank_search < 21 THEN
1211      INSERT INTO location_point_21 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1212    IF rank_search < 20 THEN
1213      INSERT INTO location_point_20 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1214    IF rank_search < 19 THEN
1215      INSERT INTO location_point_19 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1216    IF rank_search < 18 THEN
1217      INSERT INTO location_point_18 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1218    IF rank_search < 17 THEN
1219      INSERT INTO location_point_17 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1220    IF rank_search < 16 THEN
1221      INSERT INTO location_point_16 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1222    IF rank_search < 15 THEN
1223      INSERT INTO location_point_15 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1224    IF rank_search < 14 THEN
1225      INSERT INTO location_point_14 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1226    IF rank_search < 13 THEN
1227      INSERT INTO location_point_13 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1228    IF rank_search < 12 THEN
1229      INSERT INTO location_point_12 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1230    IF rank_search < 11 THEN
1231      INSERT INTO location_point_11 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1232    IF rank_search < 10 THEN
1233      INSERT INTO location_point_10 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1234    IF rank_search < 9 THEN
1235      INSERT INTO location_point_9 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1236    IF rank_search < 8 THEN
1237      INSERT INTO location_point_8 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1238    IF rank_search < 7 THEN
1239      INSERT INTO location_point_7 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1240    IF rank_search < 6 THEN
1241      INSERT INTO location_point_6 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1242    IF rank_search < 5 THEN
1243      INSERT INTO location_point_5 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1244    IF rank_search < 4 THEN
1245      INSERT INTO location_point_4 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1246    IF rank_search < 3 THEN
1247      INSERT INTO location_point_3 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1248    IF rank_search < 2 THEN
1249      INSERT INTO location_point_2 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1250    IF rank_search < 1 THEN
1251      INSERT INTO location_point_1 values (place_id,country_code,name,keywords,rank_search,rank_address,isarea,ST_Centroid(geometry));
1252    END IF;END IF;END IF;END IF;END IF;END IF;END IF;END IF;END IF;END IF;
1253    END IF;END IF;END IF;END IF;END IF;END IF;END IF;END IF;END IF;END IF;
1254    END IF;END IF;END IF;END IF;END IF;END IF;END IF;
1255    RETURN true;
1256  END IF;
1257  RETURN false;
1258END;
1259$$
1260LANGUAGE plpgsql;
1261
1262CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT) RETURNS INTEGER
1263  AS $$
1264DECLARE
1265 
1266  newpoints INTEGER;
1267  waynodes integer[];
1268  nodeid INTEGER;
1269  prevnode RECORD;
1270  nextnode RECORD;
1271  startnumber INTEGER;
1272  endnumber INTEGER;
1273  stepsize INTEGER;
1274  orginalstartnumber INTEGER;
1275  originalnumberrange INTEGER;
1276  housenum INTEGER;
1277  linegeo GEOMETRY;
1278  search_place_id INTEGER;
1279
1280  havefirstpoint BOOLEAN;
1281  linestr TEXT;
1282BEGIN
1283  newpoints := 0;
1284  IF interpolationtype = 'odd' OR interpolationtype = 'even' OR interpolationtype = 'all' THEN
1285
1286--RAISE WARNING 'interpolation % %',wayid,interpolationtype;
1287
1288    select nodes from planet_osm_ways where id = wayid INTO waynodes;
1289    IF array_upper(waynodes, 1) IS NOT NULL THEN
1290
1291      havefirstpoint := false;
1292
1293      FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
1294
1295        select min(place_id) from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::bigint and type = 'house' INTO search_place_id;
1296        IF search_place_id IS NOT NULL THEN
1297          select * from placex where place_id = search_place_id INTO nextnode;
1298        ELSE
1299          select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::bigint and type = 'house' limit 1 INTO nextnode;
1300        END IF;
1301
1302-- TODO: need to tag interpolated places so we can seperate them from the original node if someone changes the ways, tag with wayid ?
1303--        select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::bigint and type = 'house' limit 1 INTO nextnode;
1304
1305        IF nextnode.geometry IS NULL THEN
1306          select ST_SetSRID(ST_Point(lon::float/10000000,lat::float/10000000),4326) from planet_osm_nodes where id = waynodes[nodeidpos] INTO nextnode.geometry;
1307        END IF;
1308     
1309        IF havefirstpoint THEN
1310
1311          -- add point to the line string
1312          linestr := linestr||','||ST_X(nextnode.geometry)||' '||ST_Y(nextnode.geometry);
1313          startnumber := ('0'||substring(prevnode.housenumber,'[0-9]+'))::integer;
1314          endnumber := ('0'||substring(nextnode.housenumber,'[0-9]+'))::integer;
1315
1316          IF startnumber IS NOT NULL and startnumber > 0 AND endnumber IS NOT NULL and endnumber > 0 THEN
1317
1318            IF startnumber != endnumber THEN
1319
1320              linestr := linestr || ')';
1321--RAISE WARNING 'linestr %',linestr;
1322              linegeo := ST_GeomFromText(linestr,4326);
1323              linestr := 'LINESTRING('||ST_X(nextnode.geometry)||' '||ST_Y(nextnode.geometry);
1324              IF (startnumber > endnumber) THEN
1325                housenum := endnumber;
1326                endnumber := startnumber;
1327                startnumber := housenum;
1328                linegeo := ST_Reverse(linegeo);
1329              END IF;
1330              orginalstartnumber := startnumber;
1331              originalnumberrange := endnumber - startnumber;
1332
1333-- Too much broken data worldwide for this test to be worth using
1334--              IF originalnumberrange > 500 THEN
1335--                RAISE WARNING 'Number block of % while processing % %', originalnumberrange, prevnode, nextnode;
1336--              END IF;
1337
1338              IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN
1339                startnumber := startnumber + 1;
1340                stepsize := 2;
1341              ELSE
1342                IF (interpolationtype = 'odd' OR interpolationtype = 'even') THEN
1343                  startnumber := startnumber + 2;
1344                  stepsize := 2;
1345                ELSE
1346                  startnumber := startnumber + 1;
1347                  stepsize := 1;
1348                END IF;
1349              END IF;
1350              endnumber := endnumber - 1;
1351              delete from placex where osm_type = 'N' and osm_id = prevnode.osm_id and type = 'house' and place_id != prevnode.place_id;
1352              FOR housenum IN startnumber..endnumber BY stepsize LOOP
1353                -- this should really copy postcodes but it puts a huge burdon on the system for no big benefit
1354                -- ideally postcodes should move up to the way
1355                 insert into placex values (null,'N',prevnode.osm_id,prevnode.class,prevnode.type,NULL,prevnode.admin_level,housenum,prevnode.street,prevnode.isin,null,prevnode.country_code,prevnode.street_place_id,prevnode.rank_address,prevnode.rank_search,false,ST_Line_Interpolate_Point(linegeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float));
1356                newpoints := newpoints + 1;
1357              END LOOP;
1358            END IF;
1359            prevnode := nextnode;
1360          END IF;
1361        ELSE
1362          startnumber := ('0'||substring(nextnode.housenumber,'[0-9]+'))::integer;
1363          IF startnumber IS NOT NULL AND startnumber > 0 THEN
1364            havefirstpoint := true;
1365            linestr := 'LINESTRING('||ST_X(nextnode.geometry)||' '||ST_Y(nextnode.geometry);
1366            prevnode := nextnode;
1367          END IF;
1368        END IF;
1369      END LOOP;
1370    END IF;
1371  END IF;
1372
1373  RETURN newpoints;
1374END;
1375$$
1376LANGUAGE plpgsql;
1377
1378CREATE OR REPLACE FUNCTION placex_insert() RETURNS TRIGGER
1379  AS $$
1380DECLARE
1381  i INTEGER;
1382  postcode TEXT;
1383  result BOOLEAN;
1384  country_code VARCHAR(2);
1385  diameter FLOAT;
1386BEGIN
1387--  RAISE WARNING '%',NEW.osm_id;
1388--  RAISE WARNING '%',NEW.osm_id;
1389
1390  -- just block these
1391  IF NEW.class = 'highway' and NEW.type in ('turning_circle','traffic_signals','mini_roundabout','noexit','crossing') THEN
1392    RETURN null;
1393  END IF;
1394  IF NEW.class in ('landuse','natural') and NEW.name is null THEN
1395    RETURN null;
1396  END IF;
1397
1398--  RAISE WARNING '%',NEW.osm_id;
1399
1400  IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN 
1401    -- block all invalid geometary - just not worth the risk.  seg faults are causing serious problems.
1402    RETURN NULL;
1403    IF NEW.osm_type = 'R' THEN
1404      -- invalid multipolygons can crash postgis, don't even bother to try!
1405      RETURN NULL;
1406    END IF;
1407    NEW.geometry := ST_buffer(NEW.geometry,0);
1408    IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN 
1409      RAISE WARNING 'Invalid geometary, rejecting: % %', NEW.osm_type, NEW.osm_id;
1410      RETURN NULL;
1411    END IF;
1412  END IF;
1413
1414  NEW.place_id := nextval('seq_place');
1415  NEW.indexed := false;
1416  NEW.country_code := lower(NEW.country_code);
1417
1418--RAISE WARNING '%',NEW.country_code;
1419
1420  IF NEW.housenumber IS NOT NULL THEN
1421    i := getorcreate_housenumber_id(make_standard_name(NEW.housenumber));
1422  END IF;
1423
1424  IF NEW.osm_type = 'X' THEN
1425    -- E'X'ternal records should already be in the right format so do nothing
1426  ELSE
1427    NEW.rank_search := 30;
1428    NEW.rank_address := NEW.rank_search;
1429
1430    -- By doing in postgres we have the country available to us - currently only used for postcode
1431    IF NEW.class = 'place' THEN
1432      IF NEW.type in ('continent') THEN
1433        NEW.rank_search := 2;
1434        NEW.rank_address := NEW.rank_search;
1435      ELSEIF NEW.type in ('sea') THEN
1436        NEW.rank_search := 2;
1437        NEW.rank_address := 0;
1438      ELSEIF NEW.type in ('country') THEN
1439        NEW.rank_search := 4;
1440        NEW.rank_address := NEW.rank_search;
1441      ELSEIF NEW.type in ('state') THEN
1442        NEW.rank_search := 8;
1443        NEW.rank_address := NEW.rank_search;
1444      ELSEIF NEW.type in ('region') THEN
1445        NEW.rank_search := 10;
1446        NEW.rank_address := NEW.rank_search;
1447      ELSEIF NEW.type in ('county') THEN
1448        NEW.rank_search := 12;
1449        NEW.rank_address := NEW.rank_search;
1450      ELSEIF NEW.type in ('city') THEN
1451        NEW.rank_search := 16;
1452        NEW.rank_address := NEW.rank_search;
1453      ELSEIF NEW.type in ('island') THEN
1454        NEW.rank_search := 17;
1455        NEW.rank_address := 0;
1456      ELSEIF NEW.type in ('town') THEN
1457        NEW.rank_search := 17;
1458        NEW.rank_address := NEW.rank_search;
1459      ELSEIF NEW.type in ('village','hamlet','municipality','districy','unincorporated_area','borough','airport') THEN
1460        NEW.rank_search := 18;
1461        NEW.rank_address := 17;
1462      ELSEIF NEW.type in ('moor') THEN
1463        NEW.rank_search := 17;
1464        NEW.rank_address := 0;
1465      ELSEIF NEW.type in ('national_park') THEN
1466        NEW.rank_search := 18;
1467        NEW.rank_address := 18;
1468      ELSEIF NEW.type in ('suburb','croft','subdivision') THEN
1469        NEW.rank_search := 20;
1470        NEW.rank_address := NEW.rank_search;
1471      ELSEIF NEW.type in ('farm','locality','islet') THEN
1472        NEW.rank_search := 20;
1473        NEW.rank_address := 0;
1474      ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN
1475        NEW.rank_search := 22;
1476        NEW.rank_address := 22;
1477      ELSEIF NEW.type in ('postcode') THEN
1478
1479        -- Postcode processing is very country dependant
1480        IF NEW.country_code IS NULL THEN
1481          NEW.country_code := get_country_code(NEW.geometry);
1482        END IF;
1483
1484        IF NEW.country_code = 'gb' THEN
1485          IF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
1486            NEW.rank_search := 25;
1487            NEW.rank_address := 5;
1488            NEW.name := ARRAY[ROW('ref',NEW.postcode)::keyvalue];
1489          ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
1490            NEW.rank_search := 23;
1491            NEW.rank_address := 5;
1492            NEW.name := ARRAY[ROW('ref',NEW.postcode)::keyvalue];
1493          ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
1494            NEW.rank_search := 21;
1495            NEW.rank_address := 5;
1496            NEW.name := ARRAY[ROW('ref',NEW.postcode)::keyvalue];
1497          END IF;
1498
1499        ELSEIF NEW.country_code = 'de' THEN
1500          IF NEW.postcode ~ '^([0-9]{5})$' THEN
1501            NEW.name := ARRAY[ROW('ref',NEW.postcode)::keyvalue];
1502            NEW.rank_search := 21;
1503            NEW.rank_address := 11;
1504          END IF;
1505
1506        ELSE
1507          -- Guess at the postcode format and coverage (!)
1508          IF upper(NEW.postcode) ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
1509            NEW.name := ARRAY[ROW('ref',NEW.postcode)::keyvalue];
1510            NEW.rank_search := 21;
1511            NEW.rank_address := 11;
1512          ELSE
1513            -- Does it look splitable into and area and local code?
1514            postcode := substring(upper(NEW.postcode) from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
1515
1516            IF postcode IS NOT NULL THEN
1517
1518              -- TODO: insert new line into location instead
1519              --result := add_location(NEW.place_id,NEW.country_code,ARRAY[ROW('ref',postcode)::keyvalue],21,11,NEW.geometry);
1520
1521              NEW.name := ARRAY[ROW('ref',NEW.postcode)::keyvalue];
1522              NEW.rank_search := 25;
1523              NEW.rank_address := 11;
1524            ELSEIF NEW.postcode ~ '^[- :A-Z0-9]{6,}$' THEN
1525              NEW.name := ARRAY[ROW('ref',NEW.postcode)::keyvalue];
1526              NEW.rank_search := 21;
1527              NEW.rank_address := 11;
1528            END IF;
1529          END IF;
1530        END IF;
1531
1532      ELSEIF NEW.type in ('airport','street') THEN
1533        NEW.rank_search := 26;
1534        NEW.rank_address := NEW.rank_search;
1535      ELSEIF NEW.type in ('house','building') THEN
1536        NEW.rank_search := 28;
1537        NEW.rank_address := NEW.rank_search;
1538      ELSEIF NEW.type in ('houses') THEN
1539        -- insert new point into place for each derived building
1540        i := create_interpolation(NEW.osm_id, NEW.housenumber);
1541      END IF;
1542
1543    ELSEIF NEW.class = 'boundary' THEN
1544      IF NEW.country_code is null THEN
1545        NEW.country_code := get_country_code(NEW.geometry);
1546      END IF;
1547      NEW.rank_search := NEW.admin_level * 2;
1548      NEW.rank_address := NEW.rank_search;
1549    ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
1550      NEW.rank_search := 22;
1551      NEW.rank_address := NEW.rank_search;
1552    -- any feature more than 5 square miles is probably worth indexing
1553    ELSEIF ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_Area(NEW.geometry) > 0.1 THEN
1554      NEW.rank_search := 22;
1555      NEW.rank_address := NEW.rank_search;
1556    ELSEIF NEW.class = 'highway' AND NEW.name is NULL AND 
1557           NEW.type in ('service','cycleway','path','footway','steps','bridleway','track','byway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN
1558      RETURN NULL;
1559    ELSEIF NEW.class = 'railway' AND NEW.type in ('rail') THEN
1560      RETURN NULL;
1561    ELSEIF NEW.class = 'waterway' AND NEW.name is NULL THEN
1562      RETURN NULL;
1563    ELSEIF NEW.class = 'waterway' THEN
1564      NEW.rank_address := 17;
1565    ELSEIF NEW.class = 'highway' AND NEW.osm_type != 'N' AND NEW.type in ('service','cycleway','path','footway','steps','bridleway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN
1566      NEW.rank_search := 27;
1567      NEW.rank_address := NEW.rank_search;
1568    ELSEIF NEW.class = 'highway' AND NEW.osm_type != 'N' THEN
1569      NEW.rank_search := 26;
1570      NEW.rank_address := NEW.rank_search;
1571    ELSEIF NEW.class = 'natural' and NEW.type = 'sea' THEN
1572      NEW.rank_search := 4;
1573      NEW.rank_address := NEW.rank_search;
1574    END IF;
1575
1576  END IF;
1577
1578  IF array_upper(NEW.name, 1) is not null THEN
1579    result := add_location(NEW.place_id,NEW.country_code,NEW.name,NEW.rank_search,NEW.rank_address,NEW.geometry);
1580  END IF;
1581
1582  --RETURN NEW;
1583  -- The following is not needed until doing diff updates, and slows the main index process down
1584
1585  IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN
1586    -- Performance: We just can't handle re-indexing for country level changes
1587    IF NEW.admin_level > 2 THEN
1588      -- mark items within the geometry for re-indexing
1589--    RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
1590      update placex set indexed = false where indexed and (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) AND rank_search > NEW.rank_search;
1591    END IF;
1592  ELSE
1593    -- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(
1594    diameter := 0;
1595    -- 16 = city, anything higher than city is effectively ignored (polygon required!)
1596    IF NEW.type='postcode' THEN
1597      diameter := 0.001;
1598    ELSEIF NEW.rank_search < 16 THEN
1599      diameter := 0;
1600    ELSEIF NEW.rank_search < 18 THEN
1601      diameter := 0.1;
1602    ELSEIF NEW.rank_search < 20 THEN
1603      diameter := 0.05;
1604    ELSEIF NEW.rank_search < 24 THEN
1605      diameter := 0.02;
1606    ELSEIF NEW.rank_search < 26 THEN
1607      diameter := 0.002; -- 100 to 200 meters
1608    ELSEIF NEW.rank_search < 28 THEN
1609      diameter := 0.001; -- 50 to 100 meters
1610    END IF;
1611    IF diameter > 0 THEN
1612--      RAISE WARNING 'placex point insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,diameter;
1613      update placex set indexed = false where indexed and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter);
1614    END IF;
1615
1616  END IF;
1617
1618--IF NEW.rank_search < 26 THEN
1619--  RAISE WARNING 'placex insert end: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
1620--END IF;
1621
1622  RETURN NEW;
1623
1624END;
1625$$
1626LANGUAGE plpgsql;
1627
1628CREATE OR REPLACE FUNCTION placex_update() RETURNS 
1629TRIGGER
1630  AS $$
1631DECLARE
1632
1633  place_centroid GEOMETRY;
1634  place_geometry_text TEXT;
1635
1636  search_maxdistance FLOAT[];
1637  search_mindistance FLOAT[];
1638  address_havelevel BOOLEAN[];
1639--  search_scores wordscore[];
1640--  search_scores_pos INTEGER;
1641  search_country_code_conflict BOOLEAN;
1642
1643  i INTEGER;
1644  iMax FLOAT;
1645  location RECORD;
1646  relation RECORD;
1647  search_diameter FLOAT;
1648  search_prevdiameter FLOAT;
1649  search_maxrank INTEGER;
1650  address_maxrank INTEGER;
1651  address_street_word_id INTEGER;
1652  street_place_id_count INTEGER;
1653  isin TEXT[];
1654
1655  bPointCountryCode BOOLEAN;
1656
1657  name_vector INTEGER[];
1658  nameaddress_vector INTEGER[];
1659
1660BEGIN
1661
1662--  RAISE WARNING '%',NEW.osm_id;
1663--RAISE WARNING '%', NEW;
1664
1665  IF NEW.class = 'place' AND NEW.type = 'postcodearea' THEN
1666    -- Silently do nothing
1667    RETURN NEW;
1668  END IF;
1669
1670  IF NEW.indexed and NOT OLD.indexed THEN
1671
1672--RAISE WARNING 'PROCESSING: % %', NEW.place_id, NEW.name;
1673
1674    search_country_code_conflict := false;
1675
1676    DELETE FROM search_name WHERE place_id = NEW.place_id;
1677--RAISE WARNING 'x1';
1678    DELETE FROM place_addressline WHERE place_id = NEW.place_id;
1679--RAISE WARNING 'x2';
1680
1681    -- Adding ourselves to the list simplifies address calculations later
1682    INSERT INTO place_addressline VALUES (NEW.place_id, NEW.place_id, true, true, 0, NEW.rank_address); 
1683--RAISE WARNING 'x3';
1684
1685    -- What level are we searching from
1686    search_maxrank := NEW.rank_search;
1687
1688    -- Default max/min distances to look for a location
1689    FOR i IN 1..28 LOOP
1690      search_maxdistance[i] := 1;
1691      search_mindistance[i] := 0.0;
1692      address_havelevel[i] := false;
1693    END LOOP;
1694    -- Minimum size to search, can be larger but don't let it shink below this
1695    search_mindistance[14] := 0.2;
1696    search_mindistance[15] := 0.1;
1697    search_mindistance[16] := 0.05;
1698    search_mindistance[17] := 0.03;
1699    search_mindistance[18] := 0.015;
1700    search_mindistance[19] := 0.008;
1701    search_mindistance[20] := 0.006;
1702    search_mindistance[21] := 0.004;
1703    search_mindistance[22] := 0.003;
1704    search_mindistance[23] := 0.002;
1705    search_mindistance[24] := 0.002;
1706    search_mindistance[25] := 0.001;
1707    search_mindistance[26] := 0.001;
1708
1709    search_maxdistance[14] := 1;
1710    search_maxdistance[15] := 0.5;
1711    search_maxdistance[16] := 0.15;
1712    search_maxdistance[17] := 0.05;
1713    search_maxdistance[18] := 0.02;
1714    search_maxdistance[19] := 0.02;
1715    search_maxdistance[20] := 0.02;
1716    search_maxdistance[21] := 0.02;
1717    search_maxdistance[22] := 0.02;
1718    search_maxdistance[23] := 0.02;
1719    search_maxdistance[24] := 0.02;
1720    search_maxdistance[25] := 0.02;
1721    search_maxdistance[26] := 0.02;
1722
1723    -- Speed up searches - just use the centroid of the feature
1724    -- cheaper but less acurate
1725    place_centroid := ST_Centroid(NEW.geometry);
1726    place_geometry_text := 'ST_GeomFromText('''||ST_AsText(NEW.geometry)||''','||ST_SRID(NEW.geometry)||')';
1727
1728    -- copy the building number to the name
1729    -- done here rather than on insert to avoid initial indexing
1730    -- TODO: This might be a silly thing to do
1731    --IF (NEW.name IS NULL OR array_upper(NEW.name,1) IS NULL) AND NEW.housenumber IS NOT NULL THEN
1732    --  NEW.name := ARRAY[ROW('ref',NEW.housenumber)::keyvalue];
1733    --END IF;
1734
1735    --Temp hack to prevent need to re-index
1736    IF NEW.name::text = '{"(ref,'||NEW.housenumber||')"}' THEN
1737      NEW.name := NULL;
1738    END IF;
1739
1740    --IF (NEW.name IS NULL OR array_upper(NEW.name,1) IS NULL) AND NEW.type IS NOT NULL THEN
1741    --  NEW.name := ARRAY[ROW('type',NEW.type)::keyvalue];
1742    --END IF;
1743
1744    -- Initialise the name and address vectors using our name
1745    name_vector := make_keywords(NEW.name);
1746    nameaddress_vector := name_vector;
1747
1748--RAISE WARNING '% %', NEW.place_id, NEW.rank_search;
1749
1750    -- For low level elements we inherit from our parent road
1751    IF (NEW.rank_search > 26 OR (NEW.type = 'postcode' AND NEW.rank_search = 25)) THEN
1752
1753--RAISE WARNING 'finding street for %', NEW;
1754
1755      NEW.street_place_id := null;
1756
1757      -- to do that we have to find our parent road
1758      -- Copy data from linked items (points on ways, addr:street links, relations)
1759      -- Note that addr:street links can only be indexed once the street itself is indexed
1760      IF NEW.street_place_id IS NULL AND NEW.osm_type = 'N' THEN
1761
1762        -- Is this node part of a relation?
1763        FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['n'||NEW.osm_id]
1764        LOOP
1765          -- At the moment we only process one type of relation - associatedStreet
1766          IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
1767            FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
1768              IF NEW.street_place_id IS NULL AND relation.members[i+1] = 'street' THEN
1769--RAISE WARNING 'node in relation %',relation;
1770                SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer 
1771                  and rank_search = 26 INTO NEW.street_place_id;
1772              END IF;
1773            END LOOP;
1774          END IF;
1775        END LOOP;     
1776
1777--RAISE WARNING 'x1';
1778        -- Is this node part of a way?
1779        -- Limit 10 is to work round problem in query plan optimiser
1780        FOR location IN select * from placex where osm_type = 'W' 
1781          and osm_id in (select id from planet_osm_ways where nodes && ARRAY[NEW.osm_id::integer] limit 10)
1782        LOOP
1783--RAISE WARNING '%', location;
1784          -- Way IS a road then we are on it - that must be our road
1785          IF location.rank_search = 26 AND NEW.street_place_id IS NULL THEN
1786--RAISE WARNING 'node in way that is a street %',location;
1787            NEW.street_place_id := location.place_id;
1788          END IF;
1789
1790          -- Is the WAY part of a relation
1791          FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id::integer] and members @> ARRAY['w'||location.osm_id]
1792          LOOP
1793            -- At the moment we only process one type of relation - associatedStreet
1794            IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
1795              FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
1796                IF NEW.street_place_id IS NULL AND relation.members[i+1] = 'street' THEN
1797--RAISE WARNING 'node in way that is in a relation %',relation;
1798                  SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer 
1799                    and rank_search = 26 INTO NEW.street_place_id;
1800                END IF;
1801              END LOOP;
1802            END IF;
1803          END LOOP;
1804         
1805          -- If the way contains an explicit name of a street copy it
1806          IF NEW.street IS NULL AND location.street IS NOT NULL THEN
1807--RAISE WARNING 'node in way that has a streetname %',location;
1808            NEW.street := location.street;
1809          END IF;
1810
1811          -- If this way is a street interpolation line then it is probably as good as we are going to get
1812          IF NEW.street_place_id IS NULL AND NEW.street IS NULL AND location.class = 'place' and location.type='houses' THEN
1813            -- Try and find a way that is close roughly parellel to this line
1814            FOR relation IN SELECT place_id FROM placex
1815              WHERE ST_DWithin(location.geometry, placex.geometry, 0.001) and placex.rank_search = 26
1816              ORDER BY (ST_distance(placex.geometry, ST_Line_Interpolate_Point(location.geometry,0))+
1817                        ST_distance(placex.geometry, ST_Line_Interpolate_Point(location.geometry,0.5))+
1818                        ST_distance(placex.geometry, ST_Line_Interpolate_Point(location.geometry,1))) ASC limit 1
1819            LOOP
1820--RAISE WARNING 'using nearest street to address interpolation line,0.001 %',relation;
1821              NEW.street_place_id := relation.place_id;
1822            END LOOP;
1823          END IF;
1824
1825        END LOOP;
1826               
1827      END IF;
1828
1829--RAISE WARNING 'x2';
1830
1831      IF NEW.street_place_id IS NULL AND NEW.osm_type = 'W' THEN
1832        -- Is this way part of a relation?
1833        FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['w'||NEW.osm_id]
1834        LOOP
1835          -- At the moment we only process one type of relation - associatedStreet
1836          IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
1837            FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
1838              IF NEW.street_place_id IS NULL AND relation.members[i+1] = 'street' THEN
1839--RAISE WARNING 'way that is in a relation %',relation;
1840                SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer
1841                  and rank_search = 26 INTO NEW.street_place_id;
1842              END IF;
1843            END LOOP;
1844          END IF;
1845        END LOOP;
1846      END IF;
1847     
1848--RAISE WARNING 'x3';
1849
1850      IF NEW.street_place_id IS NULL AND NEW.street IS NOT NULL THEN
1851        address_street_word_id := get_name_id(make_standard_name(NEW.street));
1852        IF address_street_word_id IS NOT NULL THEN
1853          FOR location IN SELECT place_id,ST_distance(NEW.geometry, search_name.centroid) as distance
1854            FROM search_name WHERE search_name.name_vector @> ARRAY[address_street_word_id]
1855            AND ST_DWithin(NEW.geometry, search_name.centroid, 0.01) and search_rank = 26 
1856            ORDER BY ST_distance(NEW.geometry, search_name.centroid) ASC limit 1
1857          LOOP
1858--RAISE WARNING 'streetname found nearby %',location;
1859            NEW.street_place_id := location.place_id;
1860          END LOOP;
1861        END IF;
1862        -- Failed, fall back to nearest - don't just stop
1863        IF NEW.street_place_id IS NULL THEN
1864--RAISE WARNING 'unable to find streetname nearby % %',NEW.street,address_street_word_id;
1865--          RETURN null;
1866        END IF;
1867      END IF;
1868
1869--RAISE WARNING 'x4';
1870
1871      search_diameter := 0.00005;
1872      WHILE NEW.street_place_id IS NULL AND search_diameter < 0.1 LOOP
1873--RAISE WARNING '% %', search_diameter,ST_AsText(ST_Centroid(NEW.geometry));
1874        FOR location IN SELECT place_id FROM placex
1875          WHERE ST_DWithin(place_centroid, placex.geometry, search_diameter) and rank_search = 26
1876          ORDER BY ST_distance(NEW.geometry, placex.geometry) ASC limit 1
1877        LOOP
1878--RAISE WARNING 'using nearest street,% % %',search_diameter,NEW.street,location;
1879          NEW.street_place_id := location.place_id;
1880        END LOOP;
1881        search_diameter := search_diameter * 2;
1882      END LOOP;
1883
1884--RAISE WARNING 'x6 %',NEW.street_place_id;
1885
1886      -- If we didn't find any road give up, should be VERY rare
1887      IF NEW.street_place_id IS NULL THEN
1888        return NEW;
1889      END IF;
1890
1891      -- Some unnamed roads won't have been indexed, index now if needed
1892      select count(*) from place_addressline where place_id = NEW.street_place_id INTO street_place_id_count;
1893      IF street_place_id_count = 0 THEN
1894        UPDATE placex set indexed = true where indexed = false and place_id = NEW.street_place_id;
1895      END IF;
1896
1897      -- Add the street to the address as zero distance to force to front of list
1898      INSERT INTO place_addressline VALUES (NEW.place_id, NEW.street_place_id, true, true, 0, 26);
1899      address_havelevel[26] := true;
1900
1901      -- Import address details from parent, reclculating distance in process
1902      INSERT INTO place_addressline select NEW.place_id, x.address_place_id, x.fromarea, x.isaddress, ST_distance(NEW.geometry, placex.geometry), placex.rank_address
1903        from place_addressline as x join placex on (address_place_id = placex.place_id)
1904        where x.place_id = NEW.street_place_id and x.address_place_id != NEW.street_place_id;
1905
1906      -- Get the details of the parent road
1907      select * from search_name where place_id = NEW.street_place_id INTO location;
1908      NEW.country_code := location.country_code;
1909
1910--RAISE WARNING '%', NEW.name;
1911      -- If there is no name it isn't searchable, don't bother to create a search record
1912      IF NEW.name is NULL THEN
1913        return NEW;
1914      END IF;
1915
1916      -- Merge address from parent
1917      nameaddress_vector := array_merge(nameaddress_vector, location.nameaddress_vector);
1918
1919      -- Performance, it would be more acurate to do all the rest of the import process but it takes too long
1920      -- Just be happy with inheriting from parent road only
1921      INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_address, NEW.country_code,
1922        name_vector, nameaddress_vector, place_centroid);
1923
1924      return NEW;
1925
1926    END IF;
1927
1928--RAISE WARNING '  INDEXING: %',NEW;
1929
1930    -- Process area matches (tend to be better quality)
1931    FOR location IN SELECT
1932      place_id,
1933      name,
1934      keywords,
1935      country_code,
1936      rank_address,
1937      rank_search,
1938      ST_Distance(place_centroid, centroid) as distance
1939      FROM location_area
1940      WHERE ST_Contains(area, place_centroid) and location_area.rank_search < search_maxrank
1941      ORDER BY ST_Distance(place_centroid, centroid) ASC
1942    LOOP
1943
1944--RAISE WARNING '  AREA: %',location.keywords;
1945
1946      IF NEW.country_code IS NULL THEN
1947        NEW.country_code := location.country_code;
1948      ELSEIF NEW.country_code != location.country_code and location.rank_search > 3 THEN
1949        search_country_code_conflict := true;
1950      END IF;
1951 
1952      -- Add it to the list of search terms
1953      nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
1954      INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); 
1955      address_havelevel[location.rank_address] := true;
1956
1957    END LOOP;
1958
1959    -- try using the isin value to find parent places
1960    address_maxrank := search_maxrank;
1961    IF NEW.isin IS NOT NULL THEN
1962      isin := regexp_split_to_array(NEW.isin, E'[;,]');
1963      FOR i IN 1..array_upper(isin, 1) LOOP
1964        address_street_word_id := get_name_id(make_standard_name(isin[i]));
1965        IF address_street_word_id IS NOT NULL THEN
1966--RAISE WARNING '  search: %',address_street_word_id;
1967          FOR location IN SELECT place_id,keywords,rank_search,location_point.country_code,rank_address,
1968            ST_Distance(place_centroid, search_name.centroid) as distance
1969            FROM search_name join location_point using (place_id)
1970            WHERE search_name.name_vector @> ARRAY[address_street_word_id]
1971            AND rank_search < NEW.rank_search
1972            AND (NEW.country_code IS NULL OR search_name.country_code = NEW.country_code)
1973            ORDER BY ST_distance(NEW.geometry, search_name.centroid) ASC limit 1
1974          LOOP
1975
1976            IF NEW.country_code IS NULL THEN
1977              NEW.country_code := location.country_code;
1978            ELSEIF NEW.country_code != location.country_code and location.rank_search > 3 THEN
1979              search_country_code_conflict := true;
1980            END IF;
1981
1982--RAISE WARNING '  found: %',location.place_id;
1983            nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
1984            INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
1985
1986            IF address_maxrank > location.rank_address THEN
1987              address_maxrank := location.rank_address;
1988            END IF;
1989          END LOOP;
1990        END IF;
1991      END LOOP;
1992      FOR i IN address_maxrank..28 LOOP
1993        address_havelevel[i] := true;
1994      END LOOP;
1995    END IF;
1996
1997    -- If we have got a consistent country code from the areas and/or isin then we don't care about points (too inacurate)
1998    bPointCountryCode := NEW.country_code IS NULL;
1999
2000    IF true THEN
2001    -- full search using absolute position
2002
2003    search_diameter := 0;
2004    -- 16 = city, anything larger tends to be an area so don't continue
2005    WHILE search_diameter < 1 AND search_maxrank > 16 LOOP
2006
2007      search_prevdiameter := search_diameter;
2008      IF search_diameter = 0 THEN
2009        search_diameter := 0.001;
2010      ELSE
2011        search_diameter := search_diameter * 2;
2012      END IF;
2013
2014      -- Try nearest
2015      FOR location IN EXECUTE 'SELECT place_id, name, keywords, country_code, rank_address, rank_search,'||
2016        'ST_Distance('||place_geometry_text||', centroid) as distance'||
2017        ' FROM location_point_'||(case when search_maxrank > 26 THEN 26 ELSE search_maxrank end)||
2018        ' WHERE ST_DWithin('||place_geometry_text||', centroid, '||search_diameter||') '||
2019        '  AND ST_Distance('||place_geometry_text||', centroid) > '||search_prevdiameter||
2020        ' ORDER BY ST_Distance('||place_geometry_text||', centroid) ASC'
2021      LOOP
2022
2023        IF bPointCountryCode THEN     
2024          IF NEW.country_code IS NULL THEN
2025            NEW.country_code := location.country_code;
2026          ELSEIF NEW.country_code != location.country_code THEN
2027            search_country_code_conflict := true;
2028          END IF;
2029        END IF;
2030   
2031        -- Find search words
2032--RAISE WARNING 'IF % % % %', location.name, location.distance, location.rank_search, search_maxdistance;
2033        IF (location.distance < search_maxdistance[location.rank_search]) THEN
2034--RAISE WARNING '  POINT: % % % %', location.name, location.rank_search, location.place_id, location.distance;
2035   
2036          -- Add it to the list of search terms, de-duplicate
2037          nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
2038   
2039          iMax := (location.distance*1.5)::float;
2040          FOR i IN location.rank_search..28 LOOP
2041            IF iMax < search_maxdistance[i] THEN
2042              IF iMax > search_mindistance[i] THEN
2043                search_maxdistance[i] := iMax;
2044              ELSE
2045                search_maxdistance[i] := search_mindistance[i];
2046              END IF;
2047            END IF;
2048          END LOOP;
2049
2050          INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); 
2051          address_havelevel[location.rank_address] := true;
2052 
2053        ELSE
2054--RAISE WARNING '  Stopped: % % % %', location.rank_search, location.distance, search_maxdistance[location.rank_search], location.name;
2055          IF search_maxrank > location.rank_search THEN
2056            search_maxrank := location.rank_search;
2057          END IF;
2058        END IF;
2059   
2060      END LOOP;
2061 
2062--RAISE WARNING '  POINT LOCATIONS, % %', search_maxrank, search_diameter;
2063 
2064    END LOOP; --WHILE
2065
2066    ELSE
2067      -- Cascading search using nearest parent
2068    END IF;
2069
2070    IF search_country_code_conflict OR NEW.country_code IS NULL THEN
2071      NEW.country_code := get_country_code(place_centroid);
2072    END IF;
2073
2074    INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, NEW.country_code, 
2075      name_vector, nameaddress_vector, place_centroid);
2076
2077  END IF;
2078
2079  return NEW;
2080END;
2081$$
2082LANGUAGE plpgsql;
2083
2084CREATE OR REPLACE FUNCTION placex_delete() RETURNS TRIGGER
2085  AS $$
2086DECLARE
2087BEGIN
2088
2089--IF OLD.rank_search < 26 THEN
2090--RAISE WARNING 'delete % % % % %',OLD.place_id,OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;
2091--END IF;
2092
2093  -- mark everything linked to this place for re-indexing
2094  UPDATE placex set indexed = false from place_addressline where address_place_id = OLD.place_id and placex.place_id = place_addressline.place_id and indexed;
2095
2096  -- do the actual delete
2097  DELETE FROM location_area where place_id = OLD.place_id;
2098  DELETE FROM location_point where place_id = OLD.place_id;
2099  DELETE FROM location_point_0 where place_id = OLD.place_id;
2100  DELETE FROM location_point_1 where place_id = OLD.place_id;
2101  DELETE FROM location_point_2 where place_id = OLD.place_id;
2102  DELETE FROM location_point_3 where place_id = OLD.place_id;
2103  DELETE FROM location_point_4 where place_id = OLD.place_id;
2104  DELETE FROM location_point_5 where place_id = OLD.place_id;
2105  DELETE FROM location_point_6 where place_id = OLD.place_id;
2106  DELETE FROM location_point_7 where place_id = OLD.place_id;
2107  DELETE FROM location_point_8 where place_id = OLD.place_id;
2108  DELETE FROM location_point_9 where place_id = OLD.place_id;
2109  DELETE FROM location_point_10 where place_id = OLD.place_id;
2110  DELETE FROM location_point_11 where place_id = OLD.place_id;
2111  DELETE FROM location_point_12 where place_id = OLD.place_id;
2112  DELETE FROM location_point_13 where place_id = OLD.place_id;
2113  DELETE FROM location_point_14 where place_id = OLD.place_id;
2114  DELETE FROM location_point_15 where place_id = OLD.place_id;
2115  DELETE FROM location_point_16 where place_id = OLD.place_id;
2116  DELETE FROM location_point_17 where place_id = OLD.place_id;
2117  DELETE FROM location_point_18 where place_id = OLD.place_id;
2118  DELETE FROM location_point_19 where place_id = OLD.place_id;
2119  DELETE FROM location_point_20 where place_id = OLD.place_id;
2120  DELETE FROM location_point_21 where place_id = OLD.place_id;
2121  DELETE FROM location_point_22 where place_id = OLD.place_id;
2122  DELETE FROM location_point_23 where place_id = OLD.place_id;
2123  DELETE FROM location_point_24 where place_id = OLD.place_id;
2124  DELETE FROM location_point_25 where place_id = OLD.place_id;
2125  DELETE FROM location_point_26 where place_id = OLD.place_id;
2126  DELETE FROM search_name where place_id = OLD.place_id;
2127  DELETE FROM place_addressline where place_id = OLD.place_id;
2128  DELETE FROM place_addressline where address_place_id = OLD.place_id;
2129
2130  RETURN OLD;
2131
2132END;
2133$$
2134LANGUAGE plpgsql;
2135
2136CREATE OR REPLACE FUNCTION place_delete() RETURNS TRIGGER
2137  AS $$
2138DECLARE
2139  placeid INTEGER;
2140BEGIN
2141
2142--  RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;
2143  delete from placex where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class;
2144  RETURN OLD;
2145
2146END;
2147$$
2148LANGUAGE plpgsql;
2149
2150CREATE OR REPLACE FUNCTION place_insert() RETURNS TRIGGER
2151  AS $$
2152DECLARE
2153  existing RECORD;
2154  existinggeometry GEOMETRY;
2155  existingplace_id INTEGER;
2156BEGIN
2157
2158--    RAISE WARNING 'place_insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW;
2159
2160  -- Just block these - lots and pointless
2161  IF NEW.class = 'highway' and NEW.type in ('turning_circle','traffic_signals','mini_roundabout','noexit','crossing') THEN
2162    RETURN null;
2163  END IF;
2164  IF NEW.class in ('landuse','natural') and NEW.name is null THEN
2165    RETURN null;
2166  END IF;
2167
2168--  IF NEW.osm_type = 'W' then
2169--  END IF;
2170
2171  -- Patch in additional country names
2172  IF NEW.admin_level = 2 AND NEW.type = 'adminitrative' AND NEW.country_code is not null THEN
2173    select add_keywords(NEW.name, country_name.name) from country_name where country_name.country_code = lower(NEW.country_code) INTO NEW.name;
2174  END IF;
2175   
2176  -- Have we already done this place?
2177  select * from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existing;
2178
2179  -- Handle a place changing type by removing the old data
2180  -- My generated 'place' types are causing havok because they overlap with real tags
2181  -- TODO: move them to their own special purpose tag to avoid collisions
2182  IF existing IS NULL AND (NEW.type not in ('postcode','house','houses')) THEN
2183    DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type not in ('postcode','house','houses');
2184  END IF;
2185
2186--IF NEW.class != 'highway' THEN
2187--  RAISE WARNING ': % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,existing;
2188--END IF;
2189
2190  -- To paraphrase, if there isn't an existing item, OR if the admin level has changed, OR if it is a major change in geometry
2191  IF existing IS NULL OR coalesce(existing.admin_level, 100) != coalesce(NEW.admin_level, 100) 
2192     OR (existing.geometry != NEW.geometry AND ST_Distance(ST_Centroid(existing.geometry),ST_Centroid(NEW.geometry)) > 0.01 AND NOT
2193     (ST_GeometryType(existing.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')))
2194     THEN
2195
2196    IF existing IS NOT NULL THEN
2197      RAISE WARNING 'insert delete % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,ST_Distance(ST_Centroid(existing.geometry),ST_Centroid(NEW.geometry)),existing;
2198      DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
2199    END IF;   
2200
2201--IF existing.rank_search < 26 THEN
2202--    RAISE WARNING 'insert placex % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
2203--END IF;
2204
2205    -- No - process it as a new insertion (hopefully of low rank or it will be slow)
2206    insert into placex values (NEW.place_id
2207        ,NEW.osm_type
2208        ,NEW.osm_id
2209        ,NEW.class
2210        ,NEW.type
2211        ,NEW.name
2212        ,NEW.admin_level
2213        ,NEW.housenumber
2214        ,NEW.street
2215        ,NEW.isin
2216        ,NEW.postcode
2217        ,NEW.country_code
2218        ,NEW.street_place_id
2219        ,NEW.rank_address,NEW.rank_search
2220        ,NEW.indexed
2221        ,NEW.geometry
2222        );
2223
2224--    RAISE WARNING 'insert done % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
2225
2226    RETURN NEW;
2227  END IF;
2228
2229  -- Special case for polygon shape changes because they tend to be large and we can be a bit clever about how we handle them
2230  IF existing IS NOT NULL AND existing.geometry != NEW.geometry THEN
2231
2232    IF ST_GeometryType(existing.geometry) in ('ST_Polygon','ST_MultiPolygon')
2233     AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN 
2234
2235      -- Validate the new geometry or it can have weard results
2236      IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN 
2237        NEW.geometry := ST_buffer(NEW.geometry,0);
2238        IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN 
2239          RAISE WARNING 'Invalid geometary, rejecting: % %', NEW.osm_type, NEW.osm_id;
2240          RETURN NULL;
2241        END IF;
2242      END IF;
2243
2244      -- Get the version of the geometry actually used (in placex table)
2245      select geometry from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type into existinggeometry;
2246
2247      RAISE WARNING 'update polygon1 % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
2248
2249      -- re-index points that have moved in / out of the polygon, could be done as a single query but postgres gets the index usage wrong
2250      update placex set indexed = false where indexed and 
2251        (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
2252        AND NOT (ST_Contains(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
2253        AND rank_search > NEW.rank_search;
2254
2255      RAISE WARNING 'update polygon2 % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
2256
2257      update placex set indexed = false where indexed and 
2258        (ST_Contains(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
2259        AND NOT (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
2260        AND rank_search > NEW.rank_search;
2261
2262       RAISE WARNING 'update place % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
2263
2264      update place set 
2265        geometry = NEW.geometry
2266        where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
2267
2268       RAISE WARNING 'update placex % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
2269
2270      update placex set 
2271        geometry = NEW.geometry
2272        where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
2273    ELSE
2274
2275--      RAISE WARNING 'update minor geo % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
2276
2277      -- minor geometery change, update the point itself, but don't bother to reindex anything else (performance)
2278      update place set 
2279        geometry = NEW.geometry
2280        where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
2281
2282      update placex set 
2283        geometry = NEW.geometry
2284        where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
2285
2286    END IF;
2287  END IF;
2288
2289  IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
2290    OR coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '')
2291    OR coalesce(existing.street, '') != coalesce(NEW.street, '')
2292    OR coalesce(existing.isin, '') != coalesce(NEW.isin, '')
2293    OR coalesce(existing.postcode, '') != coalesce(NEW.postcode, '')
2294    OR coalesce(existing.country_code, '') != coalesce(NEW.country_code, '') THEN
2295
2296IF existing.rank_search < 26 THEN
2297  IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '') THEN
2298    RAISE WARNING 'update details, name: % % % %',NEW.osm_type,NEW.osm_id,existing.name::text,NEW.name::text;
2299  END IF;
2300  IF coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '') THEN
2301    RAISE WARNING 'update details, housenumber: % % % %',NEW.osm_type,NEW.osm_id,existing.housenumber,NEW.housenumber;
2302  END IF;
2303  IF coalesce(existing.street, '') != coalesce(NEW.street, '') THEN
2304    RAISE WARNING 'update details, street: % % % %',NEW.osm_type,NEW.osm_id,existing.street,NEW.street;
2305  END IF;
2306  IF coalesce(existing.isin, '') != coalesce(NEW.isin, '') THEN
2307    RAISE WARNING 'update details, isin: % % % %',NEW.osm_type,NEW.osm_id,existing.isin,NEW.isin;
2308  END IF;
2309  IF coalesce(existing.postcode, '') != coalesce(NEW.postcode, '') THEN
2310    RAISE WARNING 'update details, postcode: % % % %',NEW.osm_type,NEW.osm_id,existing.postcode,NEW.postcode;
2311  END IF;
2312  IF coalesce(existing.country_code, '') != coalesce(NEW.country_code, '') THEN
2313    RAISE WARNING 'update details, country_code: % % % %',NEW.osm_type,NEW.osm_id,existing.country_code,NEW.country_code;
2314  END IF;
2315  END IF;
2316
2317--    RAISE WARNING 'update details % % %',NEW.osm_type,NEW.osm_id,existing.place_id;
2318
2319    update place set 
2320      name = NEW.name,
2321      housenumber  = NEW.housenumber,
2322      street = NEW.street,
2323      isin = NEW.isin,
2324      postcode = NEW.postcode,
2325      country_code = null,
2326      street_place_id = null
2327      where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
2328
2329--    RAISE WARNING 'update placex % % %',NEW.osm_type,NEW.osm_id,existing.place_id;
2330
2331    update placex set 
2332      name = NEW.name,
2333      housenumber  = NEW.housenumber,
2334      street = NEW.street,
2335      isin = NEW.isin,
2336      postcode = NEW.postcode,
2337      country_code = null,
2338      street_place_id = null
2339      where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
2340
2341--    RAISE WARNING 'update children % % %',NEW.osm_type,NEW.osm_id,existing.place_id;
2342
2343    select place_id from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type into existingplace_id;
2344   
2345    -- performance, can't take the load of re-indexing a whole country
2346    IF existing.rank_search > 4 THEN
2347      UPDATE placex set indexed = false from place_addressline where address_place_id = existingplace_id and placex.place_id = place_addressline.place_id and indexed;
2348    END IF;
2349  END IF;
2350
2351--  RAISE WARNING 'update end % % %',NEW.osm_type,NEW.osm_id,existing.place_id;
2352
2353  -- Abort the add
2354  RETURN NULL;
2355
2356END;
2357$$
2358LANGUAGE plpgsql;
2359
2360CREATE OR REPLACE FUNCTION get_name_by_language(name keyvalue[], languagepref TEXT[]) RETURNS TEXT
2361  AS $$
2362DECLARE
2363  search TEXT[];
2364  found BOOLEAN;
2365BEGIN
2366
2367  IF (array_upper(name, 1) is null) THEN
2368    return null;
2369  END IF;
2370
2371  search := languagepref;
2372
2373  FOR j IN 1..array_upper(search, 1) LOOP
2374    FOR k IN 1..array_upper(name, 1) LOOP
2375      IF (name[k].key = search[j] AND trim(name[k].value) != '') THEN
2376        return trim(name[k].value);
2377      END IF;
2378    END LOOP;
2379  END LOOP;
2380
2381  RETURN null;
2382END;
2383$$
2384LANGUAGE plpgsql IMMUTABLE;
2385
2386CREATE OR REPLACE FUNCTION get_connected_ways(way_ids INTEGER[]) RETURNS SETOF planet_osm_ways
2387  AS $$
2388DECLARE
2389  searchnodes INTEGER[];
2390  location RECORD;
2391  j INTEGER;
2392BEGIN
2393
2394  searchnodes := '{}';
2395  FOR j IN 1..array_upper(way_ids, 1) LOOP
2396    FOR location IN 
2397      select nodes from planet_osm_ways where id = way_ids[j] LIMIT 1
2398    LOOP
2399      searchnodes := searchnodes | location.nodes;
2400    END LOOP;
2401  END LOOP;
2402
2403  RETURN QUERY select * from planet_osm_ways where nodes && searchnodes and NOT ARRAY[id] <@ way_ids;
2404END;
2405$$
2406LANGUAGE plpgsql IMMUTABLE;
2407
2408CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id BIGINT) RETURNS TEXT
2409  AS $$
2410DECLARE
2411  result TEXT[];
2412  search TEXT[];
2413  for_postcode TEXT;
2414  found INTEGER;
2415  location RECORD;
2416BEGIN
2417
2418  found := 1000;
2419  search := ARRAY['ref'];
2420  result := '{}';
2421
2422  UPDATE placex set indexed = true where indexed = false and place_id = for_place_id;
2423
2424  select postcode from placex where place_id = for_place_id limit 1 into for_postcode;
2425
2426  FOR location IN 
2427    select rank_address,name,distance,length(name::text) as namelength
2428      from place_addressline join placex on (address_place_id = placex.place_id) 
2429      where place_addressline.place_id = for_place_id and rank_address in (5,11)
2430      order by rank_address desc,rank_search desc,fromarea desc,distance asc,namelength desc
2431  LOOP
2432    IF array_upper(search, 1) IS NOT NULL AND array_upper(location.name, 1) IS NOT NULL THEN
2433      FOR j IN 1..array_upper(search, 1) LOOP
2434        FOR k IN 1..array_upper(location.name, 1) LOOP
2435          IF (found > location.rank_address AND location.name[k].key = search[j] AND location.name[k].value != '') AND NOT result && ARRAY[trim(location.name[k].value)] AND (for_postcode IS NULL OR location.name[k].value ilike for_postcode||'%') THEN
2436            result[(100 - location.rank_address)] := trim(location.name[k].value);
2437            found := location.rank_address;
2438          END IF;
2439        END LOOP;
2440      END LOOP;
2441    END IF;
2442  END LOOP;
2443
2444  RETURN array_to_string(result,', ');
2445END;
2446$$
2447LANGUAGE plpgsql;
2448
2449CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT, languagepref TEXT[]) RETURNS TEXT
2450  AS $$
2451DECLARE
2452  result TEXT[];
2453  search TEXT[];
2454  found INTEGER;
2455  location RECORD;
2456  searchcountrycode varchar(2);
2457  searchhousenumber TEXT;
2458  searchrankaddress INTEGER;
2459BEGIN
2460
2461  found := 1000;
2462  search := languagepref;
2463  result := '{}';
2464
2465--  UPDATE placex set indexed = false where indexed = true and place_id = for_place_id;
2466  UPDATE placex set indexed = true where indexed = false and place_id = for_place_id;
2467
2468  select country_code,housenumber,rank_address from placex where place_id = for_place_id into searchcountrycode,searchhousenumber,searchrankaddress;
2469
2470  FOR location IN 
2471    select CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 ELSE rank_address END as rank_address,
2472      name,distance,length(name::text) as namelength
2473      from place_addressline join placex on (address_place_id = placex.place_id) 
2474      where place_addressline.place_id = for_place_id and ((rank_address > 0 AND rank_address < searchrankaddress) OR address_place_id = for_place_id)
2475      and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode OR rank_address < 4)
2476      order by rank_address desc,rank_search desc,fromarea desc,distance asc,namelength desc
2477  LOOP
2478    IF array_upper(search, 1) IS NOT NULL AND array_upper(location.name, 1) IS NOT NULL THEN
2479      FOR j IN 1..array_upper(search, 1) LOOP
2480        FOR k IN 1..array_upper(location.name, 1) LOOP
2481          IF (found > location.rank_address AND location.name[k].key = search[j] AND location.name[k].value != '') AND NOT result && ARRAY[trim(location.name[k].value)] THEN
2482            result[(100 - location.rank_address)] := trim(location.name[k].value);
2483            found := location.rank_address;
2484          END IF;
2485        END LOOP;
2486      END LOOP;
2487    END IF;
2488  END LOOP;
2489
2490  IF searchhousenumber IS NOT NULL AND result[(100 - 28)] IS NULL THEN
2491    result[(100 - 28)] := searchhousenumber;
2492  END IF;
2493
2494  -- No country polygon - add it from the country_code
2495  IF found > 4 THEN
2496    select get_name_by_language(country_name.name,languagepref) as name from placex join country_name using (country_code) 
2497      where place_id = for_place_id limit 1 INTO location;
2498    IF location IS NOT NULL THEN
2499      result[(100 - 4)] := trim(location.name);
2500    END IF;
2501  END IF;
2502
2503  RETURN array_to_string(result,', ');
2504END;
2505$$
2506LANGUAGE plpgsql;
2507
2508CREATE OR REPLACE FUNCTION get_addressdata_by_language(for_place_id BIGINT, languagepref TEXT[]) RETURNS TEXT[]
2509  AS $$
2510DECLARE
2511  result TEXT[];
2512  search TEXT[];
2513  found INTEGER;
2514  location RECORD;
2515  searchcountrycode varchar(2);
2516  searchhousenumber TEXT;
2517BEGIN
2518
2519  found := 1000;
2520  search := languagepref;
2521  result := '{}';
2522
2523--  UPDATE placex set indexed = false where indexed = true and place_id = for_place_id;
2524  UPDATE placex set indexed = true where indexed = false and place_id = for_place_id;
2525
2526  select country_code,housenumber from placex where place_id = for_place_id into searchcountrycode,searchhousenumber;
2527
2528  FOR location IN 
2529    select CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 ELSE rank_address END as rank_address,
2530      name,distance,length(name::text) as namelength
2531      from place_addressline join placex on (address_place_id = placex.place_id) 
2532      where place_addressline.place_id = for_place_id and (rank_address > 0 OR address_place_id = for_place_id)
2533      and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode OR rank_address < 4)
2534      order by rank_address desc,rank_search desc,fromarea desc,distance asc,namelength desc
2535  LOOP
2536    IF array_upper(search, 1) IS NOT NULL AND array_upper(location.name, 1) IS NOT NULL THEN
2537      FOR j IN 1..array_upper(search, 1) LOOP
2538        FOR k IN 1..array_upper(location.name, 1) LOOP
2539          IF (found > location.rank_address AND location.name[k].key = search[j] AND location.name[k].value != '') AND NOT result && ARRAY[trim(location.name[k].value)] THEN
2540            result[(100 - location.rank_address)] := trim(location.name[k].value);
2541            found := location.rank_address;
2542          END IF;
2543        END LOOP;
2544      END LOOP;
2545    END IF;
2546  END LOOP;
2547
2548  IF searchhousenumber IS NOT NULL AND result[(100 - 28)] IS NULL THEN
2549    result[(100 - 28)] := searchhousenumber;
2550  END IF;
2551
2552  -- No country polygon - add it from the country_code
2553  IF found > 4 THEN
2554    select get_name_by_language(country_name.name,languagepref) as name from placex join country_name using (country_code) 
2555      where place_id = for_place_id limit 1 INTO location;
2556    IF location IS NOT NULL THEN
2557      result[(100 - 4)] := trim(location.name);
2558    END IF;
2559  END IF;
2560
2561  RETURN result;
2562END;
2563$$
2564LANGUAGE plpgsql;
2565
2566CREATE OR REPLACE FUNCTION get_place_boundingbox(search_place_id INTEGER) RETURNS place_boundingbox
2567  AS $$
2568DECLARE
2569  result place_boundingbox;
2570  numfeatures integer;
2571BEGIN
2572  select * from place_boundingbox into result where place_id = search_place_id;
2573  IF result IS NULL THEN
2574    select count(*) from place_addressline where address_place_id = search_place_id and isaddress = true into numfeatures;
2575    insert into place_boundingbox select place_id,
2576             ST_Y(ST_PointN(ExteriorRing(ST_Box2D(area)),4)),ST_Y(ST_PointN(ExteriorRing(ST_Box2D(area)),2)),
2577             ST_X(ST_PointN(ExteriorRing(ST_Box2D(area)),1)),ST_X(ST_PointN(ExteriorRing(ST_Box2D(area)),3)),
2578             numfeatures, ST_Area(area),
2579             area from location_area where place_id = search_place_id;
2580    select * from place_boundingbox into result where place_id = search_place_id;
2581  END IF;
2582  IF result IS NULL THEN
2583-- TODO 0.0001
2584    insert into place_boundingbox select address_place_id,
2585             min(ST_Y(ST_Centroid(geometry))) as minlon,max(ST_Y(ST_Centroid(geometry))) as maxlon,
2586             min(ST_X(ST_Centroid(geometry))) as minlat,max(ST_X(ST_Centroid(geometry))) as maxlat,
2587             count(*), ST_Area(ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001)) as area,
2588             ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001) as boundary
2589             from place_addressline join placex using (place_id) 
2590             where address_place_id = search_place_id
2591               and (isaddress = true OR place_id = search_place_id)
2592               and (st_length(geometry) < 0.01 or place_id = search_place_id)
2593             group by address_place_id limit 1;
2594    select * from place_boundingbox into result where place_id = search_place_id;
2595  END IF;
2596  return result;
2597END;
2598$$
2599LANGUAGE plpgsql;
2600
2601-- don't do the operation if it would be slow
2602CREATE OR REPLACE FUNCTION get_place_boundingbox_quick(search_place_id INTEGER) RETURNS place_boundingbox
2603  AS $$
2604DECLARE
2605  result place_boundingbox;
2606  numfeatures integer;
2607  rank integer;
2608BEGIN
2609  select * from place_boundingbox into result where place_id = search_place_id;
2610  IF result IS NULL AND rank > 14 THEN
2611    select count(*) from place_addressline where address_place_id = search_place_id and isaddress = true into numfeatures;
2612    insert into place_boundingbox select place_id,
2613             ST_Y(ST_PointN(ExteriorRing(ST_Box2D(area)),4)),ST_Y(ST_PointN(ExteriorRing(ST_Box2D(area)),2)),
2614             ST_X(ST_PointN(ExteriorRing(ST_Box2D(area)),1)),ST_X(ST_PointN(ExteriorRing(ST_Box2D(area)),3)),
2615             numfeatures, ST_Area(area),
2616             area from location_area where place_id = search_place_id;
2617    select * from place_boundingbox into result where place_id = search_place_id;
2618  END IF;
2619  IF result IS NULL THEN
2620    select rank_search from placex where place_id = search_place_id into rank;
2621    IF rank > 20 THEN
2622-- TODO 0.0001
2623      insert into place_boundingbox select address_place_id,
2624             min(ST_Y(ST_Centroid(geometry))) as minlon,max(ST_Y(ST_Centroid(geometry))) as maxlon,
2625             min(ST_X(ST_Centroid(geometry))) as minlat,max(ST_X(ST_Centroid(geometry))) as maxlat,
2626             count(*), ST_Area(ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001)) as area,
2627             ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001) as boundary
2628             from place_addressline join placex using (place_id) 
2629             where address_place_id = search_place_id
2630               and (isaddress = true OR place_id = search_place_id)
2631               and (st_length(geometry) < 0.01 or place_id = search_place_id)
2632             group by address_place_id limit 1;
2633      select * from place_boundingbox into result where place_id = search_place_id;
2634    END IF;
2635  END IF;
2636  return result;
2637END;
2638$$
2639LANGUAGE plpgsql;
2640
2641CREATE OR REPLACE FUNCTION update_place(search_place_id INTEGER) RETURNS BOOLEAN
2642  AS $$
2643DECLARE
2644  result place_boundingbox;
2645  numfeatures integer;
2646BEGIN
2647  update placex set indexed = false where place_id = search_place_id and indexed = true;
2648  update placex set indexed = true where place_id = search_place_id and indexed = false;
2649  return true;
2650END;
2651$$
2652LANGUAGE plpgsql;
2653
2654CREATE OR REPLACE FUNCTION get_searchrank_label(rank INTEGER) RETURNS TEXT
2655  AS $$
2656DECLARE
2657BEGIN
2658  IF rank < 2 THEN
2659    RETURN 'Continent';
2660  ELSEIF rank < 4 THEN
2661    RETURN 'Sea';
2662  ELSEIF rank < 8 THEN
2663    RETURN 'Country';
2664  ELSEIF rank < 12 THEN
2665    RETURN 'State';
2666  ELSEIF rank < 16 THEN
2667    RETURN 'County';
2668  ELSEIF rank = 16 THEN
2669    RETURN 'City';
2670  ELSEIF rank = 17 THEN
2671    RETURN 'Town / Island';
2672  ELSEIF rank = 18 THEN
2673    RETURN 'Village / Hamlet';
2674  ELSEIF rank = 20 THEN
2675    RETURN 'Suburb';
2676  ELSEIF rank = 21 THEN
2677    RETURN 'Postcode Area';
2678  ELSEIF rank = 22 THEN
2679    RETURN 'Croft / Farm / Locality / Islet';
2680  ELSEIF rank = 23 THEN
2681    RETURN 'Postcode Area';
2682  ELSEIF rank = 25 THEN
2683    RETURN 'Postcode Point';
2684  ELSEIF rank = 26 THEN
2685    RETURN 'Street / Major Landmark';
2686  ELSEIF rank = 27 THEN
2687    RETURN 'Minory Street / Path';
2688  ELSEIF rank = 28 THEN
2689    RETURN 'House / Building';
2690  ELSE
2691    RETURN 'Other: '||rank;
2692  END IF;
2693 
2694END;
2695$$
2696LANGUAGE plpgsql;
2697
2698CREATE OR REPLACE FUNCTION get_addressrank_label(rank INTEGER) RETURNS TEXT
2699  AS $$
2700DECLARE
2701BEGIN
2702  IF rank = 0 THEN
2703    RETURN 'None';
2704  ELSEIF rank < 2 THEN
2705    RETURN 'Continent';
2706  ELSEIF rank < 4 THEN
2707    RETURN 'Sea';
2708  ELSEIF rank = 5 THEN
2709    RETURN 'Postcode';
2710  ELSEIF rank < 8 THEN
2711    RETURN 'Country';
2712  ELSEIF rank < 12 THEN
2713    RETURN 'State';
2714  ELSEIF rank < 16 THEN
2715    RETURN 'County';
2716  ELSEIF rank = 16 THEN
2717    RETURN 'City';
2718  ELSEIF rank = 17 THEN
2719    RETURN 'Town / Village / Hamlet';
2720  ELSEIF rank = 20 THEN
2721    RETURN 'Suburb';
2722  ELSEIF rank = 21 THEN
2723    RETURN 'Postcode Area';
2724  ELSEIF rank = 22 THEN
2725    RETURN 'Croft / Farm / Locality / Islet';
2726  ELSEIF rank = 23 THEN
2727    RETURN 'Postcode Area';
2728  ELSEIF rank = 25 THEN
2729    RETURN 'Postcode Point';
2730  ELSEIF rank = 26 THEN
2731    RETURN 'Street / Major Landmark';
2732  ELSEIF rank = 27 THEN
2733    RETURN 'Minory Street / Path';
2734  ELSEIF rank = 28 THEN
2735    RETURN 'House / Building';
2736  ELSE
2737    RETURN 'Other: '||rank;
2738  END IF;
2739 
2740END;
2741$$
2742LANGUAGE plpgsql;
2743
2744CREATE OR REPLACE FUNCTION get_word_suggestion(srcword TEXT) RETURNS TEXT
2745  AS $$
2746DECLARE
2747  trigramtoken TEXT;
2748  result TEXT;
2749BEGIN
2750
2751  trigramtoken := regexp_replace(make_standard_name(srcword),E'([^0-9])\\1+',E'\\1','g');
2752  SELECT word FROM word WHERE word_trigram like ' %' and word_trigram % trigramtoken ORDER BY similarity(word_trigram, trigramtoken) DESC, word limit 1 into result;
2753
2754  return result;
2755END;
2756$$
2757LANGUAGE plpgsql;
2758
2759CREATE OR REPLACE FUNCTION get_word_suggestions(srcword TEXT) RETURNS TEXT[]
2760  AS $$
2761DECLARE
2762  trigramtoken TEXT;
2763  result TEXT[];
2764  r RECORD;
2765BEGIN
2766
2767  trigramtoken := regexp_replace(make_standard_name(srcword),E'([^0-9])\\1+',E'\\1','g');
2768
2769  FOR r IN SELECT word,similarity(word_trigram, trigramtoken) as score FROM word
2770    WHERE word_trigram like ' %' and word_trigram % trigramtoken ORDER BY similarity(word_trigram, trigramtoken) DESC, word limit 4
2771  LOOP
2772    result[coalesce(array_upper(result,1)+1,1)] := r.word;
2773  END LOOP;
2774
2775  return result;
2776END;
2777$$
2778LANGUAGE plpgsql;
Note: See TracBrowser for help on using the repository browser.