NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
-- copy 'name' to or from the default language (if there is a default language)
- IF NEW.name is not null AND array_upper(%#NEW.name,1) > 1 THEN
+ IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
default_language := get_country_language_code(NEW.country_code);
IF default_language IS NOT NULL THEN
IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
-- work around bug in postgis, this may have been fixed in 2.0.0 (see http://trac.osgeo.org/postgis/ticket/547)
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;
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)
-- 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 is not null AND array_upper(%#NEW.name,1) > 1 THEN
+ IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
default_language := get_country_language_code(NEW.country_code);
IF default_language IS NOT NULL THEN
IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) 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;
+ DELETE from search_name where place_id = linkedPlacex.place_id;
+
+ END LOOP;
+
+ 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' 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;
+ DELETE from search_name where place_id = linkedPlacex.place_id;
+ END IF;
+
+ END LOOP;
+
+ -- not found one yet? how about doing a name search
+ IF NEW.centroid IS NULL THEN
+ FOR linkedPlacex IN select placex.* from search_name join placex using (place_id) WHERE
+ search_name.name_vector @> ARRAY[getorcreate_name_id(make_standard_name(NEW.name->'name'))]
+ AND search_name.search_rank = NEW.rank_search
+ AND search_name.place_id != NEW.place_id
+ AND osm_type = 'N'
+ AND NEW.name->'name' = placex.name->'name'
+ 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;
+ DELETE from search_name 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
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;
$$
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
numberrange INTEGER;
rangestartnumber INTEGER;
place_centroid GEOMETRY;
- partition INTEGER;
- parent_place_id BIGINT;
+ out_partition INTEGER;
+ out_parent_place_id BIGINT;
location RECORD;
address_street_word_id INTEGER;
END IF;
place_centroid := ST_Centroid(linegeo);
- partition := get_partition(place_centroid, 'us');
- parent_place_id := null;
+ out_partition := get_partition(place_centroid, 'us');
+ out_parent_place_id := null;
address_street_word_id := get_name_id(make_standard_name(in_street));
IF address_street_word_id IS NOT NULL THEN
- FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
- parent_place_id := location.place_id;
+ FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
+ out_parent_place_id := location.place_id;
END LOOP;
END IF;
- IF parent_place_id IS NULL THEN
- FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(partition, linegeo) LOOP
- parent_place_id := location.place_id;
+ IF out_parent_place_id IS NULL THEN
+ FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(out_partition, linegeo) LOOP
+ out_parent_place_id := location.place_id;
END LOOP;
END IF;
- IF parent_place_id IS NULL THEN
- FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
- parent_place_id := location.place_id;
+ IF out_parent_place_id IS NULL THEN
+ FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
+ out_parent_place_id := location.place_id;
END LOOP;
END IF;
newpoints := 0;
FOR housenum IN startnumber..endnumber BY stepsize LOOP
insert into location_property_tiger (place_id, partition, parent_place_id, housenumber, postcode, centroid)
- values (nextval('seq_place'), partition, parent_place_id, housenum, in_postcode,
+ values (nextval('seq_place'), out_partition, out_parent_place_id, housenum, in_postcode,
ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
newpoints := newpoints + 1;
END LOOP;
newpoints INTEGER;
place_centroid GEOMETRY;
- partition INTEGER;
+ out_partition INTEGER;
out_parent_place_id BIGINT;
location RECORD;
address_street_word_id INTEGER;
BEGIN
place_centroid := ST_Centroid(pointgeo);
- partition := get_partition(place_centroid, in_countrycode);
+ out_partition := get_partition(place_centroid, in_countrycode);
out_parent_place_id := null;
address_street_word_id := get_name_id(make_standard_name(in_street));
IF address_street_word_id IS NOT NULL THEN
- FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
+ FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
out_parent_place_id := location.place_id;
END LOOP;
END IF;
IF out_parent_place_id IS NULL THEN
- FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
+ FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
out_parent_place_id := location.place_id;
END LOOP;
END IF;
SELECT postcode from placex where place_id = out_parent_place_id INTO out_postcode;
END IF;
IF out_postcode IS NULL THEN
- out_postcode := getNearestPostcode(partition, place_centroid);
+ out_postcode := getNearestPostcode(out_partition, place_centroid);
END IF;
newpoints := 0;
insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)
- values (nextval('seq_place'), partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
+ values (nextval('seq_place'), out_partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
newpoints := newpoints + 1;
RETURN newpoints;
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