sql/functions/address_lookup.sql

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