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