END IF;
r := a;
FOR i IN 1..array_upper(b, 1) LOOP
- IF NOT (ARRAY[b[i]] && r) THEN
+ IF NOT (ARRAY[b[i]] <@ r) THEN
r := r || b[i];
END IF;
END LOOP;
s := make_standard_name(item.value);
w := getorcreate_name_id(s, item.value);
- result := result | w;
+
+ IF not(ARRAY[w] <@ result) THEN
+ result := result || w;
+ END IF;
words := string_to_array(s, ' ');
IF array_upper(words, 1) IS NOT NULL THEN
FOR j IN 1..array_upper(words, 1) LOOP
IF (words[j] != '') THEN
w = getorcreate_word_id(words[j]);
- IF NOT (ARRAY[w] && result) THEN
- result := result | w;
+ IF NOT (ARRAY[w] <@ result) THEN
+ result := result || w;
END IF;
END IF;
END LOOP;
s := make_standard_name(words[j]);
IF s != '' THEN
w := getorcreate_word_id(s);
- IF NOT (ARRAY[w] && result) THEN
- result := result | w;
+ IF NOT (ARRAY[w] <@ result) THEN
+ result := result || w;
END IF;
END IF;
END LOOP;
s := make_standard_name(s);
IF s != '' THEN
w := getorcreate_name_id(s, item.value);
- IF NOT (ARRAY[w] && result) THEN
- result := result | w;
+ IF NOT (ARRAY[w] <@ result) THEN
+ result := result || w;
END IF;
END IF;
END IF;
s := make_standard_name(src);
w := getorcreate_name_id(s);
- IF NOT (ARRAY[w] && result) THEN
+ IF NOT (ARRAY[w] <@ result) THEN
result := result || w;
END IF;
FOR j IN 1..array_upper(words, 1) LOOP
IF (words[j] != '') THEN
w = getorcreate_word_id(words[j]);
- IF NOT (ARRAY[w] && result) THEN
+ IF NOT (ARRAY[w] <@ result) THEN
result := result || w;
END IF;
END IF;
and search_name.place_id = place_addressline.place_id
LOOP
delete from search_name where place_id = childplace.place_id;
- childplace.nameaddress_vector := uniq(sort_asc(childplace.nameaddress_vector + to_add));
- IF childplace.place_id = parent_place_id THEN
- childplace.name_vector := uniq(sort_asc(childplace.name_vector + to_add));
+ IF not (ARRAY[to_add] <@ childplace.nameaddress_vector) THEN
+ childplace.nameaddress_vector := childplace.nameaddress_vector || to_add;
+ END IF;
+ IF childplace.place_id = parent_place_id and not (ARRAY[to_add] <@ childplace.name_vector) THEN
+ childplace.name_vector := childplace.name_vector || to_add;
END IF;
insert into search_name (place_id, search_rank, address_rank, country_code, name_vector, nameaddress_vector, centroid)
values (childplace.place_id, childplace.search_rank, childplace.address_rank, childplace.country_code,
linestr := linestr||','||ST_X(nextnode.geometry)||' '||ST_Y(nextnode.geometry);
endnumber := ('0'||substring(nextnode.housenumber,'[0-9]+'))::integer;
- IF startnumber IS NOT NULL and startnumber > 0 AND endnumber IS NOT NULL and endnumber > 0 THEN
+ IF startnumber IS NOT NULL and startnumber > 0 AND endnumber IS NOT NULL and endnumber > 0 AND @(startnumber - endnumber) < 1000 THEN
--RAISE WARNING 'interpolation end % % ',nextnode.place_id,endnumber;
country_code VARCHAR(2);
default_language VARCHAR(10);
diameter FLOAT;
+ classtable TEXT;
BEGIN
-- RAISE WARNING '%',NEW.osm_id;
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') THEN
+ ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling') THEN
NEW.rank_search := 20;
NEW.rank_address := 0;
ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN
-- RETURN NULL;
-- END IF;
- RETURN NEW; -- The following is not needed until doing diff updates, and slows the main index process down
+-- RETURN NEW; -- The following is not needed until doing diff updates, and slows the main index process down
IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN
-- Performance: We just can't handle re-indexing for country level changes
-- RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
-- work around bug in postgis
update placex set indexed_status = 2 where (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
- AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point';
+ 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);
update placex set indexed_status = 2 where (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
- AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point';
+ 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);
END IF;
ELSE
-- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(
END IF;
IF diameter > 0 THEN
-- RAISE WARNING 'placex point insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,diameter;
- update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter);
+ 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);
END IF;
END IF;
+ -- add to tables for special search
+ -- Note: won't work on initial import because the classtype tables
+ -- do not yet exist. It won't hurt either.
+ classtable := 'place_classtype_' || NEW.class || '_' || NEW.type;
+ SELECT count(*)>0 FROM pg_tables WHERE tablename = classtable INTO result;
+ IF result THEN
+ EXECUTE 'INSERT INTO ' || classtable::regclass || ' (place_id, centroid) VALUES ($1,$2)'
+ USING NEW.place_id, ST_Centroid(NEW.geometry);
+ END IF;
+
+
-- IF NEW.rank_search < 26 THEN
-- RAISE WARNING 'placex insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
-- END IF;
RETURN NEW;
END IF;
- -- defered delete
+ -- deferred delete
IF OLD.indexed_status = 100 THEN
- delete from placex where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
+ delete from placex where place_id = OLD.place_id;
RETURN NULL;
END IF;
RETURN NEW;
END IF;
- result := deleteSearchName(NEW.partition, NEW.place_id);
- DELETE FROM place_addressline WHERE place_id = NEW.place_id;
- DELETE FROM place_boundingbox where place_id = NEW.place_id;
- result := deleteRoad(NEW.partition, NEW.place_id);
- result := deleteLocationArea(NEW.partition, NEW.place_id);
+ IF OLD.indexed_status > 1 THEN
+ result := deleteSearchName(NEW.partition, NEW.place_id);
+ DELETE FROM place_addressline WHERE place_id = NEW.place_id;
+ DELETE FROM place_boundingbox where place_id = NEW.place_id;
+ result := deleteRoad(NEW.partition, NEW.place_id);
+ result := deleteLocationArea(NEW.partition, 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;
--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::integer] LOOP
--RAISE WARNING '%', way;
FOR location IN select * from placex where osm_type = 'W' and osm_id = way.id
LOOP
END IF;
-- Is the WAY part of a relation
- FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id::integer] 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
- FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
- IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
---RAISE WARNING 'node in way that is in a relation %',relation;
- SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer
- and rank_search = 26 INTO NEW.parent_place_id;
+ 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]
+ 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
+ FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
+ IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
+ --RAISE WARNING 'node in way that is in a relation %',relation;
+ SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer
+ and rank_search = 26 INTO NEW.parent_place_id;
+ END IF;
+ END LOOP;
END IF;
END LOOP;
- END IF;
- END LOOP;
+ END IF;
-- If the way contains an explicit name of a street copy it
IF NEW.street IS NULL AND location.street IS NOT NULL THEN
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 THEN
- isin_tokens := isin_tokens + address_street_word_id;
+ 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;
- isin_tokens := uniq(sort(isin_tokens));
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 THEN
- isin_tokens := isin_tokens + address_street_word_id;
+ 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;
- isin_tokens := uniq(sort(isin_tokens));
END IF;
--RAISE WARNING 'ISIN: %', isin_tokens;
AS $$
DECLARE
b BOOLEAN;
+ classtable TEXT;
BEGIN
IF OLD.rank_address < 30 THEN
DELETE FROM place_addressline where place_id = OLD.place_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;
+
RETURN OLD;
END;
update placex set indexed_status = 2 where indexed_status = 0 and
(ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
AND NOT (ST_Contains(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
- AND rank_search > existingplacex.rank_search;
+ AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
update placex set indexed_status = 2 where indexed_status = 0 and
(ST_Contains(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
AND NOT (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
- AND rank_search > existingplacex.rank_search;
+ AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
END IF;
IF st_area(NEW.geometry) < 0.5 THEN
UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = existingplacex.place_id
- and placex.place_id = place_addressline.place_id and indexed_status = 0;
+ and placex.place_id = place_addressline.place_id and indexed_status = 0
+ and (rank_search < 28 or name is not null);
END IF;
END IF;
FOR location IN
select nodes from planet_osm_ways where id = way_ids[j] LIMIT 1
LOOP
- searchnodes := searchnodes | location.nodes;
+ IF not (ARRAY[location.nodes] <@ searchnodes) THEN
+ searchnodes := searchnodes || location.nodes;
+ END IF;
END LOOP;
END LOOP;
IF array_upper(search, 1) IS NOT NULL AND array_upper(location.name, 1) IS NOT NULL THEN
FOR j IN 1..array_upper(search, 1) LOOP
FOR k IN 1..array_upper(location.name, 1) LOOP
- IF (found > location.rank_address AND location.name[k].key = search[j] AND location.name[k].value != '') AND NOT result && ARRAY[trim(location.name[k].value)] AND (for_postcode IS NULL OR location.name[k].value ilike for_postcode||'%') THEN
+ IF (found > location.rank_address AND location.name[k].key = search[j] AND location.name[k].value != '') AND NOT result @> ARRAY[trim(location.name[k].value)] AND (for_postcode IS NULL OR location.name[k].value ilike for_postcode||'%') THEN
result[(100 - location.rank_address)] := trim(location.name[k].value);
found := location.rank_address;
END IF;
$$
LANGUAGE plpgsql;
-CREATE AGGREGATE array_agg(INT[])
-(
- sfunc = array_cat,
- stype = INT[],
- initcond = '{}'
-);
-
CREATE OR REPLACE FUNCTION tigger_create_interpolation(linegeo GEOMETRY, in_startnumber INTEGER,
in_endnumber INTEGER, interpolationtype TEXT,
in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER
projects / nominatim.git / blobdiff