1 -- Functions for returning address information for a place.
3 DROP TYPE IF EXISTS addressline CASCADE;
4 CREATE TYPE addressline as (
20 CREATE OR REPLACE FUNCTION get_name_by_language(name hstore, languagepref TEXT[])
30 FOR j IN 1..array_upper(languagepref,1) LOOP
31 IF name ? languagepref[j] THEN
32 result := trim(name->languagepref[j]);
39 -- anything will do as a fallback - just take the first name type thing there is
40 RETURN trim((avals(name))[1]);
43 LANGUAGE plpgsql IMMUTABLE;
46 --housenumber only needed for tiger data
47 CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT,
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
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
72 result[(100 - location.rank_address)] := currresult;
73 prevresult := currresult;
77 RETURN array_to_string(result,', ');
80 LANGUAGE plpgsql STABLE;
83 -- Compute the list of address parts for the given place.
85 -- If in_housenumber is greator or equal 0, look for an interpolation.
86 CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber INTEGER)
87 RETURNS setof addressline
92 current_rank_address INTEGER;
94 -- The place in question might not have a direct entry in place_addressline.
95 -- Look for the parent of such places then and save if in for_place_id.
97 -- first query osmline (interpolation lines)
98 IF in_housenumber >= 0 THEN
99 SELECT parent_place_id as place_id, country_code,
100 in_housenumber::text as housenumber, postcode,
101 'place' as class, 'house' as type,
102 null as name, null::hstore as address
104 FROM location_property_osmline
105 WHERE place_id = in_place_id
106 AND in_housenumber between startnumber and endnumber;
109 --then query tiger data
110 -- %NOTIGERDATA% IF 0 THEN
111 IF place IS NULL AND in_housenumber >= 0 THEN
112 SELECT parent_place_id as place_id, 'us' as country_code,
113 in_housenumber::text as housenumber, postcode,
114 'place' as class, 'house' as type,
115 null as name, null::hstore as address
117 FROM location_property_tiger
118 WHERE place_id = in_place_id
119 AND in_housenumber between startnumber and endnumber;
121 -- %NOTIGERDATA% END IF;
123 -- %NOAUXDATA% IF 0 THEN
124 IF place IS NULL THEN
125 SELECT parent_place_id as place_id, 'us' as country_code,
126 housenumber, postcode,
127 'place' as class, 'house' as type,
128 null as name, null::hstore as address
130 FROM location_property_aux
131 WHERE place_id = in_place_id;
133 -- %NOAUXDATA% END IF;
136 IF place IS NULL THEN
137 SELECT parent_place_id as place_id, country_code,
138 null as housenumber, postcode,
139 'place' as class, 'postcode' as type,
140 null as name, null::hstore as address
142 FROM location_postcode
143 WHERE place_id = in_place_id;
146 -- POI objects in the placex table
147 IF place IS NULL THEN
148 SELECT parent_place_id as place_id, country_code,
149 housenumber, postcode,
154 WHERE place_id = in_place_id and rank_search > 27;
157 -- If for_place_id is still NULL at this point then the object has its own
158 -- entry in place_address line. However, still check if there is not linked
159 -- place we should be using instead.
160 IF place IS NULL THEN
161 select coalesce(linked_place_id, place_id) as place_id, country_code,
162 housenumber, postcode,
164 null as name, address
166 FROM placex where place_id = in_place_id;
169 --RAISE WARNING '% % % %',searchcountrycode, searchhousenumber, searchpostcode;
171 -- --- Return the record for the base entry.
174 SELECT placex.place_id, osm_type, osm_id, name,
175 coalesce(extratags->'linked_place', extratags->'place') as place_type,
176 class, type, admin_level,
177 CASE WHEN rank_address = 0 THEN 100
178 WHEN rank_address = 11 THEN 5
179 ELSE rank_address END as rank_address,
182 WHERE place_id = place.place_id
184 --RAISE WARNING '%',location;
185 IF location.rank_address < 4 THEN
186 -- no country locations for ranks higher than country
187 place.country_code := NULL;
188 ELSEIF place.country_code IS NULL AND location.country_code IS NOT NULL THEN
189 place.country_code := location.country_code;
192 RETURN NEXT ROW(location.place_id, location.osm_type, location.osm_id,
193 location.name, location.class, location.type,
195 location.admin_level, true,
196 location.type not in ('postcode', 'postal_code'),
197 location.rank_address, 0)::addressline;
199 current_rank_address := location.rank_address;
202 -- --- Return records for address parts.
205 SELECT placex.place_id, osm_type, osm_id, name, class, type,
206 coalesce(extratags->'linked_place', extratags->'place') as place_type,
207 admin_level, fromarea, 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 IN (place.place_id, in_place_id)
212 AND linked_place_id is null
213 AND (placex.country_code IS NULL OR place.country_code IS NULL
214 OR placex.country_code = place.country_code)
215 ORDER BY rank_address desc, (place_addressline.place_id = in_place_id) desc,
216 isaddress desc, fromarea desc,
217 distance asc, rank_search desc
219 -- RAISE WARNING '%',location;
220 IF place.country_code IS NULL AND location.country_code IS NOT NULL THEN
221 place.country_code := location.country_code;
223 IF location.type in ('postcode', 'postal_code')
224 AND place.postcode is not null
226 -- If the place had a postcode assigned, take this one only
227 -- into consideration when it is an area and the place does not have
228 -- a postcode itself.
229 IF location.fromarea AND location.isaddress
230 AND (place.address is null or not place.address ? 'postcode')
232 place.postcode := null; -- remove the less exact postcode
234 location.isaddress := false;
237 RETURN NEXT ROW(location.place_id, location.osm_type, location.osm_id,
238 location.name, location.class, location.type,
240 location.admin_level, location.fromarea,
241 location.isaddress and location.rank_address != current_rank_address,
242 location.rank_address,
243 location.distance)::addressline;
245 IF location.isaddress THEN
246 current_rank_address := location.rank_address;
250 -- If no country was included yet, add the name information from country_name.
251 IF current_rank_address > 4 THEN
253 SELECT name FROM country_name WHERE country_code = place.country_code LIMIT 1
255 --RAISE WARNING '% % %',current_rank_address,searchcountrycode,countryname;
256 RETURN NEXT ROW(null, null, null, location.name, 'place', 'country', NULL,
257 null, true, true, 4, 0)::addressline;
261 -- Finally add some artificial rows.
262 IF place.country_code IS NOT NULL THEN
263 location := ROW(null, null, null, hstore('ref', place.country_code),
264 'place', 'country_code', null, null, true, false, 4, 0)::addressline;
265 RETURN NEXT location;
268 IF place.name IS NOT NULL THEN
269 location := ROW(in_place_id, null, null, place.name, place.class,
270 place.type, null, null, true, true, 29, 0)::addressline;
271 RETURN NEXT location;
274 IF place.housenumber IS NOT NULL THEN
275 location := ROW(null, null, null, hstore('ref', place.housenumber),
276 'place', 'house_number', null, null, true, true, 28, 0)::addressline;
277 RETURN NEXT location;
280 IF place.address is not null and place.address ? '_unlisted_place' THEN
281 RETURN NEXT ROW(null, null, null, hstore('name', place.address->'_unlisted_place'),
282 'place', 'locality', null, null, true, true, 25, 0)::addressline;
285 IF place.postcode is not null THEN
286 location := ROW(null, null, null, hstore('ref', place.postcode), 'place',
287 'postcode', null, null, false, true, 5, 0)::addressline;
288 RETURN NEXT location;
294 LANGUAGE plpgsql STABLE;