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
|
-- location_extract_place_fuzzy(string, stateAbbrev)
-- This function checks the place_lookup table to find a potential match to
-- the location described at the end of the given string. If an exact match
-- fails, a fuzzy match is performed. The location as found in the given
-- string is returned.
CREATE OR REPLACE FUNCTION location_extract_place_fuzzy(
fullStreet VARCHAR,
stateAbbrev VARCHAR
) RETURNS VARCHAR
AS $_$
DECLARE
ws VARCHAR;
tempString VARCHAR;
location VARCHAR;
tempInt INTEGER;
word_count INTEGER;
rec RECORD;
test BOOLEAN;
BEGIN
ws := E'[ ,.\n\f\t]';
tempString := substring(fullStreet, '(?i)' || ws
|| '([a-zA-Z0-9]+)$');
IF tempString IS NULL THEN
tempString := fullStreet;
END IF;
IF stateAbbrev IS NOT NULL THEN
SELECT into tempInt count(*) FROM place_lookup
WHERE place_lookup.state = stateAbbrev
AND soundex(tempString) = end_soundex(name);
ELSE
SELECT into tempInt count(*) FROM place_lookup
WHERE soundex(tempString) = end_soundex(name);
END IF;
IF tempInt > 0 THEN
-- Some potentials were found. Begin a word-by-word soundex on each.
tempInt := 50;
IF stateAbbrev IS NOT NULL THEN
FOR rec IN SELECT name FROM place_lookup
WHERE place_lookup.state = stateAbbrev
AND soundex(tempString) = end_soundex(name) LOOP
word_count := count_words(rec.name);
test := TRUE;
tempString := get_last_words(fullStreet, word_count);
FOR i IN 1..word_count LOOP
IF soundex(split_part(tempString, ' ', i)) !=
soundex(split_part(rec.name, ' ', i)) THEN
test := FALSE;
END IF;
END LOOP;
IF test THEN
-- The soundex matched, determine if the distance is better.
IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN
location := tempString;
tempInt := levenshtein_ignore_case(rec.name, tempString);
END IF;
END IF;
END LOOP;
ELSE
FOR rec IN SELECT name FROM place_lookup
WHERE soundex(tempString) = end_soundex(name) LOOP
word_count := count_words(rec.name);
test := TRUE;
tempString := get_last_words(fullStreet, word_count);
FOR i IN 1..word_count LOOP
IF soundex(split_part(tempString, ' ', i)) !=
soundex(split_part(rec.name, ' ', i)) THEN
test := FALSE;
END IF;
END LOOP;
IF test THEN
-- The soundex matched, determine if the distance is better.
IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN
location := tempString;
tempInt := levenshtein_ignore_case(rec.name, tempString);
END IF;
END IF;
END LOOP;
END IF;
END IF;
RETURN location;
END;
$_$ LANGUAGE plpgsql;
|
