1 -- SPDX-License-Identifier: GPL-2.0-only
3 -- This file is part of Nominatim. (https://nominatim.org)
5 -- Copyright (C) 2022 by the Nominatim developer community.
6 -- For a full list of authors see the git log.
8 -- Functions for address interpolation objects in location_property_osmline.
11 CREATE OR REPLACE FUNCTION get_interpolation_address(in_address HSTORE, wayid BIGINT)
18 IF akeys(in_address) != ARRAY['interpolation'] THEN
22 SELECT nodes INTO waynodes FROM planet_osm_ways WHERE id = wayid;
24 SELECT placex.address, placex.osm_id FROM placex
25 WHERE osm_type = 'N' and osm_id = ANY(waynodes)
26 and placex.address is not null
27 and (placex.address ? 'street' or placex.address ? 'place')
28 and indexed_status < 100
30 -- mark it as a derived address
31 RETURN location.address || in_address || hstore('_inherited', '');
37 LANGUAGE plpgsql STABLE;
41 -- find the parent road of the cut road parts
42 CREATE OR REPLACE FUNCTION get_interpolation_parent(token_info JSONB,
44 centroid GEOMETRY, geom GEOMETRY)
48 parent_place_id BIGINT;
51 parent_place_id := find_parent_for_address(token_info, partition, centroid);
53 IF parent_place_id is null THEN
54 FOR location IN SELECT place_id FROM placex
55 WHERE ST_DWithin(geom, placex.geometry, 0.001) and placex.rank_search = 26
56 ORDER BY (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+
57 ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+
58 ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,1))) ASC limit 1
60 parent_place_id := location.place_id;
64 IF parent_place_id is null THEN
68 RETURN parent_place_id;
71 LANGUAGE plpgsql STABLE;
74 CREATE OR REPLACE FUNCTION reinsert_interpolation(way_id BIGINT, addr HSTORE,
81 -- Get the existing entry from the interpolation table.
82 SELECT array_agg(place_id) INTO existing
83 FROM location_property_osmline WHERE osm_id = way_id;
85 IF existing IS NULL or array_length(existing, 1) = 0 THEN
86 INSERT INTO location_property_osmline (osm_id, address, linegeo)
87 VALUES (way_id, addr, geom);
89 -- Update the interpolation table:
90 -- The first entry gets the original data, all other entries
91 -- are removed and will be recreated on indexing.
92 -- (An interpolation can be split up, if it has more than 2 address nodes)
93 UPDATE location_property_osmline
98 WHERE place_id = existing[1];
99 IF array_length(existing, 1) > 1 THEN
100 DELETE FROM location_property_osmline
101 WHERE place_id = any(existing[2:]);
111 CREATE OR REPLACE FUNCTION osmline_insert()
115 NEW.place_id := nextval('seq_place');
116 NEW.indexed_date := now();
118 IF NEW.indexed_status IS NULL THEN
119 IF NEW.address is NULL OR NOT NEW.address ? 'interpolation'
120 OR NOT (NEW.address->'interpolation' in ('odd', 'even', 'all')
121 or NEW.address->'interpolation' similar to '[1-9]')
123 -- alphabetic interpolation is not supported
127 NEW.indexed_status := 1; --STATUS_NEW
128 NEW.country_code := lower(get_country_code(NEW.linegeo));
130 NEW.partition := get_partition(NEW.country_code);
131 NEW.geometry_sector := geometry_sector(NEW.partition, NEW.linegeo);
140 CREATE OR REPLACE FUNCTION osmline_update()
155 interpol_postcode TEXT;
160 IF OLD.indexed_status = 100 THEN
161 delete from location_property_osmline where place_id = OLD.place_id;
165 IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
169 NEW.parent_place_id := get_interpolation_parent(NEW.token_info, NEW.partition,
170 ST_PointOnSurface(NEW.linegeo),
173 interpol_postcode := token_normalized_postcode(NEW.address->'postcode');
175 NEW.token_info := token_strip_info(NEW.token_info);
176 IF NEW.address ? '_inherited' THEN
177 NEW.address := hstore('interpolation', NEW.address->'interpolation');
180 -- If the line was newly inserted, split the line as necessary.
181 IF OLD.indexed_status = 1 THEN
182 IF NEW.address->'interpolation' in ('odd', 'even') THEN
184 stepmod := CASE WHEN NEW.address->'interpolation' = 'odd' THEN 1 ELSE 0 END;
186 NEW.step := CASE WHEN NEW.address->'interpolation' = 'all'
188 ELSE (NEW.address->'interpolation')::SMALLINT END;
192 SELECT nodes INTO waynodes
193 FROM planet_osm_ways WHERE id = NEW.osm_id;
195 IF array_upper(waynodes, 1) IS NULL THEN
200 SELECT null::integer as hnr INTO prevnode;
202 -- Go through all nodes on the interpolation line that have a housenumber.
204 SELECT DISTINCT ON (nodeidpos)
205 osm_id, address, geometry,
206 substring(address->'housenumber','[0-9]+')::integer as hnr
207 FROM placex, generate_series(1, array_upper(waynodes, 1)) nodeidpos
208 WHERE osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
209 and address is not NULL and address ? 'housenumber'
212 RAISE WARNING 'processing point % (%)', nextnode.hnr, ST_AsText(nextnode.geometry);
213 IF linegeo is null THEN
214 linegeo := NEW.linegeo;
216 splitline := ST_Split(ST_Snap(linegeo, nextnode.geometry, 0.0005), nextnode.geometry);
217 sectiongeo := ST_GeometryN(splitline, 1);
218 linegeo := ST_GeometryN(splitline, 2);
221 IF prevnode.hnr is not null
222 -- Check if there are housenumbers to interpolate between the
223 -- regularly mapped housenumbers.
224 -- (Conveniently also fails if one of the house numbers is not a number.)
225 and abs(prevnode.hnr - nextnode.hnr) > NEW.step
227 IF prevnode.hnr < nextnode.hnr THEN
228 startnumber := prevnode.hnr;
229 endnumber := nextnode.hnr;
231 startnumber := nextnode.hnr;
232 endnumber := prevnode.hnr;
233 sectiongeo := ST_Reverse(sectiongeo);
236 -- Adjust the interpolation, so that only inner housenumbers
237 -- are taken into account.
238 IF stepmod is null THEN
239 newstart := startnumber + NEW.step;
241 newstart := startnumber + 1;
242 moddiff := newstart % NEW.step - stepmod;
244 newstart := newstart + (NEW.step + moddiff);
246 newstart := newstart + moddiff;
249 newend := newstart + ((endnumber - 1 - newstart) / NEW.step) * NEW.step;
251 -- If newstart and newend are the same, then this returns a point.
252 sectiongeo := ST_LineSubstring(sectiongeo,
253 (newstart - startnumber)::float / (endnumber - startnumber)::float,
254 (newend - startnumber)::float / (endnumber - startnumber)::float);
255 startnumber := newstart;
258 -- determine postcode
259 postcode := coalesce(interpol_postcode,
260 token_normalized_postcode(prevnode.address->'postcode'),
261 token_normalized_postcode(nextnode.address->'postcode'),
263 IF postcode is NULL THEN
264 SELECT token_normalized_postcode(placex.postcode)
265 FROM placex WHERE place_id = NEW.parent_place_id INTO postcode;
267 IF postcode is NULL THEN
268 postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry);
271 -- Add the interpolation. If this is the first segment, just modify
272 -- the interpolation to be inserted, otherwise add an additional one
273 -- (marking it indexed already).
274 IF NEW.startnumber IS NULL THEN
275 NEW.startnumber := startnumber;
276 NEW.endnumber := endnumber;
277 NEW.linegeo := sectiongeo;
278 NEW.postcode := postcode;
280 INSERT INTO location_property_osmline
281 (linegeo, partition, osm_id, parent_place_id,
282 startnumber, endnumber, step,
283 address, postcode, country_code,
284 geometry_sector, indexed_status)
285 VALUES (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
286 startnumber, endnumber, NEW.step,
287 NEW.address, postcode,
288 NEW.country_code, NEW.geometry_sector, 0);
291 -- early break if we are out of line string,
292 -- might happen when a line string loops back on itself
293 IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
298 prevnode := nextnode;