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