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(street INTEGER[], place INTEGER[],
  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(street, place, 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(token_addr_street_match_tokens(NEW.token_info),
 159                                                  token_addr_place_match_tokens(NEW.token_info),
 160                                                  NEW.partition, place_centroid, NEW.linegeo);
 161
 162   interpol_postcode := token_normalized_postcode(NEW.address->'postcode');
 163
 164   NEW.token_info := token_strip_info(NEW.token_info);
 165   IF NEW.address ? '_inherited' THEN
 166     NEW.address := hstore('interpolation', NEW.interpolationtype);
 167   END IF;
 168
 169   -- if the line was newly inserted, split the line as necessary
 170   IF OLD.indexed_status = 1 THEN
 171       select nodes from planet_osm_ways where id = NEW.osm_id INTO waynodes;
 172
 173       IF array_upper(waynodes, 1) IS NULL THEN
 174         RETURN NEW;
 175       END IF;
 176
 177       linegeo := NEW.linegeo;
 178       startnumber := NULL;
 179
 180       FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
 181
 182         select osm_id, address, geometry
 183           from place where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
 184                            and address is not NULL and address ? 'housenumber' limit 1 INTO nextnode;
 185         --RAISE NOTICE 'Nextnode.place_id: %s', nextnode.place_id;
 186         IF nextnode.osm_id IS NOT NULL THEN
 187           --RAISE NOTICE 'place_id is not null';
 188           IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN
 189             -- Make sure that the point is actually on the line. That might
 190             -- be a bit paranoid but ensures that the algorithm still works
 191             -- should osm2pgsql attempt to repair geometries.
 192             splitline := split_line_on_node(linegeo, nextnode.geometry);
 193             sectiongeo := ST_GeometryN(splitline, 1);
 194             linegeo := ST_GeometryN(splitline, 2);
 195           ELSE
 196             sectiongeo = linegeo;
 197           END IF;
 198           endnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer;
 199
 200           IF startnumber IS NOT NULL AND endnumber IS NOT NULL
 201              AND startnumber != endnumber
 202              AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN
 203
 204             IF (startnumber > endnumber) THEN
 205               housenum := endnumber;
 206               endnumber := startnumber;
 207               startnumber := housenum;
 208               sectiongeo := ST_Reverse(sectiongeo);
 209             END IF;
 210
 211             -- determine postcode
 212             postcode := coalesce(interpol_postcode,
 213                                  token_normalized_postcode(prevnode.address->'postcode'),
 214                                  token_normalized_postcode(nextnode.address->'postcode'),
 215                                  postcode);
 216
 217             IF postcode is NULL THEN
 218                 SELECT token_normalized_postcode(placex.postcode)
 219                   FROM placex WHERE place_id = NEW.parent_place_id INTO postcode;
 220             END IF;
 221             IF postcode is NULL THEN
 222                 postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry);
 223             END IF;
 224
 225             IF NEW.startnumber IS NULL THEN
 226                 NEW.startnumber := startnumber;
 227                 NEW.endnumber := endnumber;
 228                 NEW.linegeo := sectiongeo;
 229                 NEW.postcode := postcode;
 230              ELSE
 231               insert into location_property_osmline
 232                      (linegeo, partition, osm_id, parent_place_id,
 233                       startnumber, endnumber, interpolationtype,
 234                       address, postcode, country_code,
 235                       geometry_sector, indexed_status)
 236               values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
 237                       startnumber, endnumber, NEW.interpolationtype,
 238                       NEW.address, postcode,
 239                       NEW.country_code, NEW.geometry_sector, 0);
 240              END IF;
 241           END IF;
 242
 243           -- early break if we are out of line string,
 244           -- might happen when a line string loops back on itself
 245           IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
 246               RETURN NEW;
 247           END IF;
 248
 249           startnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer;
 250           prevnode := nextnode;
 251         END IF;
 252       END LOOP;
 253   END IF;
 254
 255   -- marking descendants for reparenting is not needed, because there are
 256   -- actually no descendants for interpolation lines
 257   RETURN NEW;
 258 END;
 259 $$
 260 LANGUAGE plpgsql;