1 CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place geometry) RETURNS INTEGER
6 -- RAISE WARNING '%',place;
7 NEWgeometry := ST_PointOnSurface(place);
8 RETURN (partition*1000000) + (500-ST_X(NEWgeometry)::integer)*1000 + (500-ST_Y(NEWgeometry)::integer);
11 LANGUAGE plpgsql IMMUTABLE;
14 CREATE OR REPLACE FUNCTION array_merge(a INTEGER[], b INTEGER[])
21 IF array_upper(a, 1) IS NULL THEN
24 IF array_upper(b, 1) IS NULL THEN
28 FOR i IN 1..array_upper(b, 1) LOOP
29 IF NOT (ARRAY[b[i]] <@ r) THEN
36 LANGUAGE plpgsql IMMUTABLE;
38 CREATE OR REPLACE FUNCTION reverse_place_diameter(rank_search SMALLINT)
42 IF rank_search <= 4 THEN
44 ELSIF rank_search <= 8 THEN
46 ELSIF rank_search <= 12 THEN
48 ELSIF rank_search <= 17 THEN
50 ELSIF rank_search <= 18 THEN
52 ELSIF rank_search <= 19 THEN
59 LANGUAGE plpgsql IMMUTABLE;
61 CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT,
62 OUT rank_search SMALLINT, OUT rank_address SMALLINT)
69 postcode := upper(postcode);
71 IF country_code = 'gb' THEN
72 IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
75 ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
78 ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
83 ELSEIF country_code = 'sg' THEN
84 IF postcode ~ '^([0-9]{6})$' THEN
89 ELSEIF country_code = 'de' THEN
90 IF postcode ~ '^([0-9]{5})$' THEN
96 -- Guess at the postcode format and coverage (!)
97 IF postcode ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
101 -- Does it look splitable into and area and local code?
102 part := substring(postcode from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
104 IF part IS NOT NULL THEN
107 ELSEIF postcode ~ '^[- :A-Z0-9]{6,}$' THEN
116 LANGUAGE plpgsql IMMUTABLE;
118 -- Find the nearest artificial postcode for the given geometry.
119 -- TODO For areas there should not be more than two inside the geometry.
120 CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY) RETURNS TEXT
126 -- If the geometry is an area then only one postcode must be within
127 -- that area, otherwise consider the area as not having a postcode.
128 IF ST_GeometryType(geom) in ('ST_Polygon','ST_MultiPolygon') THEN
129 SELECT min(postcode), count(*) FROM
130 (SELECT postcode FROM location_postcode
131 WHERE ST_Contains(geom, location_postcode.geometry) LIMIT 2) sub
141 SELECT postcode FROM location_postcode
142 WHERE ST_DWithin(geom, location_postcode.geometry, 0.05)
143 AND location_postcode.country_code = country
144 ORDER BY ST_Distance(geom, location_postcode.geometry) LIMIT 1
153 CREATE OR REPLACE FUNCTION get_country_code(place geometry) RETURNS TEXT
156 place_centre GEOMETRY;
159 place_centre := ST_PointOnSurface(place);
161 -- RAISE WARNING 'get_country_code, start: %', ST_AsText(place_centre);
163 -- Try for a OSM polygon
164 FOR nearcountry IN select country_code from location_area_country where country_code is not null and st_covers(geometry, place_centre) limit 1
166 RETURN nearcountry.country_code;
169 -- RAISE WARNING 'osm fallback: %', ST_AsText(place_centre);
171 -- Try for OSM fallback data
172 -- The order is to deal with places like HongKong that are 'states' within another polygon
173 FOR nearcountry IN select country_code from country_osm_grid where st_covers(geometry, place_centre) order by area asc limit 1
175 RETURN nearcountry.country_code;
178 -- RAISE WARNING 'near osm fallback: %', ST_AsText(place_centre);
181 FOR nearcountry IN select country_code from country_osm_grid where st_dwithin(geometry, place_centre, 0.5) order by st_distance(geometry, place_centre) asc, area asc limit 1
183 RETURN nearcountry.country_code;
189 LANGUAGE plpgsql IMMUTABLE;
191 CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR(2)) RETURNS TEXT
196 FOR nearcountry IN select distinct country_default_language_code from country_name where country_code = search_country_code limit 1
198 RETURN lower(nearcountry.country_default_language_code);
203 LANGUAGE plpgsql IMMUTABLE;
205 CREATE OR REPLACE FUNCTION get_country_language_codes(search_country_code VARCHAR(2)) RETURNS TEXT[]
210 FOR nearcountry IN select country_default_language_codes from country_name where country_code = search_country_code limit 1
212 RETURN lower(nearcountry.country_default_language_codes);
217 LANGUAGE plpgsql IMMUTABLE;
219 CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10)) RETURNS INTEGER
224 FOR nearcountry IN select partition from country_name where country_code = in_country_code
226 RETURN nearcountry.partition;
231 LANGUAGE plpgsql IMMUTABLE;
233 CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT) RETURNS BOOLEAN
237 DELETE FROM location_area where place_id = OLD_place_id;
238 -- TODO:location_area
244 CREATE OR REPLACE FUNCTION add_location(
246 country_code varchar(2),
250 rank_address INTEGER,
266 IF rank_search > 25 THEN
267 RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
270 x := deleteLocationArea(partition, place_id, rank_search);
272 -- add postcode only if it contains a single entry, i.e. ignore postcode lists
274 IF in_postcode is not null AND in_postcode not similar to '%(,|;)%' THEN
275 postcode := upper(trim (in_postcode));
278 IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
279 centroid := ST_Centroid(geometry);
281 FOR secgeo IN select split_geometry(geometry) AS geom LOOP
282 x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
288 IF rank_address = 0 THEN
290 ELSEIF rank_search <= 14 THEN
292 ELSEIF rank_search <= 15 THEN
294 ELSEIF rank_search <= 16 THEN
296 ELSEIF rank_search <= 17 THEN
298 ELSEIF rank_search <= 21 THEN
300 ELSEIF rank_search = 25 THEN
304 -- RAISE WARNING 'adding % diameter %', place_id, diameter;
306 secgeo := ST_Buffer(geometry, diameter);
307 x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, ST_Centroid(geometry), secgeo);
316 -- Trigger for updates of location_postcode
318 -- Computes the parent object the postcode most likely refers to.
319 -- This will be the place that determines the address displayed when
320 -- searching for this postcode.
321 CREATE OR REPLACE FUNCTION postcode_update() RETURNS
328 IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
332 NEW.indexed_date = now();
334 partition := get_partition(NEW.country_code);
336 SELECT * FROM get_postcode_rank(NEW.country_code, NEW.postcode)
337 INTO NEW.rank_search, NEW.rank_address;
339 NEW.parent_place_id = 0;
342 FROM getNearFeatures(partition, NEW.geometry, NEW.rank_search, '{}'::int[])
343 WHERE NOT isguess ORDER BY rank_address DESC LIMIT 1
345 NEW.parent_place_id = location.place_id;
354 CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], member TEXT) RETURNS TEXT[]
361 FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
362 IF members[i+1] = member THEN
363 result := result || members[i];
372 CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], memberLabels TEXT[]) RETURNS SETOF TEXT
378 FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
379 IF members[i+1] = ANY(memberLabels) THEN
380 RETURN NEXT members[i];
389 CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT, maxdepth INTEGER)
390 RETURNS SETOF GEOMETRY
404 remainingdepth INTEGER;
409 -- RAISE WARNING 'quad_split_geometry: maxarea=%, depth=%',maxarea,maxdepth;
411 IF (ST_GeometryType(geometry) not in ('ST_Polygon','ST_MultiPolygon') OR NOT ST_IsValid(geometry)) THEN
412 RETURN NEXT geometry;
416 remainingdepth := maxdepth - 1;
417 area := ST_AREA(geometry);
418 IF remainingdepth < 1 OR area < maxarea THEN
419 RETURN NEXT geometry;
423 xmin := st_xmin(geometry);
424 xmax := st_xmax(geometry);
425 ymin := st_ymin(geometry);
426 ymax := st_ymax(geometry);
427 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(ymin,xmin),ST_Point(ymax,xmax)),4326);
429 -- if the geometry completely covers the box don't bother to slice any more
430 IF ST_AREA(secbox) = area THEN
431 RETURN NEXT geometry;
435 xmid := (xmin+xmax)/2;
436 ymid := (ymin+ymax)/2;
442 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymin),ST_Point(xmid,ymid)),4326);
445 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymid),ST_Point(xmid,ymax)),4326);
448 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymin),ST_Point(xmax,ymid)),4326);
451 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymid),ST_Point(xmax,ymax)),4326);
454 IF st_intersects(geometry, secbox) THEN
455 secgeo := st_intersection(geometry, secbox);
456 IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN
457 FOR geo IN select quad_split_geometry(secgeo, maxarea, remainingdepth) as geom LOOP
458 IF NOT ST_IsEmpty(geo.geom) AND ST_GeometryType(geo.geom) in ('ST_Polygon','ST_MultiPolygon') THEN
460 RETURN NEXT geo.geom;
472 CREATE OR REPLACE FUNCTION split_geometry(geometry GEOMETRY)
473 RETURNS SETOF GEOMETRY
478 -- 10000000000 is ~~ 1x1 degree
479 FOR geo IN select quad_split_geometry(geometry, 0.25, 20) as geom LOOP
480 RETURN NEXT geo.geom;
488 CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT) RETURNS BOOLEAN
492 osmtype character(1);
496 SELECT osm_type, osm_id, class, type FROM placex WHERE place_id = placeid INTO osmtype, osmid, pclass, ptype;
497 DELETE FROM import_polygon_delete where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
498 DELETE FROM import_polygon_error where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
499 -- force delete from place/placex by making it a very small geometry
500 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;
501 DELETE FROM place where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
508 CREATE OR REPLACE FUNCTION place_force_update(placeid BIGINT) RETURNS BOOLEAN
516 UPDATE placex SET indexed_status = 2 WHERE place_id = placeid;
517 SELECT geometry, rank_search FROM placex WHERE place_id = placeid INTO placegeom, rank;
518 IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN
519 IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
520 FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
521 update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
522 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'));
523 update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
524 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'));
530 ELSEIF rank < 18 THEN
532 ELSEIF rank < 20 THEN
534 ELSEIF rank = 21 THEN
536 ELSEIF rank < 24 THEN
538 ELSEIF rank < 26 THEN
539 diameter := 0.002; -- 100 to 200 meters
540 ELSEIF rank < 28 THEN
541 diameter := 0.001; -- 50 to 100 meters
545 -- roads may cause reparenting for >27 rank places
546 update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter);
547 ELSEIF rank >= 16 THEN
548 -- up to rank 16, street-less addresses may need reparenting
549 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');
551 -- for all other places the search terms may change as well
552 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);