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