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