factor out computation of address and search rank

diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql
index 0151162eb74d192f7f86115276a41cc327154ce6..3f9fae7a8803bb487fe5b145d620c4c72958801d 100644 (file)
--- a/sql/functions/placex_triggers.sql
+++ b/sql/functions/placex_triggers.sql
@@ -410,8 +410,8 @@ BEGIN
     is_area := ST_GeometryType(NEW.geometry) IN ('ST_Polygon','ST_MultiPolygon');
 
     IF NEW.class in ('place','boundary')
-       AND NEW.type in ('postcode','postal_code') THEN
-
+       AND NEW.type in ('postcode','postal_code')
+    THEN
       IF NEW.address IS NULL OR NOT NEW.address ? 'postcode' THEN
           -- most likely just a part of a multipolygon postcode boundary, throw it away
           RETURN NULL;
@@ -419,63 +419,27 @@ BEGIN
 
       NEW.name := hstore('ref', NEW.address->'postcode');
 
-      SELECT * FROM get_postcode_rank(NEW.country_code, NEW.address->'postcode')
-        INTO NEW.rank_search, NEW.rank_address;
-
-      IF NOT is_area THEN
-          NEW.rank_address := 0;
-      END IF;
     ELSEIF NEW.class = 'boundary' AND NOT is_area THEN
-        return NULL;
+        RETURN NULL;
     ELSEIF NEW.class = 'boundary' AND NEW.type = 'administrative'
-           AND NEW.admin_level <= 4 AND NEW.osm_type = 'W' THEN
-        return NULL;
-    ELSEIF NEW.osm_type = 'N' AND NEW.class = 'highway' THEN
-        NEW.rank_search = 30;
-        NEW.rank_address = 0;
-    ELSEIF NEW.class = 'landuse' AND NOT is_area THEN
-        NEW.rank_search = 30;
-        NEW.rank_address = 0;
-    ELSE
-      -- do table lookup stuff
-      IF NEW.class = 'boundary' and NEW.type = 'administrative' THEN
-        classtype = NEW.type || NEW.admin_level::TEXT;
-      ELSE
-        classtype = NEW.type;
-      END IF;
-      SELECT l.rank_search, l.rank_address FROM address_levels l
-       WHERE (l.country_code = NEW.country_code or l.country_code is NULL)
-             AND l.class = NEW.class AND (l.type = classtype or l.type is NULL)
-       ORDER BY l.country_code, l.class, l.type LIMIT 1
-        INTO NEW.rank_search, NEW.rank_address;
-
-      IF NEW.rank_search is NULL THEN
-        NEW.rank_search := 30;
-      END IF;
-
-      IF NEW.rank_address is NULL THEN
-        NEW.rank_address := 30;
-      END IF;
+           AND NEW.admin_level <= 4 AND NEW.osm_type = 'W'
+    THEN
+        RETURN NULL;
     END IF;
 
-    -- some postcorrections
-    IF NEW.class = 'waterway' AND NEW.osm_type = 'R' THEN
-        -- Slightly promote waterway relations so that they are processed
-        -- before their members.
-        NEW.rank_search := NEW.rank_search - 1;
-    END IF;
+    SELECT * INTO NEW.rank_search, NEW.rank_address
+      FROM compute_place_rank(NEW.country_code, NEW.osm_type, NEW.class,
+                              NEW.type, NEW.admin_level, is_area,
+                              (NEW.extratags->'capital') = 'yes',
+                              NEW.address->'postcode');
 
-    IF (NEW.extratags -> 'capital') = 'yes' THEN
-      NEW.rank_search := NEW.rank_search - 1;
+    -- a country code make no sense below rank 4 (country)
+    IF NEW.rank_search < 4 THEN
+      NEW.country_code := NULL;
     END IF;
 
   END IF;
 
-  -- a country code make no sense below rank 4 (country)
-  IF NEW.rank_search < 4 THEN
-    NEW.country_code := NULL;
-  END IF;
-
   --DEBUG: RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
 
   RETURN NEW; -- %DIFFUPDATES% The following is not needed until doing diff updates, and slows the main index process down

diff --git a/sql/functions/ranking.sql b/sql/functions/ranking.sql
index ecd31e90d03e81d3629f0c92d2b77296483a884d..d23944b1c64271a2a0a49060751b5221fd53211c 100644 (file)
--- a/sql/functions/ranking.sql
+++ b/sql/functions/ranking.sql
@@ -114,3 +114,68 @@ BEGIN
 END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
+
+
+-- Get standard search and address rank for an object
+CREATE OR REPLACE FUNCTION compute_place_rank(country VARCHAR(2),
+                                              osm_type VARCHAR(1),
+                                              place_class TEXT, place_type TEXT,
+                                              admin_level SMALLINT,
+                                              is_area BOOLEAN, is_major BOOLEAN,
+                                              postcode TEXT,
+                                              OUT search_rank SMALLINT,
+                                              OUT address_rank SMALLINT)
+AS $$
+DECLARE
+  classtype TEXT;
+BEGIN
+  IF place_class in ('place','boundary')
+     and place_type in ('postcode','postal_code')
+  THEN
+    SELECT * INTO search_rank, address_rank
+      FROM get_postcode_rank(country, postcode);
+
+    IF NOT is_area THEN
+      address_rank := 0;
+    END IF;
+  ELSEIF osm_type = 'N' AND place_class = 'highway' THEN
+    search_rank = 30;
+    address_rank = 0;
+  ELSEIF place_class = 'landuse' AND NOT is_area THEN
+    search_rank = 30;
+    address_rank = 0;
+  ELSE
+    IF place_class = 'boundary' and place_type = 'administrative' THEN
+      classtype = place_type || admin_level::TEXT;
+    ELSE
+      classtype = place_type;
+    END IF;
+
+    SELECT l.rank_search, l.rank_address INTO search_rank, address_rank
+      FROM address_levels l
+     WHERE (l.country_code = country or l.country_code is NULL)
+           AND l.class = place_class AND (l.type = classtype or l.type is NULL)
+     ORDER BY l.country_code, l.class, l.type LIMIT 1;
+
+    IF search_rank is NULL THEN
+      search_rank := 30;
+    END IF;
+
+    IF address_rank is NULL THEN
+      address_rank := 30;
+    END IF;
+
+    -- some postcorrections
+    IF place_class = 'waterway' AND osm_type = 'R' THEN
+        -- Slightly promote waterway relations so that they are processed
+        -- before their members.
+        search_rank := search_rank - 1;
+    END IF;
+
+    IF is_major THEN
+      search_rank := search_rank - 1;
+    END IF;
+  END IF;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;