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