+ NEW.interpolationtype = NEW.address->'interpolation';
+
+ place_centroid := ST_PointOnSurface(NEW.linegeo);
+ NEW.parent_place_id = get_interpolation_parent(NEW.osm_id, NEW.address->'street',
+ NEW.address->'place',
+ NEW.partition, place_centroid, NEW.linegeo);
+
+ IF NEW.address is not NULL AND NEW.address ? 'postcode' AND NEW.address->'postcode' not similar to '%(,|;)%' THEN
+ interpol_postcode := NEW.address->'postcode';
+ housenum := getorcreate_postcode_id(NEW.address->'postcode');
+ ELSE
+ interpol_postcode := NULL;
+ END IF;
+
+ -- if the line was newly inserted, split the line as necessary
+ IF OLD.indexed_status = 1 THEN
+ select nodes from planet_osm_ways where id = NEW.osm_id INTO waynodes;
+
+ IF array_upper(waynodes, 1) IS NULL THEN
+ RETURN NEW;
+ END IF;
+
+ linegeo := NEW.linegeo;
+ startnumber := NULL;
+
+ FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
+
+ select osm_id, address, geometry
+ from place where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
+ and address is not NULL and address ? 'housenumber' limit 1 INTO nextnode;
+ --RAISE NOTICE 'Nextnode.place_id: %s', nextnode.place_id;
+ IF nextnode.osm_id IS NOT NULL THEN
+ --RAISE NOTICE 'place_id is not null';
+ IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN
+ -- Make sure that the point is actually on the line. That might
+ -- be a bit paranoid but ensures that the algorithm still works
+ -- should osm2pgsql attempt to repair geometries.
+ splitline := split_line_on_node(linegeo, nextnode.geometry);
+ sectiongeo := ST_GeometryN(splitline, 1);
+ linegeo := ST_GeometryN(splitline, 2);
+ ELSE
+ sectiongeo = linegeo;
+ END IF;
+ endnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer;
+
+ IF startnumber IS NOT NULL AND endnumber IS NOT NULL
+ AND startnumber != endnumber
+ AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN
+
+ IF (startnumber > endnumber) THEN
+ housenum := endnumber;
+ endnumber := startnumber;
+ startnumber := housenum;
+ sectiongeo := ST_Reverse(sectiongeo);
+ END IF;
+
+ -- determine postcode
+ postcode := coalesce(interpol_postcode,
+ prevnode.address->'postcode',
+ nextnode.address->'postcode',
+ postcode);
+
+ IF postcode is NULL THEN
+ SELECT placex.postcode FROM placex WHERE place_id = NEW.parent_place_id INTO postcode;
+ END IF;
+ IF postcode is NULL THEN
+ postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry);
+ END IF;
+
+ IF NEW.startnumber IS NULL THEN
+ NEW.startnumber := startnumber;
+ NEW.endnumber := endnumber;
+ NEW.linegeo := sectiongeo;
+ NEW.postcode := upper(trim(postcode));
+ ELSE
+ insert into location_property_osmline
+ (linegeo, partition, osm_id, parent_place_id,
+ startnumber, endnumber, interpolationtype,
+ address, postcode, country_code,
+ geometry_sector, indexed_status)
+ values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
+ startnumber, endnumber, NEW.interpolationtype,
+ NEW.address, postcode,
+ NEW.country_code, NEW.geometry_sector, 0);
+ END IF;
+ END IF;
+
+ -- early break if we are out of line string,
+ -- might happen when a line string loops back on itself
+ IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
+ RETURN NEW;
+ END IF;
+
+ startnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer;
+ prevnode := nextnode;
+ END IF;
+ END LOOP;
+ END IF;
+
+ -- marking descendants for reparenting is not needed, because there are
+ -- actually no descendants for interpolation lines
+ RETURN NEW;
+END;
+$$
+LANGUAGE plpgsql;
+
+-- Trigger for updates of location_postcode
+--
+-- Computes the parent object the postcode most likely refers to.
+-- This will be the place that determines the address displayed when
+-- searching for this postcode.
+CREATE OR REPLACE FUNCTION postcode_update() RETURNS
+TRIGGER
+ AS $$
+DECLARE
+ partition SMALLINT;
+ location RECORD;
+BEGIN
+ IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
+ RETURN NEW;
+ END IF;
+
+ NEW.indexed_date = now();
+
+ partition := get_partition(NEW.country_code);
+
+ SELECT * FROM get_postcode_rank(NEW.country_code, NEW.postcode)
+ INTO NEW.rank_search, NEW.rank_address;
+
+ NEW.parent_place_id = 0;
+ FOR location IN
+ SELECT place_id
+ FROM getNearFeatures(partition, NEW.geometry, NEW.rank_search, '{}'::int[])
+ WHERE NOT isguess ORDER BY rank_address DESC LIMIT 1
+ LOOP
+ NEW.parent_place_id = location.place_id;
+ END LOOP;
+
+ RETURN NEW;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION placex_update() RETURNS
+TRIGGER
+ AS $$
+DECLARE
+
+ place_centroid GEOMETRY;
+
+ search_maxdistance FLOAT[];
+ search_mindistance FLOAT[];
+ address_havelevel BOOLEAN[];
+
+ i INTEGER;
+ iMax FLOAT;
+ location RECORD;
+ way RECORD;
+ relation RECORD;