change place node expansion for large area table

So far we've used a buffer around a place node to define its
potential address reach. This had two problems: the buffer was
so large that addresses often contain false positives and the
buffer is really distorted when getting closer to the poles.

Change the buffer here to draw a bounndig box at a certain
distance in meter. This means that we always use the same
box everywhere on the planet and can make the extent much
smaller. Using a box has the advantage that it is much faster
to figure out if a point is within the box.

diff --git a/settings/address-levels.json b/settings/address-levels.json
index 8b25fb1a48da6d3a5ef15d8aff1ba58d1cc82c67..b7fbe2f2f2f06f8ec1a822fb6c34bcc311be28ff 100644 (file)
--- a/settings/address-levels.json
+++ b/settings/address-levels.json
@@ -2,6 +2,8 @@
 { "tags" : {
       "place" : {
           "sea" : [2, 0],
 { "tags" : {
       "place" : {
           "sea" : [2, 0],

+          "island" : [17, 0],
+          "islet" : [20, 0],

           "continent" : [2, 0],
           "country" : [4, 0],
           "state" : [8, 0],
           "continent" : [2, 0],
           "country" : [4, 0],
           "state" : [8, 0],


@@ -10,25 +12,23 @@
           "county" : 12,
           "municipality" : [17, 14],
           "city" : 16,
           "county" : 12,
           "municipality" : [17, 14],
           "city" : 16,

-          "island" : [17, 0],

           "town" : [18, 16],
           "town" : [18, 16],

+          "borough" : 18,

           "village" : [19, 16],
           "district" : [19, 16],
           "village" : [19, 16],
           "district" : [19, 16],

-          "borough" : [19, 18],
+          "suburb" : [19, 20],

           "hamlet" : 20,
           "hamlet" : 20,

-          "suburb" : 20,

           "croft" : 20,
           "subdivision" : 20,
           "croft" : 20,
           "subdivision" : 20,

-          "isolated_dwelling" : 20,

           "allotments" : 20,
           "allotments" : 20,

-          "farm" : [20, 0],
-          "locality" : [20, 0],
-          "islet" : [20, 0],
-          "mountain_pass" : [20, 0],
-          "neighbourhood" : 22,
-          "quarter" : 22,
+          "neighbourhood" : [20, 22],
+          "quarter" : [20, 22],
+          "isolated_dwelling" : [22, 20],

           "city_block" : 22,
           "city_block" : 22,

-          "houses" : [28, 0]
+          "mountain_pass" : [20, 0],
+          "houses" : [28, 0],
+          "farm" : [20, 0],
+          "locality" : [20, 0]

       },
       "boundary" : {
           "administrative2" : 4,
       },
       "boundary" : {
           "administrative2" : 4,

diff --git a/sql/functions/utils.sql b/sql/functions/utils.sql
index 61033fb4d3a5db50118f66fa93a6a7378c4f5660..2b3db04a92116b38c4f0504f1372923d313ed897 100644 (file)
--- a/sql/functions/utils.sql
+++ b/sql/functions/utils.sql
@@ -272,6 +272,25 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 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[],
 
 CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2),
                                         partition INTEGER, keywords INTEGER[],


@@ -282,18 +301,11 @@ CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2)
 DECLARE
   locationid INTEGER;
   centroid GEOMETRY;
 DECLARE
   locationid INTEGER;
   centroid GEOMETRY;

-  diameter FLOAT;
-  x BOOLEAN;
-  splitGeom RECORD;
+  radius FLOAT;

   secgeo GEOMETRY;
   postcode TEXT;
 BEGIN
   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;
 
   -- add postcode only if it contains a single entry, i.e. ignore postcode lists
   postcode := NULL;


@@ -305,32 +317,18 @@ BEGIN
     centroid := ST_Centroid(geometry);
 
     FOR secgeo IN select split_geometry(geometry) AS geom LOOP
     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;
 
     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;
+  ELSEIF ST_GeometryType(geometry) = 'ST_Point' THEN
+    radius := near_feature_rank_distance(rank_search);
+    --DEBUG: 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;
 
 
   END IF;