+-- RAISE WARNING ' INDEXING: %',NEW;
+
+ IF NEW.osm_type = 'R' AND NEW.rank_search < 26 THEN
+
+ -- see if we have any special relation members
+ select members from planet_osm_rels where id = NEW.osm_id INTO relation_members;
+
+ FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
+
+ select * from placex where osm_type = upper(substring(relMember.member,1,1))
+ and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
+
+ -- If we don't already have one use this as the centre point of the geometry
+ IF NEW.centroid IS NULL THEN
+ NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
+ END IF;
+
+ -- merge in the label name, re-init word vector
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := make_keywords(NEW.name);
+
+ -- merge in extra tags
+ NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+
+ -- mark the linked place (excludes from search results)
+ UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+
+ END LOOP;
+
+ IF NEW.centroid IS NULL THEN
+
+ FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
+
+ select * from placex where osm_type = upper(substring(relMember.member,1,1))
+ and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
+
+ -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
+ -- But that can be fixed by explicitly setting the label in the data
+ IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name')
+ AND NEW.rank_search = linkedPlacex.rank_search THEN
+
+ -- If we don't already have one use this as the centre point of the geometry
+ IF NEW.centroid IS NULL THEN
+ NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
+ END IF;
+
+ -- merge in the name, re-init word vector
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := make_keywords(NEW.name);
+
+ -- merge in extra tags
+ NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+
+ -- mark the linked place (excludes from search results)
+ UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+ END IF;
+
+ END LOOP;
+
+ END IF;
+
+ -- not found one yet? how about doing a name search
+ IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' THEN
+
+ FOR linkedPlacex IN select placex.* from placex WHERE
+ make_standard_name(name->'name') = make_standard_name(NEW.name->'name')
+ AND placex.rank_search = NEW.rank_search
+ AND placex.place_id != NEW.place_id
+ AND osm_type = 'N'
+ AND st_contains(NEW.geometry, placex.geometry)
+ LOOP
+
+ -- If we don't already have one use this as the centre point of the geometry
+ IF NEW.centroid IS NULL THEN
+ NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
+ END IF;
+
+ -- merge in the name, re-init word vector
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := make_keywords(NEW.name);
+
+ -- merge in extra tags
+ NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+
+ -- mark the linked place (excludes from search results)
+ UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+
+ END LOOP;
+ END IF;
+
+ IF NEW.centroid IS NOT NULL THEN
+ place_centroid := NEW.centroid;
+ END IF;
+
+ -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance
+ -- WARNING: duplicate of code above (yuk!)
+ IF NEW.importance is null AND NEW.extratags?'wikipedia' THEN
+ wiki_article := replace(regexp_replace(NEW.extratags->'wikipedia',E'(.*?)([a-z]+).wikipedia.org/wiki/',E'\\2:'),' ','_');
+ wiki_article_title := split_part(wiki_article, ':', 2);
+ IF wiki_article_title IS NULL OR wiki_article_title = '' THEN
+ wiki_article_title := wiki_article;
+ wiki_article_language := 'en';
+ ELSE
+ wiki_article_language := lower(split_part(wiki_article, ':', 1));
+ END IF;
+
+ select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title
+ from wikipedia_article
+ where language = wiki_article_language and
+ (title = wiki_article_title OR title = decode_url_part(wiki_article_title) OR title = replace(decode_url_part(wiki_article_title),E'\\',''))
+ UNION ALL
+ select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title
+ from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title)
+ where wikipedia_redirect.language = wiki_article_language and
+ (from_title = wiki_article_title OR from_title = decode_url_part(wiki_article_title) OR from_title = replace(decode_url_part(wiki_article_title),E'\\',''))
+ order by importance asc limit 1 INTO NEW.importance,NEW.wikipedia;
+
+ END IF;
+
+ END IF;
+
+ NEW.parent_place_id = 0;
+ parent_place_id_rank = 0;
projects / nominatim.git / blobdiff