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