sql/tiger_import_start.sql

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