LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION create_interpolation(wayid INTEGER, interpolationtype TEXT) RETURNS INTEGER
+CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT) RETURNS INTEGER
AS $$
DECLARE
NEW.place_id := nextval('seq_place');
NEW.indexed_status := 1; --STATUS_NEW
- NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+ IF NEW.rank_search >= 4 THEN
+ NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+ ELSE
+ NEW.country_code := NULL;
+ END IF;
+
NEW.partition := get_partition(NEW.geometry, NEW.country_code);
NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
NEW.name := NEW.name || (('name:'||default_language) => (NEW.name -> 'name'));
ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
- NEW.name := NEW.name || ('name' => (NEW.name -> 'name:'||default_language));
+ NEW.name := NEW.name || ('name' => (NEW.name -> ('name:'||default_language)));
END IF;
END IF;
END IF;
-- By doing in postgres we have the country available to us - currently only used for postcode
IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
+ IF NEW.postcode IS NULL THEN
+ -- most likely just a part of a multipolygon postcode boundary, throw it away
+ RETURN NULL;
+ END IF;
+
NEW.name := 'ref'=>NEW.postcode;
IF NEW.country_code = 'gb' THEN
NEW.rank_search := 8;
NEW.rank_address := NEW.rank_search;
ELSEIF NEW.type in ('region') THEN
- NEW.rank_search := 10;
- NEW.rank_address := NEW.rank_search;
+ NEW.rank_search := 18; -- dropped from previous value of 10
+ NEW.rank_address := 0; -- So badly miss-used that better to just drop it!
ELSEIF NEW.type in ('county') THEN
NEW.rank_search := 12;
NEW.rank_address := NEW.rank_search;
ELSEIF NEW.type in ('suburb','croft','subdivision') THEN
NEW.rank_search := 20;
NEW.rank_address := NEW.rank_search;
- ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling') THEN
+ ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling','mountain_pass') THEN
NEW.rank_search := 20;
NEW.rank_address := 0;
+ -- Irish townlands, tagged as place=locality and locality=townland
+ IF (NEW.extratags -> 'locality') = 'townland' THEN
+ NEW.rank_address := 20;
+ END IF;
ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN
NEW.rank_search := 22;
NEW.rank_address := 22;
location RECORD;
way RECORD;
relation RECORD;
+ relation_members TEXT[];
+ relMember RECORD;
+ linkedplacex RECORD;
search_diameter FLOAT;
search_prevdiameter FLOAT;
search_maxrank INTEGER;
DELETE FROM place_boundingbox where place_id = NEW.place_id;
result := deleteRoad(NEW.partition, NEW.place_id);
result := deleteLocationArea(NEW.partition, NEW.place_id);
+ UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
END IF;
-- reclaculate country and partition (should probably have a country_code and calculated_country_code as seperate fields)
- SELECT country_code from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO NEW.country_code;
- NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+ IF NEW.rank_search >= 4 THEN
+ SELECT country_code from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO NEW.country_code;
+ NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+ ELSE
+ NEW.country_code := NULL;
+ END IF;
NEW.partition := get_partition(NEW.geometry, NEW.country_code);
NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
-- Speed up searches - just use the centroid of the feature
-- cheaper but less acurate
place_centroid := ST_Centroid(NEW.geometry);
+ NEW.centroid := null;
-- Thought this wasn't needed but when we add new languages to the country_name table
-- we need to update the existing names
IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
NEW.name := NEW.name || (('name:'||default_language) => (NEW.name -> 'name'));
ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
- NEW.name := NEW.name || ('name' => (NEW.name -> 'name:'||default_language));
+ NEW.name := NEW.name || ('name' => (NEW.name -> ('name:'||default_language)));
END IF;
END IF;
END IF;
IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
-- Is this node part of a relation?
- FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['n'||NEW.osm_id]
+ FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['n'||NEW.osm_id]
LOOP
-- At the moment we only process one type of relation - associatedStreet
IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
--RAISE WARNING 'x1';
-- Is this node part of a way?
- FOR way IN select id from planet_osm_ways where nodes @> ARRAY[NEW.osm_id::integer] LOOP
+ FOR way IN select id from planet_osm_ways where nodes @> ARRAY[NEW.osm_id] LOOP
--RAISE WARNING '%', way;
FOR location IN select * from placex where osm_type = 'W' and osm_id = way.id
LOOP
-- Is the WAY part of a relation
IF NEW.parent_place_id IS NULL THEN
- FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id::integer] and members @> ARRAY['w'||location.osm_id]
+ FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id]
LOOP
-- At the moment we only process one type of relation - associatedStreet
IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'W' THEN
-- Is this way part of a relation?
- FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['w'||NEW.osm_id]
+ FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['w'||NEW.osm_id]
LOOP
-- At the moment we only process one type of relation - associatedStreet
IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
-- 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;
+
+ IF NEW.name->'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 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;
+
+ END IF;
+
NEW.parent_place_id = 0;
parent_place_id_rank = 0;
-- INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, 0, NEW.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;
classtable TEXT;
BEGIN
+ update placex set linked_place_id = null where linked_place_id = OLD.place_id;
+ update placex set indexed_status = 2 where linked_place_id = OLD.place_id and indexed_status = 0;
+
IF OLD.rank_address < 30 THEN
-- mark everything linked to this place for re-indexing
END;
$$
LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], member TEXT) RETURNS TEXT[]
+ AS $$
+DECLARE
+ result TEXT[];
+ i INTEGER;
+BEGIN
+
+ FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
+ IF members[i+1] = member THEN
+ result := result || members[i];
+ END IF;
+ END LOOP;
+
+ return result;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], memberLabels TEXT[]) RETURNS SETOF TEXT
+ AS $$
+DECLARE
+ i INTEGER;
+BEGIN
+
+ FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
+ IF members[i+1] = ANY(memberLabels) THEN
+ RETURN NEXT members[i];
+ END IF;
+ END LOOP;
+
+ RETURN;
+END;
+$$
+LANGUAGE plpgsql;
+
+
projects / nominatim.git / blobdiff