$sBasePath = CONST_BasePath.'/sql/functions/';
$sTemplate = file_get_contents($sBasePath.'utils.sql');
$sTemplate .= file_get_contents($sBasePath.'normalization.sql');
+ $sTemplate .= file_get_contents($sBasePath.'ranking.sql');
$sTemplate .= file_get_contents($sBasePath.'importance.sql');
$sTemplate .= file_get_contents($sBasePath.'address_lookup.sql');
$sTemplate .= file_get_contents($sBasePath.'interpolation.sql');
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
- 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;
+ diameter := update_place_diameter(NEW.rank_search);
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
--- /dev/null
+-- Functions related to search and address ranks
+
+-- Return an approximate search radius according to the search rank.
+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;
+
+
+-- Return an approximate update radius according to the search rank.
+CREATE OR REPLACE FUNCTION update_place_diameter(rank_search SMALLINT)
+ RETURNS FLOAT
+ AS $$
+BEGIN
+ -- postcodes
+ IF rank_search = 11 or rank_search = 5 THEN
+ RETURN 0.05;
+ -- anything higher than city is effectively ignored (polygon required)
+ ELSIF rank_search < 16 THEN
+ RETURN 0;
+ ELSIF rank_search < 18 THEN
+ RETURN 0.1;
+ ELSIF rank_search < 20 THEN
+ RETURN 0.05;
+ ELSIF rank_search = 21 THEN
+ RETURN 0.001;
+ ELSIF rank_search < 24 THEN
+ RETURN 0.02;
+ ELSIF rank_search < 26 THEN
+ RETURN 0.002;
+ ELSIF rank_search < 28 THEN
+ RETURN 0.001;
+ END IF;
+
+ RETURN 0;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
+
+-- Guess a ranking for postcodes from country and postcode format.
+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;
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)
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
projects / nominatim.git / commitdiff