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