+ -- 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;
+
+ -- 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;
+ 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;
+
+ 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
+ FOR i IN 1..array_upper(isin, 1) LOOP
+ address_street_word_id := get_name_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+ isin_tokens := isin_tokens || address_street_word_id;
+ END IF;
+ END LOOP;
+ END IF;
+ END IF;
+ IF NEW.postcode IS NOT NULL THEN
+ isin := regexp_split_to_array(NEW.postcode, E'[;,]');
+ IF array_upper(isin, 1) IS NOT NULL THEN
+ FOR i IN 1..array_upper(isin, 1) LOOP
+ address_street_word_id := get_name_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+ isin_tokens := isin_tokens || address_street_word_id;
+ END IF;
+ END LOOP;
+ END IF;
+ END IF;
+-- RAISE WARNING 'ISIN: %', isin_tokens;
+
+ -- Process area matches
+ location_rank_search := 0;
+ location_distance := 0;
+ location_parent := NULL;
+ -- added ourself as address already
+ address_havelevel[NEW.rank_address] := true;
+ -- RAISE WARNING ' getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens;
+ FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
+
+--RAISE WARNING ' AREA: %',location;
+
+ IF location.rank_address != location_rank_search THEN
+ location_rank_search := location.rank_address;
+ location_distance := location.distance * 1.5;
+ END IF;
+
+ IF location.distance < location_distance OR NOT location.isguess THEN
+
+ location_isaddress := NOT address_havelevel[location.rank_address];
+ IF location_isaddress AND location.isguess AND location_parent IS NOT NULL THEN
+ location_isaddress := ST_Contains(location_parent,location.centroid);
+ END IF;
+
+ -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress;
+ -- Add it to the list of search terms
+ nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
+ INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address);
+
+ IF location_isaddress THEN
+ address_havelevel[location.rank_address] := true;
+ IF NOT location.isguess THEN
+ SELECT geometry FROM placex WHERE place_id = location.place_id INTO location_parent;
+ END IF;
+ END IF;
+
+--RAISE WARNING ' Terms: (%) %',location, nameaddress_vector;
+
+ IF location.rank_address > parent_place_id_rank THEN
+ NEW.parent_place_id = location.place_id;
+ parent_place_id_rank = location.rank_address;
+ END IF;
+
+ END IF;
+
+ END LOOP;
+
+ -- try using the isin value to find parent places
+ IF array_upper(isin_tokens, 1) IS NOT NULL THEN
+ FOR i IN 1..array_upper(isin_tokens, 1) LOOP
+--RAISE WARNING ' getNearestNamedFeature: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
+ IF NOT ARRAY[isin_tokens[i]] <@ nameaddress_vector THEN
+
+ FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
+
+ --RAISE WARNING ' ISIN: %',location;
+
+ nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
+ INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
+ address_havelevel[location.rank_address] := true;
+
+ IF location.rank_address > parent_place_id_rank THEN
+ NEW.parent_place_id = location.place_id;
+ parent_place_id_rank = location.rank_address;
+ END IF;
+
+ END LOOP;
+
+ END IF;
+
+ END LOOP;
+ END IF;
+
+ -- for long ways we should add search terms for the entire length
+ IF st_length(NEW.geometry) > 0.05 THEN
+
+ location_rank_search := 0;
+ location_distance := 0;
+
+ FOR location IN SELECT * from getNearFeatures(NEW.partition, NEW.geometry, search_maxrank, isin_tokens) LOOP
+
+ IF location.rank_address != location_rank_search THEN
+ location_rank_search := location.rank_address;
+ location_distance := location.distance * 1.5;
+ END IF;
+
+ IF location.distance < location_distance THEN
+
+ -- Add it to the list of search terms
+ nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
+ INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, false, location.distance, location.rank_address);
+
+ END IF;
+
+ END LOOP;
+
+ END IF;
+
+ -- if we have a name add this to the name search table
+ IF NEW.name IS NOT NULL THEN
+
+ IF NEW.rank_search <= 25 THEN
+ result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
+ END IF;
+
+ IF NEW.rank_search between 26 and 27 and NEW.class = 'highway' THEN
+ result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.calculated_country_code, NEW.geometry);
+ END IF;
+
+ result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid);
+
+-- INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, 0, NEW.calculated_country_code, name_vector, nameaddress_vector, place_centroid);
+ END IF;
+
+ -- If we've not managed to pick up a better one - default centroid
+ IF NEW.centroid IS NULL THEN
+ NEW.centroid := place_centroid;
+ END IF;
+
+ END IF;
+
+ RETURN NEW;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION placex_delete() RETURNS TRIGGER
+ AS $$
+DECLARE
+ b BOOLEAN;
+ classtable TEXT;
+BEGIN
+ -- RAISE WARNING 'placex_delete % %',OLD.osm_type,OLD.osm_id;
+
+ update placex set linked_place_id = null, indexed_status = 2 where linked_place_id = OLD.place_id and indexed_status = 0;
+ --DEBUG: RAISE WARNING 'placex_delete:01 % %',OLD.osm_type,OLD.osm_id;
+ update placex set linked_place_id = null where linked_place_id = OLD.place_id;
+ --DEBUG: RAISE WARNING 'placex_delete:02 % %',OLD.osm_type,OLD.osm_id;
+
+ IF OLD.rank_address < 30 THEN
+
+ -- mark everything linked to this place for re-indexing
+ --DEBUG: RAISE WARNING 'placex_delete:03 % %',OLD.osm_type,OLD.osm_id;
+ UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = OLD.place_id
+ and placex.place_id = place_addressline.place_id and indexed_status = 0;
+
+ --DEBUG: RAISE WARNING 'placex_delete:04 % %',OLD.osm_type,OLD.osm_id;
+ DELETE FROM place_addressline where address_place_id = OLD.place_id;
+
+ --DEBUG: RAISE WARNING 'placex_delete:05 % %',OLD.osm_type,OLD.osm_id;
+ b := deleteRoad(OLD.partition, OLD.place_id);
+
+ --DEBUG: RAISE WARNING 'placex_delete:06 % %',OLD.osm_type,OLD.osm_id;
+ update placex set indexed_status = 2 where parent_place_id = OLD.place_id and indexed_status = 0;
+ --DEBUG: RAISE WARNING 'placex_delete:07 % %',OLD.osm_type,OLD.osm_id;
+
+ END IF;
+
+ --DEBUG: RAISE WARNING 'placex_delete:08 % %',OLD.osm_type,OLD.osm_id;
+
+ IF OLD.rank_address < 26 THEN
+ b := deleteLocationArea(OLD.partition, OLD.place_id);
+ END IF;
+
+ --DEBUG: RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id;
+
+ IF OLD.name is not null THEN
+ b := deleteSearchName(OLD.partition, OLD.place_id);
+ END IF;
+
+ --DEBUG: RAISE WARNING 'placex_delete:10 % %',OLD.osm_type,OLD.osm_id;
+
+ DELETE FROM place_addressline where place_id = OLD.place_id;
+
+ --DEBUG: RAISE WARNING 'placex_delete:11 % %',OLD.osm_type,OLD.osm_id;
+
+ -- remove from tables for special search
+ classtable := 'place_classtype_' || OLD.class || '_' || OLD.type;
+ SELECT count(*)>0 FROM pg_tables WHERE tablename = classtable INTO b;
+ IF b THEN
+ EXECUTE 'DELETE FROM ' || classtable::regclass || ' WHERE place_id = $1' USING OLD.place_id;
+ END IF;
+
+ --DEBUG: RAISE WARNING 'placex_delete:12 % %',OLD.osm_type,OLD.osm_id;
projects / nominatim.git / blobdiff