+CREATE OR REPLACE FUNCTION osmline_update() RETURNS
+TRIGGER
+ AS $$
+DECLARE
+ place_centroid GEOMETRY;
+ waynodes BIGINT[];
+ prevnode RECORD;
+ nextnode RECORD;
+ startnumber INTEGER;
+ endnumber INTEGER;
+ housenum INTEGER;
+ linegeo GEOMETRY;
+ splitline GEOMETRY;
+ sectiongeo GEOMETRY;
+ street TEXT;
+ addr_place TEXT;
+ postcode TEXT;
+BEGIN
+ -- deferred delete
+ IF OLD.indexed_status = 100 THEN
+ delete from location_property_osmline where place_id = OLD.place_id;
+ RETURN NULL;
+ END IF;
+
+ IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
+ RETURN NEW;
+ END IF;
+
+ -- do the reparenting: (finally here, because ALL places in placex,
+ -- that are needed for reparenting, need to be up to date)
+ -- (the osm interpolationline in location_property_osmline was marked for
+ -- reparenting in placex_insert/placex_delete with index_status = 1 or 2 (1 inset, 2 delete)
+ -- => index.c: sets index_status back to 0
+ -- => triggers this function)
+ place_centroid := ST_PointOnSurface(NEW.linegeo);
+ -- marking descendants for reparenting is not needed, because there are
+ -- actually no descendants for interpolation lines
+ NEW.parent_place_id = get_interpolation_parent(NEW.osm_id, NEW.street, NEW.addr_place,
+ NEW.partition, place_centroid, NEW.linegeo);
+
+ -- if we are just updating then our work is done
+ IF OLD.indexed_status != 1 THEN
+ return NEW;
+ END IF;
+
+ -- otherwise split the line as necessary
+ select nodes from planet_osm_ways where id = NEW.osm_id INTO waynodes;
+
+ IF array_upper(waynodes, 1) IS NULL THEN
+ RETURN 0;
+ END IF;
+
+ linegeo := NEW.linegeo;
+ startnumber := NULL;
+ street := NEW.street;
+ addr_place := NEW.addr_place;
+ postcode := NEW.postcode;
+
+ FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
+
+ select * from place where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
+ and housenumber is not NULL 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.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;
+
+ IF NEW.startnumber IS NULL THEN
+ NEW.startnumber := startnumber;
+ NEW.endnumber := endnumber;
+ NEW.linegeo := sectiongeo;
+ NEW.street := coalesce(street, prevnode.street, nextnode.street);
+ NEW.addr_place := coalesce(addr_place, prevnode.addr_place, nextnode.addr_place);
+ NEW.postcode := coalesce(postcode, prevnode.postcode, nextnode.postcode);
+ ELSE
+ insert into location_property_osmline
+ (linegeo, partition, osm_id, parent_place_id,
+ startnumber, endnumber, interpolationtype,
+ street, addr_place, postcode, calculated_country_code,
+ geometry_sector, indexed_status)
+ values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
+ startnumber, endnumber, NEW.interpolationtype,
+ coalesce(street, prevnode.street, nextnode.street),
+ coalesce(addr_place, prevnode.addr_place, nextnode.addr_place),
+ coalesce(postcode, prevnode.postcode, nextnode.postcode),
+ NEW.calculated_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;
projects / nominatim.git / blobdiff