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