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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
CREATE OR REPLACE FUNCTION geocode_address_countysub_exact(
result REFCURSOR,
parsed NORM_ADDY
) RETURNS REFCURSOR
AS $_$
DECLARE
result REFCURSOR;
tempString VARCHAR;
tempInt VARCHAR;
BEGIN
IF parsed.location IS NULL THEN
-- location is manditory. This is the location geocoder after all.
RETURN NULL;
END IF;
-- Check to see if the road name can be matched.
IF parsed.stateAbbrev IS NOT NULL THEN
SELECT INTO tempInt count(*) FROM tiger_geocode_roads
WHERE parsed.location = tiger_geocode_roads.cousub
AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename)
AND parsed.stateAbbrev = tiger_geocode_roads.state;
ELSE
SELECT INTO tempInt count(*) FROM tiger_geocode_roads
WHERE parsed.location = tiger_geocode_roads.cousub
AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename);
END IF;
IF tempInt = 0 THEN
RETURN NULL;
ELSE
-- The road name matches, now we check to see if the addresses match
IF parsed.stateAbbrev IS NOT NULL THEN
SELECT INTO tempInt count(*)
FROM (
SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp,
parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev,
tiger_geocode_roads.fetype, parsed.postDirAbbrev,
tiger_geocode_roads.fedirs) as rating
FROM tiger_geocode_roads
WHERE parsed.location = tiger_geocode_roads.cousub
AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename)
AND parsed.stateAbbrev = tiger_geocode_roads.state
) AS subquery, roads_local
WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl,
roads_local.fraddr, roads_local.toaddr)
AND subquery.tlid = roads_local.tlid;
ELSE
SELECT INTO tempInt count(*)
FROM (
SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp,
parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev,
tiger_geocode_roads.fetype, parsed.postDirAbbrev,
tiger_geocode_roads.fedirs) as rating
FROM tiger_geocode_roads
WHERE parsed.location = tiger_geocode_roads.cousub
AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename)
) AS subquery, roads_local
WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl,
roads_local.fraddr, roads_local.toaddr)
AND subquery.tlid = roads_local.tlid;
END IF;
IF tempInt = 0 THEN
RETURN NULL;
ELSE
IF parsed.stateAbbrev IS NOT NULL THEN
OPEN result FOR
SELECT
roads_local.fedirp as fedirp,
roads_local.fename as fename,
roads_local.fetype as fetype,
roads_local.fedirs as fedirs,
CASE WHEN (parsed.address % 2) = roads_local.fraddl
OR (parsed.address % 2) = roads_local.toaddl
THEN coalesce(pl.name,zipl.city,csl.name,col.name) ELSE coalesce(pr.name,zipr.city,csr.name,cor.name) END as place,
CASE WHEN (parsed.address % 2) = roads_local.fraddl
OR (parsed.address % 2) = roads_local.toaddl
THEN sl.abbrev ELSE sr.abbrev END as state,
CASE WHEN (parsed.address % 2) = roads_local.fraddl
OR (parsed.address % 2) = roads_local.toaddl
THEN zipl ELSE zipr END as zip,
interpolate_from_address(parsed.address, roads_local.fraddl,
roads_local.toaddl, roads_local.fraddr, roads_local.toaddr,
roads_local.geom) as address_geom,
subquery.rating as rating
FROM (
SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp,
parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev,
tiger_geocode_roads.fetype, parsed.postDirAbbrev,
tiger_geocode_roads.fedirs, parsed.location,
tiger_geocode_roads.cousub) as rating
FROM tiger_geocode_roads
WHERE parsed.location = tiger_geocode_roads.cousub
AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename)
AND parsed.stateAbbrev = tiger_geocode_roads.state
) AS subquery
JOIN roads_local ON (subquery.tlid = roads_local.tlid)
JOIN state_lookup sl ON (roads_local.statel = sl.st_code)
JOIN state_lookup sr ON (roads_local.stater = sr.st_code)
LEFT JOIN place_lookup pl ON (roads_local.statel = pl.st_code AND roads_local.placel = pl.pl_code)
LEFT JOIN place_lookup pr ON (roads_local.stater = pr.st_code AND roads_local.placer = pr.pl_code)
LEFT JOIN county_lookup col ON (roads_local.statel = col.st_code AND roads_local.countyl = col.co_code)
LEFT JOIN county_lookup cor ON (roads_local.stater = cor.st_code AND roads_local.countyr = cor.co_code)
LEFT JOIN countysub_lookup csl ON (roads_local.statel = csl.st_code AND roads_local.countyl = csl.co_code AND roads_local.cousubl = csl.cs_code)
LEFT JOIN countysub_lookup csr ON (roads_local.stater = csr.st_code AND roads_local.countyr = csr.co_code AND roads_local.cousubr = csr.cs_code)
LEFT JOIN zip_lookup_base zipl ON (roads_local.zipl = zipl.zip)
LEFT JOIN zip_lookup_base zipr ON (roads_local.zipr = zipr.zip)
WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl,
roads_local.fraddr, roads_local.toaddr)
ORDER BY subquery.rating;
RETURN result;
ELSE
OPEN result FOR
SELECT
roads_local.fedirp as fedirp,
roads_local.fename as fename,
roads_local.fetype as fetype,
roads_local.fedirs as fedirs,
CASE WHEN (parsed.address % 2) = roads_local.fraddl
OR (parsed.address % 2) = roads_local.toaddl
THEN coalesce(pl.name,zipl.city,csl.name,col.name) ELSE coalesce(pr.name,zipr.city,csr.name,cor.name) END as place,
CASE WHEN (parsed.address % 2) = roads_local.fraddl
OR (parsed.address % 2) = roads_local.toaddl
THEN sl.abbrev ELSE sr.abbrev END as state,
CASE WHEN (parsed.address % 2) = roads_local.fraddl
OR (parsed.address % 2) = roads_local.toaddl
THEN zipl ELSE zipr END as zip,
interpolate_from_address(parsed.address, roads_local.fraddl,
roads_local.toaddl, roads_local.fraddr, roads_local.toaddr,
roads_local.geom) as address_geom,
subquery.rating as rating
FROM (
SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp,
parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev,
tiger_geocode_roads.fetype, parsed.postDirAbbrev,
tiger_geocode_roads.fedirs, parsed.location,
tiger_geocode_roads.cousub) as rating
FROM tiger_geocode_roads
WHERE parsed.location = tiger_geocode_roads.cousub
AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename)
) AS subquery
JOIN roads_local ON (subquery.tlid = roads_local.tlid)
JOIN state_lookup sl ON (roads_local.statel = sl.st_code)
JOIN state_lookup sr ON (roads_local.stater = sr.st_code)
LEFT JOIN place_lookup pl ON (roads_local.statel = pl.st_code AND roads_local.placel = pl.pl_code)
LEFT JOIN place_lookup pr ON (roads_local.stater = pr.st_code AND roads_local.placer = pr.pl_code)
LEFT JOIN county_lookup col ON (roads_local.statel = col.st_code AND roads_local.countyl = col.co_code)
LEFT JOIN county_lookup cor ON (roads_local.stater = cor.st_code AND roads_local.countyr = cor.co_code)
LEFT JOIN countysub_lookup csl ON (roads_local.statel = csl.st_code AND roads_local.countyl = csl.co_code AND roads_local.cousubl = csl.cs_code)
LEFT JOIN countysub_lookup csr ON (roads_local.stater = csr.st_code AND roads_local.countyr = csr.co_code AND roads_local.cousubr = csr.cs_code)
LEFT JOIN zip_lookup_base zipl ON (roads_local.zipl = zipl.zip)
LEFT JOIN zip_lookup_base zipr ON (roads_local.zipr = zipr.zip)
WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl,
roads_local.fraddr, roads_local.toaddr)
ORDER BY subquery.rating;
RETURN result;
END IF;
END IF;
END IF;
END;
$_$ LANGUAGE plpgsql;
|
