move ranks-related functions in separate sql file

Also adds a common function for computing the update radius
around place nodes.

diff --git a/lib/setup/SetupClass.php b/lib/setup/SetupClass.php
index aa1b291d1a54eda111bb7740ed2b15b2771bbad3..ac0f8f02f89e33ccc6d0cae4a23c14fb042f98cb 100755 (executable)
--- a/lib/setup/SetupClass.php
+++ b/lib/setup/SetupClass.php
@@ -695,6 +695,7 @@ class SetupFunctions
         $sBasePath = CONST_BasePath.'/sql/functions/';
         $sTemplate = file_get_contents($sBasePath.'utils.sql');
         $sTemplate .= file_get_contents($sBasePath.'normalization.sql');
         $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');
         $sTemplate .= file_get_contents($sBasePath.'importance.sql');
         $sTemplate .= file_get_contents($sBasePath.'address_lookup.sql');
         $sTemplate .= file_get_contents($sBasePath.'interpolation.sql');

diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql
index 19367db477165b6bfd173948da8782f0f591f177..0151162eb74d192f7f86115276a41cc327154ce6 100644 (file)
--- a/sql/functions/placex_triggers.sql
+++ b/sql/functions/placex_triggers.sql
@@ -498,25 +498,7 @@ BEGIN
       END IF;
     ELSE
       -- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(
       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
       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

diff --git a/sql/functions/ranking.sql b/sql/functions/ranking.sql
new file mode 100644 (file)
index 0000000..ecd31e9
--- /dev/null
+++ b/sql/functions/ranking.sql
@@ -0,0 +1,116 @@
+-- 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;

diff --git a/sql/functions/utils.sql b/sql/functions/utils.sql
index ccb6a2033ca057ec521150711c16637c780ba99d..61033fb4d3a5db50118f66fa93a6a7378c4f5660 100644 (file)
--- a/sql/functions/utils.sql
+++ b/sql/functions/utils.sql
@@ -95,90 +95,6 @@ $$
 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)
 -- 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)


@@ -566,22 +482,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
         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
         IF diameter > 0 THEN
           IF rank >= 26 THEN
             -- roads may cause reparenting for >27 rank places