sql/tiger_import_start.sql

   1 DROP TABLE IF EXISTS location_property_tiger_import;
   2 CREATE TABLE location_property_tiger_import () INHERITS (location_property) {ts:aux-data};
   3
   4 CREATE OR REPLACE FUNCTION tigger_create_interpolation(linegeo GEOMETRY, in_startnumber INTEGER,
   5   in_endnumber INTEGER, interpolationtype TEXT,
   6   in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER
   7   AS $$
   8 DECLARE
   9
  10   startnumber INTEGER;
  11   endnumber INTEGER;
  12   stepsize INTEGER;
  13   housenum INTEGER;
  14   newpoints INTEGER;
  15   numberrange INTEGER;
  16   rangestartnumber INTEGER;
  17   place_centroid GEOMETRY;
  18   out_partition INTEGER;
  19   out_parent_place_id BIGINT;
  20   location RECORD;
  21   address_street_word_id INTEGER;
  22
  23 BEGIN
  24
  25   IF in_endnumber > in_startnumber THEN
  26     startnumber = in_startnumber;
  27     endnumber = in_endnumber;
  28   ELSE
  29     startnumber = in_endnumber;
  30     endnumber = in_startnumber;
  31   END IF;
  32
  33   numberrange := endnumber - startnumber;
  34   rangestartnumber := startnumber;
  35
  36   IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN
  37     startnumber := startnumber + 1;
  38     stepsize := 2;
  39   ELSE
  40     IF (interpolationtype = 'odd' OR interpolationtype = 'even') THEN
  41       stepsize := 2;
  42     ELSE -- everything else assumed to be 'all'
  43       stepsize := 1;
  44     END IF;
  45   END IF;
  46
  47   -- Filter out really broken tiger data
  48   IF numberrange > 0 AND (numberrange::float/stepsize::float > 500)
  49                     AND ST_length(linegeo)/(numberrange::float/stepsize::float) < 0.000001 THEN
  50     RAISE WARNING 'Road too short for number range % to % on %, % (%)',startnumber,endnumber,in_street,in_isin,
  51                   ST_length(linegeo)/(numberrange::float/stepsize::float);
  52     RETURN 0;
  53   END IF;
  54
  55   place_centroid := ST_Centroid(linegeo);
  56   out_partition := get_partition('us');
  57   out_parent_place_id := null;
  58
  59   address_street_word_id := get_name_id(make_standard_name(in_street));
  60   IF address_street_word_id IS NOT NULL THEN
  61     FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, ARRAY[address_street_word_id]) LOOP
  62       out_parent_place_id := location.place_id;
  63     END LOOP;
  64   END IF;
  65
  66   IF out_parent_place_id IS NULL THEN
  67     FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(out_partition, linegeo) LOOP
  68       out_parent_place_id := location.place_id;
  69     END LOOP;
  70   END IF;
  71
  72   IF out_parent_place_id IS NULL THEN
  73     FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
  74       out_parent_place_id := location.place_id;
  75     END LOOP;
  76   END IF;
  77
  78   newpoints := 0;
  79   FOR housenum IN startnumber..endnumber BY stepsize LOOP
  80     insert into location_property_tiger_import (place_id, partition, parent_place_id, housenumber, postcode, centroid)
  81     values (nextval('seq_place'), out_partition, out_parent_place_id, housenum, in_postcode,
  82       ST_LineInterpolatePoint(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
  83     newpoints := newpoints + 1;
  84   END LOOP;
  85
  86   RETURN newpoints;
  87 END;
  88 $$
  89 LANGUAGE plpgsql;