$$
LANGUAGE plpgsql IMMUTABLE;
+CREATE OR REPLACE FUNCTION create_country(src HSTORE, lookup_country_code varchar(2)) RETURNS VOID
+ AS $$
+DECLARE
+ s TEXT;
+ w INTEGER;
+ words TEXT[];
+ item RECORD;
+ j INTEGER;
+BEGIN
+ FOR item IN SELECT (each(src)).* LOOP
+
+ s := make_standard_name(item.value);
+ w := getorcreate_country(s, lookup_country_code);
+
+ words := regexp_split_to_array(item.value, E'[,;()]');
+ IF array_upper(words, 1) != 1 THEN
+ FOR j IN 1..array_upper(words, 1) LOOP
+ s := make_standard_name(words[j]);
+ IF s != '' THEN
+ w := getorcreate_country(s, lookup_country_code);
+ END IF;
+ END LOOP;
+ END IF;
+ END LOOP;
+END;
+$$
+LANGUAGE plpgsql;
+
CREATE OR REPLACE FUNCTION make_keywords(src HSTORE) RETURNS INTEGER[]
AS $$
DECLARE
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 || (('name:'||default_language) => (NEW.name -> 'name'));
+ 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 || ('name' => (NEW.name -> ('name:'||default_language)));
+ NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
END IF;
END IF;
END IF;
RETURN NULL;
END IF;
- NEW.name := 'ref'=>NEW.postcode;
+ NEW.name := hstore('ref', NEW.postcode);
IF NEW.calculated_country_code = 'gb' 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 || (('name:'||default_language) => (NEW.name -> 'name'));
+ 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 || ('name' => (NEW.name -> ('name:'||default_language)));
+ NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
END IF;
END IF;
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;
-- 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[]);
+ IF location.rank_search > 4 THEN
+ nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
+ END IF;
INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address);
IF location_isaddress THEN
--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_search > 4 THEN
+ 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;
+ 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;
END IF;
location_distance := location.distance * 1.5;
END IF;
- IF location.distance < location_distance THEN
+ IF location.rank_search > 4 AND location.distance < location_distance THEN
-- Add it to the list of search terms
nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
hadcountry := false;
FOR location IN
select placex.place_id, osm_type, osm_id,
- CASE WHEN class = 'place' and type = 'postcode' THEN 'name' => postcode ELSE name END as name,
+ CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
class, type, admin_level, true as fromarea, true as isaddress,
CASE WHEN rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
0 as distance, calculated_country_code
FOR location IN
select placex.place_id, osm_type, osm_id,
- CASE WHEN class = 'place' and type = 'postcode' THEN 'name' => postcode ELSE name END as name,
+ CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
class, type, admin_level, fromarea, isaddress,
CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
distance,calculated_country_code
END IF;
IF searchcountrycode IS NOT NULL THEN
- location := ROW(null, null, null, 'ref'=>searchcountrycode, 'place', 'country_code', null, true, false, 4, 0)::addressline;
+ location := ROW(null, null, null, hstore('ref', searchcountrycode), 'place', 'country_code', null, true, false, 4, 0)::addressline;
RETURN NEXT location;
END IF;
END IF;
IF searchhousenumber IS NOT NULL THEN
- location := ROW(in_place_id, null, null, 'ref'=>searchhousenumber, 'place', 'house_number', null, true, true, 28, 0)::addressline;
+ location := ROW(in_place_id, null, null, hstore('ref', searchhousenumber), 'place', 'house_number', null, true, true, 28, 0)::addressline;
RETURN NEXT location;
END IF;
IF searchpostcode IS NOT NULL THEN
- location := ROW(null, null, null, 'ref'=>searchpostcode, 'place', 'postcode', null, true, true, 5, 0)::addressline;
+ location := ROW(null, null, null, hstore('ref', searchpostcode), 'place', 'postcode', null, true, true, 5, 0)::addressline;
RETURN NEXT location;
END IF;
END;
$$
LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT) RETURNS BOOLEAN
+ AS $$
+DECLARE
+ osmid BIGINT;
+ osmtype character(1);
+ pclass text;
+ ptype text;
+BEGIN
+ SELECT osm_type, osm_id, class, type FROM placex WHERE place_id = placeid INTO osmtype, osmid, pclass, ptype;
+ DELETE FROM import_polygon_delete where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+ DELETE FROM import_polygon_error where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+ -- force delete from place/placex by making it a very small geometry
+ UPDATE place set geometry = ST_SetSRID(ST_Point(0,0), 4326) where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+ DELETE FROM place where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+
+ RETURN TRUE;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION place_force_update(placeid BIGINT) RETURNS BOOLEAN
+ AS $$
+DECLARE
+ placegeom GEOMETRY;
+ geom GEOMETRY;
+ diameter FLOAT;
+ rank INTEGER;
+BEGIN
+ SELECT geometry, rank_search FROM placex WHERE place_id = placeid INTO placegeom, rank;
+ IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN
+ IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
+ FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
+ update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
+ AND rank_search > rank 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_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
+ AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null);
+ END LOOP;
+ ELSE
+ diameter := 0;
+ IF rank = 11 THEN
+ diameter := 0.05;
+ ELSEIF rank < 18 THEN
+ diameter := 0.1;
+ ELSEIF rank < 20 THEN
+ diameter := 0.05;
+ ELSEIF rank = 21 THEN
+ diameter := 0.001;
+ ELSEIF rank < 24 THEN
+ diameter := 0.02;
+ ELSEIF rank < 26 THEN
+ diameter := 0.002; -- 100 to 200 meters
+ ELSEIF rank < 28 THEN
+ diameter := 0.001; -- 50 to 100 meters
+ END IF;
+ IF diameter > 0 THEN
+ update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null);
+ END IF;
+ END IF;
+ RETURN TRUE;
+ END IF;
+
+ RETURN FALSE;
+END;
+$$
+LANGUAGE plpgsql;
projects / nominatim.git / blobdiff