+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;
+ postcode TEXT;
+ seg_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;
+
+ 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' THEN
+ NEW.postcode = NEW.address->'postcode';
+ 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;
+ postcode := NEW.postcode;
+
+ 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;
+
+ seg_postcode := coalesce(postcode,
+ prevnode.address->'postcode',
+ nextnode.address->'postcode');
+
+ IF NEW.startnumber IS NULL THEN
+ NEW.startnumber := startnumber;
+ NEW.endnumber := endnumber;
+ NEW.linegeo := sectiongeo;
+ NEW.postcode := seg_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, seg_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;
+
+
+
+CREATE OR REPLACE FUNCTION placex_update() RETURNS
projects / nominatim.git / blobdiff