X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/4a30ec28b9f5be3851289d1eb0bf1fe5228e5b6b..6478058946efbd9cfd8495ac0e14e485e4004838:/sql/functions/utils.sql diff --git a/sql/functions/utils.sql b/sql/functions/utils.sql index ccb6a203..33ae340a 100644 --- a/sql/functions/utils.sql +++ b/sql/functions/utils.sql @@ -95,90 +95,6 @@ $$ LANGUAGE plpgsql IMMUTABLE; - -CREATE OR REPLACE FUNCTION reverse_place_diameter(rank_search SMALLINT) - RETURNS FLOAT - AS $$ -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; - - RETURN 0.02; -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 $$ -DECLARE - part TEXT; -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; - END IF; - -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - - -- Find the nearest artificial postcode for the given geometry. -- TODO For areas there should not be more than two inside the geometry. CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY) @@ -356,6 +272,25 @@ END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION near_feature_rank_distance(rank_search INTEGER) + RETURNS FLOAT + AS $$ +BEGIN + IF rank_search <= 16 THEN -- city + RETURN 7500; + ELSIF rank_search <= 18 THEN -- town + RETURN 4000; + ELSIF rank_search <= 19 THEN -- village + RETURN 2000; + ELSIF rank_search <= 20 THEN -- hamlet + RETURN 1000; + END IF; + + RETURN 500; +END; +$$ +LANGUAGE plpgsql IMMUTABLE; + CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2), partition INTEGER, keywords INTEGER[], @@ -366,18 +301,11 @@ CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2) DECLARE locationid INTEGER; centroid GEOMETRY; - diameter FLOAT; - x BOOLEAN; - splitGeom RECORD; + radius FLOAT; 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; @@ -389,32 +317,18 @@ BEGIN 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; + ELSEIF ST_GeometryType(geometry) = 'ST_Point' THEN + radius := near_feature_rank_distance(rank_search); + --DEBUG: RAISE WARNING 'adding % diameter %', place_id, diameter; --- 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); + -- Create a bounding box with an extent computed from the radius (in meters). + secgeo := ST_Envelope(ST_Collect( + ST_Project(geometry, radius, 0.785398)::geometry, + ST_Project(geometry, radius, 3.9269908)::geometry)); + PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, geometry, secgeo); END IF; @@ -553,7 +467,7 @@ 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; @@ -566,22 +480,7 @@ BEGIN 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')); 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