X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/4a3dc3187cc5d220f2728a741d4b6789917655c0..d01d0c13f4d51ddcff2be065f330278516c0c458:/sql/functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index e404da7c..4256490e 100644
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -1,3 +1,15 @@
+-- Splits the line at the given point and returns the two parts
+-- in a multilinestring.
+CREATE OR REPLACE FUNCTION split_line_on_node(line GEOMETRY, point GEOMETRY)
+RETURNS GEOMETRY
+  AS $$
+BEGIN
+  RETURN ST_Split(ST_Snap(line, point, 0.0005), point);
+END;
+$$
+LANGUAGE plpgsql;
+
+
 CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place geometry) RETURNS INTEGER
   AS $$
 DECLARE
@@ -689,7 +701,7 @@ BEGIN
     -- one with the smallest id because the original node was created first.
     -- Ignore all nodes marked for deletion. (Might happen when the type changes.)
     select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
-                               and indexed_status < 100
+                               and indexed_status < 100 and housenumber is not NULL
                          order by (type = 'address'),place_id limit 1 INTO nextnode;
     IF nextnode.place_id IS NOT NULL THEN
 
@@ -1012,7 +1024,7 @@ BEGIN
 
   --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
+  RETURN NEW; -- %DIFFUPDATES% The following is not needed until doing diff updates, and slows the main index process down
 
   IF NEW.rank_address > 0 THEN
     IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN
@@ -1161,7 +1173,8 @@ BEGIN
     DELETE FROM place_addressline WHERE place_id = NEW.place_id;
     result := deleteRoad(NEW.partition, NEW.place_id);
     result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
-    UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
+    UPDATE placex set linked_place_id = null, indexed_status = 2
+           where linked_place_id = NEW.place_id;
 
     IF NEW.linked_place_id is not null THEN
       RETURN NEW;
@@ -1403,6 +1416,26 @@ BEGIN
         select * from search_name where place_id = NEW.parent_place_id INTO location;
         NEW.calculated_country_code := location.country_code;
 
+        -- Merge the postcode into the parent's address if necessary XXXX
+        IF NEW.postcode IS NOT NULL THEN
+          isin_tokens := '{}'::int[];
+          address_street_word_id := getorcreate_word_id(make_standard_name(NEW.postcode));
+          IF address_street_word_id is not null
+             and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN
+             isin_tokens := isin_tokens || address_street_word_id;
+          END IF;
+          address_street_word_id := getorcreate_name_id(make_standard_name(NEW.postcode));
+          IF address_street_word_id is not null
+             and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN
+             isin_tokens := isin_tokens || address_street_word_id;
+          END IF;
+          IF isin_tokens != '{}'::int[] THEN
+             UPDATE search_name
+                SET nameaddress_vector = search_name.nameaddress_vector || isin_tokens
+              WHERE place_id = NEW.parent_place_id;
+          END IF;
+        END IF;
+
 --RAISE WARNING '%', NEW.name;
         -- If there is no name it isn't searchable, don't bother to create a search record
         IF NEW.name is NULL THEN
@@ -1617,6 +1650,7 @@ BEGIN
       END IF;
     END IF;
 
+    -- %NOTIGERDATA% IF 0 THEN
     -- for the USA we have an additional address table.  Merge in zip codes from there too
     IF NEW.rank_search = 26 AND NEW.calculated_country_code = 'us' THEN
       FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP
@@ -1629,6 +1663,7 @@ BEGIN
         nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
       END LOOP;
     END IF;
+    -- %NOTIGERDATA% END IF;
 
 -- RAISE WARNING 'ISIN: %', isin_tokens;
 
@@ -2098,28 +2133,27 @@ BEGIN
 END; 
 $$ LANGUAGE plpgsql;
 
+
 CREATE OR REPLACE FUNCTION get_name_by_language(name hstore, languagepref TEXT[]) RETURNS TEXT
   AS $$
 DECLARE
-  search TEXT[];
-  found BOOLEAN;
+  result TEXT;
 BEGIN
-
   IF name is null THEN
     RETURN null;
   END IF;
 
-  search := languagepref;
-
-  FOR j IN 1..array_upper(search, 1) LOOP
-    IF name ? search[j] AND trim(name->search[j]) != '' THEN
-      return trim(name->search[j]);
+  FOR j IN 1..array_upper(languagepref,1) LOOP
+    IF name ? languagepref[j] THEN
+      result := trim(name->languagepref[j]);
+      IF result != '' THEN
+        return result;
+      END IF;
     END IF;
   END LOOP;
 
   -- anything will do as a fallback - just take the first name type thing there is
-  search := avals(name);
-  RETURN search[1];
+  RETURN trim((avals(name))[1]);
 END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
@@ -2164,7 +2198,8 @@ END;
 $$
 LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT, languagepref TEXT[]) RETURNS TEXT
+--housenumber only needed for tiger data
+CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT, housenumber INTEGER, languagepref TEXT[]) RETURNS TEXT
   AS $$
 DECLARE
   result TEXT[];
@@ -2176,7 +2211,7 @@ BEGIN
   result := '{}';
   prevresult := '';
 
-  FOR location IN select * from get_addressdata(for_place_id) where isaddress order by rank_address desc LOOP
+  FOR location IN select * from get_addressdata(for_place_id, housenumber) where isaddress order by rank_address desc LOOP
     currresult := trim(get_name_by_language(location.name, languagepref));
     IF currresult != prevresult AND currresult IS NOT NULL AND result[(100 - location.rank_address)] IS NULL THEN
       result[(100 - location.rank_address)] := trim(get_name_by_language(location.name, languagepref));
@@ -2204,10 +2239,10 @@ create type addressline as (
   distance FLOAT
 );
 
-CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT) RETURNS setof addressline 
+CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber INTEGER) RETURNS setof addressline 
   AS $$
 DECLARE
-  for_place_id BIGINT;
+  for_place_id BIGINT;--parent_place_id
   result TEXT[];
   search TEXT[];
   found INTEGER;
@@ -2223,16 +2258,23 @@ DECLARE
   countryname HSTORE;
   hadcountry BOOLEAN;
 BEGIN
+    --first query tiger data
+  -- %NOTIGERDATA% IF 0 THEN
+  select parent_place_id,'us', 30, postcode, null, 'place', 'house' from location_property_tiger 
+    WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber
+    INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
+  IF for_place_id IS NOT NULL THEN
+    searchhousenumber = in_housenumber::text;
+  END IF;
+  -- %NOTIGERDATA% END IF;
 
-  select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_tiger 
-    WHERE place_id = in_place_id 
-    INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
-
+  -- %NOAUXDATA% IF 0 THEN
   IF for_place_id IS NULL THEN
     select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux
       WHERE place_id = in_place_id 
       INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
   END IF;
+  -- %NOAUXDATA% END IF;
 
   IF for_place_id IS NULL THEN
     select parent_place_id, calculated_country_code, housenumber, rank_search, postcode, name, class, type from placex 
@@ -2241,9 +2283,10 @@ BEGIN
   END IF;
 
   IF for_place_id IS NULL THEN
-    for_place_id := in_place_id;
-    select calculated_country_code, housenumber, rank_search, postcode, null from placex where place_id = for_place_id 
-      INTO searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename;
+    select coalesce(linked_place_id, place_id),  calculated_country_code,
+           housenumber, rank_search, postcode, null
+      from placex where place_id = in_place_id
+      INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename;
   END IF;
 
 --RAISE WARNING '% % % %',searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode;
