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