-- score integer
--);
-
CREATE OR REPLACE FUNCTION getclasstypekey(c text, t text) RETURNS TEXT
AS $$
DECLARE
$$
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
$$
LANGUAGE plpgsql IMMUTABLE;
+CREATE OR REPLACE FUNCTION get_country_language_codes(search_country_code VARCHAR(2)) RETURNS TEXT[]
+ AS $$
+DECLARE
+ nearcountry RECORD;
+BEGIN
+ FOR nearcountry IN select country_default_language_codes from country_name where country_code = search_country_code limit 1
+ LOOP
+ RETURN lower(nearcountry.country_default_language_codes);
+ END LOOP;
+ RETURN NULL;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
CREATE OR REPLACE FUNCTION get_partition(place geometry, in_country_code VARCHAR(10)) RETURNS INTEGER
AS $$
DECLARE
-- RAISE WARNING 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
- x := deleteLocationArea(partition, place_id);
+ x := deleteLocationArea(partition, place_id, rank_search);
isarea := false;
IF (ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(geometry)) THEN
DECLARE
b BOOLEAN;
BEGIN
- b := deleteLocationArea(partition, place_id);
+ b := deleteLocationArea(partition, place_id, rank_search);
-- result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
RETURN add_location(place_id, place_country_code, name, rank_search, rank_address, geometry);
END;
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
ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
NEW.rank_search := 22;
NEW.rank_address := NEW.rank_search;
+ ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN
+ NEW.rank_search := 18;
+ NEW.rank_address := 0;
-- any feature more than 5 square miles is probably worth indexing
ELSEIF ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_Area(NEW.geometry) > 0.1 THEN
NEW.rank_search := 22;
NEW.rank_address := NEW.rank_search;
ELSEIF NEW.class = 'natural' and NEW.type in ('coastline') THEN
RETURN NULL;
- ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano') THEN
- NEW.rank_search := 18;
- NEW.rank_address := 0;
END IF;
END IF;
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) and (rank_search < 28 or name is not null);
+ IF NEW.rank_search >= 26 THEN
+ -- roads may cause reparenting for >27 rank places
+ update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter);
+ ELSE
+ -- for all other places the search terms may change as well
+ 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;
END IF;
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);
+ result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
END IF;
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;
--RAISE WARNING 'finding street for %', NEW;
+ -- We won't get a better centroid, besides these places are too small to care
+ NEW.centroid := place_centroid;
+
NEW.parent_place_id := null;
-- to do that we have to find our parent road
-- Merge address from parent
nameaddress_vector := array_merge(nameaddress_vector, location.nameaddress_vector);
+ nameaddress_vector := array_merge(nameaddress_vector, location.name_vector);
--return NEW;
-- Performance, it would be more acurate to do all the rest of the import process but it takes too long
-- Just be happy with inheriting from parent road only
select members from planet_osm_rels where id = NEW.osm_id INTO relation_members;
-- RAISE WARNING 'get_osm_rel_members, label';
- FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
+ IF relation_members IS NOT NULL THEN
+ FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
- FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
- and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP
+ FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
+ and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 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;
+ -- 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
- IF NOT linkedPlacex.name IS NULL THEN
- NEW.name := linkedPlacex.name || NEW.name;
- name_vector := make_keywords(NEW.name);
- END IF;
+ -- merge in the label name, re-init word vector
+ IF NOT linkedPlacex.name IS NULL THEN
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := make_keywords(NEW.name);
+ END IF;
- -- merge in extra tags
- IF NOT linkedPlacex.extratags IS NULL THEN
- NEW.extratags := linkedPlacex.extratags || NEW.extratags;
- END IF;
+ -- merge in extra tags
+ IF NOT linkedPlacex.extratags IS NULL THEN
+ NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+ END IF;
- -- mark the linked place (excludes from search results)
- UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+ -- 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 LOOP;
- END LOOP;
+ IF NEW.centroid IS NULL THEN
- IF NEW.centroid IS NULL THEN
+ FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
- FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
+ FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
+ and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP
- FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
- and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP
+ -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
+ -- But that can be fixed by explicitly setting the label in the data
+ IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name')
+ AND NEW.rank_address = linkedPlacex.rank_address THEN
- -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
- -- But that can be fixed by explicitly setting the label in the data
- IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name')
- AND NEW.rank_address = linkedPlacex.rank_address 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;
- -- 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
+ IF NOT linkedPlacex.name IS NULL THEN
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := make_keywords(NEW.name);
+ END IF;
- -- merge in the name, re-init word vector
- IF NOT linkedPlacex.name IS NULL THEN
- NEW.name := linkedPlacex.name || NEW.name;
- name_vector := make_keywords(NEW.name);
- END IF;
+ -- merge in extra tags
+ IF NOT linkedPlacex.extratags IS NULL THEN
+ NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+ END IF;
- -- merge in extra tags
- IF NOT linkedPlacex.extratags IS NULL THEN
- NEW.extratags := linkedPlacex.extratags || NEW.extratags;
- END IF;
+ -- mark the linked place (excludes from search results)
+ UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
- -- 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 IF;
- -- 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 LOOP;
-
+ END IF;
END IF;
-- not found one yet? how about doing a name search
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;
END LOOP;
END IF;
END IF;
+
+ -- for the USA we have an additional address table. Merge in zip codes from there too
+ IF NEW.rank_search = 26 AND NEW.calculated_country_code = 'us' THEN
+ FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP
+ address_street_word_id := get_name_id(make_standard_name(location.postcode));
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ isin_tokens := isin_tokens || address_street_word_id;
+ END LOOP;
+ END IF;
+
-- RAISE WARNING 'ISIN: %', isin_tokens;
-- Process area matches
-- 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
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
+ FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
---RAISE WARNING ' ISIN: %',location;
+ --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;
- END IF;
+ 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 LOOP;
+ 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[]);
--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);
+ b := deleteLocationArea(OLD.partition, OLD.place_id, OLD.rank_search);
END IF;
--DEBUG: RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id;
END IF;
END LOOP;
- RETURN null;
+ -- anything will do as a fallback - just take the first name type thing there is
+ search := avals(name);
+ RETURN search[1];
END;
$$
LANGUAGE plpgsql IMMUTABLE;
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