lib-sql/functions/address_lookup.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
   8 -- Functions for returning address information for a place.
   9
  10 DROP TYPE IF EXISTS addressline CASCADE;
  11 CREATE TYPE addressline as (
  12   place_id BIGINT,
  13   osm_type CHAR(1),
  14   osm_id BIGINT,
  15   name HSTORE,
  16   class TEXT,
  17   type TEXT,
  18   place_type TEXT,
  19   admin_level INTEGER,
  20   fromarea BOOLEAN,
  21   isaddress BOOLEAN,
  22   rank_address INTEGER,
  23   distance FLOAT
  24 );
  25
  26
  27 CREATE OR REPLACE FUNCTION get_name_by_language(name hstore, languagepref TEXT[])
  28   RETURNS TEXT
  29   AS $$
  30 DECLARE
  31   result TEXT;
  32 BEGIN
  33   IF name is null THEN
  34     RETURN null;
  35   END IF;
  36
  37   FOR j IN 1..array_upper(languagepref,1) LOOP
  38     IF name ? languagepref[j] THEN
  39       result := trim(name->languagepref[j]);
  40       IF result != '' THEN
  41         return result;
  42       END IF;
  43     END IF;
  44   END LOOP;
  45
  46   -- as a fallback - take the last element since it is the default name
  47   RETURN trim((avals(name))[array_length(avals(name), 1)]);
  48 END;
  49 $$
  50 LANGUAGE plpgsql IMMUTABLE;
  51
  52
  53 --housenumber only needed for tiger data
  54 CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT,
  55                                                    housenumber INTEGER,
  56                                                    languagepref TEXT[])
  57   RETURNS TEXT
  58   AS $$
  59 DECLARE
  60   result TEXT[];
  61   currresult TEXT;
  62   prevresult TEXT;
  63   location RECORD;
  64 BEGIN
  65
  66   result := '{}';
  67   prevresult := '';
  68
  69   FOR location IN
  70     SELECT name,
  71        CASE WHEN place_id = for_place_id THEN 99 ELSE rank_address END as rank_address
  72     FROM get_addressdata(for_place_id, housenumber)
  73     WHERE isaddress order by rank_address desc
  74   LOOP
  75     currresult := trim(get_name_by_language(location.name, languagepref));
  76     IF currresult != prevresult AND currresult IS NOT NULL
  77        AND result[(100 - location.rank_address)] IS NULL
  78     THEN
  79       result[(100 - location.rank_address)] := currresult;
  80       prevresult := currresult;
  81     END IF;
  82   END LOOP;
  83
  84   RETURN array_to_string(result,', ');
  85 END;
  86 $$
  87 LANGUAGE plpgsql STABLE;
  88
  89 DROP TYPE IF EXISTS addressdata_place;
  90 CREATE TYPE addressdata_place AS (
  91   place_id BIGINT,
  92   country_code VARCHAR(2),
  93   housenumber TEXT,
  94   postcode TEXT,
  95   class TEXT,
  96   type TEXT,
  97   name HSTORE,
  98   address HSTORE,
  99   centroid GEOMETRY
 100 );
 101
 102 -- Compute the list of address parts for the given place.
 103 --
 104 -- If in_housenumber is greator or equal 0, look for an interpolation.
 105 CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber INTEGER)
 106   RETURNS setof addressline
 107   AS $$
 108 DECLARE
 109   place addressdata_place;
 110   location RECORD;
 111   country RECORD;
 112   current_rank_address INTEGER;
 113   location_isaddress BOOLEAN;
 114 BEGIN
 115   -- The place in question might not have a direct entry in place_addressline.
 116   -- Look for the parent of such places then and save it in place.
 117
 118   -- first query osmline (interpolation lines)
 119   IF in_housenumber >= 0 THEN
 120     SELECT parent_place_id as place_id, country_code,
 121            in_housenumber as housenumber, postcode,
 122            'place' as class, 'house' as type,
 123            null as name, null as address,
 124            ST_Centroid(linegeo) as centroid
 125       INTO place
 126       FROM location_property_osmline
 127       WHERE place_id = in_place_id
 128             AND in_housenumber between startnumber and endnumber;
 129   END IF;
 130
 131   --then query tiger data
 132   {% if config.get_bool('USE_US_TIGER_DATA') %}
 133   IF place IS NULL AND in_housenumber >= 0 THEN
 134     SELECT parent_place_id as place_id, 'us' as country_code,
 135            in_housenumber as housenumber, postcode,
 136            'place' as class, 'house' as type,
 137            null as name, null as address,
 138            ST_Centroid(linegeo) as centroid
 139       INTO place
 140       FROM location_property_tiger
 141       WHERE place_id = in_place_id
 142             AND in_housenumber between startnumber and endnumber;
 143   END IF;
 144   {% endif %}
 145
 146   -- postcode table
 147   IF place IS NULL THEN
 148     SELECT parent_place_id as place_id, country_code,
 149            null::text as housenumber, postcode,
 150            'place' as class, 'postcode' as type,
 151            null as name, null as address,
 152            null as centroid
 153       INTO place
 154       FROM location_postcode
 155       WHERE place_id = in_place_id;
 156   END IF;
 157
 158   -- POI objects in the placex table
 159   IF place IS NULL THEN
 160     SELECT parent_place_id as place_id, country_code,
 161            coalesce(address->'housenumber',
 162                     address->'streetnumber',
 163                     address->'conscriptionnumber')::text as housenumber,
 164            postcode,
 165            class, type,
 166            name, address,
 167            centroid
 168       INTO place
 169       FROM placex
 170       WHERE place_id = in_place_id and rank_search > 27;
 171   END IF;
 172
 173   -- If place is still NULL at this point then the object has its own
 174   -- entry in place_address line. However, still check if there is not linked
 175   -- place we should be using instead.
 176   IF place IS NULL THEN
 177     select coalesce(linked_place_id, place_id) as place_id,  country_code,
 178            null::text as housenumber, postcode,
 179            class, type,
 180            null as name, address,
 181            null as centroid
 182       INTO place
 183       FROM placex where place_id = in_place_id;
 184   END IF;
 185
 186 --RAISE WARNING '% % % %',searchcountrycode, searchhousenumber, searchpostcode;
 187
 188   -- --- Return the record for the base entry.
 189
 190   current_rank_address := 1000;
 191   FOR location IN
 192     SELECT placex.place_id, osm_type, osm_id, name,
 193            coalesce(extratags->'linked_place', extratags->'place') as place_type,
 194            class, type, admin_level,
 195            CASE WHEN rank_address = 0 THEN 100
 196                 WHEN rank_address = 11 THEN 5
 197                 ELSE rank_address END as rank_address,
 198            country_code
 199       FROM placex
 200       WHERE place_id = place.place_id
 201   LOOP
 202 --RAISE WARNING '%',location;
 203     -- mix in default names for countries
 204     IF location.rank_address = 4 and place.country_code is not NULL THEN
 205       FOR country IN
 206         SELECT coalesce(name, ''::hstore) as name FROM country_name
 207           WHERE country_code = place.country_code LIMIT 1
 208       LOOP
 209         place.name := country.name || place.name;
 210       END LOOP;
 211     END IF;
 212
 213     IF location.rank_address < 4 THEN
 214       -- no country locations for ranks higher than country
 215       place.country_code := NULL::varchar(2);
 216     ELSEIF place.country_code IS NULL AND location.country_code IS NOT NULL THEN
 217       place.country_code := location.country_code;
 218     END IF;
 219
 220     RETURN NEXT ROW(location.place_id, location.osm_type, location.osm_id,
 221                     location.name, location.class, location.type,
 222                     location.place_type,
 223                     location.admin_level, true,
 224                     location.type not in ('postcode', 'postal_code'),
 225                     location.rank_address, 0)::addressline;
 226
 227     current_rank_address := location.rank_address;
 228   END LOOP;
 229
 230   -- --- Return records for address parts.
 231
 232   FOR location IN
 233     SELECT placex.place_id, osm_type, osm_id, name, class, type,
 234            coalesce(extratags->'linked_place', extratags->'place') as place_type,
 235            admin_level, fromarea, isaddress and linked_place_id is NULL as isaddress,
 236            CASE WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
 237            distance, country_code, postcode
 238       FROM place_addressline join placex on (address_place_id = placex.place_id)
 239       WHERE place_addressline.place_id IN (place.place_id, in_place_id)
 240             AND linked_place_id is null
 241             AND (placex.country_code IS NULL OR place.country_code IS NULL
 242                  OR placex.country_code = place.country_code)
 243       ORDER BY rank_address desc,
 244                (place_addressline.place_id = in_place_id) desc,
 245                (CASE WHEN coalesce((avals(name) && avals(place.address)), False) THEN 2
 246                      WHEN isaddress THEN 0
 247                      WHEN fromarea
 248                           and place.centroid is not null
 249                           and ST_Contains(geometry, place.centroid) THEN 1
 250                      ELSE -1 END) desc,
 251                fromarea desc, distance asc, rank_search desc
 252   LOOP
 253     -- RAISE WARNING '%',location;
 254     location_isaddress := location.rank_address != current_rank_address;
 255
 256     IF place.country_code IS NULL AND location.country_code IS NOT NULL THEN
 257       place.country_code := location.country_code;
 258     END IF;
 259     IF location.type in ('postcode', 'postal_code')
 260        AND place.postcode is not null
 261     THEN
 262       -- If the place had a postcode assigned, take this one only
 263       -- into consideration when it is an area and the place does not have
 264       -- a postcode itself.
 265       IF location.fromarea AND location.isaddress
 266          AND (place.address is null or not place.address ? 'postcode')
 267       THEN
 268         place.postcode := null; -- remove the less exact postcode
 269       ELSE
 270         location_isaddress := false;
 271       END IF;
 272     END IF;
 273     RETURN NEXT ROW(location.place_id, location.osm_type, location.osm_id,
 274                     location.name, location.class, location.type,
 275                     location.place_type,
 276                     location.admin_level, location.fromarea,
 277                     location_isaddress,
 278                     location.rank_address,
 279                     location.distance)::addressline;
 280
 281     current_rank_address := location.rank_address;
 282   END LOOP;
 283
 284   -- If no country was included yet, add the name information from country_name.
 285   IF current_rank_address > 4 THEN
 286     FOR location IN
 287       SELECT name || coalesce(derived_name, ''::hstore) as name FROM country_name
 288         WHERE country_code = place.country_code LIMIT 1
 289     LOOP
 290 --RAISE WARNING '% % %',current_rank_address,searchcountrycode,countryname;
 291       RETURN NEXT ROW(null, null, null, location.name, 'place', 'country', NULL,
 292                       null, true, true, 4, 0)::addressline;
 293     END LOOP;
 294   END IF;
 295
 296   -- Finally add some artificial rows.
 297   IF place.country_code IS NOT NULL THEN
 298     location := ROW(null, null, null, hstore('ref', place.country_code),
 299                     'place', 'country_code', null, null, true, false, 4, 0)::addressline;
 300     RETURN NEXT location;
 301   END IF;
 302
 303   IF place.name IS NOT NULL THEN
 304     location := ROW(in_place_id, null, null, place.name, place.class,
 305                     place.type, null, null, true, true, 29, 0)::addressline;
 306     RETURN NEXT location;
 307   END IF;
 308
 309   IF place.housenumber IS NOT NULL THEN
 310     location := ROW(null, null, null, hstore('ref', place.housenumber),
 311                     'place', 'house_number', null, null, true, true, 28, 0)::addressline;
 312     RETURN NEXT location;
 313   END IF;
 314
 315   IF place.address is not null and place.address ? '_unlisted_place' THEN
 316     RETURN NEXT ROW(null, null, null, hstore('name', place.address->'_unlisted_place'),
 317                     'place', 'locality', null, null, true, true, 25, 0)::addressline;
 318   END IF;
 319
 320   IF place.postcode is not null THEN
 321     location := ROW(null, null, null, hstore('ref', place.postcode), 'place',
 322                     'postcode', null, null, false, true, 5, 0)::addressline;
 323     RETURN NEXT location;
 324   ELSEIF place.address is not null and place.address ? 'postcode'
 325          and not place.address->'postcode' SIMILAR TO '%(,|;)%' THEN
 326     location := ROW(null, null, null, hstore('ref', place.address->'postcode'), 'place',
 327                     'postcode', null, null, false, true, 5, 0)::addressline;
 328     RETURN NEXT location;
 329   END IF;
 330
 331   RETURN;
 332 END;
 333 $$
 334 LANGUAGE plpgsql STABLE;