move geometry split into insertLocationAreaLarge()

[nominatim.git] / lib-sql / functions / partition-functions.sql

diff --git a/lib-sql/functions/partition-functions.sql b/lib-sql/functions/partition-functions.sql
index cfa151de14c8aa268ebad34b70901e94c38058f5..595e4a614a775d4d8623f8b4c2708e24fe6d570d 100644 (file)
--- a/lib-sql/functions/partition-functions.sql
+++ b/lib-sql/functions/partition-functions.sql
@@ -1,3 +1,10 @@
+-- SPDX-License-Identifier: GPL-2.0-only
+--
+-- This file is part of Nominatim. (https://nominatim.org)
+--
+-- Copyright (C) 2022 by the Nominatim developer community.
+-- For a full list of authors see the git log.
+

 DROP TYPE IF EXISTS nearfeaturecentr CASCADE;
 CREATE TYPE nearfeaturecentr AS (
   place_id BIGINT,
 DROP TYPE IF EXISTS nearfeaturecentr CASCADE;
 CREATE TYPE nearfeaturecentr AS (
   place_id BIGINT,


@@ -10,29 +17,10 @@ CREATE TYPE nearfeaturecentr AS (
   centroid GEOMETRY
 );
 
   centroid GEOMETRY
 );
 

--- feature intersects geoemtry
--- 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, maxrank INTEGER) RETURNS setof nearfeaturecentr AS $$
+CREATE OR REPLACE function getNearFeatures(in_partition INTEGER, feature GEOMETRY,
+                                           feature_centroid GEOMETRY,
+                                           maxrank INTEGER)
+RETURNS setof nearfeaturecentr AS $$

 DECLARE
   r nearfeaturecentr%rowtype;
 BEGIN
 DECLARE
   r nearfeaturecentr%rowtype;
 BEGIN


@@ -41,11 +29,20 @@ BEGIN
   IF in_partition = {{ partition }} THEN
     FOR r IN
       SELECT place_id, keywords, rank_address, rank_search,
   IF in_partition = {{ partition }} THEN
     FOR r IN
       SELECT place_id, keywords, rank_address, rank_search,

-             min(ST_Distance(feature, centroid)) as distance,
+             CASE WHEN isguess THEN ST_Distance(feature, centroid)
+                  ELSE min(ST_Distance(feature_centroid, geometry))
+                       -- tie breaker when distance is the same (i.e. way is on boundary)
+                       + 0.00001 * ST_Distance(feature, centroid)
+             END as distance,

              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.


@@ -63,54 +60,36 @@ END
 $$
 LANGUAGE plpgsql STABLE;
 
 $$
 LANGUAGE plpgsql STABLE;
 

-CREATE OR REPLACE FUNCTION get_places_for_addr_tags(in_partition SMALLINT,
-                                                    feature GEOMETRY,
-                                                    address HSTORE, country TEXT)
-  RETURNS SETOF nearfeaturecentr
+
+CREATE OR REPLACE FUNCTION get_address_place(in_partition SMALLINT, feature GEOMETRY,
+                                             from_rank SMALLINT, to_rank SMALLINT,
+                                             extent FLOAT, token_info JSONB, key TEXT)
+  RETURNS nearfeaturecentr

   AS $$
 DECLARE
   r nearfeaturecentr%rowtype;
   AS $$
 DECLARE
   r nearfeaturecentr%rowtype;

-  item RECORD;

 BEGIN
 BEGIN

-  FOR item IN
-    SELECT (get_addr_tag_rank(key, country)).*, key, name FROM
-      (SELECT skeys(address) as key, svals(address) as name) x
-  LOOP
-   IF item.from_rank is null THEN
-     CONTINUE;
-   END IF;
-

 {% for partition in db.partitions %}
 {% for partition in db.partitions %}

-    IF in_partition = {{ partition }} THEN
-        SELECT place_id, keywords, rank_address, rank_search,
-               min(ST_Distance(feature, centroid)) as distance,
-               isguess, postcode, centroid INTO r
+  IF in_partition = {{ partition }} THEN
+      SELECT place_id, keywords, rank_address, rank_search,
+             min(ST_Distance(feature, centroid)) as distance,
+             isguess, postcode, centroid INTO r

         FROM location_area_large_{{ partition }}
         FROM location_area_large_{{ partition }}

-        WHERE geometry && ST_Expand(feature, item.extent)
-          AND rank_address between item.from_rank and item.to_rank
-          AND word_ids_from_name(item.name) && keywords
+        WHERE geometry && ST_Expand(feature, extent)
+              AND rank_address between from_rank and to_rank
+              AND token_matches_address(token_info, key, keywords)

         GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid
         GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid

-        ORDER BY bool_or(ST_Intersects(geometry, feature)), distance LIMIT 1;
-      IF r.place_id is null THEN
-        -- If we cannot find a place for the term, just return the
-        -- search term for the given name. That ensures that the address
-        -- element can still be searched for, even though it will not be
-        -- displayed.
-        RETURN NEXT ROW(null, addr_ids_from_name(item.name), null, null,
-                        null, null, null, null)::nearfeaturecentr;
-      ELSE
-        RETURN NEXT r;
-      END IF;
-      CONTINUE;
-    END IF;
+        ORDER BY bool_or(ST_Intersects(geometry, feature)) DESC, distance LIMIT 1;
+      RETURN r;
+  END IF;

 {% endfor %}
 
 {% endfor %}
 

-    RAISE EXCEPTION 'Unknown partition %', in_partition;
-  END LOOP;
+  RAISE EXCEPTION 'Unknown partition %', in_partition;

 END;
 $$
 LANGUAGE plpgsql STABLE;
 
 END;
 $$
 LANGUAGE plpgsql STABLE;
 

+

 create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT, in_rank_search INTEGER) RETURNS BOOLEAN AS $$
 DECLARE
 BEGIN
 create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT, in_rank_search INTEGER) RETURNS BOOLEAN AS $$
 DECLARE
 BEGIN


@@ -146,14 +125,16 @@ BEGIN
 
   IF in_rank_search <= 4 and not in_estimate THEN
     INSERT INTO location_area_country (place_id, country_code, geometry)
 
   IF in_rank_search <= 4 and not in_estimate THEN
     INSERT INTO location_area_country (place_id, country_code, geometry)

-      values (in_place_id, in_country_code, in_geometry);
+      (SELECT in_place_id, in_country_code, geom
+       FROM split_geometry(in_geometry) as geom);

     RETURN TRUE;
   END IF;
 
 {% for partition in db.partitions %}
   IF in_partition = {{ partition }} THEN
     INSERT INTO location_area_large_{{ partition }} (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, postcode, centroid, geometry)
     RETURN TRUE;
   END IF;
 
 {% for partition in db.partitions %}
   IF in_partition = {{ partition }} THEN
     INSERT INTO location_area_large_{{ partition }} (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, postcode, centroid, geometry)

-      values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, postcode, in_centroid, in_geometry);
+      (SELECT in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, postcode, in_centroid, geom
+       FROM split_geometry(in_geometry) as geom);

     RETURN TRUE;
   END IF;
 {% endfor %}
     RETURN TRUE;
   END IF;
 {% endfor %}


@@ -166,18 +147,21 @@ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION getNearestNamedRoadPlaceId(in_partition INTEGER,
                                                       point GEOMETRY,
 
 CREATE OR REPLACE FUNCTION getNearestNamedRoadPlaceId(in_partition INTEGER,
                                                       point GEOMETRY,

-                                                      isin_token INTEGER[])
+                                                      token_info JSONB)

   RETURNS BIGINT
   AS $$
 DECLARE
   parent BIGINT;
 BEGIN
   RETURNS BIGINT
   AS $$
 DECLARE
   parent BIGINT;
 BEGIN

+  IF not token_has_addr_street(token_info) THEN
+    RETURN NULL;
+  END IF;

 
 {% for partition in db.partitions %}
   IF in_partition = {{ partition }} THEN
     SELECT place_id FROM search_name_{{ partition }}
       INTO parent
 
 {% for partition in db.partitions %}
   IF in_partition = {{ partition }} THEN
     SELECT place_id FROM search_name_{{ partition }}
       INTO parent

-      WHERE name_vector && isin_token
+      WHERE token_matches_street(token_info, name_vector)

             AND centroid && ST_Expand(point, 0.015)
             AND address_rank between 26 and 27
       ORDER BY ST_Distance(centroid, point) ASC limit 1;
             AND centroid && ST_Expand(point, 0.015)
             AND address_rank between 26 and 27
       ORDER BY ST_Distance(centroid, point) ASC limit 1;


@@ -192,19 +176,22 @@ LANGUAGE plpgsql STABLE;
 
 CREATE OR REPLACE FUNCTION getNearestNamedPlacePlaceId(in_partition INTEGER,
                                                        point GEOMETRY,
 
 CREATE OR REPLACE FUNCTION getNearestNamedPlacePlaceId(in_partition INTEGER,
                                                        point GEOMETRY,

-                                                       isin_token INTEGER[])
+                                                       token_info JSONB)

   RETURNS BIGINT
   AS $$
 DECLARE
   parent BIGINT;
 BEGIN
   RETURNS BIGINT
   AS $$
 DECLARE
   parent BIGINT;
 BEGIN

+  IF not token_has_addr_place(token_info) THEN
+    RETURN NULL;
+  END IF;

 
 {% for partition in db.partitions %}
   IF in_partition = {{ partition }} THEN
     SELECT place_id
       INTO parent
       FROM search_name_{{ partition }}
 
 {% for partition in db.partitions %}
   IF in_partition = {{ partition }} THEN
     SELECT place_id
       INTO parent
       FROM search_name_{{ partition }}

-      WHERE name_vector && isin_token
+      WHERE token_matches_place(token_info, name_vector)

             AND centroid && ST_Expand(point, 0.04)
             AND address_rank between 16 and 25
       ORDER BY ST_Distance(centroid, point) ASC limit 1;
             AND centroid && ST_Expand(point, 0.04)
             AND address_rank between 16 and 25
       ORDER BY ST_Distance(centroid, point) ASC limit 1;