]> git.openstreetmap.org Git - nominatim.git/blob - lib-sql/functions/address_lookup.sql
introduce jinja2 for preprocessing SQL
[nominatim.git] / 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            housenumber, postcode,
168            class, type,
169            name, address,
170            centroid
171       INTO place
172       FROM placex
173       WHERE place_id = in_place_id and rank_search > 27;
174   END IF;
175
176   -- If place is still NULL at this point then the object has its own
177   -- entry in place_address line. However, still check if there is not linked
178   -- place we should be using instead.
179   IF place IS NULL THEN
180     select coalesce(linked_place_id, place_id) as place_id,  country_code,
181            housenumber, postcode,
182            class, type,
183            null as name, address,
184            null as centroid
185       INTO place
186       FROM placex where place_id = in_place_id;
187   END IF;
188
189 --RAISE WARNING '% % % %',searchcountrycode, searchhousenumber, searchpostcode;
190
191   -- --- Return the record for the base entry.
192
193   FOR location IN
194     SELECT placex.place_id, osm_type, osm_id, name,
195            coalesce(extratags->'linked_place', extratags->'place') as place_type,
196            class, type, admin_level,
197            CASE WHEN rank_address = 0 THEN 100
198                 WHEN rank_address = 11 THEN 5
199                 ELSE rank_address END as rank_address,
200            country_code
201       FROM placex
202       WHERE place_id = place.place_id
203   LOOP
204 --RAISE WARNING '%',location;
205     IF location.rank_address < 4 THEN
206       -- no country locations for ranks higher than country
207       place.country_code := NULL::varchar(2);
208     ELSEIF place.country_code IS NULL AND location.country_code IS NOT NULL THEN
209       place.country_code := location.country_code;
210     END IF;
211
212     RETURN NEXT ROW(location.place_id, location.osm_type, location.osm_id,
213                     location.name, location.class, location.type,
214                     location.place_type,
215                     location.admin_level, true,
216                     location.type not in ('postcode', 'postal_code'),
217                     location.rank_address, 0)::addressline;
218
219     current_rank_address := location.rank_address;
220   END LOOP;
221
222   -- --- Return records for address parts.
223
224   FOR location IN
225     SELECT placex.place_id, osm_type, osm_id, name, class, type,
226            coalesce(extratags->'linked_place', extratags->'place') as place_type,
227            admin_level, fromarea, isaddress,
228            CASE WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
229            distance, country_code, postcode
230       FROM place_addressline join placex on (address_place_id = placex.place_id)
231       WHERE place_addressline.place_id IN (place.place_id, in_place_id)
232             AND linked_place_id is null
233             AND (placex.country_code IS NULL OR place.country_code IS NULL
234                  OR placex.country_code = place.country_code)
235       ORDER BY rank_address desc,
236                (place_addressline.place_id = in_place_id) desc,
237                (fromarea and place.centroid is not null and not isaddress
238                 and (place.address is null or avals(name) && avals(place.address))
239                 and ST_Contains(geometry, place.centroid)) desc,
240                isaddress desc, fromarea desc,
241                distance asc, rank_search desc
242   LOOP
243     -- RAISE WARNING '%',location;
244     location_isaddress := location.rank_address != current_rank_address;
245
246     IF place.country_code IS NULL AND location.country_code IS NOT NULL THEN
247       place.country_code := location.country_code;
248     END IF;
249     IF location.type in ('postcode', 'postal_code')
250        AND place.postcode is not null
251     THEN
252       -- If the place had a postcode assigned, take this one only
253       -- into consideration when it is an area and the place does not have
254       -- a postcode itself.
255       IF location.fromarea AND location.isaddress
256          AND (place.address is null or not place.address ? 'postcode')
257       THEN
258         place.postcode := null; -- remove the less exact postcode
259       ELSE
260         location_isaddress := false;
261       END IF;
262     END IF;
263     RETURN NEXT ROW(location.place_id, location.osm_type, location.osm_id,
264                     location.name, location.class, location.type,
265                     location.place_type,
266                     location.admin_level, location.fromarea,
267                     location_isaddress,
268                     location.rank_address,
269                     location.distance)::addressline;
270
271     current_rank_address := location.rank_address;
272   END LOOP;
273
274   -- If no country was included yet, add the name information from country_name.
275   IF current_rank_address > 4 THEN
276     FOR location IN
277       SELECT name FROM country_name WHERE country_code = place.country_code LIMIT 1
278     LOOP
279 --RAISE WARNING '% % %',current_rank_address,searchcountrycode,countryname;
280       RETURN NEXT ROW(null, null, null, location.name, 'place', 'country', NULL,
281                       null, true, true, 4, 0)::addressline;
282     END LOOP;
283   END IF;
284
285   -- Finally add some artificial rows.
286   IF place.country_code IS NOT NULL THEN
287     location := ROW(null, null, null, hstore('ref', place.country_code),
288                     'place', 'country_code', null, null, true, false, 4, 0)::addressline;
289     RETURN NEXT location;
290   END IF;
291
292   IF place.name IS NOT NULL THEN
293     location := ROW(in_place_id, null, null, place.name, place.class,
294                     place.type, null, null, true, true, 29, 0)::addressline;
295     RETURN NEXT location;
296   END IF;
297
298   IF place.housenumber IS NOT NULL THEN
299     location := ROW(null, null, null, hstore('ref', place.housenumber),
300                     'place', 'house_number', null, null, true, true, 28, 0)::addressline;
301     RETURN NEXT location;
302   END IF;
303
304   IF place.address is not null and place.address ? '_unlisted_place' THEN
305     RETURN NEXT ROW(null, null, null, hstore('name', place.address->'_unlisted_place'),
306                     'place', 'locality', null, null, true, true, 25, 0)::addressline;
307   END IF;
308
309   IF place.postcode is not null THEN
310     location := ROW(null, null, null, hstore('ref', place.postcode), 'place',
311                     'postcode', null, null, false, true, 5, 0)::addressline;
312     RETURN NEXT location;
313   END IF;
314
315   RETURN;
316 END;
317 $$
318 LANGUAGE plpgsql STABLE;