X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/c8c8471e737ff9344d98296cf6c238f9f659b3d4..cc43bedd2145d1cfc740b0635df3746b620b4778:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index 0174387b..a68a90b3 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -742,7 +742,7 @@ $$ 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 @@ -909,7 +909,12 @@ BEGIN 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); @@ -920,7 +925,7 @@ BEGIN 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; @@ -942,6 +947,11 @@ BEGIN -- 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 @@ -997,8 +1007,8 @@ BEGIN 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; @@ -1029,9 +1039,13 @@ BEGIN 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; @@ -1211,6 +1225,10 @@ DECLARE name_vector INTEGER[]; nameaddress_vector INTEGER[]; + wiki_article TEXT; + wiki_article_title TEXT; + wiki_article_language TEXT; + result BOOLEAN; BEGIN @@ -1247,8 +1265,12 @@ BEGIN 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); @@ -1271,7 +1293,7 @@ BEGIN 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; @@ -1290,6 +1312,34 @@ BEGIN address_havelevel[i] := false; END LOOP; + NEW.importance := null; + -- WARNING: see duplicate of code below (yuk!) + IF NEW.extratags?'wikipedia' THEN + wiki_article := replace(regexp_replace(NEW.extratags->'wikipedia',E'(.*?)([a-z]+).wikipedia.org/wiki/',E'\\2:'),' ','_'); + wiki_article_title := split_part(wiki_article, ':', 2); + IF wiki_article_title IS NULL OR wiki_article_title = '' THEN + wiki_article_title := wiki_article; + wiki_article_language := 'en'; + ELSE + wiki_article_language := lower(split_part(wiki_article, ':', 1)); + END IF; +--RAISE WARNING '% %', wiki_article_language, wiki_article_title; + + select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title + from wikipedia_article + where language = wiki_article_language and + (title = wiki_article_title OR title = catch_decode_url_part(wiki_article_title) OR title = replace(catch_decode_url_part(wiki_article_title),E'\\','')) + UNION ALL + select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title + from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title) + where wikipedia_redirect.language = wiki_article_language and + (from_title = wiki_article_title OR from_title = catch_decode_url_part(wiki_article_title) OR from_title = replace(catch_decode_url_part(wiki_article_title),E'\\','')) + order by importance asc limit 1 INTO NEW.importance,NEW.wikipedia; + + ELSE + select importance,language||':'||title from wikipedia_article where osm_type = NEW.osm_type and osm_id = NEW.osm_id order by importance asc limit 1 INTO NEW.importance,NEW.wikipedia; + END IF; + --RAISE WARNING '% %', NEW.place_id, NEW.rank_search; -- For low level elements we inherit from our parent road @@ -1305,7 +1355,7 @@ BEGIN 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 @@ -1321,7 +1371,7 @@ BEGIN --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 @@ -1334,7 +1384,7 @@ BEGIN -- 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 @@ -1379,7 +1429,7 @@ BEGIN 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 @@ -1481,45 +1531,52 @@ BEGIN -- 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 + IF NEW.centroid IS NULL THEN - 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; + FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP - 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; + 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; - -- merge in the name, re-init word vector - NEW.name := linkedPlacex.name || NEW.name; - name_vector := make_keywords(NEW.name); + -- 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_search = linkedPlacex.rank_search THEN - -- merge in extra tags - NEW.extratags := linkedPlacex.extratags || NEW.extratags; + -- 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; - -- 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; + -- merge in the name, re-init word vector + NEW.name := linkedPlacex.name || NEW.name; + name_vector := make_keywords(NEW.name); - END LOOP; + -- 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 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' + IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' 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 placex.osm_type = 'N' AND placex.rank_search < 26 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)); @@ -1534,12 +1591,37 @@ BEGIN -- 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 + place_centroid := NEW.centroid; + END IF; + + -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance + -- WARNING: duplicate of code above (yuk!) + IF NEW.importance is null AND NEW.extratags?'wikipedia' THEN + wiki_article := replace(regexp_replace(NEW.extratags->'wikipedia',E'(.*?)([a-z]+).wikipedia.org/wiki/',E'\\2:'),' ','_'); + wiki_article_title := split_part(wiki_article, ':', 2); + IF wiki_article_title IS NULL OR wiki_article_title = '' THEN + wiki_article_title := wiki_article; + wiki_article_language := 'en'; + ELSE + wiki_article_language := lower(split_part(wiki_article, ':', 1)); + END IF; + + select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title + from wikipedia_article + where language = wiki_article_language and + (title = wiki_article_title OR title = catch_decode_url_part(wiki_article_title) OR title = replace(catch_decode_url_part(wiki_article_title),E'\\','')) + UNION ALL + select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title + from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title) + where wikipedia_redirect.language = wiki_article_language and + (from_title = wiki_article_title OR from_title = catch_decode_url_part(wiki_article_title) OR from_title = replace(catch_decode_url_part(wiki_article_title),E'\\','')) + order by importance asc limit 1 INTO NEW.importance,NEW.wikipedia; + END IF; END IF; @@ -2681,4 +2763,23 @@ END; $$ LANGUAGE plpgsql; - +-- See: http://stackoverflow.com/questions/6410088/how-can-i-mimic-the-php-urldecode-function-in-postgresql +CREATE OR REPLACE FUNCTION decode_url_part(p varchar) RETURNS varchar + AS $$ +SELECT convert_from(CAST(E'\\x' || array_to_string(ARRAY( + SELECT CASE WHEN length(r.m[1]) = 1 THEN encode(convert_to(r.m[1], 'SQL_ASCII'), 'hex') ELSE substring(r.m[1] from 2 for 2) END + FROM regexp_matches($1, '%[0-9a-f][0-9a-f]|.', 'gi') AS r(m) +), '') AS bytea), 'UTF8'); +$$ +LANGUAGE SQL IMMUTABLE STRICT; + +CREATE OR REPLACE FUNCTION catch_decode_url_part(p varchar) RETURNS varchar + AS $$ +DECLARE +BEGIN + RETURN decode_url_part(p); +EXCEPTION + WHEN others THEN return null; +END; +$$ +LANGUAGE plpgsql IMMUTABLE;