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