X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/589bad809ef11bd8fe94ffa006e1255efec72480..866b702e0e1807b17180af79943ce02ea0890a7c:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index 3c3a01cc..54f46cfe 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 $$ @@ -922,6 +936,11 @@ DECLARE BEGIN --DEBUG: RAISE WARNING '% %',NEW.osm_type,NEW.osm_id; + -- remove operator tag for most places, messes too much with search_name indexes + IF NEW.class not in ('amenity', 'shop') THEN + NEW.name := delete(NEW.name, 'operator'); + END IF; + -- just block these IF NEW.class in ('landuse','natural') and NEW.name is null THEN -- RAISE WARNING 'empty landuse %',NEW.osm_id; @@ -1004,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 @@ -1104,6 +1130,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,18 +1148,18 @@ BEGIN ELSEIF NEW.class = 'waterway' AND NEW.name is NULL THEN RETURN NULL; ELSEIF NEW.class = 'waterway' THEN - NEW.rank_address := 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; NEW.rank_address := NEW.rank_search; 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; @@ -1149,7 +1180,7 @@ BEGIN END IF; -- a country code make no sense below rank 4 (country) - IF NEW.rank_address < 4 THEN + IF NEW.rank_search < 4 THEN NEW.calculated_country_code := NULL; END IF; @@ -1162,53 +1193,54 @@ BEGIN RETURN NEW; -- @DIFFUPDATES@ The following is not needed until doing diff updates, and slows the main index process down - IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN - -- Performance: We just can't handle re-indexing for country level changes - IF st_area(NEW.geometry) < 1 THEN - -- mark items within the geometry for re-indexing --- RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type; - - -- work around bug in postgis, this may have been fixed in 2.0.0 (see http://trac.osgeo.org/postgis/ticket/547) - update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) - AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and addr_place is not null)); - update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) - AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and addr_place is not null)); - END IF; - ELSE - -- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :( - diameter := 0; - -- 16 = city, anything higher than city is effectively ignored (polygon required!) - IF NEW.type='postcode' THEN - diameter := 0.05; - ELSEIF NEW.rank_search < 16 THEN + IF NEW.rank_address > 0 THEN + IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN + -- Performance: We just can't handle re-indexing for country level changes + IF st_area(NEW.geometry) < 1 THEN + -- mark items within the geometry for re-indexing + -- RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type; + + -- work around bug in postgis, this may have been fixed in 2.0.0 (see http://trac.osgeo.org/postgis/ticket/547) + update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) + AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and addr_place is not null)); + update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) + AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and addr_place is not null)); + END IF; + ELSE + -- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :( diameter := 0; - ELSEIF NEW.rank_search < 18 THEN - diameter := 0.1; - ELSEIF NEW.rank_search < 20 THEN - diameter := 0.05; - ELSEIF NEW.rank_search = 21 THEN - diameter := 0.001; - ELSEIF NEW.rank_search < 24 THEN - diameter := 0.02; - ELSEIF NEW.rank_search < 26 THEN - diameter := 0.002; -- 100 to 200 meters - ELSEIF NEW.rank_search < 28 THEN - diameter := 0.001; -- 50 to 100 meters - END IF; - IF diameter > 0 THEN --- RAISE WARNING 'placex point insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,diameter; - 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); - ELSEIF NEW.rank_search >= 16 THEN - -- up to rank 16, street-less addresses may need reparenting - 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 or addr_place is not null); - 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); + -- 16 = city, anything higher than city is effectively ignored (polygon required!) + IF NEW.type='postcode' THEN + diameter := 0.05; + ELSEIF NEW.rank_search < 16 THEN + diameter := 0; + ELSEIF NEW.rank_search < 18 THEN + diameter := 0.1; + ELSEIF NEW.rank_search < 20 THEN + diameter := 0.05; + ELSEIF NEW.rank_search = 21 THEN + diameter := 0.001; + ELSEIF NEW.rank_search < 24 THEN + diameter := 0.02; + ELSEIF NEW.rank_search < 26 THEN + diameter := 0.002; -- 100 to 200 meters + ELSEIF NEW.rank_search < 28 THEN + diameter := 0.001; -- 50 to 100 meters + END IF; + IF diameter > 0 THEN + -- RAISE WARNING 'placex point insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,diameter; + 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); + ELSEIF NEW.rank_search >= 16 THEN + -- up to rank 16, street-less addresses may need reparenting + 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 or addr_place is not null); + 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; - END IF; -- add to tables for special search @@ -1258,6 +1290,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[]; @@ -1349,6 +1382,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); @@ -1405,6 +1457,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 @@ -1414,7 +1476,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; @@ -1443,7 +1505,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; @@ -1451,13 +1513,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; @@ -1493,7 +1552,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; @@ -1503,18 +1562,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; @@ -1598,17 +1657,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; @@ -1637,13 +1692,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; @@ -1685,11 +1734,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; @@ -1986,15 +2031,18 @@ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION place_delete() RETURNS TRIGGER AS $$ DECLARE - placeid BIGINT; + has_rank BOOLEAN; BEGIN --DEBUG: RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type; -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through IF st_area(OLD.geometry) > 2 and st_isvalid(OLD.geometry) THEN - insert into import_polygon_delete values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type); - RETURN NULL; + SELECT bool_or(not (rank_address = 0 or rank_address > 26)) as ranked FROM placex WHERE osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type INTO has_rank; + IF has_rank THEN + insert into import_polygon_delete values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type); + RETURN NULL; + END IF; END IF; -- mark for delete @@ -2026,6 +2074,11 @@ BEGIN --DEBUG: RAISE WARNING '%', existingplacex; END IF; + -- remove operator tag for most places, messes too much with search_name indexes + IF NEW.class not in ('amenity', 'shop') THEN + NEW.name := delete(NEW.name, 'operator'); + END IF; + -- Just block these - lots and pointless IF NEW.class in ('landuse','natural') and NEW.name is null THEN -- if the name tag was removed, older versions might still be lurking in the place table @@ -2054,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; @@ -2216,6 +2269,12 @@ BEGIN END IF; + -- refuse to update multiplpoygons with too many objects, too much of a performance hit + IF ST_NumGeometries(NEW.geometry) > 2000 THEN + RAISE WARNING 'Dropping update of % % because of geometry complexity.', NEW.osm_type, NEW.osm_id; + RETURN NULL; + END IF; + IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '') OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::text, '') OR coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '') @@ -2300,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 $$ @@ -2454,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 @@ -2465,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; @@ -2485,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) @@ -2916,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 @@ -3075,6 +3139,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