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_isaddress 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_isaddress := true;
 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, postcode,
 155            name, class, type
 156       FROM placex
 157       WHERE place_id = in_place_id and rank_search > 27
 158       INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress,
 159            searchpostcode, searchhousename, searchclass, searchtype;
 160   END IF;
 161
 162   -- If for_place_id is still NULL at this point then the object has its own
 163   -- entry in place_address line. However, still check if there is not linked
 164   -- place we should be using instead.
 165   IF for_place_id IS NULL THEN
 166     select coalesce(linked_place_id, place_id),  country_code,
 167            housenumber, rank_search, postcode, null
 168       from placex where place_id = in_place_id
 169       INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename;
 170   END IF;
 171
 172 --RAISE WARNING '% % % %',searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode;
 173
 174   found := 1000; -- the lowest rank_address included
 175
 176   -- Return the record for the base entry.
 177   FOR location IN
 178     SELECT placex.place_id, osm_type, osm_id, name,
 179            class, type, admin_level,
 180            type not in ('postcode', 'postal_code') as isaddress,
 181            CASE WHEN rank_address = 0 THEN 100
 182                 WHEN rank_address = 11 THEN 5
 183                 ELSE rank_address END as rank_address,
 184            0 as distance, country_code, postcode
 185       FROM placex
 186       WHERE place_id = for_place_id
 187   LOOP
 188 --RAISE WARNING '%',location;
 189     IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN
 190       searchcountrycode := location.country_code;
 191     END IF;
 192     IF location.rank_address < 4 THEN
 193       -- no country locations for ranks higher than country
 194       searchcountrycode := NULL;
 195     END IF;
 196     countrylocation := ROW(location.place_id, location.osm_type, location.osm_id,
 197                            location.name, location.class, location.type, NULL,
 198                            location.admin_level, true, location.isaddress,
 199                            location.rank_address, location.distance)::addressline;
 200     RETURN NEXT countrylocation;
 201     found := location.rank_address;
 202   END LOOP;
 203
 204   FOR location IN
 205     SELECT placex.place_id, osm_type, osm_id, name, class, type,
 206            coalesce(extratags->'place', extratags->'linked_place') as place_type,
 207            admin_level, fromarea, isaddress and linked_place_id is NULL as isaddress,
 208            CASE WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
 209            distance, country_code, postcode
 210       FROM place_addressline join placex on (address_place_id = placex.place_id)
 211       WHERE place_addressline.place_id = for_place_id
 212             AND (cached_rank_address >= 4 AND cached_rank_address < searchrankaddress)
 213             AND linked_place_id is null
 214             AND (placex.country_code IS NULL OR searchcountrycode IS NULL
 215                  OR placex.country_code = searchcountrycode)
 216       ORDER BY rank_address desc, isaddress desc, fromarea desc,
 217                distance asc, rank_search desc
 218   LOOP
 219 --RAISE WARNING '%',location;
 220     IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN
 221       searchcountrycode := location.country_code;
 222     END IF;
 223     IF location.type in ('postcode', 'postal_code') THEN
 224       postcode_isaddress := false;
 225       IF location.osm_type != 'R' THEN
 226         location.isaddress := FALSE;
 227       END IF;
 228     END IF;
 229     countrylocation := ROW(location.place_id, location.osm_type, location.osm_id,
 230                            location.name, location.class, location.type,
 231                            location.place_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', NULL,
 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, 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, 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, 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, null, false, postcode_isaddress, 5, 0)::addressline;
 273     RETURN NEXT location;
 274   END IF;
 275
 276   RETURN;
 277 END;
 278 $$
 279 LANGUAGE plpgsql STABLE;