+ -- see if we have any special relation members
+ select members from planet_osm_rels where id = NEW.osm_id INTO relation_members;
+
+-- RAISE WARNING 'get_osm_rel_members, label';
+ IF relation_members IS NOT NULL THEN
+ FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
+
+ FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
+ and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 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 label name, re-init word vector
+ IF NOT linkedPlacex.name IS NULL THEN
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := array_merge(name_vector, make_keywords(linkedPlacex.name));
+ END IF;
+
+ -- merge in extra tags
+ IF NOT linkedPlacex.extratags IS NULL THEN
+ NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+ END IF;
+
+ IF NOT NEW.extratags ? linkedPlacex.class THEN
+ NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type);
+ END IF;
+
+ -- 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 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
+
+ FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
+ and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP
+
+ -- 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_address = linkedPlacex.rank_address 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
+ IF NOT linkedPlacex.name IS NULL THEN
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := make_keywords(NEW.name);
+ END IF;
+
+ -- merge in extra tags
+ IF NOT linkedPlacex.extratags IS NULL THEN
+ NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+ END IF;
+
+ IF NOT NEW.extratags ? linkedPlacex.class THEN
+ NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type);
+ END IF;
+
+ -- mark the linked place (excludes from search results)
+ UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+
+ -- keep a note of the node id in case we need it for wikipedia in a bit
+ linked_node_id := linkedPlacex.osm_id;
+ END IF;
+
+ END LOOP;
+
+ END LOOP;
+
+ END IF;
+ 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_address = NEW.rank_address
+ AND placex.place_id != NEW.place_id
+ AND placex.osm_type = 'N'::char(1) AND placex.rank_search < 26
+ AND st_covers(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;
+
+ IF NOT NEW.extratags ? linkedPlacex.class THEN
+ NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type);
+ END IF;
+
+ -- mark the linked place (excludes from search results)
+ UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+
+ -- keep a note of the node id in case we need it for wikipedia in a bit
+ linked_node_id := linkedPlacex.osm_id;
+ END LOOP;
+ END IF;
+
+ IF NEW.centroid IS NOT NULL THEN
+ place_centroid := NEW.centroid;
+ -- Place might have had only a name tag before but has now received translations
+ -- from the linked place. Make sure a name tag for the default language exists in
+ -- this case.
+ IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
+ default_language := get_country_language_code(NEW.calculated_country_code);
+ IF default_language IS NOT NULL THEN
+ IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
+ NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
+ ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
+ NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
+ END IF;
+ END IF;
+ END IF;
+ END IF;
+
+ -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance
+ IF NEW.importance is null THEN
+ select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance;
+ END IF;
+ -- Still null? how about looking it up by the node id
+ IF NEW.importance IS NULL THEN
+ 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;
+ END IF;
+
+ END IF;
+
+ -- make sure all names are in the word table
+ IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN
+ perform create_country(NEW.name, lower(NEW.country_code));
+ END IF;
+
+ NEW.parent_place_id = 0;
+ parent_place_id_rank = 0;
+
+ -- convert isin to array of tokenids
+ isin_tokens := '{}'::int[];
+ IF NEW.isin IS NOT NULL THEN
+ isin := regexp_split_to_array(NEW.isin, E'[;,]');
+ IF array_upper(isin, 1) IS NOT NULL THEN
projects / nominatim.git / blobdiff