1 -- Assorted helper functions for the triggers.
3 CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place geometry)
9 -- RAISE WARNING '%',place;
10 NEWgeometry := ST_PointOnSurface(place);
11 RETURN (partition*1000000) + (500-ST_X(NEWgeometry)::integer)*1000 + (500-ST_Y(NEWgeometry)::integer);
14 LANGUAGE plpgsql IMMUTABLE;
17 CREATE OR REPLACE FUNCTION array_merge(a INTEGER[], b INTEGER[])
24 IF array_upper(a, 1) IS NULL THEN
27 IF array_upper(b, 1) IS NULL THEN
31 FOR i IN 1..array_upper(b, 1) LOOP
32 IF NOT (ARRAY[b[i]] <@ r) THEN
39 LANGUAGE plpgsql IMMUTABLE;
41 -- Return the node members with a given label from a relation member list
44 -- \param members Member list in osm2pgsql middle format.
45 -- \param memberLabels Array of labels to accept.
47 -- \returns Set of OSM ids of nodes that are found.
49 CREATE OR REPLACE FUNCTION get_rel_node_members(members TEXT[],
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'
60 RETURN NEXT substring(members[i], 2)::bigint;
67 LANGUAGE plpgsql IMMUTABLE;
69 -- Copy 'name' to or from the default language.
71 -- \param country_code Country code of the object being named.
72 -- \param[inout] name List of names of the object.
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),
81 default_language VARCHAR(10);
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)));
95 LANGUAGE plpgsql IMMUTABLE;
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)
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
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
131 LANGUAGE plpgsql STABLE;
134 CREATE OR REPLACE FUNCTION get_country_code(place geometry)
138 place_centre GEOMETRY;
141 place_centre := ST_PointOnSurface(place);
143 -- RAISE WARNING 'get_country_code, start: %', ST_AsText(place_centre);
145 -- Try for a OSM polygon
147 SELECT country_code from location_area_country
148 WHERE country_code is not null and st_covers(geometry, place_centre) limit 1
150 RETURN nearcountry.country_code;
153 -- RAISE WARNING 'osm fallback: %', ST_AsText(place_centre);
155 -- Try for OSM fallback data
156 -- The order is to deal with places like HongKong that are 'states' within another polygon
158 SELECT country_code from country_osm_grid
159 WHERE st_covers(geometry, place_centre) order by area asc limit 1
161 RETURN nearcountry.country_code;
164 -- RAISE WARNING 'near osm fallback: %', ST_AsText(place_centre);
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
172 RETURN nearcountry.country_code;
178 LANGUAGE plpgsql STABLE;
181 CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR(2))
188 SELECT distinct country_default_language_code from country_name
189 WHERE country_code = search_country_code limit 1
191 RETURN lower(nearcountry.country_default_language_code);
196 LANGUAGE plpgsql STABLE;
199 CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10))
206 SELECT partition from country_name where country_code = in_country_code
208 RETURN nearcountry.partition;
213 LANGUAGE plpgsql STABLE;
216 -- Find the parent of an address with addr:street/addr:place tag.
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.
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,
230 parent_place_id BIGINT;
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;
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;
261 LANGUAGE plpgsql STABLE;
263 CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT)
268 DELETE FROM location_area where place_id = OLD_place_id;
269 -- TODO:location_area
275 -- Create a bounding box with an extent computed from the radius (in meters)
276 -- which in turn is derived from the given search rank.
277 CREATE OR REPLACE FUNCTION place_node_fuzzy_area(geom GEOMETRY, rank_search INTEGER)
283 IF rank_search <= 16 THEN -- city
285 ELSIF rank_search <= 18 THEN -- town
287 ELSIF rank_search <= 19 THEN -- village
289 ELSIF rank_search <= 20 THEN -- hamlet
293 RETURN ST_Envelope(ST_Collect(
294 ST_Project(geom, radius, 0.785398)::geometry,
295 ST_Project(geom, radius, 3.9269908)::geometry));
298 LANGUAGE plpgsql IMMUTABLE;
301 CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2),
302 partition INTEGER, keywords INTEGER[],
303 rank_search INTEGER, rank_address INTEGER,
304 in_postcode TEXT, geometry GEOMETRY)
313 PERFORM deleteLocationArea(partition, place_id, rank_search);
315 -- add postcode only if it contains a single entry, i.e. ignore postcode lists
317 IF in_postcode is not null AND in_postcode not similar to '%(,|;)%' THEN
318 postcode := upper(trim (in_postcode));
321 IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
322 centroid := ST_Centroid(geometry);
324 FOR secgeo IN select split_geometry(geometry) AS geom LOOP
325 PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
328 ELSEIF ST_GeometryType(geometry) = 'ST_Point' THEN
329 secgeo := place_node_fuzzy_area(geometry, rank_search);
330 PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, geometry, secgeo);
340 CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT,
342 RETURNS SETOF GEOMETRY
356 remainingdepth INTEGER;
360 -- RAISE WARNING 'quad_split_geometry: maxarea=%, depth=%',maxarea,maxdepth;
362 IF (ST_GeometryType(geometry) not in ('ST_Polygon','ST_MultiPolygon') OR NOT ST_IsValid(geometry)) THEN
363 RETURN NEXT geometry;
367 remainingdepth := maxdepth - 1;
368 area := ST_AREA(geometry);
369 IF remainingdepth < 1 OR area < maxarea THEN
370 RETURN NEXT geometry;
374 xmin := st_xmin(geometry);
375 xmax := st_xmax(geometry);
376 ymin := st_ymin(geometry);
377 ymax := st_ymax(geometry);
378 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(ymin,xmin),ST_Point(ymax,xmax)),4326);
380 -- if the geometry completely covers the box don't bother to slice any more
381 IF ST_AREA(secbox) = area THEN
382 RETURN NEXT geometry;
386 xmid := (xmin+xmax)/2;
387 ymid := (ymin+ymax)/2;
393 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymin),ST_Point(xmid,ymid)),4326);
396 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymid),ST_Point(xmid,ymax)),4326);
399 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymin),ST_Point(xmax,ymid)),4326);
402 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymid),ST_Point(xmax,ymax)),4326);
405 IF st_intersects(geometry, secbox) THEN
406 secgeo := st_intersection(geometry, secbox);
407 IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN
408 FOR geo IN select quad_split_geometry(secgeo, maxarea, remainingdepth) as geom LOOP
409 IF NOT ST_IsEmpty(geo.geom) AND ST_GeometryType(geo.geom) in ('ST_Polygon','ST_MultiPolygon') THEN
411 RETURN NEXT geo.geom;
421 LANGUAGE plpgsql IMMUTABLE;
424 CREATE OR REPLACE FUNCTION split_geometry(geometry GEOMETRY)
425 RETURNS SETOF GEOMETRY
430 -- 10000000000 is ~~ 1x1 degree
431 FOR geo IN select quad_split_geometry(geometry, 0.01, 20) as geom LOOP
432 RETURN NEXT geo.geom;
437 LANGUAGE plpgsql IMMUTABLE;
440 CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT)
445 osmtype character(1);
449 SELECT osm_type, osm_id, class, type FROM placex WHERE place_id = placeid INTO osmtype, osmid, pclass, ptype;
450 DELETE FROM import_polygon_delete where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
451 DELETE FROM import_polygon_error where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
452 -- force delete from place/placex by making it a very small geometry
453 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;
454 DELETE FROM place where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
462 CREATE OR REPLACE FUNCTION place_force_update(placeid BIGINT)
471 UPDATE placex SET indexed_status = 2 WHERE place_id = placeid;
473 SELECT geometry, rank_address INTO placegeom, rank
474 FROM placex WHERE place_id = placeid;
476 IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN
477 IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon')
480 FOR geom IN SELECT split_geometry(placegeom) LOOP
481 UPDATE placex SET indexed_status = 2
482 WHERE ST_Intersects(geom, placex.geometry)
483 and indexed_status = 0
484 and ((rank_address = 0 and rank_search > rank) or rank_address > rank)
485 and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place'));
488 diameter := update_place_diameter(rank);
491 -- roads may cause reparenting for >27 rank places
492 update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter);
493 ELSEIF rank >= 16 THEN
494 -- up to rank 16, street-less addresses may need reparenting
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 or address ? 'place');
497 -- for all other places the search terms may change as well
498 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);