]> git.openstreetmap.org Git - nominatim.git/blob - sql/functions/utils.sql
factor out searching for parent road for pois
[nominatim.git] / sql / functions / utils.sql
1 -- Assorted helper functions for the triggers.
2
3 CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place geometry)
4   RETURNS INTEGER
5   AS $$
6 DECLARE
7   NEWgeometry geometry;
8 BEGIN
9 --  RAISE WARNING '%',place;
10   NEWgeometry := ST_PointOnSurface(place);
11   RETURN (partition*1000000) + (500-ST_X(NEWgeometry)::integer)*1000 + (500-ST_Y(NEWgeometry)::integer);
12 END;
13 $$
14 LANGUAGE plpgsql IMMUTABLE;
15
16
17 CREATE OR REPLACE FUNCTION array_merge(a INTEGER[], b INTEGER[])
18   RETURNS INTEGER[]
19   AS $$
20 DECLARE
21   i INTEGER;
22   r INTEGER[];
23 BEGIN
24   IF array_upper(a, 1) IS NULL THEN
25     RETURN b;
26   END IF;
27   IF array_upper(b, 1) IS NULL THEN
28     RETURN a;
29   END IF;
30   r := a;
31   FOR i IN 1..array_upper(b, 1) LOOP  
32     IF NOT (ARRAY[b[i]] <@ r) THEN
33       r := r || b[i];
34     END IF;
35   END LOOP;
36   RETURN r;
37 END;
38 $$
39 LANGUAGE plpgsql IMMUTABLE;
40
41
42 CREATE OR REPLACE FUNCTION reverse_place_diameter(rank_search SMALLINT)
43   RETURNS FLOAT
44   AS $$
45 BEGIN
46   IF rank_search <= 4 THEN
47     RETURN 5.0;
48   ELSIF rank_search <= 8 THEN
49     RETURN 1.8;
50   ELSIF rank_search <= 12 THEN
51     RETURN 0.6;
52   ELSIF rank_search <= 17 THEN
53     RETURN 0.16;
54   ELSIF rank_search <= 18 THEN
55     RETURN 0.08;
56   ELSIF rank_search <= 19 THEN
57     RETURN 0.04;
58   END IF;
59
60   RETURN 0.02;
61 END;
62 $$
63 LANGUAGE plpgsql IMMUTABLE;
64
65
66 CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT,
67                                              OUT rank_search SMALLINT,
68                                              OUT rank_address SMALLINT)
69 AS $$
70 DECLARE
71   part TEXT;
72 BEGIN
73     rank_search := 30;
74     rank_address := 30;
75     postcode := upper(postcode);
76
77     IF country_code = 'gb' THEN
78         IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
79             rank_search := 25;
80             rank_address := 5;
81         ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
82             rank_search := 23;
83             rank_address := 5;
84         ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
85             rank_search := 21;
86             rank_address := 5;
87         END IF;
88
89     ELSEIF country_code = 'sg' THEN
90         IF postcode ~ '^([0-9]{6})$' THEN
91             rank_search := 25;
92             rank_address := 11;
93         END IF;
94
95     ELSEIF country_code = 'de' THEN
96         IF postcode ~ '^([0-9]{5})$' THEN
97             rank_search := 21;
98             rank_address := 11;
99         END IF;
100
101     ELSE
102         -- Guess at the postcode format and coverage (!)
103         IF postcode ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
104             rank_search := 21;
105             rank_address := 11;
106         ELSE
107             -- Does it look splitable into and area and local code?
108             part := substring(postcode from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
109
110             IF part IS NOT NULL THEN
111                 rank_search := 25;
112                 rank_address := 11;
113             ELSEIF postcode ~ '^[- :A-Z0-9]{6,}$' THEN
114                 rank_search := 21;
115                 rank_address := 11;
116             END IF;
117         END IF;
118     END IF;
119
120 END;
121 $$
122 LANGUAGE plpgsql IMMUTABLE;
123
124
125 -- Find the nearest artificial postcode for the given geometry.
126 -- TODO For areas there should not be more than two inside the geometry.
127 CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY)
128   RETURNS TEXT
129   AS $$
130 DECLARE
131   outcode TEXT;
132   cnt INTEGER;
133 BEGIN
134     -- If the geometry is an area then only one postcode must be within
135     -- that area, otherwise consider the area as not having a postcode.
136     IF ST_GeometryType(geom) in ('ST_Polygon','ST_MultiPolygon') THEN
137         SELECT min(postcode), count(*) FROM
138               (SELECT postcode FROM location_postcode
139                 WHERE ST_Contains(geom, location_postcode.geometry) LIMIT 2) sub
140           INTO outcode, cnt;
141
142         IF cnt = 1 THEN
143             RETURN outcode;
144         ELSE
145             RETURN null;
146         END IF;
147     END IF;
148
149     SELECT postcode FROM location_postcode
150      WHERE ST_DWithin(geom, location_postcode.geometry, 0.05)
151           AND location_postcode.country_code = country
152      ORDER BY ST_Distance(geom, location_postcode.geometry) LIMIT 1
153     INTO outcode;
154
155     RETURN outcode;
156 END;
157 $$
158 LANGUAGE plpgsql STABLE;
159
160
161 CREATE OR REPLACE FUNCTION get_country_code(place geometry)
162   RETURNS TEXT
163   AS $$
164 DECLARE
165   place_centre GEOMETRY;
166   nearcountry RECORD;
167 BEGIN
168   place_centre := ST_PointOnSurface(place);
169
170 -- RAISE WARNING 'get_country_code, start: %', ST_AsText(place_centre);
171
172   -- Try for a OSM polygon
173   FOR nearcountry IN
174     SELECT country_code from location_area_country
175     WHERE country_code is not null and st_covers(geometry, place_centre) limit 1
176   LOOP
177     RETURN nearcountry.country_code;
178   END LOOP;
179
180 -- RAISE WARNING 'osm fallback: %', ST_AsText(place_centre);
181
182   -- Try for OSM fallback data
183   -- The order is to deal with places like HongKong that are 'states' within another polygon
184   FOR nearcountry IN
185     SELECT country_code from country_osm_grid
186     WHERE st_covers(geometry, place_centre) order by area asc limit 1
187   LOOP
188     RETURN nearcountry.country_code;
189   END LOOP;
190
191 -- RAISE WARNING 'near osm fallback: %', ST_AsText(place_centre);
192
193   -- 
194   FOR nearcountry IN
195     SELECT country_code from country_osm_grid
196     WHERE st_dwithin(geometry, place_centre, 0.5)
197     ORDER BY st_distance(geometry, place_centre) asc, area asc limit 1
198   LOOP
199     RETURN nearcountry.country_code;
200   END LOOP;
201
202   RETURN NULL;
203 END;
204 $$
205 LANGUAGE plpgsql STABLE;
206
207
208 CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR(2))
209   RETURNS TEXT
210   AS $$
211 DECLARE
212   nearcountry RECORD;
213 BEGIN
214   FOR nearcountry IN
215     SELECT distinct country_default_language_code from country_name
216     WHERE country_code = search_country_code limit 1
217   LOOP
218     RETURN lower(nearcountry.country_default_language_code);
219   END LOOP;
220   RETURN NULL;
221 END;
222 $$
223 LANGUAGE plpgsql STABLE;
224
225
226 CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10))
227   RETURNS INTEGER
228   AS $$
229 DECLARE
230   nearcountry RECORD;
231 BEGIN
232   FOR nearcountry IN
233     SELECT partition from country_name where country_code = in_country_code
234   LOOP
235     RETURN nearcountry.partition;
236   END LOOP;
237   RETURN 0;
238 END;
239 $$
240 LANGUAGE plpgsql STABLE;
241
242
243 CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT)
244   RETURNS BOOLEAN
245   AS $$
246 DECLARE
247 BEGIN
248   DELETE FROM location_area where place_id = OLD_place_id;
249 -- TODO:location_area
250   RETURN true;
251 END;
252 $$
253 LANGUAGE plpgsql;
254
255
256 CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2),
257                                         partition INTEGER, keywords INTEGER[],
258                                         rank_search INTEGER, rank_address INTEGER,
259                                         in_postcode TEXT, geometry GEOMETRY)
260   RETURNS BOOLEAN
261   AS $$
262 DECLARE
263   locationid INTEGER;
264   centroid GEOMETRY;
265   diameter FLOAT;
266   x BOOLEAN;
267   splitGeom RECORD;
268   secgeo GEOMETRY;
269   postcode TEXT;
270 BEGIN
271
272   IF rank_search > 25 THEN
273     RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
274   END IF;
275
276   x := deleteLocationArea(partition, place_id, rank_search);
277
278   -- add postcode only if it contains a single entry, i.e. ignore postcode lists
279   postcode := NULL;
280   IF in_postcode is not null AND in_postcode not similar to '%(,|;)%' THEN
281       postcode := upper(trim (in_postcode));
282   END IF;
283
284   IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
285     centroid := ST_Centroid(geometry);
286
287     FOR secgeo IN select split_geometry(geometry) AS geom LOOP
288       x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
289     END LOOP;
290
291   ELSE
292
293     diameter := 0.02;
294     IF rank_address = 0 THEN
295       diameter := 0.02;
296     ELSEIF rank_search <= 14 THEN
297       diameter := 1.2;
298     ELSEIF rank_search <= 15 THEN
299       diameter := 1;
300     ELSEIF rank_search <= 16 THEN
301       diameter := 0.5;
302     ELSEIF rank_search <= 17 THEN
303       diameter := 0.2;
304     ELSEIF rank_search <= 21 THEN
305       diameter := 0.05;
306     ELSEIF rank_search = 25 THEN
307       diameter := 0.005;
308     END IF;
309
310 --    RAISE WARNING 'adding % diameter %', place_id, diameter;
311
312     secgeo := ST_Buffer(geometry, diameter);
313     x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, ST_Centroid(geometry), secgeo);
314
315   END IF;
316
317   RETURN true;
318 END;
319 $$
320 LANGUAGE plpgsql;
321
322
323 CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT,
324                                                maxdepth INTEGER)
325   RETURNS SETOF GEOMETRY
326   AS $$
327 DECLARE
328   xmin FLOAT;
329   ymin FLOAT;
330   xmax FLOAT;
331   ymax FLOAT;
332   xmid FLOAT;
333   ymid FLOAT;
334   secgeo GEOMETRY;
335   secbox GEOMETRY;
336   seg INTEGER;
337   geo RECORD;
338   area FLOAT;
339   remainingdepth INTEGER;
340   added INTEGER;
341 BEGIN
342
343 --  RAISE WARNING 'quad_split_geometry: maxarea=%, depth=%',maxarea,maxdepth;
344
345   IF (ST_GeometryType(geometry) not in ('ST_Polygon','ST_MultiPolygon') OR NOT ST_IsValid(geometry)) THEN
346     RETURN NEXT geometry;
347     RETURN;
348   END IF;
349
350   remainingdepth := maxdepth - 1;
351   area := ST_AREA(geometry);
352   IF remainingdepth < 1 OR area < maxarea THEN
353     RETURN NEXT geometry;
354     RETURN;
355   END IF;
356
357   xmin := st_xmin(geometry);
358   xmax := st_xmax(geometry);
359   ymin := st_ymin(geometry);
360   ymax := st_ymax(geometry);
361   secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(ymin,xmin),ST_Point(ymax,xmax)),4326);
362
363   -- if the geometry completely covers the box don't bother to slice any more
364   IF ST_AREA(secbox) = area THEN
365     RETURN NEXT geometry;
366     RETURN;
367   END IF;
368
369   xmid := (xmin+xmax)/2;
370   ymid := (ymin+ymax)/2;
371
372   added := 0;
373   FOR seg IN 1..4 LOOP
374
375     IF seg = 1 THEN
376       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymin),ST_Point(xmid,ymid)),4326);
377     END IF;
378     IF seg = 2 THEN
379       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymid),ST_Point(xmid,ymax)),4326);
380     END IF;
381     IF seg = 3 THEN
382       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymin),ST_Point(xmax,ymid)),4326);
383     END IF;
384     IF seg = 4 THEN
385       secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymid),ST_Point(xmax,ymax)),4326);
386     END IF;
387
388     IF st_intersects(geometry, secbox) THEN
389       secgeo := st_intersection(geometry, secbox);
390       IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN
391         FOR geo IN select quad_split_geometry(secgeo, maxarea, remainingdepth) as geom LOOP
392           IF NOT ST_IsEmpty(geo.geom) AND ST_GeometryType(geo.geom) in ('ST_Polygon','ST_MultiPolygon') THEN
393             added := added + 1;
394             RETURN NEXT geo.geom;
395           END IF;
396         END LOOP;
397       END IF;
398     END IF;
399   END LOOP;
400
401   RETURN;
402 END;
403 $$
404 LANGUAGE plpgsql IMMUTABLE;
405
406
407 CREATE OR REPLACE FUNCTION split_geometry(geometry GEOMETRY)
408   RETURNS SETOF GEOMETRY
409   AS $$
410 DECLARE
411   geo RECORD;
412 BEGIN
413   -- 10000000000 is ~~ 1x1 degree
414   FOR geo IN select quad_split_geometry(geometry, 0.25, 20) as geom LOOP
415     RETURN NEXT geo.geom;
416   END LOOP;
417   RETURN;
418 END;
419 $$
420 LANGUAGE plpgsql IMMUTABLE;
421
422
423 CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT)
424   RETURNS BOOLEAN
425   AS $$
426 DECLARE
427     osmid BIGINT;
428     osmtype character(1);
429     pclass text;
430     ptype text;
431 BEGIN
432   SELECT osm_type, osm_id, class, type FROM placex WHERE place_id = placeid INTO osmtype, osmid, pclass, ptype;
433   DELETE FROM import_polygon_delete where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
434   DELETE FROM import_polygon_error where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
435   -- force delete from place/placex by making it a very small geometry
436   UPDATE place set geometry = ST_SetSRID(ST_Point(0,0), 4326) where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
437   DELETE FROM place where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
438
439   RETURN TRUE;
440 END;
441 $$
442 LANGUAGE plpgsql;
443
444
445 CREATE OR REPLACE FUNCTION place_force_update(placeid BIGINT)
446   RETURNS BOOLEAN
447   AS $$
448 DECLARE
449   placegeom GEOMETRY;
450   geom GEOMETRY;
451   diameter FLOAT;
452   rank INTEGER;
453 BEGIN
454   UPDATE placex SET indexed_status = 2 WHERE place_id = placeid;
455   SELECT geometry, rank_search FROM placex WHERE place_id = placeid INTO placegeom, rank;
456   IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN
457     IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
458       FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
459         update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) 
460         AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place'));
461         update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) 
462         AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place'));
463       END LOOP;
464     ELSE
465         diameter := 0;
466         IF rank = 11 THEN
467           diameter := 0.05;
468         ELSEIF rank < 18 THEN
469           diameter := 0.1;
470         ELSEIF rank < 20 THEN
471           diameter := 0.05;
472         ELSEIF rank = 21 THEN
473           diameter := 0.001;
474         ELSEIF rank < 24 THEN
475           diameter := 0.02;
476         ELSEIF rank < 26 THEN
477           diameter := 0.002; -- 100 to 200 meters
478         ELSEIF rank < 28 THEN
479           diameter := 0.001; -- 50 to 100 meters
480         END IF;
481         IF diameter > 0 THEN
482           IF rank >= 26 THEN
483             -- roads may cause reparenting for >27 rank places
484             update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter);
485           ELSEIF rank >= 16 THEN
486             -- up to rank 16, street-less addresses may need reparenting
487             update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null or address ? 'place');
488           ELSE
489             -- for all other places the search terms may change as well
490             update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null);
491           END IF;
492         END IF;
493     END IF;
494     RETURN TRUE;
495   END IF;
496
497   RETURN FALSE;
498 END;
499 $$
500 LANGUAGE plpgsql;