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.
10 -- Splits the line at the given point and returns the two parts
11 -- in a multilinestring.
12 CREATE OR REPLACE FUNCTION split_line_on_node(line GEOMETRY, point GEOMETRY)
16 RETURN ST_Split(ST_Snap(line, point, 0.0005), point);
19 LANGUAGE plpgsql IMMUTABLE;
22 CREATE OR REPLACE FUNCTION get_interpolation_address(in_address HSTORE, wayid BIGINT)
29 IF akeys(in_address) != ARRAY['interpolation'] THEN
33 SELECT nodes INTO waynodes FROM planet_osm_ways WHERE id = wayid;
35 SELECT placex.address, placex.osm_id FROM placex
36 WHERE osm_type = 'N' and osm_id = ANY(waynodes)
37 and placex.address is not null
38 and (placex.address ? 'street' or placex.address ? 'place')
39 and indexed_status < 100
41 -- mark it as a derived address
42 RETURN location.address || in_address || hstore('_inherited', '');
48 LANGUAGE plpgsql STABLE;
52 -- find the parent road of the cut road parts
53 CREATE OR REPLACE FUNCTION get_interpolation_parent(token_info JSONB,
55 centroid GEOMETRY, geom GEOMETRY)
59 parent_place_id BIGINT;
62 parent_place_id := find_parent_for_address(token_info, partition, centroid);
64 IF parent_place_id is null THEN
65 FOR location IN SELECT place_id FROM placex
66 WHERE ST_DWithin(geom, placex.geometry, 0.001) and placex.rank_search = 26
67 ORDER BY (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+
68 ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+
69 ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,1))) ASC limit 1
71 parent_place_id := location.place_id;
75 IF parent_place_id is null THEN
79 RETURN parent_place_id;
82 LANGUAGE plpgsql STABLE;
85 CREATE OR REPLACE FUNCTION reinsert_interpolation(way_id BIGINT, addr HSTORE,
92 -- Get the existing entry from the interpolation table.
93 SELECT array_agg(place_id) INTO existing
94 FROM location_property_osmline WHERE osm_id = way_id;
96 IF existing IS NULL or array_length(existing, 1) = 0 THEN
97 INSERT INTO location_property_osmline (osm_id, address, linegeo)
98 VALUES (way_id, addr, geom);
100 -- Update the interpolation table:
101 -- The first entry gets the original data, all other entries
102 -- are removed and will be recreated on indexing.
103 -- (An interpolation can be split up, if it has more than 2 address nodes)
104 UPDATE location_property_osmline
109 WHERE place_id = existing[1];
110 IF array_length(existing, 1) > 1 THEN
111 DELETE FROM location_property_osmline
112 WHERE place_id = any(existing[2:]);
122 CREATE OR REPLACE FUNCTION osmline_insert()
126 NEW.place_id := nextval('seq_place');
127 NEW.indexed_date := now();
129 IF NEW.indexed_status IS NULL THEN
130 IF NEW.address is NULL OR NOT NEW.address ? 'interpolation'
131 OR NEW.address->'interpolation' NOT IN ('odd', 'even', 'all') THEN
132 -- other interpolation types than odd/even/all (e.g. numeric ones) are not supported
136 NEW.indexed_status := 1; --STATUS_NEW
137 NEW.country_code := lower(get_country_code(NEW.linegeo));
139 NEW.partition := get_partition(NEW.country_code);
140 NEW.geometry_sector := geometry_sector(NEW.partition, NEW.linegeo);
149 CREATE OR REPLACE FUNCTION osmline_update()
153 place_centroid GEOMETRY;
163 interpol_postcode TEXT;
167 IF OLD.indexed_status = 100 THEN
168 delete from location_property_osmline where place_id = OLD.place_id;
172 IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
176 NEW.interpolationtype = NEW.address->'interpolation';
178 place_centroid := ST_PointOnSurface(NEW.linegeo);
179 NEW.parent_place_id = get_interpolation_parent(NEW.token_info, NEW.partition,
180 place_centroid, NEW.linegeo);
182 interpol_postcode := token_normalized_postcode(NEW.address->'postcode');
184 NEW.token_info := token_strip_info(NEW.token_info);
185 IF NEW.address ? '_inherited' THEN
186 NEW.address := hstore('interpolation', NEW.interpolationtype);
189 -- if the line was newly inserted, split the line as necessary
190 IF OLD.indexed_status = 1 THEN
191 select nodes from planet_osm_ways where id = NEW.osm_id INTO waynodes;
193 IF array_upper(waynodes, 1) IS NULL THEN
197 linegeo := NEW.linegeo;
200 FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
202 select osm_id, address, geometry
203 from place where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
204 and address is not NULL and address ? 'housenumber' limit 1 INTO nextnode;
205 --RAISE NOTICE 'Nextnode.place_id: %s', nextnode.place_id;
206 IF nextnode.osm_id IS NOT NULL THEN
207 --RAISE NOTICE 'place_id is not null';
208 IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN
209 -- Make sure that the point is actually on the line. That might
210 -- be a bit paranoid but ensures that the algorithm still works
211 -- should osm2pgsql attempt to repair geometries.
212 splitline := split_line_on_node(linegeo, nextnode.geometry);
213 sectiongeo := ST_GeometryN(splitline, 1);
214 linegeo := ST_GeometryN(splitline, 2);
216 sectiongeo = linegeo;
218 endnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer;
220 IF startnumber IS NOT NULL AND endnumber IS NOT NULL
221 AND startnumber != endnumber
222 AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN
224 IF (startnumber > endnumber) THEN
225 housenum := endnumber;
226 endnumber := startnumber;
227 startnumber := housenum;
228 sectiongeo := ST_Reverse(sectiongeo);
231 -- determine postcode
232 postcode := coalesce(interpol_postcode,
233 token_normalized_postcode(prevnode.address->'postcode'),
234 token_normalized_postcode(nextnode.address->'postcode'),
237 IF postcode is NULL THEN
238 SELECT token_normalized_postcode(placex.postcode)
239 FROM placex WHERE place_id = NEW.parent_place_id INTO postcode;
241 IF postcode is NULL THEN
242 postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry);
245 IF NEW.startnumber IS NULL THEN
246 NEW.startnumber := startnumber;
247 NEW.endnumber := endnumber;
248 NEW.linegeo := sectiongeo;
249 NEW.postcode := postcode;
251 insert into location_property_osmline
252 (linegeo, partition, osm_id, parent_place_id,
253 startnumber, endnumber, interpolationtype,
254 address, postcode, country_code,
255 geometry_sector, indexed_status)
256 values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
257 startnumber, endnumber, NEW.interpolationtype,
258 NEW.address, postcode,
259 NEW.country_code, NEW.geometry_sector, 0);
263 -- early break if we are out of line string,
264 -- might happen when a line string loops back on itself
265 IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
269 startnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer;
270 prevnode := nextnode;
275 -- marking descendants for reparenting is not needed, because there are
276 -- actually no descendants for interpolation lines