more efficient belongs-to-address determination

diff --git a/lib-sql/functions/partition-functions.sql b/lib-sql/functions/partition-functions.sql
index 20ec3da6bb6853ec38c334ec483f784320e7dc0b..94ed263991b5fcaeb032a3c1818c344aa050a98c 100644 (file)
--- a/lib-sql/functions/partition-functions.sql
+++ b/lib-sql/functions/partition-functions.sql
@@ -17,28 +17,6 @@ CREATE TYPE nearfeaturecentr AS (
   centroid GEOMETRY
 );
 
   centroid GEOMETRY
 );
 

--- feature intersects geometry
--- for areas and linestrings they must touch at least along a line
-CREATE OR REPLACE FUNCTION is_relevant_geometry(de9im TEXT, geom_type TEXT)
-RETURNS BOOLEAN
-AS $$
-BEGIN
-  IF substring(de9im from 1 for 2) != 'FF' THEN
-    RETURN TRUE;
-  END IF;
-
-  IF geom_type = 'ST_Point' THEN
-    RETURN substring(de9im from 4 for 1) = '0';
-  END IF;
-
-  IF geom_type in ('ST_LineString', 'ST_MultiLineString') THEN
-    RETURN substring(de9im from 4 for 1) = '1';
-  END IF;
-
-  RETURN substring(de9im from 4 for 1) = '2';
-END
-$$ LANGUAGE plpgsql IMMUTABLE;
-

 CREATE OR REPLACE function getNearFeatures(in_partition INTEGER, feature GEOMETRY,
                                            feature_centroid GEOMETRY,
                                            maxrank INTEGER)
 CREATE OR REPLACE function getNearFeatures(in_partition INTEGER, feature GEOMETRY,
                                            feature_centroid GEOMETRY,
                                            maxrank INTEGER)


@@ -59,7 +37,12 @@ BEGIN
              isguess, postcode, centroid
       FROM location_area_large_{{ partition }}
       WHERE geometry && feature
              isguess, postcode, centroid
       FROM location_area_large_{{ partition }}
       WHERE geometry && feature

-        AND is_relevant_geometry(ST_Relate(geometry, feature), ST_GeometryType(feature))
+        AND CASE WHEN ST_Dimension(feature) = 0
+                     THEN _ST_Covers(geometry, feature)
+                 WHEN ST_Dimension(feature) = 2
+                     THEN ST_Relate(geometry, feature, 'T********')
+                 ELSE ST_NPoints(ST_Intersection(geometry, feature)) > 1
+            END

         AND rank_address < maxrank
             -- Postcodes currently still use rank_search to define for which
             -- features they are relevant.
         AND rank_address < maxrank
             -- Postcodes currently still use rank_search to define for which
             -- features they are relevant.