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 CASE WHEN ST_GeometryType(geom) = 'ST_Line' THEN
57 (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+
58 ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+
59 ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,1)))
60 ELSE ST_distance(placex.geometry, geom) END
64 parent_place_id := location.place_id;
68 IF parent_place_id is null THEN
72 RETURN parent_place_id;
75 LANGUAGE plpgsql STABLE;
78 CREATE OR REPLACE FUNCTION reinsert_interpolation(way_id BIGINT, addr HSTORE,
85 -- Get the existing entry from the interpolation table.
86 SELECT array_agg(place_id) INTO existing
87 FROM location_property_osmline WHERE osm_id = way_id;
89 IF existing IS NULL or array_length(existing, 1) = 0 THEN
90 INSERT INTO location_property_osmline (osm_id, address, linegeo)
91 VALUES (way_id, addr, geom);
93 -- Update the interpolation table:
94 -- The first entry gets the original data, all other entries
95 -- are removed and will be recreated on indexing.
96 -- (An interpolation can be split up, if it has more than 2 address nodes)
97 UPDATE location_property_osmline
102 WHERE place_id = existing[1];
103 IF array_length(existing, 1) > 1 THEN
104 DELETE FROM location_property_osmline
105 WHERE place_id = any(existing[2:]);
115 CREATE OR REPLACE FUNCTION osmline_insert()
119 NEW.place_id := nextval('seq_place');
120 NEW.indexed_date := now();
122 IF NEW.indexed_status IS NULL THEN
123 IF NEW.address is NULL OR NOT NEW.address ? 'interpolation'
124 OR NOT (NEW.address->'interpolation' in ('odd', 'even', 'all')
125 or NEW.address->'interpolation' similar to '[1-9]')
127 -- alphabetic interpolation is not supported
131 NEW.indexed_status := 1; --STATUS_NEW
132 NEW.country_code := lower(get_country_code(NEW.linegeo));
134 NEW.partition := get_partition(NEW.country_code);
135 NEW.geometry_sector := geometry_sector(NEW.partition, NEW.linegeo);
144 CREATE OR REPLACE FUNCTION osmline_update()
159 interpol_postcode TEXT;
164 IF OLD.indexed_status = 100 THEN
165 delete from location_property_osmline where place_id = OLD.place_id;
169 IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
173 NEW.parent_place_id := get_interpolation_parent(NEW.token_info, NEW.partition,
174 ST_PointOnSurface(NEW.linegeo),
177 interpol_postcode := token_normalized_postcode(NEW.address->'postcode');
179 NEW.token_info := token_strip_info(NEW.token_info);
180 IF NEW.address ? '_inherited' THEN
181 NEW.address := hstore('interpolation', NEW.address->'interpolation');
184 -- If the line was newly inserted, split the line as necessary.
185 IF OLD.indexed_status = 1 THEN
186 IF NEW.address->'interpolation' in ('odd', 'even') THEN
188 stepmod := CASE WHEN NEW.address->'interpolation' = 'odd' THEN 1 ELSE 0 END;
190 NEW.step := CASE WHEN NEW.address->'interpolation' = 'all'
192 ELSE (NEW.address->'interpolation')::SMALLINT END;
196 SELECT nodes INTO waynodes
197 FROM planet_osm_ways WHERE id = NEW.osm_id;
199 IF array_upper(waynodes, 1) IS NULL THEN
204 SELECT null::integer as hnr INTO prevnode;
206 -- Go through all nodes on the interpolation line that have a housenumber.
208 SELECT DISTINCT ON (nodeidpos)
209 osm_id, address, geometry,
210 substring(address->'housenumber','[0-9]+')::integer as hnr
211 FROM placex, generate_series(1, array_upper(waynodes, 1)) nodeidpos
212 WHERE osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
213 and address is not NULL and address ? 'housenumber'
216 {% if debug %}RAISE WARNING 'processing point % (%)', nextnode.hnr, ST_AsText(nextnode.geometry);{% endif %}
217 IF linegeo is null THEN
218 linegeo := NEW.linegeo;
220 splitline := ST_Split(ST_Snap(linegeo, nextnode.geometry, 0.0005), nextnode.geometry);
221 sectiongeo := ST_GeometryN(splitline, 1);
222 linegeo := ST_GeometryN(splitline, 2);
225 IF prevnode.hnr is not null
226 -- Check if there are housenumbers to interpolate between the
227 -- regularly mapped housenumbers.
228 -- (Conveniently also fails if one of the house numbers is not a number.)
229 and abs(prevnode.hnr - nextnode.hnr) > NEW.step
231 IF prevnode.hnr < nextnode.hnr THEN
232 startnumber := prevnode.hnr;
233 endnumber := nextnode.hnr;
235 startnumber := nextnode.hnr;
236 endnumber := prevnode.hnr;
237 sectiongeo := ST_Reverse(sectiongeo);
240 -- Adjust the interpolation, so that only inner housenumbers
241 -- are taken into account.
242 IF stepmod is null THEN
243 newstart := startnumber + NEW.step;
245 newstart := startnumber + 1;
246 moddiff := newstart % NEW.step - stepmod;
248 newstart := newstart + (NEW.step + moddiff);
250 newstart := newstart + moddiff;
253 newend := newstart + ((endnumber - 1 - newstart) / NEW.step) * NEW.step;
255 -- If newstart and newend are the same, then this returns a point.
256 sectiongeo := ST_LineSubstring(sectiongeo,
257 (newstart - startnumber)::float / (endnumber - startnumber)::float,
258 (newend - startnumber)::float / (endnumber - startnumber)::float);
259 startnumber := newstart;
262 -- determine postcode
263 postcode := coalesce(interpol_postcode,
264 token_normalized_postcode(prevnode.address->'postcode'),
265 token_normalized_postcode(nextnode.address->'postcode'),
267 IF postcode is NULL THEN
268 SELECT token_normalized_postcode(placex.postcode)
269 FROM placex WHERE place_id = NEW.parent_place_id INTO postcode;
271 IF postcode is NULL THEN
272 postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry);
275 -- Add the interpolation. If this is the first segment, just modify
276 -- the interpolation to be inserted, otherwise add an additional one
277 -- (marking it indexed already).
278 IF NEW.startnumber IS NULL THEN
279 NEW.startnumber := startnumber;
280 NEW.endnumber := endnumber;
281 NEW.linegeo := sectiongeo;
282 NEW.postcode := postcode;
284 INSERT INTO location_property_osmline
285 (linegeo, partition, osm_id, parent_place_id,
286 startnumber, endnumber, step,
287 address, postcode, country_code,
288 geometry_sector, indexed_status)
289 VALUES (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
290 startnumber, endnumber, NEW.step,
291 NEW.address, postcode,
292 NEW.country_code, NEW.geometry_sector, 0);
295 -- early break if we are out of line string,
296 -- might happen when a line string loops back on itself
297 IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
302 prevnode := nextnode;