1CREATE OR REPLACE FUNCTION geocode(
2    input VARCHAR, max_results integer DEFAULT 10,
3    restrict_geom geometry DEFAULT NULL,
4    OUT ADDY NORM_ADDY,
5    OUT GEOMOUT GEOMETRY,
6    OUT RATING INTEGER
7) RETURNS SETOF RECORD
8AS $_$
9DECLARE
10  rec RECORD;
11BEGIN
12
13  IF input IS NULL THEN
14    RETURN;
15  END IF;
16
17  -- Pass the input string into the address normalizer
18  ADDY := normalize_address(input);
19  IF NOT ADDY.parsed THEN
20    RETURN;
21  END IF;
22
23/*  FOR rec IN SELECT * FROM geocode(ADDY)
24  LOOP
25
26    ADDY := rec.addy;
27    GEOMOUT := rec.geomout;
28    RATING := rec.rating;
29
30    RETURN NEXT;
31  END LOOP;*/
32
33  RETURN QUERY SELECT g.addy, g.geomout, g.rating FROM geocode(ADDY, max_results, restrict_geom) As g ORDER BY g.rating;
34
35END;
36$_$ LANGUAGE plpgsql STABLE;
37
38CREATE OR REPLACE FUNCTION geocode(
39    IN_ADDY NORM_ADDY,
40    max_results integer DEFAULT 10,
41    restrict_geom geometry DEFAULT null,
42    OUT ADDY NORM_ADDY,
43    OUT GEOMOUT GEOMETRY,
44    OUT RATING INTEGER
45) RETURNS SETOF RECORD
46AS $_$
47DECLARE
48  rec RECORD;
49BEGIN
50
51  IF NOT IN_ADDY.parsed THEN
52    RETURN;
53  END IF;
54
55  -- Go for the full monty if we've got enough info
56  IF IN_ADDY.streetName IS NOT NULL AND
57      (IN_ADDY.zip IS NOT NULL OR IN_ADDY.stateAbbrev IS NOT NULL) THEN
58
59    FOR rec IN
60        SELECT *
61        FROM
62          (SELECT
63            DISTINCT ON (
64              (a.addy).address,
65              (a.addy).predirabbrev,
66              (a.addy).streetname,
67              (a.addy).streettypeabbrev,
68              (a.addy).postdirabbrev,
69              (a.addy).internal,
70              (a.addy).location,
71              (a.addy).stateabbrev,
72              (a.addy).zip
73              )
74            *
75           FROM
76             tiger.geocode_address(IN_ADDY, max_results, restrict_geom) a
77           ORDER BY
78              (a.addy).address,
79              (a.addy).predirabbrev,
80              (a.addy).streetname,
81              (a.addy).streettypeabbrev,
82              (a.addy).postdirabbrev,
83              (a.addy).internal,
84              (a.addy).location,
85              (a.addy).stateabbrev,
86              (a.addy).zip,
87              a.rating
88          ) as b
89        ORDER BY b.rating LIMIT max_results
90    LOOP
91
92      ADDY := rec.addy;
93      GEOMOUT := rec.geomout;
94      RATING := rec.rating;
95
96      RETURN NEXT;
97
98      IF RATING = 0 THEN
99        RETURN;
100      END IF;
101
102    END LOOP;
103
104    IF RATING IS NOT NULL THEN
105      RETURN;
106    END IF;
107  END IF;
108
109  -- No zip code, try state/location, need both or we'll get too much stuffs.
110  IF IN_ADDY.zip IS NOT NULL OR (IN_ADDY.stateAbbrev IS NOT NULL AND IN_ADDY.location IS NOT NULL) THEN
111    FOR rec in SELECT * FROM tiger.geocode_location(IN_ADDY, restrict_geom) As b ORDER BY b.rating LIMIT max_results
112    LOOP
113      ADDY := rec.addy;
114      GEOMOUT := rec.geomout;
115      RATING := rec.rating;
116
117      RETURN NEXT;
118      IF RATING = 100 THEN
119        RETURN;
120      END IF;
121    END LOOP;
122
123  END IF;
124
125  RETURN;
126
127END;
128$_$ LANGUAGE plpgsql STABLE
129  COST 1000;
130