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 COST 1000
37STABLE PARALLEL SAFE
38ROWS 1;
39
40CREATE OR REPLACE FUNCTION geocode(
41    IN_ADDY NORM_ADDY,
42    max_results integer DEFAULT 10,
43    restrict_geom geometry DEFAULT null,
44    OUT ADDY NORM_ADDY,
45    OUT GEOMOUT GEOMETRY,
46    OUT RATING INTEGER
47) RETURNS SETOF RECORD
48AS $_$
49DECLARE
50  rec RECORD;
51BEGIN
52
53  IF NOT IN_ADDY.parsed THEN
54    RETURN;
55  END IF;
56
57  -- Go for the full monty if we've got enough info
58  IF IN_ADDY.streetName IS NOT NULL AND
59      (IN_ADDY.zip IS NOT NULL OR IN_ADDY.stateAbbrev IS NOT NULL) THEN
60
61    FOR rec IN
62        SELECT *
63        FROM
64          (SELECT
65            DISTINCT ON (
66              (a.addy).address,
67              (a.addy).predirabbrev,
68              (a.addy).streetname,
69              (a.addy).streettypeabbrev,
70              (a.addy).postdirabbrev,
71              (a.addy).internal,
72              (a.addy).location,
73              (a.addy).stateabbrev,
74              (a.addy).zip
75              )
76            *
77           FROM
78             tiger.geocode_address(IN_ADDY, max_results, restrict_geom) a
79           ORDER BY
80              (a.addy).address,
81              (a.addy).predirabbrev,
82              (a.addy).streetname,
83              (a.addy).streettypeabbrev,
84              (a.addy).postdirabbrev,
85              (a.addy).internal,
86              (a.addy).location,
87              (a.addy).stateabbrev,
88              (a.addy).zip,
89              a.rating
90          ) as b
91        ORDER BY b.rating LIMIT max_results
92    LOOP
93
94      ADDY := rec.addy;
95      GEOMOUT := rec.geomout;
96      RATING := rec.rating;
97
98      RETURN NEXT;
99
100      IF RATING = 0 THEN
101        RETURN;
102      END IF;
103
104    END LOOP;
105
106    IF RATING IS NOT NULL THEN
107      RETURN;
108    END IF;
109  END IF;
110
111  -- No zip code, try state/location, need both or we'll get too much stuffs.
112  IF IN_ADDY.zip IS NOT NULL OR (IN_ADDY.stateAbbrev IS NOT NULL AND IN_ADDY.location IS NOT NULL) THEN
113    FOR rec in SELECT * FROM tiger.geocode_location(IN_ADDY, restrict_geom) As b ORDER BY b.rating LIMIT max_results
114    LOOP
115      ADDY := rec.addy;
116      GEOMOUT := rec.geomout;
117      RATING := rec.rating;
118
119      RETURN NEXT;
120      IF RATING = 100 THEN
121        RETURN;
122      END IF;
123    END LOOP;
124
125  END IF;
126
127  RETURN;
128
129END;
130$_$ LANGUAGE plpgsql
131COST 1000
132STABLE PARALLEL SAFE
133ROWS 1;
134