lib-sql/functions/interpolation.sql

   1 -- Functions for address interpolation objects in location_property_osmline.
   2
   3 -- Splits the line at the given point and returns the two parts
   4 -- in a multilinestring.
   5 CREATE OR REPLACE FUNCTION split_line_on_node(line GEOMETRY, point GEOMETRY)
   6 RETURNS GEOMETRY
   7   AS $$
   8 BEGIN
   9   RETURN ST_Split(ST_Snap(line, point, 0.0005), point);
  10 END;
  11 $$
  12 LANGUAGE plpgsql IMMUTABLE;
  13
  14
  15 CREATE OR REPLACE FUNCTION get_interpolation_address(in_address HSTORE, wayid BIGINT)
  16 RETURNS HSTORE
  17   AS $$
  18 DECLARE
  19   location RECORD;
  20   waynodes BIGINT[];
  21 BEGIN
  22   IF akeys(in_address) != ARRAY['interpolation'] THEN
  23     RETURN in_address;
  24   END IF;
  25
  26   SELECT nodes INTO waynodes FROM planet_osm_ways WHERE id = wayid;
  27   FOR location IN
  28     SELECT placex.address, placex.osm_id FROM placex
  29      WHERE osm_type = 'N' and osm_id = ANY(waynodes)
  30            and placex.address is not null
  31            and (placex.address ? 'street' or placex.address ? 'place')
  32            and indexed_status < 100
  33   LOOP
  34     -- mark it as a derived address
  35     RETURN location.address || in_address || hstore('_inherited', '');
  36   END LOOP;
  37
  38   RETURN in_address;
  39 END;
  40 $$
  41 LANGUAGE plpgsql STABLE;
  42
  43
  44
  45 -- find the parent road of the cut road parts
  46 CREATE OR REPLACE FUNCTION get_interpolation_parent(token_info JSONB,
  47                                                     partition SMALLINT,
  48                                                     centroid GEOMETRY, geom GEOMETRY)
  49   RETURNS BIGINT
  50   AS $$
  51 DECLARE
  52   parent_place_id BIGINT;
  53   location RECORD;
  54 BEGIN
  55   parent_place_id := find_parent_for_address(token_info, partition, centroid);
  56
  57   IF parent_place_id is null THEN
  58     FOR location IN SELECT place_id FROM placex
  59         WHERE ST_DWithin(geom, placex.geometry, 0.001) and placex.rank_search = 26
  60         ORDER BY (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+
  61                   ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+
  62                   ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,1))) ASC limit 1
  63     LOOP
  64       parent_place_id := location.place_id;
  65     END LOOP;
  66   END IF;
  67
  68   IF parent_place_id is null THEN
  69     RETURN 0;
  70   END IF;
  71
  72   RETURN parent_place_id;
  73 END;
  74 $$
  75 LANGUAGE plpgsql STABLE;
  76
  77
  78 CREATE OR REPLACE FUNCTION osmline_reinsert(node_id BIGINT, geom GEOMETRY)
  79   RETURNS BOOLEAN
  80   AS $$
  81 DECLARE
  82   existingline RECORD;
  83 BEGIN
  84    SELECT w.id FROM planet_osm_ways w, location_property_osmline p
  85      WHERE p.linegeo && geom and p.osm_id = w.id and p.indexed_status = 0
  86            and node_id = any(w.nodes) INTO existingline;
  87
  88    IF existingline.id is not NULL THEN
  89        DELETE FROM location_property_osmline WHERE osm_id = existingline.id;
  90        INSERT INTO location_property_osmline (osm_id, address, linegeo)
  91          SELECT osm_id, address, geometry FROM place
  92            WHERE osm_type = 'W' and osm_id = existingline.id;
  93    END IF;
  94
  95    RETURN true;
  96 END;
  97 $$
  98 LANGUAGE plpgsql;
  99
 100
 101 CREATE OR REPLACE FUNCTION osmline_insert()
 102   RETURNS TRIGGER
 103   AS $$
 104 BEGIN
 105   NEW.place_id := nextval('seq_place');
 106   NEW.indexed_date := now();
 107
 108   IF NEW.indexed_status IS NULL THEN
 109       IF NEW.address is NULL OR NOT NEW.address ? 'interpolation'
 110          OR NEW.address->'interpolation' NOT IN ('odd', 'even', 'all') THEN
 111           -- other interpolation types than odd/even/all (e.g. numeric ones) are not supported
 112           RETURN NULL;
 113       END IF;
 114
 115       NEW.indexed_status := 1; --STATUS_NEW
 116       NEW.country_code := lower(get_country_code(NEW.linegeo));
 117
 118       NEW.partition := get_partition(NEW.country_code);
 119       NEW.geometry_sector := geometry_sector(NEW.partition, NEW.linegeo);
 120   END IF;
 121
 122   RETURN NEW;
 123 END;
 124 $$
 125 LANGUAGE plpgsql;
 126
 127
 128 CREATE OR REPLACE FUNCTION osmline_update()
 129   RETURNS TRIGGER
 130   AS $$
 131 DECLARE
 132   place_centroid GEOMETRY;
 133   waynodes BIGINT[];
 134   prevnode RECORD;
 135   nextnode RECORD;
 136   startnumber INTEGER;
 137   endnumber INTEGER;
 138   housenum INTEGER;
 139   linegeo GEOMETRY;
 140   splitline GEOMETRY;
 141   sectiongeo GEOMETRY;
 142   interpol_postcode TEXT;
 143   postcode TEXT;
 144 BEGIN
 145   -- deferred delete
 146   IF OLD.indexed_status = 100 THEN
 147     delete from location_property_osmline where place_id = OLD.place_id;
 148     RETURN NULL;
 149   END IF;
 150
 151   IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
 152     RETURN NEW;
 153   END IF;
 154
 155   NEW.interpolationtype = NEW.address->'interpolation';
 156
 157   place_centroid := ST_PointOnSurface(NEW.linegeo);
 158   NEW.parent_place_id = get_interpolation_parent(NEW.token_info, NEW.partition,
 159                                                  place_centroid, NEW.linegeo);
 160
 161   interpol_postcode := token_normalized_postcode(NEW.address->'postcode');
 162
 163   NEW.token_info := token_strip_info(NEW.token_info);
 164   IF NEW.address ? '_inherited' THEN
 165     NEW.address := hstore('interpolation', NEW.interpolationtype);
 166   END IF;
 167
 168   -- if the line was newly inserted, split the line as necessary
 169   IF OLD.indexed_status = 1 THEN
 170       select nodes from planet_osm_ways where id = NEW.osm_id INTO waynodes;
 171
 172       IF array_upper(waynodes, 1) IS NULL THEN
 173         RETURN NEW;
 174       END IF;
 175
 176       linegeo := NEW.linegeo;
 177       startnumber := NULL;
 178
 179       FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
 180
 181         select osm_id, address, geometry
 182           from place where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
 183                            and address is not NULL and address ? 'housenumber' limit 1 INTO nextnode;
 184         --RAISE NOTICE 'Nextnode.place_id: %s', nextnode.place_id;
 185         IF nextnode.osm_id IS NOT NULL THEN
 186           --RAISE NOTICE 'place_id is not null';
 187           IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN
 188             -- Make sure that the point is actually on the line. That might
 189             -- be a bit paranoid but ensures that the algorithm still works
 190             -- should osm2pgsql attempt to repair geometries.
 191             splitline := split_line_on_node(linegeo, nextnode.geometry);
 192             sectiongeo := ST_GeometryN(splitline, 1);
 193             linegeo := ST_GeometryN(splitline, 2);
 194           ELSE
 195             sectiongeo = linegeo;
 196           END IF;
 197           endnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer;
 198
 199           IF startnumber IS NOT NULL AND endnumber IS NOT NULL
 200              AND startnumber != endnumber
 201              AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN
 202
 203             IF (startnumber > endnumber) THEN
 204               housenum := endnumber;
 205               endnumber := startnumber;
 206               startnumber := housenum;
 207               sectiongeo := ST_Reverse(sectiongeo);
 208             END IF;
 209
 210             -- determine postcode
 211             postcode := coalesce(interpol_postcode,
 212                                  token_normalized_postcode(prevnode.address->'postcode'),
 213                                  token_normalized_postcode(nextnode.address->'postcode'),
 214                                  postcode);
 215
 216             IF postcode is NULL THEN
 217                 SELECT token_normalized_postcode(placex.postcode)
 218                   FROM placex WHERE place_id = NEW.parent_place_id INTO postcode;
 219             END IF;
 220             IF postcode is NULL THEN
 221                 postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry);
 222             END IF;
 223
 224             IF NEW.startnumber IS NULL THEN
 225                 NEW.startnumber := startnumber;
 226                 NEW.endnumber := endnumber;
 227                 NEW.linegeo := sectiongeo;
 228                 NEW.postcode := postcode;
 229              ELSE
 230               insert into location_property_osmline
 231                      (linegeo, partition, osm_id, parent_place_id,
 232                       startnumber, endnumber, interpolationtype,
 233                       address, postcode, country_code,
 234                       geometry_sector, indexed_status)
 235               values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
 236                       startnumber, endnumber, NEW.interpolationtype,
 237                       NEW.address, postcode,
 238                       NEW.country_code, NEW.geometry_sector, 0);
 239              END IF;
 240           END IF;
 241
 242           -- early break if we are out of line string,
 243           -- might happen when a line string loops back on itself
 244           IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
 245               RETURN NEW;
 246           END IF;
 247
 248           startnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer;
 249           prevnode := nextnode;
 250         END IF;
 251       END LOOP;
 252   END IF;
 253
 254   -- marking descendants for reparenting is not needed, because there are
 255   -- actually no descendants for interpolation lines
 256   RETURN NEW;
 257 END;
 258 $$
 259 LANGUAGE plpgsql;