X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/488cd726ed0049bc19804d549407c3c22f949b25..5f79cca558a49a4048d6ec3c179e68d0259f608e:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index 7b3a39cd..a8c5058a 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -271,6 +271,20 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; +CREATE OR REPLACE FUNCTION get_name_ids(lookup_word TEXT) + RETURNS INTEGER[] + AS $$ +DECLARE + lookup_token TEXT; + return_word_ids INTEGER[]; +BEGIN + lookup_token := ' '||trim(lookup_word); + SELECT array_agg(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_ids; + RETURN return_word_ids; +END; +$$ +LANGUAGE plpgsql IMMUTABLE; + CREATE OR REPLACE FUNCTION array_merge(a INTEGER[], b INTEGER[]) RETURNS INTEGER[] AS $$ @@ -1104,6 +1118,11 @@ BEGIN ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN NEW.rank_search := 18; NEW.rank_address := 0; + ELSEIF NEW.class = 'natural' and NEW.type = 'sea' THEN + NEW.rank_search := 4; + NEW.rank_address := NEW.rank_search; + ELSEIF NEW.class = 'natural' and NEW.type in ('coastline') THEN + RETURN NULL; -- 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; @@ -1117,7 +1136,11 @@ BEGIN ELSEIF NEW.class = 'waterway' AND NEW.name is NULL THEN RETURN NULL; ELSEIF NEW.class = 'waterway' THEN - NEW.rank_search := 17; + IF NEW.osm_type = 'R' THEN + NEW.rank_search := 16; + ELSE + NEW.rank_search := 17; + END IF; NEW.rank_address := 0; ELSEIF NEW.class = 'highway' AND NEW.osm_type != 'N' AND NEW.type in ('service','cycleway','path','footway','steps','bridleway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN NEW.rank_search := 27; @@ -1125,11 +1148,6 @@ BEGIN ELSEIF NEW.class = 'highway' AND NEW.osm_type != 'N' THEN NEW.rank_search := 26; NEW.rank_address := NEW.rank_search; - ELSEIF NEW.class = 'natural' and NEW.type = 'sea' THEN - NEW.rank_search := 4; - NEW.rank_address := NEW.rank_search; - ELSEIF NEW.class = 'natural' and NEW.type in ('coastline') THEN - RETURN NULL; ELSEIF NEW.class = 'mountain_pass' THEN NEW.rank_search := 20; NEW.rank_address := 0; @@ -1260,6 +1278,7 @@ DECLARE search_maxrank INTEGER; address_maxrank INTEGER; address_street_word_id INTEGER; + address_street_word_ids INTEGER[]; parent_place_id_rank BIGINT; isin TEXT[]; @@ -1351,6 +1370,25 @@ BEGIN END IF; NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid); + -- 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 and r.parts != array[]::bigint[] + LOOP + 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 = 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; + END IF; + END LOOP; + END LOOP; + END IF; + -- Adding ourselves to the list simplifies address calculations later INSERT INTO place_addressline VALUES (NEW.place_id, NEW.place_id, true, true, 0, NEW.rank_address); @@ -1407,6 +1445,16 @@ BEGIN -- Note that addr:street links can only be indexed once the street itself is indexed IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN + -- if there is no address information, see if we can get it from a surrounding building + IF NEW.street IS NULL AND NEW.addr_place IS NULL AND NEW.housenumber IS NULL THEN + FOR location IN select * from placex where ST_Covers(geometry, place_centroid) and rank_search > 28 and (housenumber is not null or street is not null or addr_place is not null) AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') + LOOP + NEW.housenumber := location.housenumber; + NEW.street := location.street; + NEW.addr_place := location.addr_place; + END LOOP; + END IF; + -- Is this node part of a relation? FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['n'||NEW.osm_id] LOOP @@ -1416,7 +1464,7 @@ BEGIN IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN --RAISE WARNING 'node in relation %',relation; SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint - and rank_search = 26 INTO NEW.parent_place_id; + and rank_search = 26 and name is not null INTO NEW.parent_place_id; END IF; END LOOP; END IF; @@ -1445,7 +1493,7 @@ BEGIN IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN --RAISE WARNING 'node in way that is in a relation %',relation; SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint - and rank_search = 26 INTO NEW.parent_place_id; + and rank_search = 26 and name is not null INTO NEW.parent_place_id; END IF; END LOOP; END IF; @@ -1453,13 +1501,10 @@ BEGIN END IF; -- If the way contains an explicit name of a street copy it - IF NEW.street IS NULL AND NEW.addr_place IS NULL AND location.street IS NOT NULL THEN + -- Slightly less strict then above because data is copied from any object. + IF NEW.street IS NULL AND NEW.addr_place IS NULL THEN --RAISE WARNING 'node in way that has a streetname %',location; NEW.street := location.street; - END IF; - - -- IF the way contains an explicit name of a place copy it - IF NEW.addr_place IS NULL AND NEW.street IS NULL AND location.addr_place IS NOT NULL THEN NEW.addr_place := location.addr_place; END IF; @@ -1495,7 +1540,7 @@ BEGIN IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN --RAISE WARNING 'way that is in a relation %',relation; SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint - and rank_search = 26 INTO NEW.parent_place_id; + and rank_search = 26 and name is not null INTO NEW.parent_place_id; END IF; END LOOP; END IF; @@ -1505,18 +1550,18 @@ BEGIN --RAISE WARNING 'x3 %',NEW.parent_place_id; IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN - address_street_word_id := get_name_id(make_standard_name(NEW.street)); - IF address_street_word_id IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_id) LOOP + address_street_word_ids := get_name_ids(make_standard_name(NEW.street)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP NEW.parent_place_id := location.place_id; END LOOP; END IF; END IF; IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN - address_street_word_id := get_name_id(make_standard_name(NEW.addr_place)); - IF address_street_word_id IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_id) LOOP + address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP NEW.parent_place_id := location.place_id; END LOOP; END IF; @@ -1600,17 +1645,13 @@ BEGIN END IF; -- merge in extra tags - IF NOT linkedPlacex.extratags IS NULL THEN - NEW.extratags := linkedPlacex.extratags || NEW.extratags; - END IF; - - IF NOT NEW.extratags ? linkedPlacex.class THEN - NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type); - END IF; + NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore); -- 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 LOOP; END LOOP; @@ -1639,13 +1680,7 @@ BEGIN END IF; -- merge in extra tags - IF NOT linkedPlacex.extratags IS NULL THEN - NEW.extratags := linkedPlacex.extratags || NEW.extratags; - END IF; - - IF NOT NEW.extratags ? linkedPlacex.class THEN - NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type); - END IF; + NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore); -- mark the linked place (excludes from search results) UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; @@ -1687,11 +1722,7 @@ BEGIN name_vector := make_keywords(NEW.name); -- merge in extra tags - NEW.extratags := linkedPlacex.extratags || NEW.extratags; - - IF NOT NEW.extratags ? linkedPlacex.class THEN - NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type); - END IF; + NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore); -- mark the linked place (excludes from search results) UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; @@ -2305,29 +2336,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 $$ @@ -2459,7 +2490,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 @@ -2470,6 +2501,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; @@ -2490,7 +2524,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) @@ -2921,14 +2957,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 @@ -3080,6 +3116,7 @@ DECLARE diameter FLOAT; rank INTEGER; BEGIN + UPDATE placex SET indexed_status = 2 WHERE place_id = placeid; 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