1 -- Splits the line at the given point and returns the two parts
2 -- in a multilinestring.
3 CREATE OR REPLACE FUNCTION split_line_on_node(line GEOMETRY, point GEOMETRY)
7 RETURN ST_Split(ST_Snap(line, point, 0.0005), point);
13 CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place geometry) RETURNS INTEGER
18 -- RAISE WARNING '%',place;
19 NEWgeometry := ST_PointOnSurface(place);
20 RETURN (partition*1000000) + (500-ST_X(NEWgeometry)::integer)*1000 + (500-ST_Y(NEWgeometry)::integer);
23 LANGUAGE plpgsql IMMUTABLE;
25 CREATE OR REPLACE FUNCTION transliteration(text) RETURNS text
26 AS '{modulepath}/nominatim.so', 'transliteration'
27 LANGUAGE c IMMUTABLE STRICT;
29 CREATE OR REPLACE FUNCTION gettokenstring(text) RETURNS text
30 AS '{modulepath}/nominatim.so', 'gettokenstring'
31 LANGUAGE c IMMUTABLE STRICT;
33 CREATE OR REPLACE FUNCTION make_standard_name(name TEXT) RETURNS TEXT
38 o := public.gettokenstring(public.transliteration(name));
39 RETURN trim(substr(o,1,length(o)));
42 LANGUAGE 'plpgsql' IMMUTABLE;
44 -- returns NULL if the word is too common
45 CREATE OR REPLACE FUNCTION getorcreate_word_id(lookup_word TEXT)
50 return_word_id INTEGER;
53 lookup_token := trim(lookup_word);
54 SELECT min(word_id), max(search_name_count) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id, count;
55 IF return_word_id IS NULL THEN
56 return_word_id := nextval('seq_word');
57 INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, 0);
59 IF count > get_maxwordfreq() THEN
60 return_word_id := NULL;
63 RETURN return_word_id;
68 CREATE OR REPLACE FUNCTION getorcreate_housenumber_id(lookup_word TEXT)
73 return_word_id INTEGER;
75 lookup_token := ' '||trim(lookup_word);
76 SELECT min(word_id) FROM word WHERE word_token = lookup_token and class='place' and type='house' into return_word_id;
77 IF return_word_id IS NULL THEN
78 return_word_id := nextval('seq_word');
79 INSERT INTO word VALUES (return_word_id, lookup_token, null, 'place', 'house', null, 0);
81 RETURN return_word_id;
86 CREATE OR REPLACE FUNCTION getorcreate_postcode_id(postcode TEXT)
92 return_word_id INTEGER;
94 lookup_word := upper(trim(postcode));
95 lookup_token := ' ' || make_standard_name(lookup_word);
96 SELECT min(word_id) FROM word WHERE word_token = lookup_token and class='place' and type='postcode' into return_word_id;
97 IF return_word_id IS NULL THEN
98 return_word_id := nextval('seq_word');
99 INSERT INTO word VALUES (return_word_id, lookup_token, lookup_word, 'place', 'postcode', null, 0);
101 RETURN return_word_id;
106 CREATE OR REPLACE FUNCTION getorcreate_country(lookup_word TEXT, lookup_country_code varchar(2))
111 return_word_id INTEGER;
113 lookup_token := ' '||trim(lookup_word);
114 SELECT min(word_id) FROM word WHERE word_token = lookup_token and country_code=lookup_country_code into return_word_id;
115 IF return_word_id IS NULL THEN
116 return_word_id := nextval('seq_word');
117 INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, lookup_country_code, 0);
119 RETURN return_word_id;
124 CREATE OR REPLACE FUNCTION getorcreate_amenity(lookup_word TEXT, normalized_word TEXT, lookup_class text, lookup_type text)
129 return_word_id INTEGER;
131 lookup_token := ' '||trim(lookup_word);
132 SELECT min(word_id) FROM word WHERE word_token = lookup_token and word=normalized_word and class=lookup_class and type = lookup_type into return_word_id;
133 IF return_word_id IS NULL THEN
134 return_word_id := nextval('seq_word');
135 INSERT INTO word VALUES (return_word_id, lookup_token, normalized_word, lookup_class, lookup_type, null, 0);
137 RETURN return_word_id;
142 CREATE OR REPLACE FUNCTION getorcreate_amenityoperator(lookup_word TEXT, normalized_word TEXT, lookup_class text, lookup_type text, op text)
147 return_word_id INTEGER;
149 lookup_token := ' '||trim(lookup_word);
150 SELECT min(word_id) FROM word WHERE word_token = lookup_token and word=normalized_word and class=lookup_class and type = lookup_type and operator = op into return_word_id;
151 IF return_word_id IS NULL THEN
152 return_word_id := nextval('seq_word');
153 INSERT INTO word VALUES (return_word_id, lookup_token, normalized_word, lookup_class, lookup_type, null, 0, op);
155 RETURN return_word_id;
160 CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT, src_word TEXT)
165 nospace_lookup_token TEXT;
166 return_word_id INTEGER;
168 lookup_token := ' '||trim(lookup_word);
169 SELECT min(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id;
170 IF return_word_id IS NULL THEN
171 return_word_id := nextval('seq_word');
172 INSERT INTO word VALUES (return_word_id, lookup_token, src_word, null, null, null, 0);
173 -- nospace_lookup_token := replace(replace(lookup_token, '-',''), ' ','');
174 -- IF ' '||nospace_lookup_token != lookup_token THEN
175 -- INSERT INTO word VALUES (return_word_id, '-'||nospace_lookup_token, null, src_word, null, null, null, 0, null);
178 RETURN return_word_id;
183 CREATE OR REPLACE FUNCTION getorcreate_name_id(lookup_word TEXT)
188 RETURN getorcreate_name_id(lookup_word, '');
193 CREATE OR REPLACE FUNCTION get_word_id(lookup_word TEXT)
198 return_word_id INTEGER;
200 lookup_token := trim(lookup_word);
201 SELECT min(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id;
202 RETURN return_word_id;
205 LANGUAGE plpgsql IMMUTABLE;
207 CREATE OR REPLACE FUNCTION get_name_id(lookup_word TEXT)
212 return_word_id INTEGER;
214 lookup_token := ' '||trim(lookup_word);
215 SELECT min(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id;
216 RETURN return_word_id;
219 LANGUAGE plpgsql IMMUTABLE;
221 CREATE OR REPLACE FUNCTION get_name_ids(lookup_word TEXT)
226 return_word_ids INTEGER[];
228 lookup_token := ' '||trim(lookup_word);
229 SELECT array_agg(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_ids;
230 RETURN return_word_ids;
233 LANGUAGE plpgsql IMMUTABLE;
235 CREATE OR REPLACE FUNCTION array_merge(a INTEGER[], b INTEGER[])
242 IF array_upper(a, 1) IS NULL THEN
245 IF array_upper(b, 1) IS NULL THEN
249 FOR i IN 1..array_upper(b, 1) LOOP
250 IF NOT (ARRAY[b[i]] <@ r) THEN
257 LANGUAGE plpgsql IMMUTABLE;
259 CREATE OR REPLACE FUNCTION reverse_place_diameter(rank_search SMALLINT)
263 IF rank_search <= 4 THEN
265 ELSIF rank_search <= 8 THEN
267 ELSIF rank_search <= 12 THEN
269 ELSIF rank_search <= 17 THEN
271 ELSIF rank_search <= 18 THEN
273 ELSIF rank_search <= 19 THEN
280 LANGUAGE plpgsql IMMUTABLE;
282 CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT,
283 OUT rank_search SMALLINT, OUT rank_address SMALLINT)
290 postcode := upper(postcode);
292 IF country_code = 'gb' THEN
293 IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
296 ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
299 ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
304 ELSEIF country_code = 'sg' THEN
305 IF postcode ~ '^([0-9]{6})$' THEN
310 ELSEIF country_code = 'de' THEN
311 IF postcode ~ '^([0-9]{5})$' THEN
317 -- Guess at the postcode format and coverage (!)
318 IF postcode ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
322 -- Does it look splitable into and area and local code?
323 part := substring(postcode from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
325 IF part IS NOT NULL THEN
328 ELSEIF postcode ~ '^[- :A-Z0-9]{6,}$' THEN
337 LANGUAGE plpgsql IMMUTABLE;
339 -- Find the nearest artificial postcode for the given geometry.
340 -- TODO For areas there should not be more than two inside the geometry.
341 CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY) RETURNS TEXT
347 -- If the geometry is an area then only one postcode must be within
348 -- that area, otherwise consider the area as not having a postcode.
349 IF ST_GeometryType(geom) in ('ST_Polygon','ST_MultiPolygon') THEN
350 SELECT min(postcode), count(*) FROM
351 (SELECT postcode FROM location_postcode
352 WHERE ST_Contains(geom, location_postcode.geometry) LIMIT 2) sub
362 SELECT postcode FROM location_postcode
363 WHERE ST_DWithin(geom, location_postcode.geometry, 0.05)
364 AND location_postcode.country_code = country
365 ORDER BY ST_Distance(geom, location_postcode.geometry) LIMIT 1
374 CREATE OR REPLACE FUNCTION create_country(src HSTORE, lookup_country_code varchar(2)) RETURNS VOID
383 FOR item IN SELECT (each(src)).* LOOP
385 s := make_standard_name(item.value);
386 w := getorcreate_country(s, lookup_country_code);
388 words := regexp_split_to_array(item.value, E'[,;()]');
389 IF array_upper(words, 1) != 1 THEN
390 FOR j IN 1..array_upper(words, 1) LOOP
391 s := make_standard_name(words[j]);
393 w := getorcreate_country(s, lookup_country_code);
402 CREATE OR REPLACE FUNCTION make_keywords(src HSTORE) RETURNS INTEGER[]
412 result := '{}'::INTEGER[];
414 FOR item IN SELECT (each(src)).* LOOP
416 s := make_standard_name(item.value);
418 w := getorcreate_name_id(s, item.value);
420 IF not(ARRAY[w] <@ result) THEN
421 result := result || w;
424 w := getorcreate_word_id(s);
426 IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
427 result := result || w;
430 words := string_to_array(s, ' ');
431 IF array_upper(words, 1) IS NOT NULL THEN
432 FOR j IN 1..array_upper(words, 1) LOOP
433 IF (words[j] != '') THEN
434 w = getorcreate_word_id(words[j]);
435 IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
436 result := result || w;
442 words := regexp_split_to_array(item.value, E'[,;()]');
443 IF array_upper(words, 1) != 1 THEN
444 FOR j IN 1..array_upper(words, 1) LOOP
445 s := make_standard_name(words[j]);
447 w := getorcreate_word_id(s);
448 IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
449 result := result || w;
455 s := regexp_replace(item.value, '市$', '');
456 IF s != item.value THEN
457 s := make_standard_name(s);
459 w := getorcreate_name_id(s, item.value);
460 IF NOT (ARRAY[w] <@ result) THEN
461 result := result || w;
471 LANGUAGE plpgsql IMMUTABLE;
473 CREATE OR REPLACE FUNCTION make_keywords(src TEXT) RETURNS INTEGER[]
483 result := '{}'::INTEGER[];
485 s := make_standard_name(src);
486 w := getorcreate_name_id(s, src);
488 IF NOT (ARRAY[w] <@ result) THEN
489 result := result || w;
492 w := getorcreate_word_id(s);
494 IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
495 result := result || w;
498 words := string_to_array(s, ' ');
499 IF array_upper(words, 1) IS NOT NULL THEN
500 FOR j IN 1..array_upper(words, 1) LOOP
501 IF (words[j] != '') THEN
502 w = getorcreate_word_id(words[j]);
503 IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
504 result := result || w;
510 words := regexp_split_to_array(src, E'[,;()]');
511 IF array_upper(words, 1) != 1 THEN
512 FOR j IN 1..array_upper(words, 1) LOOP
513 s := make_standard_name(words[j]);
515 w := getorcreate_word_id(s);
516 IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
517 result := result || w;
523 s := regexp_replace(src, '市$', '');
525 s := make_standard_name(s);
527 w := getorcreate_name_id(s, src);
528 IF NOT (ARRAY[w] <@ result) THEN
529 result := result || w;
537 LANGUAGE plpgsql IMMUTABLE;
539 CREATE OR REPLACE FUNCTION get_country_code(place geometry) RETURNS TEXT
542 place_centre GEOMETRY;
545 place_centre := ST_PointOnSurface(place);
547 -- RAISE WARNING 'get_country_code, start: %', ST_AsText(place_centre);
549 -- Try for a OSM polygon
550 FOR nearcountry IN select country_code from location_area_country where country_code is not null and not isguess and st_covers(geometry, place_centre) limit 1
552 RETURN nearcountry.country_code;
555 -- RAISE WARNING 'osm fallback: %', ST_AsText(place_centre);
557 -- Try for OSM fallback data
558 -- The order is to deal with places like HongKong that are 'states' within another polygon
559 FOR nearcountry IN select country_code from country_osm_grid where st_covers(geometry, place_centre) order by area asc limit 1
561 RETURN nearcountry.country_code;
564 -- RAISE WARNING 'natural earth: %', ST_AsText(place_centre);
566 -- Natural earth data
567 FOR nearcountry IN select country_code from country_naturalearthdata where st_covers(geometry, place_centre) limit 1
569 RETURN nearcountry.country_code;
572 -- RAISE WARNING 'near osm fallback: %', ST_AsText(place_centre);
575 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
577 RETURN nearcountry.country_code;
580 -- RAISE WARNING 'near natural earth: %', ST_AsText(place_centre);
582 -- Natural earth data
583 FOR nearcountry IN select country_code from country_naturalearthdata where st_dwithin(geometry, place_centre, 0.5) limit 1
585 RETURN nearcountry.country_code;
591 LANGUAGE plpgsql IMMUTABLE;
593 CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR(2)) RETURNS TEXT
598 FOR nearcountry IN select distinct country_default_language_code from country_name where country_code = search_country_code limit 1
600 RETURN lower(nearcountry.country_default_language_code);
605 LANGUAGE plpgsql IMMUTABLE;
607 CREATE OR REPLACE FUNCTION get_country_language_codes(search_country_code VARCHAR(2)) RETURNS TEXT[]
612 FOR nearcountry IN select country_default_language_codes from country_name where country_code = search_country_code limit 1
614 RETURN lower(nearcountry.country_default_language_codes);
619 LANGUAGE plpgsql IMMUTABLE;
621 CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10)) RETURNS INTEGER
626 FOR nearcountry IN select partition from country_name where country_code = in_country_code
628 RETURN nearcountry.partition;
633 LANGUAGE plpgsql IMMUTABLE;
635 CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT) RETURNS BOOLEAN
639 DELETE FROM location_area where place_id = OLD_place_id;
640 -- TODO:location_area
646 CREATE OR REPLACE FUNCTION add_location(
648 country_code varchar(2),
652 rank_address INTEGER,
668 IF rank_search > 25 THEN
669 RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
672 x := deleteLocationArea(partition, place_id, rank_search);
674 -- add postcode only if it contains a single entry, i.e. ignore postcode lists
676 IF in_postcode is not null AND in_postcode not similar to '%(,|;)%' THEN
677 postcode := upper(trim (in_postcode));
680 IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
681 centroid := ST_Centroid(geometry);
683 FOR secgeo IN select split_geometry(geometry) AS geom LOOP
684 x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
690 IF rank_address = 0 THEN
692 ELSEIF rank_search <= 14 THEN
694 ELSEIF rank_search <= 15 THEN
696 ELSEIF rank_search <= 16 THEN
698 ELSEIF rank_search <= 17 THEN
700 ELSEIF rank_search <= 21 THEN
702 ELSEIF rank_search = 25 THEN
706 -- RAISE WARNING 'adding % diameter %', place_id, diameter;
708 secgeo := ST_Buffer(geometry, diameter);
709 x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, ST_Centroid(geometry), secgeo);
719 -- find the parent road of the cut road parts
720 CREATE OR REPLACE FUNCTION get_interpolation_parent(wayid BIGINT, street TEXT, place TEXT,
721 partition INTEGER, centroid GEOMETRY, geom GEOMETRY)
726 parent_place_id BIGINT;
727 address_street_word_ids INTEGER[];
733 addr_street = street;
736 IF addr_street is null and addr_place is null THEN
737 select nodes from planet_osm_ways where id = wayid INTO waynodes;
738 FOR location IN SELECT placex.address from placex
739 where osm_type = 'N' and osm_id = ANY(waynodes)
740 and placex.address is not null
741 and (placex.address ? 'street' or placex.address ? 'place')
742 and indexed_status < 100
744 addr_street = location.address->'street';
745 addr_place = location.address->'place';
749 IF addr_street IS NOT NULL THEN
750 address_street_word_ids := get_name_ids(make_standard_name(addr_street));
751 IF address_street_word_ids IS NOT NULL THEN
752 FOR location IN SELECT place_id from getNearestNamedRoadFeature(partition, centroid, address_street_word_ids) LOOP
753 parent_place_id := location.place_id;
758 IF parent_place_id IS NULL AND addr_place IS NOT NULL THEN
759 address_street_word_ids := get_name_ids(make_standard_name(addr_place));
760 IF address_street_word_ids IS NOT NULL THEN
761 FOR location IN SELECT place_id from getNearestNamedPlaceFeature(partition, centroid, address_street_word_ids) LOOP
762 parent_place_id := location.place_id;
767 IF parent_place_id is null THEN
768 FOR location IN SELECT place_id FROM placex
769 WHERE ST_DWithin(geom, placex.geometry, 0.001) and placex.rank_search = 26
770 ORDER BY (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+
771 ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+
772 ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,1))) ASC limit 1
774 parent_place_id := location.place_id;
778 IF parent_place_id is null THEN
782 RETURN parent_place_id;
788 CREATE OR REPLACE FUNCTION osmline_insert() RETURNS TRIGGER
791 NEW.place_id := nextval('seq_place');
792 NEW.indexed_date := now();
794 IF NEW.indexed_status IS NULL THEN
795 IF NEW.address is NULL OR NOT NEW.address ? 'interpolation'
796 OR NEW.address->'interpolation' NOT IN ('odd', 'even', 'all') THEN
797 -- other interpolation types than odd/even/all (e.g. numeric ones) are not supported
801 NEW.indexed_status := 1; --STATUS_NEW
802 NEW.country_code := lower(get_country_code(NEW.linegeo));
804 NEW.partition := get_partition(NEW.country_code);
805 NEW.geometry_sector := geometry_sector(NEW.partition, NEW.linegeo);
814 CREATE OR REPLACE FUNCTION placex_insert() RETURNS TRIGGER
820 country_code VARCHAR(2);
821 default_language VARCHAR(10);
826 --DEBUG: RAISE WARNING '% % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
828 NEW.place_id := nextval('seq_place');
829 NEW.indexed_status := 1; --STATUS_NEW
831 NEW.country_code := lower(get_country_code(NEW.geometry));
833 NEW.partition := get_partition(NEW.country_code);
834 NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
836 -- copy 'name' to or from the default language (if there is a default language)
837 IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
838 default_language := get_country_language_code(NEW.country_code);
839 IF default_language IS NOT NULL THEN
840 IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
841 NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
842 ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
843 NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
848 IF NEW.osm_type = 'X' THEN
849 -- E'X'ternal records should already be in the right format so do nothing
851 NEW.rank_search := 30;
852 NEW.rank_address := NEW.rank_search;
854 -- By doing in postgres we have the country available to us - currently only used for postcode
855 IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
857 IF NEW.address IS NULL OR NOT NEW.address ? 'postcode' THEN
858 -- most likely just a part of a multipolygon postcode boundary, throw it away
862 NEW.name := hstore('ref', NEW.address->'postcode');
864 SELECT * FROM get_postcode_rank(NEW.country_code, NEW.address->'postcode')
865 INTO NEW.rank_search, NEW.rank_address;
867 IF NOT ST_GeometryType(NEW.geometry) IN ('ST_Polygon','ST_MultiPolygon') THEN
868 NEW.rank_address := 0;
871 ELSEIF NEW.class = 'place' THEN
872 IF NEW.type in ('continent') THEN
873 NEW.rank_search := 2;
874 IF ST_GeometryType(NEW.geometry) IN ('ST_Polygon','ST_MultiPolygon') THEN
875 NEW.rank_address := NEW.rank_search;
877 NEW.rank_address := 0;
879 NEW.country_code := NULL;
880 ELSEIF NEW.type in ('sea') THEN
881 NEW.rank_search := 2;
882 NEW.rank_address := 0;
883 NEW.country_code := NULL;
884 ELSEIF NEW.type in ('country') THEN
885 NEW.rank_search := 4;
886 IF ST_GeometryType(NEW.geometry) IN ('ST_Polygon','ST_MultiPolygon') THEN
887 NEW.rank_address := NEW.rank_search;
889 NEW.rank_address := 0;
891 ELSEIF NEW.type in ('state') THEN
892 NEW.rank_search := 8;
893 IF ST_GeometryType(NEW.geometry) IN ('ST_Polygon','ST_MultiPolygon') THEN
894 NEW.rank_address := NEW.rank_search;
896 NEW.rank_address := 0;
898 ELSEIF NEW.type in ('region') THEN
899 NEW.rank_search := 18; -- dropped from previous value of 10
900 NEW.rank_address := 0; -- So badly miss-used that better to just drop it!
901 ELSEIF NEW.type in ('county') THEN
902 NEW.rank_search := 12;
903 NEW.rank_address := NEW.rank_search;
904 ELSEIF NEW.type in ('city') THEN
905 NEW.rank_search := 16;
906 NEW.rank_address := NEW.rank_search;
907 ELSEIF NEW.type in ('island') THEN
908 NEW.rank_search := 17;
909 NEW.rank_address := 0;
910 ELSEIF NEW.type in ('town') THEN
911 NEW.rank_search := 18;
912 NEW.rank_address := 16;
913 ELSEIF NEW.type in ('village','hamlet','municipality','district','unincorporated_area','borough') THEN
914 NEW.rank_search := 19;
915 NEW.rank_address := 16;
916 ELSEIF NEW.type in ('suburb','croft','subdivision','isolated_dwelling') THEN
917 NEW.rank_search := 20;
918 NEW.rank_address := NEW.rank_search;
919 ELSEIF NEW.type in ('farm','locality','islet','mountain_pass') THEN
920 NEW.rank_search := 20;
921 NEW.rank_address := 0;
922 -- Irish townlands, tagged as place=locality and locality=townland
923 IF (NEW.extratags -> 'locality') = 'townland' THEN
924 NEW.rank_address := 20;
926 ELSEIF NEW.type in ('neighbourhood') THEN
927 NEW.rank_search := 22;
928 NEW.rank_address := 22;
929 ELSEIF NEW.type in ('house','building') THEN
930 NEW.rank_search := 30;
931 NEW.rank_address := NEW.rank_search;
932 ELSEIF NEW.type in ('houses') THEN
933 -- can't guarantee all required nodes loaded yet due to caching in osm2pgsql
934 NEW.rank_search := 28;
935 NEW.rank_address := 0;
938 ELSEIF NEW.class = 'boundary' THEN
939 IF ST_GeometryType(NEW.geometry) NOT IN ('ST_Polygon','ST_MultiPolygon') THEN
940 -- RAISE WARNING 'invalid boundary %',NEW.osm_id;
943 NEW.rank_search := NEW.admin_level * 2;
944 IF NEW.type = 'administrative' THEN
945 NEW.rank_address := NEW.rank_search;
947 NEW.rank_address := 0;
949 ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
950 NEW.rank_search := 22;
951 IF NEW.type in ('residential', 'farm', 'farmyard', 'industrial', 'commercial', 'allotments', 'retail') THEN
952 NEW.rank_address := NEW.rank_search;
954 NEW.rank_address := 0;
956 ELSEIF NEW.class = 'leisure' and NEW.type in ('park') THEN
957 NEW.rank_search := 24;
958 NEW.rank_address := 0;
959 ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN
960 NEW.rank_search := 18;
961 NEW.rank_address := 0;
962 ELSEIF NEW.class = 'natural' and NEW.type = 'sea' THEN
963 NEW.rank_search := 4;
964 NEW.rank_address := NEW.rank_search;
965 -- any feature more than 5 square miles is probably worth indexing
966 ELSEIF ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_Area(NEW.geometry) > 0.1 THEN
967 NEW.rank_search := 22;
968 NEW.rank_address := 0;
969 ELSEIF NEW.class = 'railway' AND NEW.type in ('rail') THEN
971 ELSEIF NEW.class = 'waterway' THEN
972 IF NEW.osm_type = 'R' THEN
973 NEW.rank_search := 16;
975 NEW.rank_search := 17;
977 NEW.rank_address := 0;
978 ELSEIF NEW.class = 'highway' AND NEW.osm_type != 'N' AND NEW.type in ('service','cycleway','path','footway','steps','bridleway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN
979 NEW.rank_search := 27;
980 NEW.rank_address := NEW.rank_search;
981 ELSEIF NEW.class = 'highway' AND NEW.osm_type != 'N' THEN
982 NEW.rank_search := 26;
983 NEW.rank_address := NEW.rank_search;
984 ELSEIF NEW.class = 'mountain_pass' THEN
985 NEW.rank_search := 20;
986 NEW.rank_address := 0;
991 IF NEW.rank_search > 30 THEN
992 NEW.rank_search := 30;
995 IF NEW.rank_address > 30 THEN
996 NEW.rank_address := 30;
999 IF (NEW.extratags -> 'capital') = 'yes' THEN
1000 NEW.rank_search := NEW.rank_search - 1;
1003 -- a country code make no sense below rank 4 (country)
1004 IF NEW.rank_search < 4 THEN
1005 NEW.country_code := NULL;
1008 -- Block import below rank 22
1009 -- IF NEW.rank_search > 22 THEN
1013 --DEBUG: RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
1015 RETURN NEW; -- %DIFFUPDATES% The following is not needed until doing diff updates, and slows the main index process down
1017 IF NEW.rank_address > 0 THEN
1018 IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN
1019 -- Performance: We just can't handle re-indexing for country level changes
1020 IF st_area(NEW.geometry) < 1 THEN
1021 -- mark items within the geometry for re-indexing
1022 -- RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
1024 -- work around bug in postgis, this may have been fixed in 2.0.0 (see http://trac.osgeo.org/postgis/ticket/547)
1025 update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
1026 AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and address ? 'place'));
1027 update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
1028 AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and address ? 'place'));
1031 -- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(
1033 -- 16 = city, anything higher than city is effectively ignored (polygon required!)
1034 IF NEW.type='postcode' THEN
1036 ELSEIF NEW.rank_search < 16 THEN
1038 ELSEIF NEW.rank_search < 18 THEN
1040 ELSEIF NEW.rank_search < 20 THEN
1042 ELSEIF NEW.rank_search = 21 THEN
1044 ELSEIF NEW.rank_search < 24 THEN
1046 ELSEIF NEW.rank_search < 26 THEN
1047 diameter := 0.002; -- 100 to 200 meters
1048 ELSEIF NEW.rank_search < 28 THEN
1049 diameter := 0.001; -- 50 to 100 meters
1051 IF diameter > 0 THEN
1052 -- RAISE WARNING 'placex point insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,diameter;
1053 IF NEW.rank_search >= 26 THEN
1054 -- roads may cause reparenting for >27 rank places
1055 update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter);
1056 -- reparenting also for OSM Interpolation Lines (and for Tiger?)
1057 update location_property_osmline set indexed_status = 2 where indexed_status = 0 and ST_DWithin(location_property_osmline.linegeo, NEW.geometry, diameter);
1058 ELSEIF NEW.rank_search >= 16 THEN
1059 -- up to rank 16, street-less addresses may need reparenting
1060 update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null or address ? 'place');
1062 -- for all other places the search terms may change as well
1063 update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null);
1070 -- add to tables for special search
1071 -- Note: won't work on initial import because the classtype tables
1072 -- do not yet exist. It won't hurt either.
1073 classtable := 'place_classtype_' || NEW.class || '_' || NEW.type;
1074 SELECT count(*)>0 FROM pg_tables WHERE tablename = classtable and schemaname = current_schema() INTO result;
1076 EXECUTE 'INSERT INTO ' || classtable::regclass || ' (place_id, centroid) VALUES ($1,$2)'
1077 USING NEW.place_id, ST_Centroid(NEW.geometry);
1086 CREATE OR REPLACE FUNCTION osmline_update() RETURNS
1090 place_centroid GEOMETRY;
1094 startnumber INTEGER;
1099 sectiongeo GEOMETRY;
1100 interpol_postcode TEXT;
1104 IF OLD.indexed_status = 100 THEN
1105 delete from location_property_osmline where place_id = OLD.place_id;
1109 IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
1113 NEW.interpolationtype = NEW.address->'interpolation';
1115 place_centroid := ST_PointOnSurface(NEW.linegeo);
1116 NEW.parent_place_id = get_interpolation_parent(NEW.osm_id, NEW.address->'street',
1117 NEW.address->'place',
1118 NEW.partition, place_centroid, NEW.linegeo);
1120 IF NEW.address is not NULL AND NEW.address ? 'postcode' AND NEW.address->'postcode' not similar to '%(,|;)%' THEN
1121 interpol_postcode := NEW.address->'postcode';
1122 housenum := getorcreate_postcode_id(NEW.address->'postcode');
1124 interpol_postcode := NULL;
1127 -- if the line was newly inserted, split the line as necessary
1128 IF OLD.indexed_status = 1 THEN
1129 select nodes from planet_osm_ways where id = NEW.osm_id INTO waynodes;
1131 IF array_upper(waynodes, 1) IS NULL THEN
1135 linegeo := NEW.linegeo;
1136 startnumber := NULL;
1138 FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
1140 select osm_id, address, geometry
1141 from place where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
1142 and address is not NULL and address ? 'housenumber' limit 1 INTO nextnode;
1143 --RAISE NOTICE 'Nextnode.place_id: %s', nextnode.place_id;
1144 IF nextnode.osm_id IS NOT NULL THEN
1145 --RAISE NOTICE 'place_id is not null';
1146 IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN
1147 -- Make sure that the point is actually on the line. That might
1148 -- be a bit paranoid but ensures that the algorithm still works
1149 -- should osm2pgsql attempt to repair geometries.
1150 splitline := split_line_on_node(linegeo, nextnode.geometry);
1151 sectiongeo := ST_GeometryN(splitline, 1);
1152 linegeo := ST_GeometryN(splitline, 2);
1154 sectiongeo = linegeo;
1156 endnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer;
1158 IF startnumber IS NOT NULL AND endnumber IS NOT NULL
1159 AND startnumber != endnumber
1160 AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN
1162 IF (startnumber > endnumber) THEN
1163 housenum := endnumber;
1164 endnumber := startnumber;
1165 startnumber := housenum;
1166 sectiongeo := ST_Reverse(sectiongeo);
1169 -- determine postcode
1170 postcode := coalesce(interpol_postcode,
1171 prevnode.address->'postcode',
1172 nextnode.address->'postcode',
1175 IF postcode is NULL THEN
1176 SELECT placex.postcode FROM placex WHERE place_id = NEW.parent_place_id INTO postcode;
1178 IF postcode is NULL THEN
1179 postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry);
1182 IF NEW.startnumber IS NULL THEN
1183 NEW.startnumber := startnumber;
1184 NEW.endnumber := endnumber;
1185 NEW.linegeo := sectiongeo;
1186 NEW.postcode := upper(trim(postcode));
1188 insert into location_property_osmline
1189 (linegeo, partition, osm_id, parent_place_id,
1190 startnumber, endnumber, interpolationtype,
1191 address, postcode, country_code,
1192 geometry_sector, indexed_status)
1193 values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
1194 startnumber, endnumber, NEW.interpolationtype,
1195 NEW.address, postcode,
1196 NEW.country_code, NEW.geometry_sector, 0);
1200 -- early break if we are out of line string,
1201 -- might happen when a line string loops back on itself
1202 IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
1206 startnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer;
1207 prevnode := nextnode;
1212 -- marking descendants for reparenting is not needed, because there are
1213 -- actually no descendants for interpolation lines
1219 -- Trigger for updates of location_postcode
1221 -- Computes the parent object the postcode most likely refers to.
1222 -- This will be the place that determines the address displayed when
1223 -- searching for this postcode.
1224 CREATE OR REPLACE FUNCTION postcode_update() RETURNS
1231 IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
1235 NEW.indexed_date = now();
1237 partition := get_partition(NEW.country_code);
1239 SELECT * FROM get_postcode_rank(NEW.country_code, NEW.postcode)
1240 INTO NEW.rank_search, NEW.rank_address;
1242 NEW.parent_place_id = 0;
1245 FROM getNearFeatures(partition, NEW.geometry, NEW.rank_search, '{}'::int[])
1246 WHERE NOT isguess ORDER BY rank_address DESC LIMIT 1
1248 NEW.parent_place_id = location.place_id;
1256 CREATE OR REPLACE FUNCTION placex_update() RETURNS
1261 place_centroid GEOMETRY;
1263 search_maxdistance FLOAT[];
1264 search_mindistance FLOAT[];
1265 address_havelevel BOOLEAN[];
1272 relation_members TEXT[];
1274 linkedplacex RECORD;
1276 search_diameter FLOAT;
1277 search_prevdiameter FLOAT;
1278 search_maxrank INTEGER;
1279 address_maxrank INTEGER;
1280 address_street_word_id INTEGER;
1281 address_street_word_ids INTEGER[];
1282 parent_place_id_rank BIGINT;
1290 location_rank_search INTEGER;
1291 location_distance FLOAT;
1292 location_parent GEOMETRY;
1293 location_isaddress BOOLEAN;
1294 location_keywords INTEGER[];
1296 default_language TEXT;
1297 name_vector INTEGER[];
1298 nameaddress_vector INTEGER[];
1300 linked_node_id BIGINT;
1301 linked_importance FLOAT;
1302 linked_wikipedia TEXT;
1307 IF OLD.indexed_status = 100 THEN
1308 --DEBUG: RAISE WARNING 'placex_update delete % %',NEW.osm_type,NEW.osm_id;
1309 delete from placex where place_id = OLD.place_id;
1313 IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
1317 --DEBUG: RAISE WARNING 'placex_update % % (%)',NEW.osm_type,NEW.osm_id,NEW.place_id;
1319 NEW.indexed_date = now();
1321 result := deleteSearchName(NEW.partition, NEW.place_id);
1322 DELETE FROM place_addressline WHERE place_id = NEW.place_id;
1323 result := deleteRoad(NEW.partition, NEW.place_id);
1324 result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
1325 UPDATE placex set linked_place_id = null, indexed_status = 2
1326 where linked_place_id = NEW.place_id;
1327 -- update not necessary for osmline, cause linked_place_id does not exist
1329 IF NEW.linked_place_id is not null THEN
1330 --DEBUG: RAISE WARNING 'place already linked to %', NEW.linked_place_id;
1334 --DEBUG: RAISE WARNING 'Copy over address tags';
1335 IF NEW.address is not NULL THEN
1336 IF NEW.address ? 'conscriptionnumber' THEN
1337 i := getorcreate_housenumber_id(make_standard_name(NEW.address->'conscriptionnumber'));
1338 IF NEW.address ? 'streetnumber' THEN
1339 i := getorcreate_housenumber_id(make_standard_name(NEW.address->'streetnumber'));
1340 NEW.housenumber := (NEW.address->'conscriptionnumber') || '/' || (NEW.address->'streetnumber');
1342 NEW.housenumber := NEW.address->'conscriptionnumber';
1344 ELSEIF NEW.address ? 'streetnumber' THEN
1345 NEW.housenumber := NEW.address->'streetnumber';
1346 i := getorcreate_housenumber_id(make_standard_name(NEW.address->'streetnumber'));
1347 ELSEIF NEW.address ? 'housenumber' THEN
1348 NEW.housenumber := NEW.address->'housenumber';
1349 i := getorcreate_housenumber_id(make_standard_name(NEW.housenumber));
1352 addr_street := NEW.address->'street';
1353 addr_place := NEW.address->'place';
1355 IF NEW.address ? 'postcode' and NEW.address->'postcode' not similar to '%(,|;)%' THEN
1356 i := getorcreate_postcode_id(NEW.address->'postcode');
1360 -- Speed up searches - just use the centroid of the feature
1361 -- cheaper but less acurate
1362 place_centroid := ST_PointOnSurface(NEW.geometry);
1363 NEW.centroid := null;
1364 NEW.postcode := null;
1365 --DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(place_centroid);
1367 -- recalculate country and partition
1368 IF NEW.rank_search = 4 AND NEW.address is not NULL AND NEW.address ? 'country' THEN
1369 -- for countries, believe the mapped country code,
1370 -- so that we remain in the right partition if the boundaries
1372 NEW.country_code := lower(NEW.address->'country');
1373 NEW.partition := get_partition(lower(NEW.country_code));
1374 IF NEW.partition = 0 THEN
1375 NEW.country_code := lower(get_country_code(place_centroid));
1376 NEW.partition := get_partition(NEW.country_code);
1379 IF NEW.rank_search >= 4 THEN
1380 NEW.country_code := lower(get_country_code(place_centroid));
1382 NEW.country_code := NULL;
1384 NEW.partition := get_partition(NEW.country_code);
1386 --DEBUG: RAISE WARNING 'Country updated: "%"', NEW.country_code;
1388 -- waterway ways are linked when they are part of a relation and have the same class/type
1389 IF NEW.osm_type = 'R' and NEW.class = 'waterway' THEN
1390 FOR relation_members IN select members from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[]
1392 FOR i IN 1..array_upper(relation_members, 1) BY 2 LOOP
1393 IF relation_members[i+1] in ('', 'main_stream', 'side_stream') AND substring(relation_members[i],1,1) = 'w' THEN
1394 --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation_members[i];
1395 FOR linked_node_id IN SELECT place_id FROM placex
1396 WHERE osm_type = 'W' and osm_id = substring(relation_members[i],2,200)::bigint
1397 and class = NEW.class and type in ('river', 'stream', 'canal', 'drain', 'ditch')
1398 and ( relation_members[i+1] != 'side_stream' or NEW.name->'name' = name->'name')
1400 UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id;
1405 --DEBUG: RAISE WARNING 'Waterway processed';
1408 -- Adding ourselves to the list simplifies address calculations later
1409 INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address)
1410 VALUES (NEW.place_id, NEW.place_id, true, true, 0, NEW.rank_address);
1412 -- What level are we searching from
1413 search_maxrank := NEW.rank_search;
1415 -- Thought this wasn't needed but when we add new languages to the country_name table
1416 -- we need to update the existing names
1417 IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
1418 default_language := get_country_language_code(NEW.country_code);
1419 IF default_language IS NOT NULL THEN
1420 IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
1421 NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
1422 ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
1423 NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
1427 --DEBUG: RAISE WARNING 'Local names updated';
1429 -- Initialise the name vector using our name
1430 name_vector := make_keywords(NEW.name);
1431 nameaddress_vector := '{}'::int[];
1434 address_havelevel[i] := false;
1437 NEW.importance := null;
1438 select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.country_code) INTO NEW.wikipedia,NEW.importance;
1439 IF NEW.importance IS NULL THEN
1440 select language||':'||title,importance from wikipedia_article where osm_type = NEW.osm_type and osm_id = NEW.osm_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
1443 --DEBUG: RAISE WARNING 'Importance computed from wikipedia: %', NEW.importance;
1445 -- ---------------------------------------------------------------------------
1446 -- For low level elements we inherit from our parent road
1447 IF (NEW.rank_search > 27 OR (NEW.type = 'postcode' AND NEW.rank_search = 25)) THEN
1449 --DEBUG: RAISE WARNING 'finding street for % %', NEW.osm_type, NEW.osm_id;
1451 -- We won't get a better centroid, besides these places are too small to care
1452 NEW.centroid := place_centroid;
1454 NEW.parent_place_id := null;
1456 -- if we have a POI and there is no address information,
1457 -- see if we can get it from a surrounding building
1458 IF NEW.osm_type = 'N' AND addr_street IS NULL AND addr_place IS NULL
1459 AND NEW.housenumber IS NULL THEN
1460 FOR location IN select address from placex where ST_Covers(geometry, place_centroid)
1461 and address is not null
1462 and (address ? 'housenumber' or address ? 'street' or address ? 'place')
1463 and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
1466 NEW.housenumber := location.address->'housenumber';
1467 addr_street := location.address->'street';
1468 addr_place := location.address->'place';
1469 --DEBUG: RAISE WARNING 'Found surrounding building % %', location.osm_type, location.osm_id;
1473 -- We have to find our parent road.
1474 -- Copy data from linked items (points on ways, addr:street links, relations)
1476 -- Is this object part of a relation?
1477 FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY[lower(NEW.osm_type)||NEW.osm_id]
1479 -- At the moment we only process one type of relation - associatedStreet
1480 IF relation.tags @> ARRAY['associatedStreet'] THEN
1481 FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
1482 IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
1483 --RAISE WARNING 'node in relation %',relation;
1484 SELECT place_id from placex where osm_type = 'W'
1485 and osm_id = substring(relation.members[i],2,200)::bigint
1486 and rank_search = 26 and name is not null INTO NEW.parent_place_id;
1491 --DEBUG: RAISE WARNING 'Checked for street relation (%)', NEW.parent_place_id;
1493 -- Note that addr:street links can only be indexed once the street itself is indexed
1494 IF NEW.parent_place_id IS NULL AND addr_street IS NOT NULL THEN
1495 address_street_word_ids := get_name_ids(make_standard_name(addr_street));
1496 IF address_street_word_ids IS NOT NULL THEN
1497 SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id;
1500 --DEBUG: RAISE WARNING 'Checked for addr:street (%)', NEW.parent_place_id;
1502 IF NEW.parent_place_id IS NULL AND addr_place IS NOT NULL THEN
1503 address_street_word_ids := get_name_ids(make_standard_name(addr_place));
1504 IF address_street_word_ids IS NOT NULL THEN
1505 SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id;
1508 --DEBUG: RAISE WARNING 'Checked for addr:place (%)', NEW.parent_place_id;
1510 -- Is this node part of an interpolation?
1511 IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
1512 SELECT q.parent_place_id FROM location_property_osmline q, planet_osm_ways x
1513 WHERE q.linegeo && NEW.geometry and x.id = q.osm_id and NEW.osm_id = any(x.nodes)
1514 LIMIT 1 INTO NEW.parent_place_id;
1516 --DEBUG: RAISE WARNING 'Checked for interpolation (%)', NEW.parent_place_id;
1518 -- Is this node part of a way?
1519 IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
1522 SELECT p.place_id, p.osm_id, p.rank_search, p.address from placex p, planet_osm_ways w
1523 WHERE p.osm_type = 'W' and p.rank_search >= 26 and p.geometry && NEW.geometry and w.id = p.osm_id and NEW.osm_id = any(w.nodes)
1525 --DEBUG: RAISE WARNING 'Node is part of way % ', location.osm_id;
1527 -- Way IS a road then we are on it - that must be our road
1528 IF location.rank_search < 28 THEN
1529 --RAISE WARNING 'node in way that is a street %',location;
1530 NEW.parent_place_id := location.place_id;
1533 --DEBUG: RAISE WARNING 'Checked if way is street (%)', NEW.parent_place_id;
1535 -- If the way mentions a street or place address, try that for parenting.
1536 IF location.address is not null THEN
1537 IF location.address ? 'street' THEN
1538 address_street_word_ids := get_name_ids(make_standard_name(location.address->'street'));
1539 IF address_street_word_ids IS NOT NULL THEN
1540 SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id;
1541 EXIT WHEN NEW.parent_place_id is not NULL;
1544 --DEBUG: RAISE WARNING 'Checked for addr:street in way (%)', NEW.parent_place_id;
1546 IF location.address ? 'place' THEN
1547 address_street_word_ids := get_name_ids(make_standard_name(location.address->'place'));
1548 IF address_street_word_ids IS NOT NULL THEN
1549 SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id;
1550 EXIT WHEN NEW.parent_place_id is not NULL;
1553 --DEBUG: RAISE WARNING 'Checked for addr:place in way (%)', NEW.parent_place_id;
1556 -- Is the WAY part of a relation
1557 FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id]
1559 -- At the moment we only process one type of relation - associatedStreet
1560 IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
1561 FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
1562 IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
1563 --RAISE WARNING 'node in way that is in a relation %',relation;
1564 SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint
1565 and rank_search = 26 and name is not null INTO NEW.parent_place_id;
1570 EXIT WHEN NEW.parent_place_id is not null;
1571 --DEBUG: RAISE WARNING 'Checked for street relation in way (%)', NEW.parent_place_id;
1576 -- Still nothing, just use the nearest road
1577 IF NEW.parent_place_id IS NULL THEN
1578 SELECT place_id FROM getNearestRoadFeature(NEW.partition, place_centroid) INTO NEW.parent_place_id;
1580 --DEBUG: RAISE WARNING 'Checked for nearest way (%)', NEW.parent_place_id;
1583 -- If we didn't find any road fallback to standard method
1584 IF NEW.parent_place_id IS NOT NULL THEN
1586 -- Get the details of the parent road
1587 select s.country_code, s.name_vector, s.nameaddress_vector from search_name s
1588 where s.place_id = NEW.parent_place_id INTO location;
1589 NEW.country_code := location.country_code;
1590 --DEBUG: RAISE WARNING 'Got parent details from search name';
1592 -- determine postcode
1593 IF NEW.rank_search > 4 THEN
1594 IF NEW.address is not null AND NEW.address ? 'postcode' THEN
1595 NEW.postcode = upper(trim(NEW.address->'postcode'));
1597 SELECT postcode FROM placex WHERE place_id = NEW.parent_place_id INTO NEW.postcode;
1599 IF NEW.postcode is null THEN
1600 NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid);
1604 -- If there is no name it isn't searchable, don't bother to create a search record
1605 IF NEW.name is NULL THEN
1606 --DEBUG: RAISE WARNING 'Not a searchable place % %', NEW.osm_type, NEW.osm_id;
1610 -- Merge address from parent
1611 nameaddress_vector := array_merge(nameaddress_vector, location.nameaddress_vector);
1612 nameaddress_vector := array_merge(nameaddress_vector, location.name_vector);
1614 -- Performance, it would be more acurate to do all the rest of the import
1615 -- process but it takes too long
1616 -- Just be happy with inheriting from parent road only
1618 IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
1619 result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry);
1620 --DEBUG: RAISE WARNING 'Place added to location table';
1623 result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);
1624 --DEBUG: RAISE WARNING 'Place added to search table';
1631 -- ---------------------------------------------------------------------------
1633 --DEBUG: RAISE WARNING 'Using full index mode for % %', NEW.osm_type, NEW.osm_id;
1635 IF NEW.osm_type = 'R' AND NEW.rank_search < 26 THEN
1637 -- see if we have any special relation members
1638 select members from planet_osm_rels where id = NEW.osm_id INTO relation_members;
1639 --DEBUG: RAISE WARNING 'Got relation members';
1641 IF relation_members IS NOT NULL THEN
1642 FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
1643 --DEBUG: RAISE WARNING 'Found label member %', relMember.member;
1645 FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
1646 and osm_id = substring(relMember.member,2,10000)::bigint
1647 and class = 'place' order by rank_search desc limit 1 LOOP
1649 -- If we don't already have one use this as the centre point of the geometry
1650 IF NEW.centroid IS NULL THEN
1651 NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
1654 -- merge in the label name, re-init word vector
1655 IF NOT linkedPlacex.name IS NULL THEN
1656 NEW.name := linkedPlacex.name || NEW.name;
1657 name_vector := array_merge(name_vector, make_keywords(linkedPlacex.name));
1660 -- merge in extra tags
1661 NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore);
1663 -- mark the linked place (excludes from search results)
1664 UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
1666 -- keep a note of the node id in case we need it for wikipedia in a bit
1667 linked_node_id := linkedPlacex.osm_id;
1668 select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance;
1669 --DEBUG: RAISE WARNING 'Linked label member';
1674 IF NEW.centroid IS NULL THEN
1676 FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
1677 --DEBUG: RAISE WARNING 'Found admin_center member %', relMember.member;
1679 FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
1680 and osm_id = substring(relMember.member,2,10000)::bigint
1681 and class = 'place' order by rank_search desc limit 1 LOOP
1683 -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
1684 -- But that can be fixed by explicitly setting the label in the data
1685 IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name')
1686 AND NEW.rank_address = linkedPlacex.rank_address THEN
1688 -- If we don't already have one use this as the centre point of the geometry
1689 IF NEW.centroid IS NULL THEN
1690 NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
1693 -- merge in the name, re-init word vector
1694 IF NOT linkedPlacex.name IS NULL THEN
1695 NEW.name := linkedPlacex.name || NEW.name;
1696 name_vector := make_keywords(NEW.name);
1699 -- merge in extra tags
1700 NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore);
1702 -- mark the linked place (excludes from search results)
1703 UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
1705 -- keep a note of the node id in case we need it for wikipedia in a bit
1706 linked_node_id := linkedPlacex.osm_id;
1707 select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance;
1708 --DEBUG: RAISE WARNING 'Linked admin_center';
1720 -- Name searches can be done for ways as well as relations
1721 IF NEW.osm_type in ('W','R') AND NEW.rank_search < 26 AND NEW.rank_address > 0 THEN
1723 -- not found one yet? how about doing a name search
1724 IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' THEN
1726 --DEBUG: RAISE WARNING 'Looking for nodes with matching names';
1727 FOR linkedPlacex IN select placex.* from placex WHERE
1728 make_standard_name(name->'name') = make_standard_name(NEW.name->'name')
1729 AND placex.rank_address = NEW.rank_address
1730 AND placex.place_id != NEW.place_id
1731 AND placex.osm_type = 'N'::char(1) AND placex.rank_search < 26
1732 AND st_covers(NEW.geometry, placex.geometry)
1734 --DEBUG: RAISE WARNING 'Found matching place node %', linkedPlacex.osm_id;
1735 -- If we don't already have one use this as the centre point of the geometry
1736 IF NEW.centroid IS NULL THEN
1737 NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
1740 -- merge in the name, re-init word vector
1741 NEW.name := linkedPlacex.name || NEW.name;
1742 name_vector := make_keywords(NEW.name);
1744 -- merge in extra tags
1745 NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore);
1747 -- mark the linked place (excludes from search results)
1748 UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
1750 -- keep a note of the node id in case we need it for wikipedia in a bit
1751 linked_node_id := linkedPlacex.osm_id;
1752 select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance;
1753 --DEBUG: RAISE WARNING 'Linked named place';
1757 IF NEW.centroid IS NOT NULL THEN
1758 place_centroid := NEW.centroid;
1759 -- Place might have had only a name tag before but has now received translations
1760 -- from the linked place. Make sure a name tag for the default language exists in
1762 IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
1763 default_language := get_country_language_code(NEW.country_code);
1764 IF default_language IS NOT NULL THEN
1765 IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
1766 NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
1767 ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
1768 NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
1772 --DEBUG: RAISE WARNING 'Names updated from linked places';
1775 -- Use the maximum importance if a one could be computed from the linked object.
1776 IF linked_importance is not null AND
1777 (NEW.importance is null or NEW.importance < linked_importance) THEN
1778 NEW.importance = linked_importance;
1781 -- Still null? how about looking it up by the node id
1782 IF NEW.importance IS NULL THEN
1783 --DEBUG: RAISE WARNING 'Looking up importance by linked node id';
1784 select language||':'||title,importance from wikipedia_article where osm_type = 'N'::char(1) and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
1789 -- make sure all names are in the word table
1790 IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R' THEN
1791 perform create_country(NEW.name, lower(NEW.country_code));
1792 --DEBUG: RAISE WARNING 'Country names updated';
1795 NEW.parent_place_id = 0;
1796 parent_place_id_rank = 0;
1799 -- convert address store to array of tokenids
1800 --DEBUG: RAISE WARNING 'Starting address search';
1801 isin_tokens := '{}'::int[];
1802 IF NEW.address IS NOT NULL THEN
1803 FOR addr_item IN SELECT * FROM each(NEW.address)
1805 IF addr_item.key IN ('city', 'tiger:county', 'state', 'suburb', 'province', 'district', 'region', 'county', 'municipality', 'hamlet', 'village', 'subdistrict', 'town', 'neighbourhood', 'quarter', 'parish') THEN
1806 address_street_word_id := get_name_id(make_standard_name(addr_item.value));
1807 IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
1808 isin_tokens := isin_tokens || address_street_word_id;
1810 address_street_word_id := get_word_id(make_standard_name(addr_item.value));
1811 IF address_street_word_id IS NOT NULL THEN
1812 nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
1815 IF addr_item.key = 'is_in' THEN
1816 -- is_in items need splitting
1817 isin := regexp_split_to_array(addr_item.value, E'[;,]');
1818 IF array_upper(isin, 1) IS NOT NULL THEN
1819 FOR i IN 1..array_upper(isin, 1) LOOP
1820 address_street_word_id := get_name_id(make_standard_name(isin[i]));
1821 IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
1822 isin_tokens := isin_tokens || address_street_word_id;
1825 -- merge word into address vector
1826 address_street_word_id := get_word_id(make_standard_name(isin[i]));
1827 IF address_street_word_id IS NOT NULL THEN
1828 nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
1835 nameaddress_vector := array_merge(nameaddress_vector, isin_tokens);
1837 -- RAISE WARNING 'ISIN: %', isin_tokens;
1839 -- Process area matches
1840 location_rank_search := 0;
1841 location_distance := 0;
1842 location_parent := NULL;
1843 -- added ourself as address already
1844 address_havelevel[NEW.rank_address] := true;
1845 --DEBUG: RAISE WARNING ' getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens;
1847 SELECT * from getNearFeatures(NEW.partition,
1848 CASE WHEN NEW.rank_search >= 26
1849 AND NEW.rank_search < 30
1851 ELSE place_centroid END,
1852 search_maxrank, isin_tokens)
1854 IF location.rank_address != location_rank_search THEN
1855 location_rank_search := location.rank_address;
1856 IF location.isguess THEN
1857 location_distance := location.distance * 1.5;
1859 IF location.rank_address <= 12 THEN
1860 -- for county and above, if we have an area consider that exact
1861 -- (It would be nice to relax the constraint for places close to
1862 -- the boundary but we'd need the exact geometry for that. Too
1864 location_distance = 0;
1866 -- Below county level remain slightly fuzzy.
1867 location_distance := location.distance * 0.5;
1871 CONTINUE WHEN location.keywords <@ location_keywords;
1874 IF location.distance < location_distance OR NOT location.isguess THEN
1875 location_keywords := location.keywords;
1877 location_isaddress := NOT address_havelevel[location.rank_address];
1878 IF location_isaddress AND location.isguess AND location_parent IS NOT NULL THEN
1879 location_isaddress := ST_Contains(location_parent,location.centroid);
1882 -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress;
1883 -- Add it to the list of search terms
1884 IF location.rank_search > 4 THEN
1885 nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
1887 INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address)
1888 VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address);
1890 IF location_isaddress THEN
1891 -- add postcode if we have one
1892 -- (If multiple postcodes are available, we end up with the highest ranking one.)
1893 IF location.postcode is not null THEN
1894 NEW.postcode = location.postcode;
1897 address_havelevel[location.rank_address] := true;
1898 IF NOT location.isguess THEN
1899 SELECT geometry FROM placex WHERE place_id = location.place_id INTO location_parent;
1902 IF location.rank_address > parent_place_id_rank THEN
1903 NEW.parent_place_id = location.place_id;
1904 parent_place_id_rank = location.rank_address;
1909 --DEBUG: RAISE WARNING ' Terms: (%) %',location, nameaddress_vector;
1914 --DEBUG: RAISE WARNING 'address computed';
1916 IF NEW.address is not null AND NEW.address ? 'postcode'
1917 AND NEW.address->'postcode' not similar to '%(,|;)%' THEN
1918 NEW.postcode := upper(trim(NEW.address->'postcode'));
1921 IF NEW.postcode is null AND NEW.rank_search > 8 THEN
1922 NEW.postcode := get_nearest_postcode(NEW.country_code, NEW.geometry);
1925 -- if we have a name add this to the name search table
1926 IF NEW.name IS NOT NULL THEN
1928 IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
1929 result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry);
1930 --DEBUG: RAISE WARNING 'added to location (full)';
1933 IF NEW.rank_search between 26 and 27 and NEW.class = 'highway' THEN
1934 result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.country_code, NEW.geometry);
1935 --DEBUG: RAISE WARNING 'insert into road location table (full)';
1938 result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);
1939 --DEBUG: RAISE WARNING 'added to serach name (full)';
1943 -- If we've not managed to pick up a better one - default centroid
1944 IF NEW.centroid IS NULL THEN
1945 NEW.centroid := place_centroid;
1948 --DEBUG: RAISE WARNING 'place update % % finsihed.', NEW.osm_type, NEW.osm_id;
1955 CREATE OR REPLACE FUNCTION placex_delete() RETURNS TRIGGER
1961 -- RAISE WARNING 'placex_delete % %',OLD.osm_type,OLD.osm_id;
1963 update placex set linked_place_id = null, indexed_status = 2 where linked_place_id = OLD.place_id and indexed_status = 0;
1964 --DEBUG: RAISE WARNING 'placex_delete:01 % %',OLD.osm_type,OLD.osm_id;
1965 update placex set linked_place_id = null where linked_place_id = OLD.place_id;
1966 --DEBUG: RAISE WARNING 'placex_delete:02 % %',OLD.osm_type,OLD.osm_id;
1968 IF OLD.rank_address < 30 THEN
1970 -- mark everything linked to this place for re-indexing
1971 --DEBUG: RAISE WARNING 'placex_delete:03 % %',OLD.osm_type,OLD.osm_id;
1972 UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = OLD.place_id
1973 and placex.place_id = place_addressline.place_id and indexed_status = 0 and place_addressline.isaddress;
1975 --DEBUG: RAISE WARNING 'placex_delete:04 % %',OLD.osm_type,OLD.osm_id;
1976 DELETE FROM place_addressline where address_place_id = OLD.place_id;
1978 --DEBUG: RAISE WARNING 'placex_delete:05 % %',OLD.osm_type,OLD.osm_id;
1979 b := deleteRoad(OLD.partition, OLD.place_id);
1981 --DEBUG: RAISE WARNING 'placex_delete:06 % %',OLD.osm_type,OLD.osm_id;
1982 update placex set indexed_status = 2 where parent_place_id = OLD.place_id and indexed_status = 0;
1983 --DEBUG: RAISE WARNING 'placex_delete:07 % %',OLD.osm_type,OLD.osm_id;
1984 -- reparenting also for OSM Interpolation Lines (and for Tiger?)
1985 update location_property_osmline set indexed_status = 2 where indexed_status = 0 and parent_place_id = OLD.place_id;
1989 --DEBUG: RAISE WARNING 'placex_delete:08 % %',OLD.osm_type,OLD.osm_id;
1991 IF OLD.rank_address < 26 THEN
1992 b := deleteLocationArea(OLD.partition, OLD.place_id, OLD.rank_search);
1995 --DEBUG: RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id;
1997 IF OLD.name is not null THEN
1998 b := deleteSearchName(OLD.partition, OLD.place_id);
2001 --DEBUG: RAISE WARNING 'placex_delete:10 % %',OLD.osm_type,OLD.osm_id;
2003 DELETE FROM place_addressline where place_id = OLD.place_id;
2005 --DEBUG: RAISE WARNING 'placex_delete:11 % %',OLD.osm_type,OLD.osm_id;
2007 -- remove from tables for special search
2008 classtable := 'place_classtype_' || OLD.class || '_' || OLD.type;
2009 SELECT count(*)>0 FROM pg_tables WHERE tablename = classtable and schemaname = current_schema() INTO b;
2011 EXECUTE 'DELETE FROM ' || classtable::regclass || ' WHERE place_id = $1' USING OLD.place_id;
2014 --DEBUG: RAISE WARNING 'placex_delete:12 % %',OLD.osm_type,OLD.osm_id;
2022 CREATE OR REPLACE FUNCTION place_delete() RETURNS TRIGGER
2028 --DEBUG: RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;
2030 -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through
2031 IF st_area(OLD.geometry) > 2 and st_isvalid(OLD.geometry) THEN
2032 SELECT bool_or(not (rank_address = 0 or rank_address > 26)) as ranked FROM placex WHERE osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type INTO has_rank;
2034 insert into import_polygon_delete (osm_type, osm_id, class, type) values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type);
2040 UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
2042 -- interpolations are special
2043 IF OLD.osm_type='W' and OLD.class = 'place' and OLD.type = 'houses' THEN
2044 UPDATE location_property_osmline set indexed_status = 100 where osm_id = OLD.osm_id; -- osm_id = wayid (=old.osm_id)
2053 CREATE OR REPLACE FUNCTION place_insert() RETURNS TRIGGER
2058 existingplacex RECORD;
2059 existingline RECORD;
2060 existinggeometry GEOMETRY;
2061 existingplace_id BIGINT;
2066 --DEBUG: RAISE WARNING '-----------------------------------------------------------------------------------';
2067 --DEBUG: RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry);
2068 -- filter wrong tupels
2069 IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN
2070 INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name, country_code, updated, errormessage, prevgeometry, newgeometry)
2071 VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.address->'country', now(), ST_IsValidReason(NEW.geometry), null, NEW.geometry);
2072 -- RAISE WARNING 'Invalid Geometry: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
2076 -- decide, whether it is an osm interpolation line => insert intoosmline, or else just placex
2077 IF NEW.class='place' and NEW.type='houses' and NEW.osm_type='W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN
2078 -- Have we already done this place?
2079 select * from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existing;
2081 -- Get the existing place_id
2082 select * from location_property_osmline where osm_id = NEW.osm_id INTO existingline;
2084 -- Handle a place changing type by removing the old data (this trigger is executed BEFORE INSERT of the NEW tupel)
2085 IF existing.osm_type IS NULL THEN
2086 DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;
2089 DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
2090 DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
2092 -- update method for interpolation lines: delete all old interpolation lines with same osm_id (update on place) and insert the new one(s) (they can be split up, if they have > 2 nodes)
2093 IF existingline.osm_id IS NOT NULL THEN
2094 delete from location_property_osmline where osm_id = NEW.osm_id;
2097 -- for interpolations invalidate all nodes on the line
2098 update placex p set indexed_status = 2
2099 from planet_osm_ways w
2100 where w.id = NEW.osm_id and p.osm_type = 'N' and p.osm_id = any(w.nodes);
2103 INSERT INTO location_property_osmline (osm_id, address, linegeo)
2104 VALUES (NEW.osm_id, NEW.address, NEW.geometry);
2107 IF existing.osm_type IS NULL THEN
2111 IF coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
2112 OR (coalesce(existing.extratags, ''::hstore) != coalesce(NEW.extratags, ''::hstore))
2113 OR existing.geometry::text != NEW.geometry::text
2118 address = NEW.address,
2119 extratags = NEW.extratags,
2120 admin_level = NEW.admin_level,
2121 geometry = NEW.geometry
2122 where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
2127 ELSE -- insert to placex
2129 -- Patch in additional country names
2130 IF NEW.admin_level = 2 AND NEW.type = 'administrative'
2131 AND NEW.address is not NULL AND NEW.address ? 'country' THEN
2132 SELECT name FROM country_name WHERE country_code = lower(NEW.address->'country') INTO existing;
2133 IF existing.name IS NOT NULL THEN
2134 NEW.name = existing.name || NEW.name;
2138 -- Have we already done this place?
2139 select * from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existing;
2141 -- Get the existing place_id
2142 select * from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existingplacex;
2144 -- Handle a place changing type by removing the old data
2145 -- My generated 'place' types are causing havok because they overlap with real keys
2146 -- TODO: move them to their own special purpose key/class to avoid collisions
2147 IF existing.osm_type IS NULL THEN
2148 DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;
2151 --DEBUG: RAISE WARNING 'Existing: %',existing.osm_id;
2152 --DEBUG: RAISE WARNING 'Existing PlaceX: %',existingplacex.place_id;
2155 IF existing.geometry is not null AND st_isvalid(existing.geometry)
2156 AND st_area(existing.geometry) > 0.02
2157 AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
2158 AND st_area(NEW.geometry) < st_area(existing.geometry)*0.5
2160 INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name, country_code, updated, errormessage, prevgeometry, newgeometry)
2161 VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.address->'country', now(),
2162 'Area reduced from '||st_area(existing.geometry)||' to '||st_area(NEW.geometry), existing.geometry, NEW.geometry);
2166 DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
2167 DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
2169 -- To paraphrase, if there isn't an existing item, OR if the admin level has changed
2170 IF existingplacex.osm_type IS NULL OR
2171 (existingplacex.class = 'boundary' AND
2172 ((coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.type = 'administrative') OR
2173 (existingplacex.type != NEW.type)))
2176 IF existingplacex.osm_type IS NOT NULL THEN
2177 -- sanity check: ignore admin_level changes on places with too many active children
2178 -- or we end up reindexing entire countries because somebody accidentally deleted admin_level
2179 --LIMIT INDEXING: SELECT count(*) FROM (SELECT 'a' FROM placex , place_addressline where address_place_id = existingplacex.place_id and placex.place_id = place_addressline.place_id and indexed_status = 0 and place_addressline.isaddress LIMIT 100001) sub INTO i;
2180 --LIMIT INDEXING: IF i > 100000 THEN
2181 --LIMIT INDEXING: RETURN null;
2182 --LIMIT INDEXING: END IF;
2185 IF existing.osm_type IS NOT NULL THEN
2186 -- pathological case caused by the triggerless copy into place during initial import
2187 -- force delete even for large areas, it will be reinserted later
2188 UPDATE place set geometry = ST_SetSRID(ST_Point(0,0), 4326) where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
2189 DELETE from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
2192 -- No - process it as a new insertion (hopefully of low rank or it will be slow)
2193 insert into placex (osm_type, osm_id, class, type, name,
2194 admin_level, address, extratags, geometry)
2195 values (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name,
2196 NEW.admin_level, NEW.address, NEW.extratags, NEW.geometry);
2198 --DEBUG: RAISE WARNING 'insert done % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,NEW.name;
2203 -- Special case for polygon shape changes because they tend to be large and we can be a bit clever about how we handle them
2204 IF existing.geometry::text != NEW.geometry::text
2205 AND ST_GeometryType(existing.geometry) in ('ST_Polygon','ST_MultiPolygon')
2206 AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
2209 -- Get the version of the geometry actually used (in placex table)
2210 select geometry from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type into existinggeometry;
2212 -- Performance limit
2213 IF st_area(NEW.geometry) < 0.000000001 AND st_area(existinggeometry) < 1 THEN
2215 -- re-index points that have moved in / out of the polygon, could be done as a single query but postgres gets the index usage wrong
2216 update placex set indexed_status = 2 where indexed_status = 0 and
2217 (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
2218 AND NOT (st_covers(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
2219 AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
2221 update placex set indexed_status = 2 where indexed_status = 0 and
2222 (st_covers(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
2223 AND NOT (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
2224 AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
2231 IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
2232 OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::text, '')
2233 OR coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
2234 OR coalesce(existing.admin_level, 15) != coalesce(NEW.admin_level, 15)
2235 OR existing.geometry::text != NEW.geometry::text
2240 address = NEW.address,
2241 extratags = NEW.extratags,
2242 admin_level = NEW.admin_level,
2243 geometry = NEW.geometry
2244 where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
2247 IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
2248 IF NEW.address is NULL OR NOT NEW.address ? 'postcode' THEN
2249 -- postcode was deleted, no longer retain in placex
2250 DELETE FROM placex where place_id = existingplacex.place_id;
2254 NEW.name := hstore('ref', NEW.address->'postcode');
2257 IF NEW.class in ('boundary')
2258 AND ST_GeometryType(NEW.geometry) not in ('ST_Polygon','ST_MultiPolygon') THEN
2259 DELETE FROM placex where place_id = existingplacex.place_id;
2265 address = NEW.address,
2266 parent_place_id = null,
2267 extratags = NEW.extratags,
2268 admin_level = NEW.admin_level,
2270 geometry = NEW.geometry
2271 where place_id = existingplacex.place_id;
2273 -- if a node(=>house), which is part of a interpolation line, changes (e.g. the street attribute) => mark this line for reparenting
2274 -- (already here, because interpolation lines are reindexed before nodes, so in the second call it would be too late)
2275 IF NEW.osm_type='N' and NEW.class='place' and NEW.type='house' THEN
2276 -- Is this node part of an interpolation line? search for it in location_property_osmline and mark the interpolation line for reparenting
2277 update location_property_osmline p set indexed_status = 2 from planet_osm_ways w where p.linegeo && NEW.geometry and p.osm_id = w.id and NEW.osm_id = any(w.nodes);
2280 -- linked places should get potential new naming and addresses
2281 IF existingplacex.linked_place_id is not NULL THEN
2284 extratags = p.extratags,
2287 where x.place_id = existingplacex.linked_place_id
2288 and x.indexed_status = 0
2289 and x.osm_type = p.osm_type
2290 and x.osm_id = p.osm_id
2291 and x.class = p.class;
2296 -- Abort the add (we modified the existing place instead)
2301 $$ LANGUAGE plpgsql;
2304 CREATE OR REPLACE FUNCTION get_name_by_language(name hstore, languagepref TEXT[]) RETURNS TEXT
2309 IF name is null THEN
2313 FOR j IN 1..array_upper(languagepref,1) LOOP
2314 IF name ? languagepref[j] THEN
2315 result := trim(name->languagepref[j]);
2316 IF result != '' THEN
2322 -- anything will do as a fallback - just take the first name type thing there is
2323 RETURN trim((avals(name))[1]);
2326 LANGUAGE plpgsql IMMUTABLE;
2328 --housenumber only needed for tiger data
2329 CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT, housenumber INTEGER, languagepref TEXT[]) RETURNS TEXT
2341 FOR location IN select * from get_addressdata(for_place_id, housenumber) where isaddress order by rank_address desc LOOP
2342 currresult := trim(get_name_by_language(location.name, languagepref));
2343 IF currresult != prevresult AND currresult IS NOT NULL AND result[(100 - location.rank_address)] IS NULL THEN
2344 result[(100 - location.rank_address)] := trim(get_name_by_language(location.name, languagepref));
2345 prevresult := currresult;
2349 RETURN array_to_string(result,', ');
2354 DROP TYPE IF EXISTS addressline CASCADE;
2355 create type addressline as (
2362 admin_level INTEGER,
2365 rank_address INTEGER,
2369 CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber INTEGER) RETURNS setof addressline
2372 for_place_id BIGINT;
2377 countrylocation RECORD;
2378 searchcountrycode varchar(2);
2379 searchhousenumber TEXT;
2380 searchhousename HSTORE;
2381 searchrankaddress INTEGER;
2382 searchpostcode TEXT;
2388 -- first query osmline (interpolation lines)
2389 select parent_place_id, country_code, 30, postcode, null, 'place', 'house' from location_property_osmline
2390 WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber
2391 INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
2392 IF for_place_id IS NOT NULL THEN
2393 searchhousenumber = in_housenumber::text;
2396 --then query tiger data
2397 -- %NOTIGERDATA% IF 0 THEN
2398 IF for_place_id IS NULL THEN
2399 select parent_place_id,'us', 30, postcode, null, 'place', 'house' from location_property_tiger
2400 WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber
2401 INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
2402 IF for_place_id IS NOT NULL THEN
2403 searchhousenumber = in_housenumber::text;
2406 -- %NOTIGERDATA% END IF;
2408 -- %NOAUXDATA% IF 0 THEN
2409 IF for_place_id IS NULL THEN
2410 select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux
2411 WHERE place_id = in_place_id
2412 INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
2414 -- %NOAUXDATA% END IF;
2417 IF for_place_id IS NULL THEN
2418 select parent_place_id, country_code, rank_address, postcode, 'place', 'postcode'
2419 FROM location_postcode
2420 WHERE place_id = in_place_id
2421 INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode, searchclass, searchtype;
2424 IF for_place_id IS NULL THEN
2425 select parent_place_id, country_code, housenumber, rank_search, postcode, name, class, type from placex
2426 WHERE place_id = in_place_id and rank_search > 27
2427 INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
2430 IF for_place_id IS NULL THEN
2431 select coalesce(linked_place_id, place_id), country_code,
2432 housenumber, rank_search, postcode, null
2433 from placex where place_id = in_place_id
2434 INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename;
2437 --RAISE WARNING '% % % %',searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode;
2440 hadcountry := false;
2442 select placex.place_id, osm_type, osm_id, name,
2443 class, type, admin_level, true as isaddress,
2444 CASE WHEN rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
2445 0 as distance, country_code, postcode
2447 where place_id = for_place_id
2449 --RAISE WARNING '%',location;
2450 IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN
2451 searchcountrycode := location.country_code;
2453 IF location.type in ('postcode', 'postal_code') THEN
2454 location.isaddress := FALSE;
2455 ELSEIF location.rank_address = 4 THEN
2458 IF location.rank_address < 4 AND NOT hadcountry THEN
2459 select name from country_name where country_code = searchcountrycode limit 1 INTO countryname;
2460 IF countryname IS NOT NULL THEN
2461 countrylocation := ROW(null, null, null, countryname, 'place', 'country', null, true, true, 4, 0)::addressline;
2462 RETURN NEXT countrylocation;
2465 countrylocation := ROW(location.place_id, location.osm_type, location.osm_id, location.name, location.class,
2466 location.type, location.admin_level, true, location.isaddress, location.rank_address,
2467 location.distance)::addressline;
2468 RETURN NEXT countrylocation;
2469 found := location.rank_address;
2473 select placex.place_id, osm_type, osm_id, name,
2474 CASE WHEN extratags ? 'place' THEN 'place' ELSE class END as class,
2475 CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as type,
2476 admin_level, fromarea, isaddress,
2477 CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
2478 distance,country_code,postcode
2479 from place_addressline join placex on (address_place_id = placex.place_id)
2480 where place_addressline.place_id = for_place_id
2481 and (cached_rank_address > 0 AND cached_rank_address < searchrankaddress)
2482 and address_place_id != for_place_id and linked_place_id is null
2483 and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode)
2484 order by rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc
2486 --RAISE WARNING '%',location;
2487 IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN
2488 searchcountrycode := location.country_code;
2490 IF location.type in ('postcode', 'postal_code') THEN
2491 location.isaddress := FALSE;
2493 IF location.rank_address = 4 AND location.isaddress THEN
2496 IF location.rank_address < 4 AND NOT hadcountry THEN
2497 select name from country_name where country_code = searchcountrycode limit 1 INTO countryname;
2498 IF countryname IS NOT NULL THEN
2499 countrylocation := ROW(null, null, null, countryname, 'place', 'country', null, true, true, 4, 0)::addressline;
2500 RETURN NEXT countrylocation;
2503 countrylocation := ROW(location.place_id, location.osm_type, location.osm_id, location.name, location.class,
2504 location.type, location.admin_level, location.fromarea, location.isaddress, location.rank_address,
2505 location.distance)::addressline;
2506 RETURN NEXT countrylocation;
2507 found := location.rank_address;
2511 select name from country_name where country_code = searchcountrycode limit 1 INTO countryname;
2512 --RAISE WARNING '% % %',found,searchcountrycode,countryname;
2513 IF countryname IS NOT NULL THEN
2514 location := ROW(null, null, null, countryname, 'place', 'country', null, true, true, 4, 0)::addressline;
2515 RETURN NEXT location;
2519 IF searchcountrycode IS NOT NULL THEN
2520 location := ROW(null, null, null, hstore('ref', searchcountrycode), 'place', 'country_code', null, true, false, 4, 0)::addressline;
2521 RETURN NEXT location;
2524 IF searchhousename IS NOT NULL THEN
2525 location := ROW(in_place_id, null, null, searchhousename, searchclass, searchtype, null, true, true, 29, 0)::addressline;
2526 RETURN NEXT location;
2529 IF searchhousenumber IS NOT NULL THEN
2530 location := ROW(in_place_id, null, null, hstore('ref', searchhousenumber), 'place', 'house_number', null, true, true, 28, 0)::addressline;
2531 RETURN NEXT location;
2534 IF searchpostcode IS NOT NULL THEN
2535 location := ROW(null, null, null, hstore('ref', searchpostcode), 'place', 'postcode', null, true, true, 5, 0)::addressline;
2536 RETURN NEXT location;
2545 CREATE OR REPLACE FUNCTION get_searchrank_label(rank INTEGER) RETURNS TEXT
2551 ELSEIF rank < 4 THEN
2553 ELSEIF rank < 8 THEN
2555 ELSEIF rank < 12 THEN
2557 ELSEIF rank < 16 THEN
2559 ELSEIF rank = 16 THEN
2561 ELSEIF rank = 17 THEN
2562 RETURN 'Town / Island';
2563 ELSEIF rank = 18 THEN
2564 RETURN 'Village / Hamlet';
2565 ELSEIF rank = 20 THEN
2567 ELSEIF rank = 21 THEN
2568 RETURN 'Postcode Area';
2569 ELSEIF rank = 22 THEN
2570 RETURN 'Croft / Farm / Locality / Islet';
2571 ELSEIF rank = 23 THEN
2572 RETURN 'Postcode Area';
2573 ELSEIF rank = 25 THEN
2574 RETURN 'Postcode Point';
2575 ELSEIF rank = 26 THEN
2576 RETURN 'Street / Major Landmark';
2577 ELSEIF rank = 27 THEN
2578 RETURN 'Minory Street / Path';
2579 ELSEIF rank = 28 THEN
2580 RETURN 'House / Building';
2582 RETURN 'Other: '||rank;
2589 CREATE OR REPLACE FUNCTION get_addressrank_label(rank INTEGER) RETURNS TEXT
2595 ELSEIF rank < 2 THEN
2597 ELSEIF rank < 4 THEN
2599 ELSEIF rank = 5 THEN
2601 ELSEIF rank < 8 THEN
2603 ELSEIF rank < 12 THEN
2605 ELSEIF rank < 16 THEN
2607 ELSEIF rank = 16 THEN
2609 ELSEIF rank = 17 THEN
2610 RETURN 'Town / Village / Hamlet';
2611 ELSEIF rank = 20 THEN
2613 ELSEIF rank = 21 THEN
2614 RETURN 'Postcode Area';
2615 ELSEIF rank = 22 THEN
2616 RETURN 'Croft / Farm / Locality / Islet';
2617 ELSEIF rank = 23 THEN
2618 RETURN 'Postcode Area';
2619 ELSEIF rank = 25 THEN
2620 RETURN 'Postcode Point';
2621 ELSEIF rank = 26 THEN
2622 RETURN 'Street / Major Landmark';
2623 ELSEIF rank = 27 THEN
2624 RETURN 'Minory Street / Path';
2625 ELSEIF rank = 28 THEN
2626 RETURN 'House / Building';
2628 RETURN 'Other: '||rank;
2635 CREATE OR REPLACE FUNCTION aux_create_property(pointgeo GEOMETRY, in_housenumber TEXT,
2636 in_street TEXT, in_isin TEXT, in_postcode TEXT, in_countrycode char(2)) RETURNS INTEGER
2641 place_centroid GEOMETRY;
2642 out_partition INTEGER;
2643 out_parent_place_id BIGINT;
2645 address_street_word_id INTEGER;
2650 place_centroid := ST_Centroid(pointgeo);
2651 out_partition := get_partition(in_countrycode);
2652 out_parent_place_id := null;
2654 address_street_word_id := get_name_id(make_standard_name(in_street));
2655 IF address_street_word_id IS NOT NULL THEN
2656 FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
2657 out_parent_place_id := location.place_id;
2661 IF out_parent_place_id IS NULL THEN
2662 FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
2663 out_parent_place_id := location.place_id;
2667 out_postcode := in_postcode;
2668 IF out_postcode IS NULL THEN
2669 SELECT postcode from placex where place_id = out_parent_place_id INTO out_postcode;
2671 -- XXX look into postcode table
2674 insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)
2675 values (nextval('seq_place'), out_partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
2676 newpoints := newpoints + 1;
2683 CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], member TEXT) RETURNS TEXT[]
2690 FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
2691 IF members[i+1] = member THEN
2692 result := result || members[i];
2701 CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], memberLabels TEXT[]) RETURNS SETOF TEXT
2707 FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
2708 IF members[i+1] = ANY(memberLabels) THEN
2709 RETURN NEXT members[i];
2718 -- See: http://stackoverflow.com/questions/6410088/how-can-i-mimic-the-php-urldecode-function-in-postgresql
2719 CREATE OR REPLACE FUNCTION decode_url_part(p varchar) RETURNS varchar
2721 SELECT convert_from(CAST(E'\\x' || array_to_string(ARRAY(
2722 SELECT CASE WHEN length(r.m[1]) = 1 THEN encode(convert_to(r.m[1], 'SQL_ASCII'), 'hex') ELSE substring(r.m[1] from 2 for 2) END
2723 FROM regexp_matches($1, '%[0-9a-f][0-9a-f]|.', 'gi') AS r(m)
2724 ), '') AS bytea), 'UTF8');
2726 LANGUAGE SQL IMMUTABLE STRICT;
2728 CREATE OR REPLACE FUNCTION catch_decode_url_part(p varchar) RETURNS varchar
2732 RETURN decode_url_part(p);
2734 WHEN others THEN return null;
2737 LANGUAGE plpgsql IMMUTABLE;
2739 DROP TYPE wikipedia_article_match CASCADE;
2740 create type wikipedia_article_match as (
2746 CREATE OR REPLACE FUNCTION get_wikipedia_match(extratags HSTORE, country_code varchar(2)) RETURNS wikipedia_article_match
2752 wiki_article_title TEXT;
2753 wiki_article_language TEXT;
2754 result wikipedia_article_match;
2756 langs := ARRAY['english','country','ar','bg','ca','cs','da','de','en','es','eo','eu','fa','fr','ko','hi','hr','id','it','he','lt','hu','ms','nl','ja','no','pl','pt','kk','ro','ru','sk','sl','sr','fi','sv','tr','uk','vi','vo','war','zh'];
2758 WHILE langs[i] IS NOT NULL LOOP
2759 wiki_article := extratags->(case when langs[i] in ('english','country') THEN 'wikipedia' ELSE 'wikipedia:'||langs[i] END);
2760 IF wiki_article is not null THEN
2761 wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/wiki/',E'\\2:');
2762 wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/w/index.php\\?title=',E'\\2:');
2763 wiki_article := regexp_replace(wiki_article,E'^(.*?)/([a-z]{2,3})/wiki/',E'\\2:');
2764 --wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:');
2765 wiki_article := replace(wiki_article,' ','_');
2766 IF strpos(wiki_article, ':') IN (3,4) THEN
2767 wiki_article_language := lower(trim(split_part(wiki_article, ':', 1)));
2768 wiki_article_title := trim(substr(wiki_article, strpos(wiki_article, ':')+1));
2770 wiki_article_title := trim(wiki_article);
2771 wiki_article_language := CASE WHEN langs[i] = 'english' THEN 'en' WHEN langs[i] = 'country' THEN get_country_language_code(country_code) ELSE langs[i] END;
2774 select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
2775 from wikipedia_article
2776 where language = wiki_article_language and
2777 (title = wiki_article_title OR title = catch_decode_url_part(wiki_article_title) OR title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
2779 select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
2780 from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title)
2781 where wikipedia_redirect.language = wiki_article_language and
2782 (from_title = wiki_article_title OR from_title = catch_decode_url_part(wiki_article_title) OR from_title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
2783 order by importance desc limit 1 INTO result;
2785 IF result.language is not null THEN
2796 CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT, maxdepth INTEGER)
2797 RETURNS SETOF GEOMETRY
2811 remainingdepth INTEGER;
2816 -- RAISE WARNING 'quad_split_geometry: maxarea=%, depth=%',maxarea,maxdepth;
2818 IF (ST_GeometryType(geometry) not in ('ST_Polygon','ST_MultiPolygon') OR NOT ST_IsValid(geometry)) THEN
2819 RETURN NEXT geometry;
2823 remainingdepth := maxdepth - 1;
2824 area := ST_AREA(geometry);
2825 IF remainingdepth < 1 OR area < maxarea THEN
2826 RETURN NEXT geometry;
2830 xmin := st_xmin(geometry);
2831 xmax := st_xmax(geometry);
2832 ymin := st_ymin(geometry);
2833 ymax := st_ymax(geometry);
2834 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(ymin,xmin),ST_Point(ymax,xmax)),4326);
2836 -- if the geometry completely covers the box don't bother to slice any more
2837 IF ST_AREA(secbox) = area THEN
2838 RETURN NEXT geometry;
2842 xmid := (xmin+xmax)/2;
2843 ymid := (ymin+ymax)/2;
2846 FOR seg IN 1..4 LOOP
2849 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymin),ST_Point(xmid,ymid)),4326);
2852 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymid),ST_Point(xmid,ymax)),4326);
2855 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymin),ST_Point(xmax,ymid)),4326);
2858 secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymid),ST_Point(xmax,ymax)),4326);
2861 IF st_intersects(geometry, secbox) THEN
2862 secgeo := st_intersection(geometry, secbox);
2863 IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN
2864 FOR geo IN select quad_split_geometry(secgeo, maxarea, remainingdepth) as geom LOOP
2865 IF NOT ST_IsEmpty(geo.geom) AND ST_GeometryType(geo.geom) in ('ST_Polygon','ST_MultiPolygon') THEN
2867 RETURN NEXT geo.geom;
2879 CREATE OR REPLACE FUNCTION split_geometry(geometry GEOMETRY)
2880 RETURNS SETOF GEOMETRY
2885 -- 10000000000 is ~~ 1x1 degree
2886 FOR geo IN select quad_split_geometry(geometry, 0.25, 20) as geom LOOP
2887 RETURN NEXT geo.geom;
2895 CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT) RETURNS BOOLEAN
2899 osmtype character(1);
2903 SELECT osm_type, osm_id, class, type FROM placex WHERE place_id = placeid INTO osmtype, osmid, pclass, ptype;
2904 DELETE FROM import_polygon_delete where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
2905 DELETE FROM import_polygon_error where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
2906 -- force delete from place/placex by making it a very small geometry
2907 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;
2908 DELETE FROM place where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
2915 CREATE OR REPLACE FUNCTION place_force_update(placeid BIGINT) RETURNS BOOLEAN
2923 UPDATE placex SET indexed_status = 2 WHERE place_id = placeid;
2924 SELECT geometry, rank_search FROM placex WHERE place_id = placeid INTO placegeom, rank;
2925 IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN
2926 IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
2927 FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
2928 update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
2929 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'));
2930 update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
2931 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'));
2937 ELSEIF rank < 18 THEN
2939 ELSEIF rank < 20 THEN
2941 ELSEIF rank = 21 THEN
2943 ELSEIF rank < 24 THEN
2945 ELSEIF rank < 26 THEN
2946 diameter := 0.002; -- 100 to 200 meters
2947 ELSEIF rank < 28 THEN
2948 diameter := 0.001; -- 50 to 100 meters
2950 IF diameter > 0 THEN
2952 -- roads may cause reparenting for >27 rank places
2953 update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter);
2954 ELSEIF rank >= 16 THEN
2955 -- up to rank 16, street-less addresses may need reparenting
2956 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');
2958 -- for all other places the search terms may change as well
2959 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);