X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/7a194789bc759e76418447b59c0cbcbbd6846d46..cca646a19e8013f39911a5c4faaaa15b89ce083f:/sql/functions/utils.sql diff --git a/sql/functions/utils.sql b/sql/functions/utils.sql index 6980a583..0a49eef5 100644 --- a/sql/functions/utils.sql +++ b/sql/functions/utils.sql @@ -38,85 +38,58 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; - -CREATE OR REPLACE FUNCTION reverse_place_diameter(rank_search SMALLINT) - RETURNS FLOAT +-- Return the node members with a given label from a relation member list +-- as a set. +-- +-- \param members Member list in osm2pgsql middle format. +-- \param memberLabels Array of labels to accept. +-- +-- \returns Set of OSM ids of nodes that are found. +-- +CREATE OR REPLACE FUNCTION get_rel_node_members(members TEXT[], + memberLabels TEXT[]) + RETURNS SETOF BIGINT AS $$ +DECLARE + i INTEGER; BEGIN - IF rank_search <= 4 THEN - RETURN 5.0; - ELSIF rank_search <= 8 THEN - RETURN 1.8; - ELSIF rank_search <= 12 THEN - RETURN 0.6; - ELSIF rank_search <= 17 THEN - RETURN 0.16; - ELSIF rank_search <= 18 THEN - RETURN 0.08; - ELSIF rank_search <= 19 THEN - RETURN 0.04; - END IF; + FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP + IF members[i+1] = ANY(memberLabels) + AND upper(substring(members[i], 1, 1))::char(1) = 'N' + THEN + RETURN NEXT substring(members[i], 2)::bigint; + END IF; + END LOOP; - RETURN 0.02; + RETURN; END; $$ LANGUAGE plpgsql IMMUTABLE; - -CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT, - OUT rank_search SMALLINT, - OUT rank_address SMALLINT) -AS $$ +-- Copy 'name' to or from the default language. +-- +-- \param country_code Country code of the object being named. +-- \param[inout] name List of names of the object. +-- +-- If the country named by country_code has a single default language, +-- then a `name` tag is copied to `name:` if this tag does +-- not yet exist and vice versa. +CREATE OR REPLACE FUNCTION add_default_place_name(country_code VARCHAR(2), + INOUT name HSTORE) + AS $$ DECLARE - part TEXT; + default_language VARCHAR(10); BEGIN - rank_search := 30; - rank_address := 30; - postcode := upper(postcode); - - IF country_code = 'gb' THEN - IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN - rank_search := 25; - rank_address := 5; - ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN - rank_search := 23; - rank_address := 5; - ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN - rank_search := 21; - rank_address := 5; - END IF; - - ELSEIF country_code = 'sg' THEN - IF postcode ~ '^([0-9]{6})$' THEN - rank_search := 25; - rank_address := 11; - END IF; - - ELSEIF country_code = 'de' THEN - IF postcode ~ '^([0-9]{5})$' THEN - rank_search := 21; - rank_address := 11; - END IF; - - ELSE - -- Guess at the postcode format and coverage (!) - IF postcode ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local - rank_search := 21; - rank_address := 11; - ELSE - -- Does it look splitable into and area and local code? - part := substring(postcode from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$'); - - IF part IS NOT NULL THEN - rank_search := 25; - rank_address := 11; - ELSEIF postcode ~ '^[- :A-Z0-9]{6,}$' THEN - rank_search := 21; - rank_address := 11; - END IF; - END IF; + IF name is not null AND array_upper(akeys(name),1) > 1 THEN + default_language := get_country_language_code(country_code); + IF default_language IS NOT NULL THEN + IF name ? 'name' AND NOT name ? ('name:'||default_language) THEN + name := name || hstore(('name:'||default_language), (name -> 'name')); + ELSEIF name ? ('name:'||default_language) AND NOT name ? 'name' THEN + name := name || hstore('name', (name -> ('name:'||default_language))); + END IF; END IF; - + END IF; END; $$ LANGUAGE plpgsql IMMUTABLE; @@ -223,41 +196,70 @@ $$ LANGUAGE plpgsql STABLE; -CREATE OR REPLACE FUNCTION get_country_language_codes(search_country_code VARCHAR(2)) - RETURNS TEXT[] +CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10)) + RETURNS INTEGER AS $$ DECLARE nearcountry RECORD; BEGIN FOR nearcountry IN - SELECT country_default_language_codes from country_name - WHERE country_code = search_country_code limit 1 + SELECT partition from country_name where country_code = in_country_code LOOP - RETURN lower(nearcountry.country_default_language_codes); + RETURN nearcountry.partition; END LOOP; - RETURN NULL; + RETURN 0; END; $$ LANGUAGE plpgsql STABLE; -CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10)) - RETURNS INTEGER +-- Find the parent of an address with addr:street/addr:place tag. +-- +-- \param street Value of addr:street or NULL if tag is missing. +-- \param place Value of addr:place or NULL if tag is missing. +-- \param partition Partition where to search the parent. +-- \param centroid Location of the address. +-- +-- \return Place ID of the parent if one was found, NULL otherwise. +CREATE OR REPLACE FUNCTION find_parent_for_address(street TEXT, place TEXT, + partition SMALLINT, + centroid GEOMETRY) + RETURNS BIGINT AS $$ DECLARE - nearcountry RECORD; + parent_place_id BIGINT; + word_ids INTEGER[]; BEGIN - FOR nearcountry IN - SELECT partition from country_name where country_code = in_country_code - LOOP - RETURN nearcountry.partition; - END LOOP; - RETURN 0; + IF street is not null THEN + -- Check for addr:street attributes + -- Note that addr:street links can only be indexed, once the street itself is indexed + word_ids := word_ids_from_name(street); + IF word_ids is not null THEN + parent_place_id := getNearestNamedRoadPlaceId(partition, centroid, word_ids); + IF parent_place_id is not null THEN + --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent_place_id; + RETURN parent_place_id; + END IF; + END IF; + END IF; + + -- Check for addr:place attributes. + IF place is not null THEN + word_ids := word_ids_from_name(place); + IF word_ids is not null THEN + parent_place_id := getNearestNamedPlacePlaceId(partition, centroid, word_ids); + IF parent_place_id is not null THEN + --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent_place_id; + RETURN parent_place_id; + END IF; + END IF; + END IF; + + RETURN NULL; END; $$ LANGUAGE plpgsql STABLE; - CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT) RETURNS BOOLEAN AS $$ @@ -270,28 +272,45 @@ END; $$ LANGUAGE plpgsql; +-- Create a bounding box with an extent computed from the radius (in meters) +-- which in turn is derived from the given search rank. +CREATE OR REPLACE FUNCTION place_node_fuzzy_area(geom GEOMETRY, rank_search INTEGER) + RETURNS GEOMETRY + AS $$ +DECLARE + radius FLOAT := 500; +BEGIN + IF rank_search <= 16 THEN -- city + radius := 15000; + ELSIF rank_search <= 18 THEN -- town + radius := 4000; + ELSIF rank_search <= 19 THEN -- village + radius := 2000; + ELSIF rank_search <= 20 THEN -- hamlet + radius := 1000; + END IF; + + RETURN ST_Envelope(ST_Collect( + ST_Project(geom, radius, 0.785398)::geometry, + ST_Project(geom, radius, 3.9269908)::geometry)); +END; +$$ +LANGUAGE plpgsql IMMUTABLE; + CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2), partition INTEGER, keywords INTEGER[], rank_search INTEGER, rank_address INTEGER, - in_postcode TEXT, geometry GEOMETRY) + in_postcode TEXT, geometry GEOMETRY, + centroid GEOMETRY) RETURNS BOOLEAN AS $$ DECLARE locationid INTEGER; - centroid GEOMETRY; - diameter FLOAT; - x BOOLEAN; - splitGeom RECORD; secgeo GEOMETRY; postcode TEXT; BEGIN - - IF rank_search > 25 THEN - RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search; - END IF; - - x := deleteLocationArea(partition, place_id, rank_search); + PERFORM deleteLocationArea(partition, place_id, rank_search); -- add postcode only if it contains a single entry, i.e. ignore postcode lists postcode := NULL; @@ -300,35 +319,13 @@ BEGIN END IF; IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN - centroid := ST_Centroid(geometry); - FOR secgeo IN select split_geometry(geometry) AS geom LOOP - x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo); + PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo); END LOOP; - ELSE - - diameter := 0.02; - IF rank_address = 0 THEN - diameter := 0.02; - ELSEIF rank_search <= 14 THEN - diameter := 1.2; - ELSEIF rank_search <= 15 THEN - diameter := 1; - ELSEIF rank_search <= 16 THEN - diameter := 0.5; - ELSEIF rank_search <= 17 THEN - diameter := 0.2; - ELSEIF rank_search <= 21 THEN - diameter := 0.05; - ELSEIF rank_search = 25 THEN - diameter := 0.005; - END IF; - --- RAISE WARNING 'adding % diameter %', place_id, diameter; - - secgeo := ST_Buffer(geometry, diameter); - x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, ST_Centroid(geometry), secgeo); + ELSEIF ST_GeometryType(geometry) = 'ST_Point' THEN + secgeo := place_node_fuzzy_area(geometry, rank_search); + PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, centroid, secgeo); END IF; @@ -338,45 +335,6 @@ $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], member TEXT) - RETURNS TEXT[] - AS $$ -DECLARE - result TEXT[]; - i INTEGER; -BEGIN - - FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP - IF members[i+1] = member THEN - result := result || members[i]; - END IF; - END LOOP; - - return result; -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - - -CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], memberLabels TEXT[]) - RETURNS SETOF TEXT - AS $$ -DECLARE - i INTEGER; -BEGIN - - FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP - IF members[i+1] = ANY(memberLabels) THEN - RETURN NEXT members[i]; - END IF; - END LOOP; - - RETURN; -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - - CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT, maxdepth INTEGER) RETURNS SETOF GEOMETRY @@ -468,7 +426,7 @@ DECLARE geo RECORD; BEGIN -- 10000000000 is ~~ 1x1 degree - FOR geo IN select quad_split_geometry(geometry, 0.25, 20) as geom LOOP + FOR geo IN select quad_split_geometry(geometry, 0.01, 20) as geom LOOP RETURN NEXT geo.geom; END LOOP; RETURN; @@ -506,35 +464,26 @@ DECLARE placegeom GEOMETRY; geom GEOMETRY; diameter FLOAT; - rank INTEGER; + rank SMALLINT; BEGIN UPDATE placex SET indexed_status = 2 WHERE place_id = placeid; - SELECT geometry, rank_search FROM placex WHERE place_id = placeid INTO placegeom, rank; + + SELECT geometry, rank_address INTO placegeom, rank + FROM placex WHERE place_id = placeid; + 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 or (rank >= 16 and address ? 'place')); - 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 or (rank >= 16 and address ? 'place')); + IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') + AND rank > 0 + THEN + FOR geom IN SELECT split_geometry(placegeom) LOOP + UPDATE placex SET indexed_status = 2 + WHERE ST_Intersects(geom, placex.geometry) + and indexed_status = 0 + and ((rank_address = 0 and rank_search > rank) or rank_address > rank) + and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place')); 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; + diameter := update_place_diameter(rank); IF diameter > 0 THEN IF rank >= 26 THEN -- roads may cause reparenting for >27 rank places