X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/5864fddb537a845e0281a433f00413dde9d70a08..866b702e0e1807b17180af79943ce02ea0890a7c:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index 9cf1bd40..54f46cfe 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -1023,6 +1023,13 @@ BEGIN NEW.rank_address := 5; END IF; + ELSEIF NEW.calculated_country_code = 'sg' THEN + + IF NEW.postcode ~ '^([0-9]{6})$' THEN + NEW.rank_search := 25; + NEW.rank_address := 11; + END IF; + ELSEIF NEW.calculated_country_code = 'de' THEN IF NEW.postcode ~ '^([0-9]{5})$' THEN @@ -1377,12 +1384,15 @@ BEGIN -- waterway ways are linked when they are part of a relation and have the same class/type IF NEW.osm_type = 'R' and NEW.class = 'waterway' THEN - FOR relation IN select * from planet_osm_rels r where r.id = NEW.osm_id + FOR relation IN select * from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[] LOOP - FOR i IN relation.way_off+1..relation.rel_off LOOP - IF relation.members[2*i] in ('', 'main_stream') THEN + FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP + IF relation.members[i+1] in ('', 'main_stream', 'side_stream') AND substring(relation.members[i],1,1) = 'w' THEN --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation.parts[i]; - FOR location IN SELECT * FROM placex WHERE osm_type = 'W' and osm_id = relation.parts[i] and class = NEW.class and type = NEW.type + FOR location IN SELECT * FROM placex + WHERE osm_type = 'W' and osm_id = substring(relation.members[i],2,200)::bigint + and class = NEW.class and type = NEW.type + and ( relation.members[i+1] != 'side_stream' or NEW.name->'name' = name->'name') LOOP UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = location.place_id; END LOOP; @@ -2097,8 +2107,8 @@ BEGIN -- Handle a place changing type by removing the old data -- My generated 'place' types are causing havok because they overlap with real keys -- TODO: move them to their own special purpose key/class to avoid collisions - IF existing.osm_type IS NULL AND (NEW.type not in ('postcode','house','houses')) THEN - DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type not in ('postcode','house','houses'); + IF existing.osm_type IS NULL THEN + DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class; END IF; --DEBUG: RAISE WARNING 'Existing: %',existing.osm_id; @@ -2349,29 +2359,29 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; -CREATE OR REPLACE FUNCTION get_connected_ways(way_ids INTEGER[]) RETURNS SETOF planet_osm_ways - AS $$ -DECLARE - searchnodes INTEGER[]; - location RECORD; - j INTEGER; -BEGIN - - searchnodes := '{}'; - FOR j IN 1..array_upper(way_ids, 1) LOOP - FOR location IN - select nodes from planet_osm_ways where id = way_ids[j] LIMIT 1 - LOOP - IF not (ARRAY[location.nodes] <@ searchnodes) THEN - searchnodes := searchnodes || location.nodes; - END IF; - END LOOP; - END LOOP; - - RETURN QUERY select * from planet_osm_ways where nodes && searchnodes and NOT ARRAY[id] <@ way_ids; -END; -$$ -LANGUAGE plpgsql IMMUTABLE; +--CREATE OR REPLACE FUNCTION get_connected_ways(way_ids INTEGER[]) RETURNS SETOF planet_osm_ways +-- AS $$ +--DECLARE +-- searchnodes INTEGER[]; +-- location RECORD; +-- j INTEGER; +--BEGIN +-- +-- searchnodes := '{}'; +-- FOR j IN 1..array_upper(way_ids, 1) LOOP +-- FOR location IN +-- select nodes from planet_osm_ways where id = way_ids[j] LIMIT 1 +-- LOOP +-- IF not (ARRAY[location.nodes] <@ searchnodes) THEN +-- searchnodes := searchnodes || location.nodes; +-- END IF; +-- END LOOP; +-- END LOOP; +-- +-- RETURN QUERY select * from planet_osm_ways where nodes && searchnodes and NOT ARRAY[id] <@ way_ids; +--END; +--$$ +--LANGUAGE plpgsql IMMUTABLE; CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id BIGINT) RETURNS TEXT AS $$ @@ -2503,7 +2513,7 @@ BEGIN 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 + 0 as distance, calculated_country_code, postcode from placex where place_id = for_place_id LOOP @@ -2514,6 +2524,9 @@ BEGIN IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN location.isaddress := FALSE; END IF; + IF searchpostcode IS NULL and location.postcode IS NOT NULL THEN + searchpostcode := location.postcode; + END IF; IF location.rank_address = 4 AND location.isaddress THEN hadcountry := true; END IF; @@ -2534,7 +2547,9 @@ BEGIN FOR location IN select placex.place_id, osm_type, osm_id, CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name, - class, type, admin_level, fromarea, isaddress, + CASE WHEN extratags ? 'place' THEN 'place' ELSE class END as class, + CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as 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,postcode from place_addressline join placex on (address_place_id = placex.place_id) @@ -2965,14 +2980,14 @@ BEGIN wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/wiki/',E'\\2:'); wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/w/index.php\\?title=',E'\\2:'); wiki_article := regexp_replace(wiki_article,E'^(.*?)/([a-z]{2,3})/wiki/',E'\\2:'); - wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:'); + --wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:'); wiki_article := replace(wiki_article,' ','_'); - wiki_article_title := trim(split_part(wiki_article, ':', 2)); - IF wiki_article_title IS NULL OR wiki_article_title = '' THEN + IF strpos(wiki_article, ':') IN (3,4) THEN + wiki_article_language := lower(trim(split_part(wiki_article, ':', 1))); + wiki_article_title := trim(substr(wiki_article, strpos(wiki_article, ':')+1)); + ELSE wiki_article_title := trim(wiki_article); wiki_article_language := CASE WHEN langs[i] = 'english' THEN 'en' WHEN langs[i] = 'country' THEN get_country_language_code(country_code) ELSE langs[i] END; - ELSE - wiki_article_language := lower(trim(split_part(wiki_article, ':', 1))); END IF; select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance