lib-sql/tiger_import_start.sql

   1 -- SPDX-License-Identifier: GPL-2.0-only
   2 --
   3 -- This file is part of Nominatim. (https://nominatim.org)
   4 --
   5 -- Copyright (C) 2022 by the Nominatim developer community.
   6 -- For a full list of authors see the git log.
   7 DROP TABLE IF EXISTS location_property_tiger_import;
   8 CREATE TABLE location_property_tiger_import (
   9   linegeo GEOMETRY,
  10   place_id BIGINT,
  11   partition INTEGER,
  12   parent_place_id BIGINT,
  13   startnumber INTEGER,
  14   endnumber INTEGER,
  15   step SMALLINT,
  16   postcode TEXT);
  17
  18
  19 -- Lookup functions for tiger import when update
  20 -- informations are dropped (see gh-issue #2463)
  21 CREATE OR REPLACE FUNCTION getNearestNamedRoadPlaceIdSlow(in_centroid GEOMETRY,
  22                                                       in_token_info JSONB)
  23   RETURNS BIGINT
  24   AS $$
  25 DECLARE
  26   out_place_id BIGINT;
  27
  28 BEGIN
  29   SELECT place_id INTO out_place_id
  30     FROM search_name
  31     WHERE
  32         -- finds rows where name_vector shares elements with search tokens.
  33         token_matches_street(in_token_info, name_vector)
  34         -- limits search area
  35         AND centroid && ST_Expand(in_centroid, 0.015)
  36         AND address_rank BETWEEN 26 AND 27
  37     ORDER BY ST_Distance(centroid, in_centroid) ASC
  38     LIMIT 1;
  39
  40   RETURN out_place_id;
  41 END
  42 $$
  43 LANGUAGE plpgsql;
  44
  45
  46 CREATE OR REPLACE FUNCTION getNearestParallelRoadFeatureSlow(line GEOMETRY)
  47   RETURNS BIGINT
  48   AS $$
  49 DECLARE
  50   r RECORD;
  51   search_diameter FLOAT;
  52   p1 GEOMETRY;
  53   p2 GEOMETRY;
  54   p3 GEOMETRY;
  55
  56 BEGIN
  57   IF ST_GeometryType(line) not in ('ST_LineString') THEN
  58     RETURN NULL;
  59   END IF;
  60
  61   p1 := ST_LineInterpolatePoint(line,0);
  62   p2 := ST_LineInterpolatePoint(line,0.5);
  63   p3 := ST_LineInterpolatePoint(line,1);
  64
  65     search_diameter := 0.0005;
  66     WHILE search_diameter < 0.01 LOOP
  67       FOR r IN
  68         SELECT place_id FROM placex
  69           WHERE ST_DWithin(line, geometry, search_diameter)
  70           AND rank_address BETWEEN 26 AND 27
  71           ORDER BY (ST_distance(geometry, p1)+
  72                     ST_distance(geometry, p2)+
  73                     ST_distance(geometry, p3)) ASC limit 1
  74       LOOP
  75         RETURN r.place_id;
  76       END LOOP;
  77       search_diameter := search_diameter * 2;
  78     END LOOP;
  79     RETURN NULL;
  80 END
  81 $$
  82 LANGUAGE plpgsql;
  83
  84
  85 CREATE OR REPLACE FUNCTION getNearestRoadPlaceIdSlow(point GEOMETRY)
  86   RETURNS BIGINT
  87   AS $$
  88 DECLARE
  89   r RECORD;
  90   search_diameter FLOAT;
  91 BEGIN
  92     search_diameter := 0.00005;
  93     WHILE search_diameter < 0.1 LOOP
  94       FOR r IN
  95         SELECT place_id FROM placex
  96           WHERE ST_DWithin(geometry, point, search_diameter)
  97           AND rank_address BETWEEN 26 AND 27
  98           ORDER BY ST_Distance(geometry, point) ASC limit 1
  99       LOOP
 100         RETURN r.place_id;
 101       END LOOP;
 102       search_diameter := search_diameter * 2;
 103     END LOOP;
 104     RETURN NULL;
 105 END
 106 $$
 107 LANGUAGE plpgsql;
 108
 109
 110 -- Tiger import function
 111 CREATE OR REPLACE FUNCTION tiger_line_import(linegeo GEOMETRY, in_startnumber INTEGER,
 112                                              in_endnumber INTEGER, interpolationtype TEXT,
 113                                              token_info JSONB, in_postcode TEXT) RETURNS INTEGER
 114   AS $$
 115 DECLARE
 116   startnumber INTEGER;
 117   endnumber INTEGER;
 118   stepsize INTEGER;
 119   numberrange INTEGER;
 120   place_centroid GEOMETRY;
 121   out_partition INTEGER;
 122   out_parent_place_id BIGINT;
 123   location RECORD;
 124
 125 BEGIN
 126
 127   IF in_endnumber > in_startnumber THEN
 128     startnumber := in_startnumber;
 129     endnumber := in_endnumber;
 130   ELSE
 131     startnumber := in_endnumber;
 132     endnumber := in_startnumber;
 133     linegeo := ST_Reverse(linegeo);
 134   END IF;
 135
 136   IF startnumber < 0 THEN
 137     RAISE WARNING 'Negative house number range (% to %)', startnumber, endnumber;
 138     RETURN 0;
 139   END IF;
 140
 141   numberrange := endnumber - startnumber;
 142
 143   IF (interpolationtype = 'odd' AND startnumber % 2 = 0) OR (interpolationtype = 'even' AND startnumber % 2 = 1) THEN
 144     startnumber := startnumber + 1;
 145     stepsize := 2;
 146   ELSE
 147     IF (interpolationtype = 'odd' OR interpolationtype = 'even') THEN
 148       stepsize := 2;
 149     ELSE -- everything else assumed to be 'all'
 150       stepsize := 1;
 151     END IF;
 152   END IF;
 153
 154   -- Filter out really broken tiger data
 155   IF numberrange > 0
 156      and numberrange::float/stepsize::float > 500
 157      and ST_length(linegeo)/(numberrange::float/stepsize::float) < 0.000001
 158   THEN
 159     RAISE WARNING 'Road too short for number range % to % (%)',startnumber,endnumber,
 160                   ST_length(linegeo)/(numberrange::float/stepsize::float);
 161     RETURN 0;
 162   END IF;
 163
 164   place_centroid := ST_Centroid(linegeo);
 165   out_partition := get_partition('us');
 166
 167   -- HYBRID LOOKUP LOGIC (see gh-issue #2463)
 168   -- if partition tables exist, use them for fast spatial lookups
 169   {% if 'location_road_0' in db.tables %}
 170     out_parent_place_id := getNearestNamedRoadPlaceId(out_partition, place_centroid,
 171                                                     token_info);
 172
 173     IF out_parent_place_id IS NULL THEN
 174       SELECT getNearestParallelRoadFeature(out_partition, linegeo)
 175         INTO out_parent_place_id;
 176     END IF;
 177
 178     IF out_parent_place_id IS NULL THEN
 179       SELECT getNearestRoadPlaceId(out_partition, place_centroid)
 180         INTO out_parent_place_id;
 181     END IF;
 182
 183   -- When updatable information has been dropped:
 184   -- Partition tables no longer exist, but search_name still persists.
 185   {% elif 'search_name' in db.tables %}
 186     -- Fallback: Look up in 'search_name' table
 187     -- though spatial lookups here can be slower.
 188     out_parent_place_id := getNearestNamedRoadPlaceIdSlow(place_centroid, token_info);
 189
 190     IF out_parent_place_id IS NULL THEN
 191       out_parent_place_id := getNearestParallelRoadFeatureSlow(linegeo);
 192     END IF;
 193
 194     IF out_parent_place_id IS NULL THEN
 195       out_parent_place_id := getNearestRoadPlaceIdSlow(place_centroid);
 196     END IF;
 197   {% endif %}
 198
 199   -- If parent was found, insert street(line) into import table
 200   IF out_parent_place_id IS NOT NULL THEN
 201     INSERT INTO location_property_tiger_import (linegeo, place_id, partition,
 202                                                 parent_place_id, startnumber, endnumber,
 203                                                 step, postcode)
 204     VALUES (linegeo, nextval('seq_place'), out_partition,
 205             out_parent_place_id, startnumber, endnumber,
 206             stepsize, in_postcode);
 207
 208     RETURN 1;
 209   END IF;
 210   RETURN 0;
 211
 212 END;
 213 $$
 214 LANGUAGE plpgsql;