From 6e7a9c6be149ac2d0614d24f7950f9e996119079 Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Fri, 15 Apr 2011 11:19:12 +0000 Subject: [PATCH] change to all line feed git-svn-id: http://svn.osgeo.org/postgis/trunk@7036 b70326c6-7e19-0410-871a-916f4a2858ee --- .../tiger_2010/normalize/count_words.sql | 64 +- .../tiger_2010/normalize/end_soundex.sql | 34 +- .../tiger_2010/normalize/get_last_words.sql | 58 +- .../tiger_2010/normalize/location_extract.sql | 170 +- .../location_extract_countysub_exact.sql | 112 +- .../location_extract_countysub_fuzzy.sql | 178 +- .../location_extract_place_exact.sql | 118 +- .../location_extract_place_fuzzy.sql | 176 +- .../normalize/normalize_address.sql | 1124 ++++---- .../tiger_2010/normalize/pprint_addy.sql | 72 +- .../tiger_2010/normalize/state_extract.sql | 178 +- .../tiger_2010/tables/lookup_tables_2010.sql | 2448 ++++++++--------- 12 files changed, 2366 insertions(+), 2366 deletions(-) diff --git a/extras/tiger_geocoder/tiger_2010/normalize/count_words.sql b/extras/tiger_geocoder/tiger_2010/normalize/count_words.sql index c800b33df..166d4e32e 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/count_words.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/count_words.sql @@ -1,32 +1,32 @@ --- Determine the number of words in a string. Words are allowed to --- be seperated only by spaces, but multiple spaces between --- words are allowed. -CREATE OR REPLACE FUNCTION count_words(VARCHAR) RETURNS INTEGER -AS $_$ -DECLARE - tempString VARCHAR; - tempInt INTEGER; - count INTEGER := 1; - lastSpace BOOLEAN := FALSE; -BEGIN - IF $1 IS NULL THEN - return -1; - END IF; - tempInt := length($1); - IF tempInt = 0 THEN - return 0; - END IF; - FOR i IN 1..tempInt LOOP - tempString := substring($1 from i for 1); - IF tempString = ' ' THEN - IF NOT lastSpace THEN - count := count + 1; - END IF; - lastSpace := TRUE; - ELSE - lastSpace := FALSE; - END IF; - END LOOP; - return count; -END; -$_$ LANGUAGE plpgsql; +-- Determine the number of words in a string. Words are allowed to +-- be seperated only by spaces, but multiple spaces between +-- words are allowed. +CREATE OR REPLACE FUNCTION count_words(VARCHAR) RETURNS INTEGER +AS $_$ +DECLARE + tempString VARCHAR; + tempInt INTEGER; + count INTEGER := 1; + lastSpace BOOLEAN := FALSE; +BEGIN + IF $1 IS NULL THEN + return -1; + END IF; + tempInt := length($1); + IF tempInt = 0 THEN + return 0; + END IF; + FOR i IN 1..tempInt LOOP + tempString := substring($1 from i for 1); + IF tempString = ' ' THEN + IF NOT lastSpace THEN + count := count + 1; + END IF; + lastSpace := TRUE; + ELSE + lastSpace := FALSE; + END IF; + END LOOP; + return count; +END; +$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2010/normalize/end_soundex.sql b/extras/tiger_geocoder/tiger_2010/normalize/end_soundex.sql index aee40fd2b..02500ddb1 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/end_soundex.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/end_soundex.sql @@ -1,17 +1,17 @@ --- Runs the soundex function on the last word in the string provided. --- Words are allowed to be seperated by space, comma, period, new-line --- tab or form feed. -CREATE OR REPLACE FUNCTION end_soundex(VARCHAR) RETURNS VARCHAR -AS $_$ -DECLARE - tempString VARCHAR; -BEGIN - tempString := substring($1, E'[ ,.\n\t\f]([a-zA-Z0-9]*)$'); - IF tempString IS NOT NULL THEN - tempString := soundex(tempString); - ELSE - tempString := soundex($1); - END IF; - return tempString; -END; -$_$ LANGUAGE plpgsql; +-- Runs the soundex function on the last word in the string provided. +-- Words are allowed to be seperated by space, comma, period, new-line +-- tab or form feed. +CREATE OR REPLACE FUNCTION end_soundex(VARCHAR) RETURNS VARCHAR +AS $_$ +DECLARE + tempString VARCHAR; +BEGIN + tempString := substring($1, E'[ ,.\n\t\f]([a-zA-Z0-9]*)$'); + IF tempString IS NOT NULL THEN + tempString := soundex(tempString); + ELSE + tempString := soundex($1); + END IF; + return tempString; +END; +$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2010/normalize/get_last_words.sql b/extras/tiger_geocoder/tiger_2010/normalize/get_last_words.sql index a6ece1239..54058a288 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/get_last_words.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/get_last_words.sql @@ -1,29 +1,29 @@ --- Returns a string consisting of the last N words. Words are allowed --- to be seperated only by spaces, but multiple spaces between --- words are allowed. Words must be alphanumberic. --- If more words are requested than exist, the full input string is --- returned. -CREATE OR REPLACE FUNCTION get_last_words( - inputString VARCHAR, - count INTEGER -) RETURNS VARCHAR -AS $_$ -DECLARE - tempString VARCHAR; - result VARCHAR := ''; -BEGIN - FOR i IN 1..count LOOP - tempString := substring(inputString from '((?: )+[a-zA-Z0-9_]*)' || result || '$'); - - IF tempString IS NULL THEN - RETURN inputString; - END IF; - - result := tempString || result; - END LOOP; - - result := trim(both from result); - - RETURN result; -END; -$_$ LANGUAGE plpgsql; +-- Returns a string consisting of the last N words. Words are allowed +-- to be seperated only by spaces, but multiple spaces between +-- words are allowed. Words must be alphanumberic. +-- If more words are requested than exist, the full input string is +-- returned. +CREATE OR REPLACE FUNCTION get_last_words( + inputString VARCHAR, + count INTEGER +) RETURNS VARCHAR +AS $_$ +DECLARE + tempString VARCHAR; + result VARCHAR := ''; +BEGIN + FOR i IN 1..count LOOP + tempString := substring(inputString from '((?: )+[a-zA-Z0-9_]*)' || result || '$'); + + IF tempString IS NULL THEN + RETURN inputString; + END IF; + + result := tempString || result; + END LOOP; + + result := trim(both from result); + + RETURN result; +END; +$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2010/normalize/location_extract.sql b/extras/tiger_geocoder/tiger_2010/normalize/location_extract.sql index b42028eee..e90166038 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/location_extract.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/location_extract.sql @@ -1,85 +1,85 @@ --- location_extract(streetAddressString, stateAbbreviation) --- This function extracts a location name from the end of the given string. --- The first attempt is to find an exact match against the place_lookup --- table. If this fails, a word-by-word soundex match is tryed against the --- same table. If multiple candidates are found, the one with the smallest --- levenshtein distance from the given string is assumed the correct one. --- If no match is found against the place_lookup table, the same tests are --- run against the countysub_lookup table. --- --- The section of the given string corresponding to the location found is --- returned, rather than the string found from the tables. All the searching --- is done largely to determine the length (words) of the location, to allow --- the intended street name to be correctly identified. -CREATE OR REPLACE FUNCTION location_extract(fullStreet VARCHAR, stateAbbrev VARCHAR) RETURNS VARCHAR -AS $_$ -DECLARE - ws VARCHAR; - location VARCHAR; - lstate VARCHAR; - stmt VARCHAR; - street_array text[]; - word_count INTEGER; - rec RECORD; - best INTEGER := 0; - tempString VARCHAR; -BEGIN - IF fullStreet IS NULL THEN - RETURN NULL; - END IF; - - ws := E'[ ,.\n\f\t]'; - - IF stateAbbrev IS NOT NULL THEN - lstate := statefp FROM state WHERE state.stusps = stateAbbrev; - END IF; - lstate := COALESCE(lstate,''); - - street_array := regexp_split_to_array(fullStreet,ws); - word_count := array_upper(street_array,1); - - tempString := ''; - FOR i IN 1..word_count LOOP - CONTINUE WHEN street_array[word_count-i+1] IS NULL OR street_array[word_count-i+1] = ''; - - tempString := COALESCE(street_array[word_count-i+1],'') || tempString; - - stmt := ' SELECT' - || ' 1,' - || ' name,' - || ' levenshtein_ignore_case(' || quote_literal(tempString) || ',name) as rating,' - || ' length(name) as len' - || ' FROM place' - || ' WHERE ' || CASE WHEN stateAbbrev IS NOT NULL THEN 'statefp = ' || quote_literal(lstate) || ' AND ' ELSE '' END - || ' soundex(' || quote_literal(tempString) || ') = soundex(name)' - || ' AND levenshtein_ignore_case(' || quote_literal(tempString) || ',name) <= 2 ' - || ' UNION ALL SELECT' - || ' 2,' - || ' name,' - || ' levenshtein_ignore_case(' || quote_literal(tempString) || ',name) as rating,' - || ' length(name) as len' - || ' FROM cousub' - || ' WHERE ' || CASE WHEN stateAbbrev IS NOT NULL THEN 'statefp = ' || quote_literal(lstate) || ' AND ' ELSE '' END - || ' soundex(' || quote_literal(tempString) || ') = soundex(name)' - || ' AND levenshtein_ignore_case(' || quote_literal(tempString) || ',name) <= 2 ' - || ' ORDER BY ' - || ' 3 ASC, 1 ASC, 4 DESC' - || ' LIMIT 1;' - ; - - EXECUTE stmt INTO rec; - - IF rec.rating >= best THEN - location := tempString; - best := rec.rating; - END IF; - - tempString := ' ' || tempString; - END LOOP; - - location := replace(location,' ',ws || '+'); - location := substring(fullStreet,'(?i)' || location || '$'); - - RETURN location; -END; -$_$ LANGUAGE plpgsql; +-- location_extract(streetAddressString, stateAbbreviation) +-- This function extracts a location name from the end of the given string. +-- The first attempt is to find an exact match against the place_lookup +-- table. If this fails, a word-by-word soundex match is tryed against the +-- same table. If multiple candidates are found, the one with the smallest +-- levenshtein distance from the given string is assumed the correct one. +-- If no match is found against the place_lookup table, the same tests are +-- run against the countysub_lookup table. +-- +-- The section of the given string corresponding to the location found is +-- returned, rather than the string found from the tables. All the searching +-- is done largely to determine the length (words) of the location, to allow +-- the intended street name to be correctly identified. +CREATE OR REPLACE FUNCTION location_extract(fullStreet VARCHAR, stateAbbrev VARCHAR) RETURNS VARCHAR +AS $_$ +DECLARE + ws VARCHAR; + location VARCHAR; + lstate VARCHAR; + stmt VARCHAR; + street_array text[]; + word_count INTEGER; + rec RECORD; + best INTEGER := 0; + tempString VARCHAR; +BEGIN + IF fullStreet IS NULL THEN + RETURN NULL; + END IF; + + ws := E'[ ,.\n\f\t]'; + + IF stateAbbrev IS NOT NULL THEN + lstate := statefp FROM state WHERE state.stusps = stateAbbrev; + END IF; + lstate := COALESCE(lstate,''); + + street_array := regexp_split_to_array(fullStreet,ws); + word_count := array_upper(street_array,1); + + tempString := ''; + FOR i IN 1..word_count LOOP + CONTINUE WHEN street_array[word_count-i+1] IS NULL OR street_array[word_count-i+1] = ''; + + tempString := COALESCE(street_array[word_count-i+1],'') || tempString; + + stmt := ' SELECT' + || ' 1,' + || ' name,' + || ' levenshtein_ignore_case(' || quote_literal(tempString) || ',name) as rating,' + || ' length(name) as len' + || ' FROM place' + || ' WHERE ' || CASE WHEN stateAbbrev IS NOT NULL THEN 'statefp = ' || quote_literal(lstate) || ' AND ' ELSE '' END + || ' soundex(' || quote_literal(tempString) || ') = soundex(name)' + || ' AND levenshtein_ignore_case(' || quote_literal(tempString) || ',name) <= 2 ' + || ' UNION ALL SELECT' + || ' 2,' + || ' name,' + || ' levenshtein_ignore_case(' || quote_literal(tempString) || ',name) as rating,' + || ' length(name) as len' + || ' FROM cousub' + || ' WHERE ' || CASE WHEN stateAbbrev IS NOT NULL THEN 'statefp = ' || quote_literal(lstate) || ' AND ' ELSE '' END + || ' soundex(' || quote_literal(tempString) || ') = soundex(name)' + || ' AND levenshtein_ignore_case(' || quote_literal(tempString) || ',name) <= 2 ' + || ' ORDER BY ' + || ' 3 ASC, 1 ASC, 4 DESC' + || ' LIMIT 1;' + ; + + EXECUTE stmt INTO rec; + + IF rec.rating >= best THEN + location := tempString; + best := rec.rating; + END IF; + + tempString := ' ' || tempString; + END LOOP; + + location := replace(location,' ',ws || '+'); + location := substring(fullStreet,'(?i)' || location || '$'); + + RETURN location; +END; +$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_countysub_exact.sql b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_countysub_exact.sql index 51720d87d..f2303a344 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_countysub_exact.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_countysub_exact.sql @@ -1,56 +1,56 @@ --- location_extract_countysub_exact(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_countysub_exact( - fullStreet VARCHAR, - stateAbbrev VARCHAR -) RETURNS VARCHAR -AS $_$ -DECLARE - ws VARCHAR; - location VARCHAR; - tempInt INTEGER; - lstate VARCHAR; - rec RECORD; -BEGIN - ws := E'[ ,.\n\f\t]'; - - -- No hope of determining the location from place. Try countysub. - IF stateAbbrev IS NOT NULL THEN - lstate := statefp FROM state WHERE stusps = stateAbbrev; - SELECT INTO tempInt count(*) FROM cousub - WHERE cousub.statefp = lstate - AND texticregexeq(fullStreet, '(?i)' || name || '$'); - ELSE - SELECT INTO tempInt count(*) FROM cousub - WHERE texticregexeq(fullStreet, '(?i)' || name || '$'); - END IF; - - IF tempInt > 0 THEN - IF stateAbbrev IS NOT NULL THEN - FOR rec IN SELECT substring(fullStreet, '(?i)(' - || name || ')$') AS value, name FROM cousub - WHERE cousub.statefp = lstate - AND texticregexeq(fullStreet, '(?i)' || ws || name || - '$') ORDER BY length(name) DESC LOOP - -- Only the first result is needed. - location := rec.value; - EXIT; - END LOOP; - ELSE - FOR rec IN SELECT substring(fullStreet, '(?i)(' - || name || ')$') AS value, name FROM cousub - WHERE texticregexeq(fullStreet, '(?i)' || ws || name || - '$') ORDER BY length(name) DESC LOOP - -- again, only the first is needed. - location := rec.value; - EXIT; - END LOOP; - END IF; - END IF; - - RETURN location; -END; -$_$ LANGUAGE plpgsql; +-- location_extract_countysub_exact(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_countysub_exact( + fullStreet VARCHAR, + stateAbbrev VARCHAR +) RETURNS VARCHAR +AS $_$ +DECLARE + ws VARCHAR; + location VARCHAR; + tempInt INTEGER; + lstate VARCHAR; + rec RECORD; +BEGIN + ws := E'[ ,.\n\f\t]'; + + -- No hope of determining the location from place. Try countysub. + IF stateAbbrev IS NOT NULL THEN + lstate := statefp FROM state WHERE stusps = stateAbbrev; + SELECT INTO tempInt count(*) FROM cousub + WHERE cousub.statefp = lstate + AND texticregexeq(fullStreet, '(?i)' || name || '$'); + ELSE + SELECT INTO tempInt count(*) FROM cousub + WHERE texticregexeq(fullStreet, '(?i)' || name || '$'); + END IF; + + IF tempInt > 0 THEN + IF stateAbbrev IS NOT NULL THEN + FOR rec IN SELECT substring(fullStreet, '(?i)(' + || name || ')$') AS value, name FROM cousub + WHERE cousub.statefp = lstate + AND texticregexeq(fullStreet, '(?i)' || ws || name || + '$') ORDER BY length(name) DESC LOOP + -- Only the first result is needed. + location := rec.value; + EXIT; + END LOOP; + ELSE + FOR rec IN SELECT substring(fullStreet, '(?i)(' + || name || ')$') AS value, name FROM cousub + WHERE texticregexeq(fullStreet, '(?i)' || ws || name || + '$') ORDER BY length(name) DESC LOOP + -- again, only the first is needed. + location := rec.value; + EXIT; + END LOOP; + END IF; + END IF; + + RETURN location; +END; +$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_countysub_fuzzy.sql b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_countysub_fuzzy.sql index bd5eb8bd2..a7c642a34 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_countysub_fuzzy.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_countysub_fuzzy.sql @@ -1,89 +1,89 @@ --- location_extract_countysub_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_countysub_fuzzy( - fullStreet VARCHAR, - stateAbbrev VARCHAR -) RETURNS VARCHAR -AS $_$ -DECLARE - ws VARCHAR; - tempString VARCHAR; - location VARCHAR; - tempInt INTEGER; - word_count INTEGER; - rec RECORD; - test BOOLEAN; - lstate VARCHAR; -BEGIN - ws := E'[ ,.\n\f\t]'; - - -- Fuzzy matching. - tempString := substring(fullStreet, '(?i)' || ws || - '([a-zA-Z0-9]+)$'); - IF tempString IS NULL THEN - tempString := fullStreet; - END IF; - - IF stateAbbrev IS NOT NULL THEN - lstate := statefp FROM state WHERE stusps = stateAbbrev; - SELECT INTO tempInt count(*) FROM cousub - WHERE cousub.statefp = lstate - AND soundex(tempString) = end_soundex(name); - ELSE - SELECT INTO tempInt count(*) FROM cousub - WHERE soundex(tempString) = end_soundex(name); - END IF; - - IF tempInt > 0 THEN - tempInt := 50; - -- Some potentials were found. Begin a word-by-word soundex on each. - IF stateAbbrev IS NOT NULL THEN - FOR rec IN SELECT name FROM cousub - WHERE cousub.statefp = lstate - 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 cousub - 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; -- If no fuzzys were found, leave location null. - - RETURN location; -END; -$_$ LANGUAGE plpgsql; +-- location_extract_countysub_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_countysub_fuzzy( + fullStreet VARCHAR, + stateAbbrev VARCHAR +) RETURNS VARCHAR +AS $_$ +DECLARE + ws VARCHAR; + tempString VARCHAR; + location VARCHAR; + tempInt INTEGER; + word_count INTEGER; + rec RECORD; + test BOOLEAN; + lstate VARCHAR; +BEGIN + ws := E'[ ,.\n\f\t]'; + + -- Fuzzy matching. + tempString := substring(fullStreet, '(?i)' || ws || + '([a-zA-Z0-9]+)$'); + IF tempString IS NULL THEN + tempString := fullStreet; + END IF; + + IF stateAbbrev IS NOT NULL THEN + lstate := statefp FROM state WHERE stusps = stateAbbrev; + SELECT INTO tempInt count(*) FROM cousub + WHERE cousub.statefp = lstate + AND soundex(tempString) = end_soundex(name); + ELSE + SELECT INTO tempInt count(*) FROM cousub + WHERE soundex(tempString) = end_soundex(name); + END IF; + + IF tempInt > 0 THEN + tempInt := 50; + -- Some potentials were found. Begin a word-by-word soundex on each. + IF stateAbbrev IS NOT NULL THEN + FOR rec IN SELECT name FROM cousub + WHERE cousub.statefp = lstate + 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 cousub + 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; -- If no fuzzys were found, leave location null. + + RETURN location; +END; +$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_exact.sql b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_exact.sql index 45f1eecb9..3f5a1fd98 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_exact.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_exact.sql @@ -1,59 +1,59 @@ --- location_extract_place_exact(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_exact( - fullStreet VARCHAR, - stateAbbrev VARCHAR -) RETURNS VARCHAR -AS $_$ -DECLARE - ws VARCHAR; - location VARCHAR; - tempInt INTEGER; - lstate VARCHAR; - rec RECORD; -BEGIN - ws := E'[ ,.\n\f\t]'; - - -- Try for an exact match against places - IF stateAbbrev IS NOT NULL THEN - lstate := statefp FROM state WHERE stusps = stateAbbrev; - SELECT INTO tempInt count(*) FROM place - WHERE place.statefp = lstate - AND texticregexeq(fullStreet, '(?i)' || name || '$'); - ELSE - SELECT INTO tempInt count(*) FROM place - WHERE texticregexeq(fullStreet, '(?i)' || name || '$'); - END IF; - - IF tempInt > 0 THEN - -- Some matches were found. Look for the last one in the string. - IF stateAbbrev IS NOT NULL THEN - FOR rec IN SELECT substring(fullStreet, '(?i)(' - || name || ')$') AS value, name FROM place - WHERE place.statefp = lstate - AND texticregexeq(fullStreet, '(?i)' - || name || '$') ORDER BY length(name) DESC LOOP - -- Since the regex is end of string, only the longest (first) result - -- is useful. - location := rec.value; - EXIT; - END LOOP; - ELSE - FOR rec IN SELECT substring(fullStreet, '(?i)(' - || name || ')$') AS value, name FROM place - WHERE texticregexeq(fullStreet, '(?i)' - || name || '$') ORDER BY length(name) DESC LOOP - -- Since the regex is end of string, only the longest (first) result - -- is useful. - location := rec.value; - EXIT; - END LOOP; - END IF; - END IF; - - RETURN location; -END; -$_$ LANGUAGE plpgsql; +-- location_extract_place_exact(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_exact( + fullStreet VARCHAR, + stateAbbrev VARCHAR +) RETURNS VARCHAR +AS $_$ +DECLARE + ws VARCHAR; + location VARCHAR; + tempInt INTEGER; + lstate VARCHAR; + rec RECORD; +BEGIN + ws := E'[ ,.\n\f\t]'; + + -- Try for an exact match against places + IF stateAbbrev IS NOT NULL THEN + lstate := statefp FROM state WHERE stusps = stateAbbrev; + SELECT INTO tempInt count(*) FROM place + WHERE place.statefp = lstate + AND texticregexeq(fullStreet, '(?i)' || name || '$'); + ELSE + SELECT INTO tempInt count(*) FROM place + WHERE texticregexeq(fullStreet, '(?i)' || name || '$'); + END IF; + + IF tempInt > 0 THEN + -- Some matches were found. Look for the last one in the string. + IF stateAbbrev IS NOT NULL THEN + FOR rec IN SELECT substring(fullStreet, '(?i)(' + || name || ')$') AS value, name FROM place + WHERE place.statefp = lstate + AND texticregexeq(fullStreet, '(?i)' + || name || '$') ORDER BY length(name) DESC LOOP + -- Since the regex is end of string, only the longest (first) result + -- is useful. + location := rec.value; + EXIT; + END LOOP; + ELSE + FOR rec IN SELECT substring(fullStreet, '(?i)(' + || name || ')$') AS value, name FROM place + WHERE texticregexeq(fullStreet, '(?i)' + || name || '$') ORDER BY length(name) DESC LOOP + -- Since the regex is end of string, only the longest (first) result + -- is useful. + location := rec.value; + EXIT; + END LOOP; + END IF; + END IF; + + RETURN location; +END; +$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_fuzzy.sql b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_fuzzy.sql index 2762d0f87..899558ace 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_fuzzy.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/location_extract_place_fuzzy.sql @@ -1,88 +1,88 @@ --- 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; - lstate VARCHAR; -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 - lstate := statefp FROM state WHERE stusps = stateAbbrev; - SELECT into tempInt count(*) FROM place - WHERE place.statefp = lstate - AND soundex(tempString) = end_soundex(name); - ELSE - SELECT into tempInt count(*) FROM place - 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 - WHERE place.statefp = lstate - 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 - 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; +-- 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; + lstate VARCHAR; +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 + lstate := statefp FROM state WHERE stusps = stateAbbrev; + SELECT into tempInt count(*) FROM place + WHERE place.statefp = lstate + AND soundex(tempString) = end_soundex(name); + ELSE + SELECT into tempInt count(*) FROM place + 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 + WHERE place.statefp = lstate + 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 + 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; diff --git a/extras/tiger_geocoder/tiger_2010/normalize/normalize_address.sql b/extras/tiger_geocoder/tiger_2010/normalize/normalize_address.sql index c3ccb7ee7..ed62b7034 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/normalize_address.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/normalize_address.sql @@ -1,562 +1,562 @@ --- normalize_address(addressString) --- This takes an address string and parses it into address (internal/street) --- street name, type, direction prefix and suffix, location, state and --- zip code, depending on what can be found in the string. --- --- The US postal address standard is used: --- --- --- --- State is assumed to be included in the string, and MUST be matchable to --- something in the state_lookup table. Fuzzy matching is used if no direct --- match is found. --- --- Two formats of zip code are acceptable: five digit, and five + 4. --- --- The internal addressing indicators are looked up from the --- secondary_unit_lookup table. A following identifier is accepted --- but it must start with a digit. --- --- The location is parsed from the string using other indicators, such --- as street type, direction suffix or internal address, if available. --- If these are not, the location is extracted using comparisons against --- the places_lookup table, then the countysub_lookup table to determine --- what, in the original string, is intended to be the location. In both --- cases, an exact match is first pursued, then a word-by-word fuzzy match. --- The result is not the name of the location from the tables, but the --- section of the given string that corresponds to the name from the tables. --- --- Zip codes and street names are not validated. --- --- Direction indicators are extracted by comparison with the direction_lookup --- table. --- --- Street addresses are assumed to be a single word, starting with a number. --- Address is manditory; if no address is given, and the street is numbered, --- the resulting address will be the street name, and the street name --- will be an empty string. --- --- In some cases, the street type is part of the street name. --- eg State Hwy 22a. As long as the word following the type starts with a --- number (this is usually the case) this will be caught. Some street names --- include a type name, and have a street type that differs. This will be --- handled properly, so long as both are given. If the street type is --- omitted, the street names included type will be parsed as the street type. --- --- The output is currently a colon seperated list of values: --- InternalAddress:StreetAddress:DirectionPrefix:StreetName:StreetType: --- DirectionSuffix:Location:State:ZipCode --- This returns each element as entered. It's mainly meant for debugging. --- There is also another option that returns: --- StreetAddress:DirectionPrefixAbbreviation:StreetName:StreetTypeAbbreviation: --- DirectionSuffixAbbreviation:Location:StateAbbreviation:ZipCode --- This is more standardized and better for use with a geocoder. -CREATE OR REPLACE FUNCTION normalize_address( - in_rawInput VARCHAR -) RETURNS norm_addy -AS $_$ -DECLARE - debug_flag boolean := false; - result norm_addy; - addressString VARCHAR; - zipString VARCHAR; - preDir VARCHAR; - postDir VARCHAR; - fullStreet VARCHAR; - reducedStreet VARCHAR; - streetType VARCHAR; - state VARCHAR; - tempString VARCHAR; - tempInt INTEGER; - rec RECORD; - ws VARCHAR; - rawInput VARCHAR; -BEGIN - result.parsed := FALSE; - - rawInput := trim(in_rawInput); - - IF rawInput IS NULL THEN - RETURN result; - END IF; - - ws := E'[ ,.\t\n\f\r]'; - - IF debug_flag THEN - raise notice 'input: %', rawInput; - END IF; - - -- Assume that the address begins with a digit, and extract it from - -- the input string. - addressString := substring(rawInput from '^([0-9].*?)[ ,/.]'); - - IF debug_flag THEN - raise notice 'addressString: %', addressString; - END IF; - - -- There are two formats for zip code, the normal 5 digit, and - -- the nine digit zip-4. It may also not exist. - zipString := substring(rawInput from ws || '([0-9]{5})$'); - IF zipString IS NULL THEN - zipString := substring(rawInput from ws || '([0-9]{5})-[0-9]{4}$'); - -- Check if all we got was a zipcode, of either form - IF zipString IS NULL THEN - zipString := substring(rawInput from '^([0-9]{5})$'); - IF zipString IS NULL THEN - zipString := substring(rawInput from '^([0-9]{5})-[0-9]{4}$'); - END IF; - -- If it was only a zipcode, then just return it. - IF zipString IS NOT NULL THEN - result.zip := zipString; - result.parsed := TRUE; - RETURN result; - END IF; - END IF; - END IF; - - IF debug_flag THEN - raise notice 'zipString: %', zipString; - END IF; - - IF zipString IS NOT NULL THEN - fullStreet := substring(rawInput from '(.*)' - || ws || '+' || cull_null(zipString) || '[- ]?([0-9]{4})?$'); - ELSE - fullStreet := rawInput; - END IF; - - IF debug_flag THEN - raise notice 'fullStreet: %', fullStreet; - END IF; - - -- FIXME: state_extract should probably be returning a record so we can - -- avoid having to parse the result from it. - tempString := state_extract(fullStreet); - IF tempString IS NOT NULL THEN - state := split_part(tempString, ':', 1); - result.stateAbbrev := split_part(tempString, ':', 2); - END IF; - - IF debug_flag THEN - raise notice 'stateAbbrev: %', result.stateAbbrev; - END IF; - - -- The easiest case is if the address is comma delimited. There are some - -- likely cases: - -- street level, location, state - -- street level, location state - -- street level, location - -- street level, internal address, location, state - -- street level, internal address, location state - -- street level, internal address location state - -- street level, internal address, location - -- street level, internal address location - -- The first three are useful. - tempString := substring(fullStreet, '(?i),' || ws || '+(.*?)(,?' || ws || - '*' || cull_null(state) || '$)'); - IF tempString = '' THEN tempString := NULL; END IF; - IF tempString IS NOT NULL THEN - result.location := tempString; - IF addressString IS NOT NULL THEN - fullStreet := substring(fullStreet, '(?i)' || addressString || ws || - '+(.*),' || ws || '+' || result.location); - ELSE - fullStreet := substring(fullStreet, '(?i)(.*),' || ws || '+' || - result.location); - END IF; - END IF; - - IF debug_flag THEN - raise notice 'fullStreet: %', fullStreet; - raise notice 'location: %', result.location; - END IF; - - -- Pull out the full street information, defined as everything between the - -- address and the state. This includes the location. - -- This doesnt need to be done if location has already been found. - IF result.location IS NULL THEN - IF addressString IS NOT NULL THEN - IF state IS NOT NULL THEN - fullStreet := substring(fullStreet, '(?i)' || addressString || - ws || '+(.*?)' || ws || '+' || state); - ELSE - fullStreet := substring(fullStreet, '(?i)' || addressString || - ws || '+(.*?)'); - END IF; - ELSE - IF state IS NOT NULL THEN - fullStreet := substring(fullStreet, '(?i)(.*?)' || ws || - '+' || state); - ELSE - fullStreet := substring(fullStreet, '(?i)(.*?)'); - END IF; - END IF; - - IF debug_flag THEN - raise notice 'fullStreet: %', fullStreet; - END IF; - - result.location := location_extract(fullStreet, result.stateAbbrev); - - -- A location can't be a street type, sorry. - IF lower(result.location) IN (SELECT lower(name) FROM street_type_lookup) THEN - result.location := NULL; - END IF; - - -- If the location was found, remove it from fullStreet - IF result.location IS NOT NULL THEN - fullStreet := substring(fullStreet, '(?i)(.*)' || ws || '+' || - result.location); - END IF; - END IF; - - IF debug_flag THEN - raise notice 'fullStreet: %', fullStreet; - raise notice 'location: %', result.location; - END IF; - - -- Determine if any internal address is included, such as apartment - -- or suite number. - SELECT INTO tempInt count(*) FROM secondary_unit_lookup - WHERE texticregexeq(fullStreet, '(?i)' || ws || name || '(' - || ws || '|$)'); - IF tempInt = 1 THEN - result.internal := substring(fullStreet, '(?i)' || ws || '(' - || name || ws || '*#?' || ws - || '*(?:[0-9][-0-9a-zA-Z]*)?' || ')(?:' || ws || '|$)') - FROM secondary_unit_lookup - WHERE texticregexeq(fullStreet, '(?i)' || ws || name || '(' - || ws || '|$)'); - ELSIF tempInt > 1 THEN - -- In the event of multiple matches to a secondary unit designation, we - -- will assume that the last one is the true one. - tempInt := 0; - FOR rec in SELECT trim(substring(fullStreet, '(?i)' || ws || '(' - || name || '(?:' || ws || '*#?' || ws - || '*(?:[0-9][-0-9a-zA-Z]*)?)' || ws || '?|$)')) as value - FROM secondary_unit_lookup - WHERE texticregexeq(fullStreet, '(?i)' || ws || name || '(' - || ws || '|$)') LOOP - IF tempInt < position(rec.value in fullStreet) THEN - tempInt := position(rec.value in fullStreet); - result.internal := rec.value; - END IF; - END LOOP; - END IF; - - IF debug_flag THEN - raise notice 'internal: %', result.internal; - END IF; - - IF result.location IS NULL THEN - -- If the internal address is given, the location is everything after it. - result.location := substring(fullStreet, result.internal || ws || '+(.*)$'); - END IF; - - IF debug_flag THEN - raise notice 'location: %', result.location; - END IF; - - -- Pull potential street types from the full street information - tempInt := count(*) FROM street_type_lookup - WHERE texticregexeq(fullStreet, '(?i)' || ws || '(' || name - || ')(?:' || ws || '|$)'); - IF tempInt = 1 THEN - SELECT INTO rec abbrev, substring(fullStreet, '(?i)' || ws || '(' - || name || ')(?:' || ws || '|$)') AS given FROM street_type_lookup - WHERE texticregexeq(fullStreet, '(?i)' || ws || '(' || name - || ')(?:' || ws || '|$)'); - streetType := rec.given; - result.streetTypeAbbrev := rec.abbrev; - ELSIF tempInt > 1 THEN - tempInt := 0; - FOR rec IN SELECT abbrev, substring(fullStreet, '(?i)' || ws || '(' - || name || ')(?:' || ws || '|$)') AS given FROM street_type_lookup - WHERE texticregexeq(fullStreet, '(?i)' || ws || '(' || name - || ')(?:' || ws || '|$)') LOOP - -- If we have found an internal address, make sure the type - -- precedes it. - IF result.internal IS NOT NULL THEN - IF position(rec.given IN fullStreet) < position(result.internal IN fullStreet) THEN - IF tempInt < position(rec.given IN fullStreet) THEN - streetType := rec.given; - result.streetTypeAbbrev := rec.abbrev; - tempInt := position(rec.given IN fullStreet); - END IF; - END IF; - ELSIF tempInt < position(rec.given IN fullStreet) THEN - streetType := rec.given; - result.streetTypeAbbrev := rec.abbrev; - tempInt := position(rec.given IN fullStreet); - END IF; - END LOOP; - END IF; - - IF debug_flag THEN - raise notice 'streetTypeAbbrev: %', result.streetTypeAbbrev; - END IF; - - -- There is a little more processing required now. If the word after the - -- street type begins with a number, the street type should be considered - -- part of the name, as well as the next word. eg, State Route 225a. If - -- the next word starts with a char, then everything after the street type - -- will be considered location. If there is no street type, then I'm sad. - IF streetType IS NOT NULL THEN - tempString := substring(fullStreet, streetType || ws || - E'+([0-9][^ ,.\t\r\n\f]*?)' || ws); - IF tempString IS NOT NULL THEN - IF result.location IS NULL THEN - result.location := substring(fullStreet, streetType || ws || '+' - || tempString || ws || '+(.*)$'); - END IF; - reducedStreet := substring(fullStreet, '(.*)' || ws || '+' - || result.location || '$'); - streetType := NULL; - result.streetTypeAbbrev := NULL; - ELSE - IF result.location IS NULL THEN - result.location := substring(fullStreet, streetType || ws || '+(.*)$'); - END IF; - reducedStreet := substring(fullStreet, '^(.*)' || ws || '+' - || streetType); - END IF; - - -- The pre direction should be at the beginning of the fullStreet string. - -- The post direction should be at the beginning of the location string - -- if there is no internal address - tempString := substring(reducedStreet, '(?i)(^' || name - || ')' || ws) FROM direction_lookup WHERE - texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) - ORDER BY length(name) DESC; - IF tempString IS NOT NULL THEN - preDir := tempString; - result.preDirAbbrev := abbrev FROM direction_lookup - where texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) - ORDER BY length(name) DESC; - result.streetName := substring(reducedStreet, '^' || preDir || ws || '(.*)'); - ELSE - result.streetName := reducedStreet; - END IF; - - IF texticregexeq(result.location, '(?i)' || result.internal || '$') THEN - -- If the internal address is at the end of the location, then no - -- location was given. We still need to look for post direction. - SELECT INTO rec abbrev, - substring(result.location, '(?i)^(' || name || ')' || ws) as value - FROM direction_lookup WHERE texticregexeq(result.location, '(?i)^' - || name || ws) ORDER BY length(name) desc; - IF rec.value IS NOT NULL THEN - postDir := rec.value; - result.postDirAbbrev := rec.abbrev; - END IF; - result.location := null; - ELSIF result.internal IS NULL THEN - -- If no location is given, the location string will be the post direction - SELECT INTO tempInt count(*) FROM direction_lookup WHERE - upper(result.location) = upper(name); - IF tempInt != 0 THEN - postDir := result.location; - SELECT INTO result.postDirAbbrev abbrev FROM direction_lookup WHERE - upper(postDir) = upper(name); - result.location := NULL; - ELSE - -- postDirection is not equal location, but may be contained in it. - SELECT INTO tempString substring(result.location, '(?i)(^' || name - || ')' || ws) FROM direction_lookup WHERE - texticregexeq(result.location, '(?i)(^' || name || ')' || ws) - ORDER BY length(name) desc; - IF tempString IS NOT NULL THEN - postDir := tempString; - SELECT INTO result.postDirAbbrev abbrev FROM direction_lookup - where texticregexeq(result.location, '(?i)(^' || name || ')' || ws); - result.location := substring(result.location, '^' || postDir || ws || '+(.*)'); - END IF; - END IF; - ELSE - -- internal is not null, but is not at the end of the location string - -- look for post direction before the internal address - SELECT INTO tempString substring(fullStreet, '(?i)' || streetType - || ws || '+(' || name || ')' || ws || '+' || result.internal) - FROM direction_lookup WHERE texticregexeq(fullStreet, '(?i)' - || ws || name || ws || '+' || result.internal) ORDER BY length(name) desc; - IF tempString IS NOT NULL THEN - postDir := tempString; - SELECT INTO result.postDirAbbrev abbrev FROM direction_lookup - WHERE texticregexeq(fullStreet, '(?i)' || ws || name || ws); - END IF; - END IF; - ELSE - -- No street type was found - - -- If an internal address was given, then the split becomes easy, and the - -- street name is everything before it, without directions. - IF result.internal IS NOT NULL THEN - reducedStreet := substring(fullStreet, '(?i)^(.*?)' || ws || '+' - || result.internal); - tempInt := count(*) FROM direction_lookup WHERE - texticregexeq(reducedStreet, '(?i)' || ws || name || '$'); - IF tempInt > 0 THEN - postDir := substring(reducedStreet, '(?i)' || ws || '(' - || name || ')' || '$') FROM direction_lookup - WHERE texticregexeq(reducedStreet, '(?i)' || ws || name || '$'); - result.postDirAbbrev := abbrev FROM direction_lookup - WHERE texticregexeq(reducedStreet, '(?i)' || ws || name || '$'); - END IF; - tempString := substring(reducedStreet, '(?i)^(' || name - || ')' || ws) FROM direction_lookup WHERE - texticregexeq(reducedStreet, '(?i)^(' || name || ')' || ws) - ORDER BY length(name) DESC; - IF tempString IS NOT NULL THEN - preDir := tempString; - result.preDirAbbrev := abbrev FROM direction_lookup WHERE - texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) - ORDER BY length(name) DESC; - result.streetName := substring(reducedStreet, '(?i)^' || preDir || ws - || '+(.*?)(?:' || ws || '+' || cull_null(postDir) || '|$)'); - ELSE - result.streetName := substring(reducedStreet, '(?i)^(.*?)(?:' || ws - || '+' || cull_null(postDir) || '|$)'); - END IF; - ELSE - - -- If a post direction is given, then the location is everything after, - -- the street name is everything before, less any pre direction. - tempInt := count(*) FROM direction_lookup - WHERE texticregexeq(fullStreet, '(?i)' || ws || name || '(?:' - || ws || '|$)'); - - IF tempInt = 1 THEN - -- A single postDir candidate was found. This makes it easier. - postDir := substring(fullStreet, '(?i)' || ws || '(' - || name || ')(?:' || ws || '|$)') FROM direction_lookup WHERE - texticregexeq(fullStreet, '(?i)' || ws || name || '(?:' - || ws || '|$)'); - result.postDirAbbrev := abbrev FROM direction_lookup - WHERE texticregexeq(fullStreet, '(?i)' || ws || name - || '(?:' || ws || '|$)'); - IF result.location IS NULL THEN - result.location := substring(fullStreet, '(?i)' || ws || postDir - || ws || '+(.*?)$'); - END IF; - reducedStreet := substring(fullStreet, '^(.*?)' || ws || '+' - || postDir); - tempString := substring(reducedStreet, '(?i)(^' || name - || ')' || ws) FROM direction_lookup WHERE - texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) - ORDER BY length(name) DESC; - IF tempString IS NOT NULL THEN - preDir := tempString; - result.preDirAbbrev := abbrev FROM direction_lookup WHERE - texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) - ORDER BY length(name) DESC; - result.streetName := substring(reducedStreet, '^' || preDir || ws - || '+(.*)'); - ELSE - result.streetName := reducedStreet; - END IF; - ELSIF tempInt > 1 THEN - -- Multiple postDir candidates were found. We need to find the last - -- incident of a direction, but avoid getting the last word from - -- a two word direction. eg extracting "East" from "North East" - -- We do this by sorting by length, and taking the last direction - -- in the results that is not included in an earlier one. - -- This wont be a problem it preDir is North East and postDir is - -- East as the regex requires a space before the direction. Only - -- the East will return from the preDir. - tempInt := 0; - FOR rec IN SELECT abbrev, substring(fullStreet, '(?i)' || ws || '(' - || name || ')(?:' || ws || '|$)') AS value - FROM direction_lookup - WHERE texticregexeq(fullStreet, '(?i)' || ws || name - || '(?:' || ws || '|$)') - ORDER BY length(name) desc LOOP - tempInt := 0; - IF tempInt < position(rec.value in fullStreet) THEN - IF postDir IS NULL THEN - tempInt := position(rec.value in fullStreet); - postDir := rec.value; - result.postDirAbbrev := rec.abbrev; - ELSIF NOT texticregexeq(postDir, '(?i)' || rec.value) THEN - tempInt := position(rec.value in fullStreet); - postDir := rec.value; - result.postDirAbbrev := rec.abbrev; - END IF; - END IF; - END LOOP; - IF result.location IS NULL THEN - result.location := substring(fullStreet, '(?i)' || ws || postDir || ws - || '+(.*?)$'); - END IF; - reducedStreet := substring(fullStreet, '(?i)^(.*?)' || ws || '+' - || postDir); - SELECT INTO tempString substring(reducedStreet, '(?i)(^' || name - || ')' || ws) FROM direction_lookup WHERE - texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) - ORDER BY length(name) DESC; - IF tempString IS NOT NULL THEN - preDir := tempString; - SELECT INTO result.preDirAbbrev abbrev FROM direction_lookup WHERE - texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) - ORDER BY length(name) DESC; - result.streetName := substring(reducedStreet, '^' || preDir || ws - || '+(.*)'); - ELSE - result.streetName := reducedStreet; - END IF; - ELSE - - -- There is no street type, directional suffix or internal address - -- to allow distinction between street name and location. - IF result.location IS NULL THEN - IF debug_flag THEN - raise notice 'fullStreet: %', fullStreet; - END IF; - - result.location := location_extract(fullStreet, result.stateAbbrev); - -- If the location was found, remove it from fullStreet - IF result.location IS NOT NULL THEN - fullStreet := substring(fullStreet, '(?i)(.*),' || ws || '+' || - result.location); - END IF; - END IF; - - -- Check for a direction prefix. - SELECT INTO tempString substring(fullStreet, '(?i)(^' || name - || ')' || ws) FROM direction_lookup WHERE - texticregexeq(fullStreet, '(?i)(^' || name || ')' || ws) - ORDER BY length(name); - IF tempString IS NOT NULL THEN - preDir := tempString; - SELECT INTO result.preDirAbbrev abbrev FROM direction_lookup WHERE - texticregexeq(fullStreet, '(?i)(^' || name || ')' || ws) - ORDER BY length(name) DESC; - IF result.location IS NOT NULL THEN - -- The location may still be in the fullStreet, or may - -- have been removed already - result.streetName := substring(fullStreet, '^' || preDir || ws - || '+(.*?)(' || ws || '+' || result.location || '|$)'); - ELSE - result.streetName := substring(fullStreet, '^' || preDir || ws - || '+(.*?)' || ws || '*'); - END IF; - ELSE - IF result.location IS NOT NULL THEN - -- The location may still be in the fullStreet, or may - -- have been removed already - result.streetName := substring(fullStreet, '^(.*?)(' || ws - || '+' || result.location || '|$)'); - ELSE - result.streetName := fullStreet; - END IF; - END IF; - END IF; - END IF; - END IF; - - result.address := to_number(addressString, '99999999999'); - result.zip := zipString; - - result.parsed := TRUE; - RETURN result; -END -$_$ LANGUAGE plpgsql; +-- normalize_address(addressString) +-- This takes an address string and parses it into address (internal/street) +-- street name, type, direction prefix and suffix, location, state and +-- zip code, depending on what can be found in the string. +-- +-- The US postal address standard is used: +-- +-- +-- +-- State is assumed to be included in the string, and MUST be matchable to +-- something in the state_lookup table. Fuzzy matching is used if no direct +-- match is found. +-- +-- Two formats of zip code are acceptable: five digit, and five + 4. +-- +-- The internal addressing indicators are looked up from the +-- secondary_unit_lookup table. A following identifier is accepted +-- but it must start with a digit. +-- +-- The location is parsed from the string using other indicators, such +-- as street type, direction suffix or internal address, if available. +-- If these are not, the location is extracted using comparisons against +-- the places_lookup table, then the countysub_lookup table to determine +-- what, in the original string, is intended to be the location. In both +-- cases, an exact match is first pursued, then a word-by-word fuzzy match. +-- The result is not the name of the location from the tables, but the +-- section of the given string that corresponds to the name from the tables. +-- +-- Zip codes and street names are not validated. +-- +-- Direction indicators are extracted by comparison with the direction_lookup +-- table. +-- +-- Street addresses are assumed to be a single word, starting with a number. +-- Address is manditory; if no address is given, and the street is numbered, +-- the resulting address will be the street name, and the street name +-- will be an empty string. +-- +-- In some cases, the street type is part of the street name. +-- eg State Hwy 22a. As long as the word following the type starts with a +-- number (this is usually the case) this will be caught. Some street names +-- include a type name, and have a street type that differs. This will be +-- handled properly, so long as both are given. If the street type is +-- omitted, the street names included type will be parsed as the street type. +-- +-- The output is currently a colon seperated list of values: +-- InternalAddress:StreetAddress:DirectionPrefix:StreetName:StreetType: +-- DirectionSuffix:Location:State:ZipCode +-- This returns each element as entered. It's mainly meant for debugging. +-- There is also another option that returns: +-- StreetAddress:DirectionPrefixAbbreviation:StreetName:StreetTypeAbbreviation: +-- DirectionSuffixAbbreviation:Location:StateAbbreviation:ZipCode +-- This is more standardized and better for use with a geocoder. +CREATE OR REPLACE FUNCTION normalize_address( + in_rawInput VARCHAR +) RETURNS norm_addy +AS $_$ +DECLARE + debug_flag boolean := false; + result norm_addy; + addressString VARCHAR; + zipString VARCHAR; + preDir VARCHAR; + postDir VARCHAR; + fullStreet VARCHAR; + reducedStreet VARCHAR; + streetType VARCHAR; + state VARCHAR; + tempString VARCHAR; + tempInt INTEGER; + rec RECORD; + ws VARCHAR; + rawInput VARCHAR; +BEGIN + result.parsed := FALSE; + + rawInput := trim(in_rawInput); + + IF rawInput IS NULL THEN + RETURN result; + END IF; + + ws := E'[ ,.\t\n\f\r]'; + + IF debug_flag THEN + raise notice 'input: %', rawInput; + END IF; + + -- Assume that the address begins with a digit, and extract it from + -- the input string. + addressString := substring(rawInput from '^([0-9].*?)[ ,/.]'); + + IF debug_flag THEN + raise notice 'addressString: %', addressString; + END IF; + + -- There are two formats for zip code, the normal 5 digit, and + -- the nine digit zip-4. It may also not exist. + zipString := substring(rawInput from ws || '([0-9]{5})$'); + IF zipString IS NULL THEN + zipString := substring(rawInput from ws || '([0-9]{5})-[0-9]{4}$'); + -- Check if all we got was a zipcode, of either form + IF zipString IS NULL THEN + zipString := substring(rawInput from '^([0-9]{5})$'); + IF zipString IS NULL THEN + zipString := substring(rawInput from '^([0-9]{5})-[0-9]{4}$'); + END IF; + -- If it was only a zipcode, then just return it. + IF zipString IS NOT NULL THEN + result.zip := zipString; + result.parsed := TRUE; + RETURN result; + END IF; + END IF; + END IF; + + IF debug_flag THEN + raise notice 'zipString: %', zipString; + END IF; + + IF zipString IS NOT NULL THEN + fullStreet := substring(rawInput from '(.*)' + || ws || '+' || cull_null(zipString) || '[- ]?([0-9]{4})?$'); + ELSE + fullStreet := rawInput; + END IF; + + IF debug_flag THEN + raise notice 'fullStreet: %', fullStreet; + END IF; + + -- FIXME: state_extract should probably be returning a record so we can + -- avoid having to parse the result from it. + tempString := state_extract(fullStreet); + IF tempString IS NOT NULL THEN + state := split_part(tempString, ':', 1); + result.stateAbbrev := split_part(tempString, ':', 2); + END IF; + + IF debug_flag THEN + raise notice 'stateAbbrev: %', result.stateAbbrev; + END IF; + + -- The easiest case is if the address is comma delimited. There are some + -- likely cases: + -- street level, location, state + -- street level, location state + -- street level, location + -- street level, internal address, location, state + -- street level, internal address, location state + -- street level, internal address location state + -- street level, internal address, location + -- street level, internal address location + -- The first three are useful. + tempString := substring(fullStreet, '(?i),' || ws || '+(.*?)(,?' || ws || + '*' || cull_null(state) || '$)'); + IF tempString = '' THEN tempString := NULL; END IF; + IF tempString IS NOT NULL THEN + result.location := tempString; + IF addressString IS NOT NULL THEN + fullStreet := substring(fullStreet, '(?i)' || addressString || ws || + '+(.*),' || ws || '+' || result.location); + ELSE + fullStreet := substring(fullStreet, '(?i)(.*),' || ws || '+' || + result.location); + END IF; + END IF; + + IF debug_flag THEN + raise notice 'fullStreet: %', fullStreet; + raise notice 'location: %', result.location; + END IF; + + -- Pull out the full street information, defined as everything between the + -- address and the state. This includes the location. + -- This doesnt need to be done if location has already been found. + IF result.location IS NULL THEN + IF addressString IS NOT NULL THEN + IF state IS NOT NULL THEN + fullStreet := substring(fullStreet, '(?i)' || addressString || + ws || '+(.*?)' || ws || '+' || state); + ELSE + fullStreet := substring(fullStreet, '(?i)' || addressString || + ws || '+(.*?)'); + END IF; + ELSE + IF state IS NOT NULL THEN + fullStreet := substring(fullStreet, '(?i)(.*?)' || ws || + '+' || state); + ELSE + fullStreet := substring(fullStreet, '(?i)(.*?)'); + END IF; + END IF; + + IF debug_flag THEN + raise notice 'fullStreet: %', fullStreet; + END IF; + + result.location := location_extract(fullStreet, result.stateAbbrev); + + -- A location can't be a street type, sorry. + IF lower(result.location) IN (SELECT lower(name) FROM street_type_lookup) THEN + result.location := NULL; + END IF; + + -- If the location was found, remove it from fullStreet + IF result.location IS NOT NULL THEN + fullStreet := substring(fullStreet, '(?i)(.*)' || ws || '+' || + result.location); + END IF; + END IF; + + IF debug_flag THEN + raise notice 'fullStreet: %', fullStreet; + raise notice 'location: %', result.location; + END IF; + + -- Determine if any internal address is included, such as apartment + -- or suite number. + SELECT INTO tempInt count(*) FROM secondary_unit_lookup + WHERE texticregexeq(fullStreet, '(?i)' || ws || name || '(' + || ws || '|$)'); + IF tempInt = 1 THEN + result.internal := substring(fullStreet, '(?i)' || ws || '(' + || name || ws || '*#?' || ws + || '*(?:[0-9][-0-9a-zA-Z]*)?' || ')(?:' || ws || '|$)') + FROM secondary_unit_lookup + WHERE texticregexeq(fullStreet, '(?i)' || ws || name || '(' + || ws || '|$)'); + ELSIF tempInt > 1 THEN + -- In the event of multiple matches to a secondary unit designation, we + -- will assume that the last one is the true one. + tempInt := 0; + FOR rec in SELECT trim(substring(fullStreet, '(?i)' || ws || '(' + || name || '(?:' || ws || '*#?' || ws + || '*(?:[0-9][-0-9a-zA-Z]*)?)' || ws || '?|$)')) as value + FROM secondary_unit_lookup + WHERE texticregexeq(fullStreet, '(?i)' || ws || name || '(' + || ws || '|$)') LOOP + IF tempInt < position(rec.value in fullStreet) THEN + tempInt := position(rec.value in fullStreet); + result.internal := rec.value; + END IF; + END LOOP; + END IF; + + IF debug_flag THEN + raise notice 'internal: %', result.internal; + END IF; + + IF result.location IS NULL THEN + -- If the internal address is given, the location is everything after it. + result.location := substring(fullStreet, result.internal || ws || '+(.*)$'); + END IF; + + IF debug_flag THEN + raise notice 'location: %', result.location; + END IF; + + -- Pull potential street types from the full street information + tempInt := count(*) FROM street_type_lookup + WHERE texticregexeq(fullStreet, '(?i)' || ws || '(' || name + || ')(?:' || ws || '|$)'); + IF tempInt = 1 THEN + SELECT INTO rec abbrev, substring(fullStreet, '(?i)' || ws || '(' + || name || ')(?:' || ws || '|$)') AS given FROM street_type_lookup + WHERE texticregexeq(fullStreet, '(?i)' || ws || '(' || name + || ')(?:' || ws || '|$)'); + streetType := rec.given; + result.streetTypeAbbrev := rec.abbrev; + ELSIF tempInt > 1 THEN + tempInt := 0; + FOR rec IN SELECT abbrev, substring(fullStreet, '(?i)' || ws || '(' + || name || ')(?:' || ws || '|$)') AS given FROM street_type_lookup + WHERE texticregexeq(fullStreet, '(?i)' || ws || '(' || name + || ')(?:' || ws || '|$)') LOOP + -- If we have found an internal address, make sure the type + -- precedes it. + IF result.internal IS NOT NULL THEN + IF position(rec.given IN fullStreet) < position(result.internal IN fullStreet) THEN + IF tempInt < position(rec.given IN fullStreet) THEN + streetType := rec.given; + result.streetTypeAbbrev := rec.abbrev; + tempInt := position(rec.given IN fullStreet); + END IF; + END IF; + ELSIF tempInt < position(rec.given IN fullStreet) THEN + streetType := rec.given; + result.streetTypeAbbrev := rec.abbrev; + tempInt := position(rec.given IN fullStreet); + END IF; + END LOOP; + END IF; + + IF debug_flag THEN + raise notice 'streetTypeAbbrev: %', result.streetTypeAbbrev; + END IF; + + -- There is a little more processing required now. If the word after the + -- street type begins with a number, the street type should be considered + -- part of the name, as well as the next word. eg, State Route 225a. If + -- the next word starts with a char, then everything after the street type + -- will be considered location. If there is no street type, then I'm sad. + IF streetType IS NOT NULL THEN + tempString := substring(fullStreet, streetType || ws || + E'+([0-9][^ ,.\t\r\n\f]*?)' || ws); + IF tempString IS NOT NULL THEN + IF result.location IS NULL THEN + result.location := substring(fullStreet, streetType || ws || '+' + || tempString || ws || '+(.*)$'); + END IF; + reducedStreet := substring(fullStreet, '(.*)' || ws || '+' + || result.location || '$'); + streetType := NULL; + result.streetTypeAbbrev := NULL; + ELSE + IF result.location IS NULL THEN + result.location := substring(fullStreet, streetType || ws || '+(.*)$'); + END IF; + reducedStreet := substring(fullStreet, '^(.*)' || ws || '+' + || streetType); + END IF; + + -- The pre direction should be at the beginning of the fullStreet string. + -- The post direction should be at the beginning of the location string + -- if there is no internal address + tempString := substring(reducedStreet, '(?i)(^' || name + || ')' || ws) FROM direction_lookup WHERE + texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) + ORDER BY length(name) DESC; + IF tempString IS NOT NULL THEN + preDir := tempString; + result.preDirAbbrev := abbrev FROM direction_lookup + where texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) + ORDER BY length(name) DESC; + result.streetName := substring(reducedStreet, '^' || preDir || ws || '(.*)'); + ELSE + result.streetName := reducedStreet; + END IF; + + IF texticregexeq(result.location, '(?i)' || result.internal || '$') THEN + -- If the internal address is at the end of the location, then no + -- location was given. We still need to look for post direction. + SELECT INTO rec abbrev, + substring(result.location, '(?i)^(' || name || ')' || ws) as value + FROM direction_lookup WHERE texticregexeq(result.location, '(?i)^' + || name || ws) ORDER BY length(name) desc; + IF rec.value IS NOT NULL THEN + postDir := rec.value; + result.postDirAbbrev := rec.abbrev; + END IF; + result.location := null; + ELSIF result.internal IS NULL THEN + -- If no location is given, the location string will be the post direction + SELECT INTO tempInt count(*) FROM direction_lookup WHERE + upper(result.location) = upper(name); + IF tempInt != 0 THEN + postDir := result.location; + SELECT INTO result.postDirAbbrev abbrev FROM direction_lookup WHERE + upper(postDir) = upper(name); + result.location := NULL; + ELSE + -- postDirection is not equal location, but may be contained in it. + SELECT INTO tempString substring(result.location, '(?i)(^' || name + || ')' || ws) FROM direction_lookup WHERE + texticregexeq(result.location, '(?i)(^' || name || ')' || ws) + ORDER BY length(name) desc; + IF tempString IS NOT NULL THEN + postDir := tempString; + SELECT INTO result.postDirAbbrev abbrev FROM direction_lookup + where texticregexeq(result.location, '(?i)(^' || name || ')' || ws); + result.location := substring(result.location, '^' || postDir || ws || '+(.*)'); + END IF; + END IF; + ELSE + -- internal is not null, but is not at the end of the location string + -- look for post direction before the internal address + SELECT INTO tempString substring(fullStreet, '(?i)' || streetType + || ws || '+(' || name || ')' || ws || '+' || result.internal) + FROM direction_lookup WHERE texticregexeq(fullStreet, '(?i)' + || ws || name || ws || '+' || result.internal) ORDER BY length(name) desc; + IF tempString IS NOT NULL THEN + postDir := tempString; + SELECT INTO result.postDirAbbrev abbrev FROM direction_lookup + WHERE texticregexeq(fullStreet, '(?i)' || ws || name || ws); + END IF; + END IF; + ELSE + -- No street type was found + + -- If an internal address was given, then the split becomes easy, and the + -- street name is everything before it, without directions. + IF result.internal IS NOT NULL THEN + reducedStreet := substring(fullStreet, '(?i)^(.*?)' || ws || '+' + || result.internal); + tempInt := count(*) FROM direction_lookup WHERE + texticregexeq(reducedStreet, '(?i)' || ws || name || '$'); + IF tempInt > 0 THEN + postDir := substring(reducedStreet, '(?i)' || ws || '(' + || name || ')' || '$') FROM direction_lookup + WHERE texticregexeq(reducedStreet, '(?i)' || ws || name || '$'); + result.postDirAbbrev := abbrev FROM direction_lookup + WHERE texticregexeq(reducedStreet, '(?i)' || ws || name || '$'); + END IF; + tempString := substring(reducedStreet, '(?i)^(' || name + || ')' || ws) FROM direction_lookup WHERE + texticregexeq(reducedStreet, '(?i)^(' || name || ')' || ws) + ORDER BY length(name) DESC; + IF tempString IS NOT NULL THEN + preDir := tempString; + result.preDirAbbrev := abbrev FROM direction_lookup WHERE + texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) + ORDER BY length(name) DESC; + result.streetName := substring(reducedStreet, '(?i)^' || preDir || ws + || '+(.*?)(?:' || ws || '+' || cull_null(postDir) || '|$)'); + ELSE + result.streetName := substring(reducedStreet, '(?i)^(.*?)(?:' || ws + || '+' || cull_null(postDir) || '|$)'); + END IF; + ELSE + + -- If a post direction is given, then the location is everything after, + -- the street name is everything before, less any pre direction. + tempInt := count(*) FROM direction_lookup + WHERE texticregexeq(fullStreet, '(?i)' || ws || name || '(?:' + || ws || '|$)'); + + IF tempInt = 1 THEN + -- A single postDir candidate was found. This makes it easier. + postDir := substring(fullStreet, '(?i)' || ws || '(' + || name || ')(?:' || ws || '|$)') FROM direction_lookup WHERE + texticregexeq(fullStreet, '(?i)' || ws || name || '(?:' + || ws || '|$)'); + result.postDirAbbrev := abbrev FROM direction_lookup + WHERE texticregexeq(fullStreet, '(?i)' || ws || name + || '(?:' || ws || '|$)'); + IF result.location IS NULL THEN + result.location := substring(fullStreet, '(?i)' || ws || postDir + || ws || '+(.*?)$'); + END IF; + reducedStreet := substring(fullStreet, '^(.*?)' || ws || '+' + || postDir); + tempString := substring(reducedStreet, '(?i)(^' || name + || ')' || ws) FROM direction_lookup WHERE + texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) + ORDER BY length(name) DESC; + IF tempString IS NOT NULL THEN + preDir := tempString; + result.preDirAbbrev := abbrev FROM direction_lookup WHERE + texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) + ORDER BY length(name) DESC; + result.streetName := substring(reducedStreet, '^' || preDir || ws + || '+(.*)'); + ELSE + result.streetName := reducedStreet; + END IF; + ELSIF tempInt > 1 THEN + -- Multiple postDir candidates were found. We need to find the last + -- incident of a direction, but avoid getting the last word from + -- a two word direction. eg extracting "East" from "North East" + -- We do this by sorting by length, and taking the last direction + -- in the results that is not included in an earlier one. + -- This wont be a problem it preDir is North East and postDir is + -- East as the regex requires a space before the direction. Only + -- the East will return from the preDir. + tempInt := 0; + FOR rec IN SELECT abbrev, substring(fullStreet, '(?i)' || ws || '(' + || name || ')(?:' || ws || '|$)') AS value + FROM direction_lookup + WHERE texticregexeq(fullStreet, '(?i)' || ws || name + || '(?:' || ws || '|$)') + ORDER BY length(name) desc LOOP + tempInt := 0; + IF tempInt < position(rec.value in fullStreet) THEN + IF postDir IS NULL THEN + tempInt := position(rec.value in fullStreet); + postDir := rec.value; + result.postDirAbbrev := rec.abbrev; + ELSIF NOT texticregexeq(postDir, '(?i)' || rec.value) THEN + tempInt := position(rec.value in fullStreet); + postDir := rec.value; + result.postDirAbbrev := rec.abbrev; + END IF; + END IF; + END LOOP; + IF result.location IS NULL THEN + result.location := substring(fullStreet, '(?i)' || ws || postDir || ws + || '+(.*?)$'); + END IF; + reducedStreet := substring(fullStreet, '(?i)^(.*?)' || ws || '+' + || postDir); + SELECT INTO tempString substring(reducedStreet, '(?i)(^' || name + || ')' || ws) FROM direction_lookup WHERE + texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) + ORDER BY length(name) DESC; + IF tempString IS NOT NULL THEN + preDir := tempString; + SELECT INTO result.preDirAbbrev abbrev FROM direction_lookup WHERE + texticregexeq(reducedStreet, '(?i)(^' || name || ')' || ws) + ORDER BY length(name) DESC; + result.streetName := substring(reducedStreet, '^' || preDir || ws + || '+(.*)'); + ELSE + result.streetName := reducedStreet; + END IF; + ELSE + + -- There is no street type, directional suffix or internal address + -- to allow distinction between street name and location. + IF result.location IS NULL THEN + IF debug_flag THEN + raise notice 'fullStreet: %', fullStreet; + END IF; + + result.location := location_extract(fullStreet, result.stateAbbrev); + -- If the location was found, remove it from fullStreet + IF result.location IS NOT NULL THEN + fullStreet := substring(fullStreet, '(?i)(.*),' || ws || '+' || + result.location); + END IF; + END IF; + + -- Check for a direction prefix. + SELECT INTO tempString substring(fullStreet, '(?i)(^' || name + || ')' || ws) FROM direction_lookup WHERE + texticregexeq(fullStreet, '(?i)(^' || name || ')' || ws) + ORDER BY length(name); + IF tempString IS NOT NULL THEN + preDir := tempString; + SELECT INTO result.preDirAbbrev abbrev FROM direction_lookup WHERE + texticregexeq(fullStreet, '(?i)(^' || name || ')' || ws) + ORDER BY length(name) DESC; + IF result.location IS NOT NULL THEN + -- The location may still be in the fullStreet, or may + -- have been removed already + result.streetName := substring(fullStreet, '^' || preDir || ws + || '+(.*?)(' || ws || '+' || result.location || '|$)'); + ELSE + result.streetName := substring(fullStreet, '^' || preDir || ws + || '+(.*?)' || ws || '*'); + END IF; + ELSE + IF result.location IS NOT NULL THEN + -- The location may still be in the fullStreet, or may + -- have been removed already + result.streetName := substring(fullStreet, '^(.*?)(' || ws + || '+' || result.location || '|$)'); + ELSE + result.streetName := fullStreet; + END IF; + END IF; + END IF; + END IF; + END IF; + + result.address := to_number(addressString, '99999999999'); + result.zip := zipString; + + result.parsed := TRUE; + RETURN result; +END +$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2010/normalize/pprint_addy.sql b/extras/tiger_geocoder/tiger_2010/normalize/pprint_addy.sql index 51742d95f..beb216fd6 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/pprint_addy.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/pprint_addy.sql @@ -1,36 +1,36 @@ -CREATE OR REPLACE FUNCTION pprint_addy( - input NORM_ADDY -) RETURNS VARCHAR -AS $_$ -DECLARE - result VARCHAR; -BEGIN - IF NOT input.parsed THEN - RETURN NULL; - END IF; - - result := cull_null(input.address::text) - || CASE WHEN input.preDirAbbrev IS NOT NULL THEN ' ' ELSE '' END - || cull_null(input.preDirAbbrev) - || CASE WHEN input.streetName IS NOT NULL THEN ' ' ELSE '' END - || cull_null(input.streetName) - || CASE WHEN input.streetTypeAbbrev IS NOT NULL THEN ' ' ELSE '' END - || cull_null(input.streetTypeAbbrev) - || CASE WHEN input.postDirAbbrev IS NOT NULL THEN ' ' ELSE '' END - || cull_null(input.postDirAbbrev) - || CASE WHEN - input.address IS NOT NULL OR - input.streetName IS NOT NULL - THEN ', ' ELSE '' END - || cull_null(input.internal) - || CASE WHEN input.internal IS NOT NULL THEN ', ' ELSE '' END - || cull_null(input.location) - || CASE WHEN input.location IS NOT NULL THEN ', ' ELSE '' END - || cull_null(input.stateAbbrev) - || CASE WHEN input.stateAbbrev IS NOT NULL THEN ' ' ELSE '' END - || cull_null(input.zip); - - RETURN trim(result); - -END; -$_$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION pprint_addy( + input NORM_ADDY +) RETURNS VARCHAR +AS $_$ +DECLARE + result VARCHAR; +BEGIN + IF NOT input.parsed THEN + RETURN NULL; + END IF; + + result := cull_null(input.address::text) + || CASE WHEN input.preDirAbbrev IS NOT NULL THEN ' ' ELSE '' END + || cull_null(input.preDirAbbrev) + || CASE WHEN input.streetName IS NOT NULL THEN ' ' ELSE '' END + || cull_null(input.streetName) + || CASE WHEN input.streetTypeAbbrev IS NOT NULL THEN ' ' ELSE '' END + || cull_null(input.streetTypeAbbrev) + || CASE WHEN input.postDirAbbrev IS NOT NULL THEN ' ' ELSE '' END + || cull_null(input.postDirAbbrev) + || CASE WHEN + input.address IS NOT NULL OR + input.streetName IS NOT NULL + THEN ', ' ELSE '' END + || cull_null(input.internal) + || CASE WHEN input.internal IS NOT NULL THEN ', ' ELSE '' END + || cull_null(input.location) + || CASE WHEN input.location IS NOT NULL THEN ', ' ELSE '' END + || cull_null(input.stateAbbrev) + || CASE WHEN input.stateAbbrev IS NOT NULL THEN ' ' ELSE '' END + || cull_null(input.zip); + + RETURN trim(result); + +END; +$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2010/normalize/state_extract.sql b/extras/tiger_geocoder/tiger_2010/normalize/state_extract.sql index 0a481a35d..193df2bd3 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/state_extract.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/state_extract.sql @@ -1,89 +1,89 @@ --- state_extract(addressStringLessZipCode) --- Extracts the state from end of the given string. --- --- This function uses the state_lookup table to determine which state --- the input string is indicating. First, an exact match is pursued, --- and in the event of failure, a word-by-word fuzzy match is attempted. --- --- The result is the state as given in the input string, and the approved --- state abbreviation, seperated by a colon. -CREATE OR REPLACE FUNCTION state_extract(rawInput VARCHAR) RETURNS VARCHAR -AS $_$ -DECLARE - tempInt INTEGER; - tempString VARCHAR; - state VARCHAR; - stateAbbrev VARCHAR; - result VARCHAR; - rec RECORD; - test BOOLEAN; - ws VARCHAR; - var_verbose boolean := false; -BEGIN - ws := E'[ ,.\t\n\f\r]'; - - -- If there is a trailing space or , get rid of it - -- this is to handle case where people use , instead of space to separate state and zip - -- such as '2450 N COLORADO ST, PHILADELPHIA, PA, 19132' instead of '2450 N COLORADO ST, PHILADELPHIA, PA 19132' - - tempString := regexp_replace(rawInput, E'(.*)' || ws || '+', E'\\1'); - -- Separate out the last word of the state, and use it to compare to - -- the state lookup table to determine the entire name, as well as the - -- abbreviation associated with it. The zip code may or may not have - -- been found. - tempString := substring(tempString from ws || E'+([^ ,.\t\n\f\r0-9]*?)$'); - IF var_verbose THEN RAISE NOTICE 'state_extract rawInput: % tempString: %', rawInput, tempString; END IF; - SELECT INTO tempInt count(*) FROM (select distinct abbrev from state_lookup - WHERE upper(abbrev) = upper(tempString)) as blah; - IF tempInt = 1 THEN - state := tempString; - SELECT INTO stateAbbrev abbrev FROM (select distinct abbrev from - state_lookup WHERE upper(abbrev) = upper(tempString)) as blah; - ELSE - SELECT INTO tempInt count(*) FROM state_lookup WHERE upper(name) - like upper('%' || tempString); - IF tempInt >= 1 THEN - FOR rec IN SELECT name from state_lookup WHERE upper(name) - like upper('%' || tempString) LOOP - SELECT INTO test texticregexeq(rawInput, name) FROM state_lookup - WHERE rec.name = name; - IF test THEN - SELECT INTO stateAbbrev abbrev FROM state_lookup - WHERE rec.name = name; - state := substring(rawInput, '(?i)' || rec.name); - EXIT; - END IF; - END LOOP; - ELSE - -- No direct match for state, so perform fuzzy match. - SELECT INTO tempInt count(*) FROM state_lookup - WHERE soundex(tempString) = end_soundex(name); - IF tempInt >= 1 THEN - FOR rec IN SELECT name, abbrev FROM state_lookup - WHERE soundex(tempString) = end_soundex(name) LOOP - tempInt := count_words(rec.name); - tempString := get_last_words(rawInput, tempInt); - test := TRUE; - FOR i IN 1..tempInt LOOP - IF soundex(split_part(tempString, ' ', i)) != - soundex(split_part(rec.name, ' ', i)) THEN - test := FALSE; - END IF; - END LOOP; - IF test THEN - state := tempString; - stateAbbrev := rec.abbrev; - EXIT; - END IF; - END LOOP; - END IF; - END IF; - END IF; - - IF state IS NOT NULL AND stateAbbrev IS NOT NULL THEN - result := state || ':' || stateAbbrev; - END IF; - - RETURN result; -END; -$_$ LANGUAGE plpgsql; +-- state_extract(addressStringLessZipCode) +-- Extracts the state from end of the given string. +-- +-- This function uses the state_lookup table to determine which state +-- the input string is indicating. First, an exact match is pursued, +-- and in the event of failure, a word-by-word fuzzy match is attempted. +-- +-- The result is the state as given in the input string, and the approved +-- state abbreviation, seperated by a colon. +CREATE OR REPLACE FUNCTION state_extract(rawInput VARCHAR) RETURNS VARCHAR +AS $_$ +DECLARE + tempInt INTEGER; + tempString VARCHAR; + state VARCHAR; + stateAbbrev VARCHAR; + result VARCHAR; + rec RECORD; + test BOOLEAN; + ws VARCHAR; + var_verbose boolean := false; +BEGIN + ws := E'[ ,.\t\n\f\r]'; + + -- If there is a trailing space or , get rid of it + -- this is to handle case where people use , instead of space to separate state and zip + -- such as '2450 N COLORADO ST, PHILADELPHIA, PA, 19132' instead of '2450 N COLORADO ST, PHILADELPHIA, PA 19132' + + tempString := regexp_replace(rawInput, E'(.*)' || ws || '+', E'\\1'); + -- Separate out the last word of the state, and use it to compare to + -- the state lookup table to determine the entire name, as well as the + -- abbreviation associated with it. The zip code may or may not have + -- been found. + tempString := substring(tempString from ws || E'+([^ ,.\t\n\f\r0-9]*?)$'); + IF var_verbose THEN RAISE NOTICE 'state_extract rawInput: % tempString: %', rawInput, tempString; END IF; + SELECT INTO tempInt count(*) FROM (select distinct abbrev from state_lookup + WHERE upper(abbrev) = upper(tempString)) as blah; + IF tempInt = 1 THEN + state := tempString; + SELECT INTO stateAbbrev abbrev FROM (select distinct abbrev from + state_lookup WHERE upper(abbrev) = upper(tempString)) as blah; + ELSE + SELECT INTO tempInt count(*) FROM state_lookup WHERE upper(name) + like upper('%' || tempString); + IF tempInt >= 1 THEN + FOR rec IN SELECT name from state_lookup WHERE upper(name) + like upper('%' || tempString) LOOP + SELECT INTO test texticregexeq(rawInput, name) FROM state_lookup + WHERE rec.name = name; + IF test THEN + SELECT INTO stateAbbrev abbrev FROM state_lookup + WHERE rec.name = name; + state := substring(rawInput, '(?i)' || rec.name); + EXIT; + END IF; + END LOOP; + ELSE + -- No direct match for state, so perform fuzzy match. + SELECT INTO tempInt count(*) FROM state_lookup + WHERE soundex(tempString) = end_soundex(name); + IF tempInt >= 1 THEN + FOR rec IN SELECT name, abbrev FROM state_lookup + WHERE soundex(tempString) = end_soundex(name) LOOP + tempInt := count_words(rec.name); + tempString := get_last_words(rawInput, tempInt); + test := TRUE; + FOR i IN 1..tempInt LOOP + IF soundex(split_part(tempString, ' ', i)) != + soundex(split_part(rec.name, ' ', i)) THEN + test := FALSE; + END IF; + END LOOP; + IF test THEN + state := tempString; + stateAbbrev := rec.abbrev; + EXIT; + END IF; + END LOOP; + END IF; + END IF; + END IF; + + IF state IS NOT NULL AND stateAbbrev IS NOT NULL THEN + result := state || ':' || stateAbbrev; + END IF; + + RETURN result; +END; +$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2010/tables/lookup_tables_2010.sql b/extras/tiger_geocoder/tiger_2010/tables/lookup_tables_2010.sql index fed3ff58a..a47db7fcb 100644 --- a/extras/tiger_geocoder/tiger_2010/tables/lookup_tables_2010.sql +++ b/extras/tiger_geocoder/tiger_2010/tables/lookup_tables_2010.sql @@ -1,1224 +1,1224 @@ ---$Id$ -SET search_path TO tiger, public; --- Create direction lookup table -DROP TABLE IF EXISTS direction_lookup; -CREATE TABLE direction_lookup (name VARCHAR(20) PRIMARY KEY, abbrev VARCHAR(3)); -INSERT INTO direction_lookup (name, abbrev) VALUES('WEST', 'W'); -INSERT INTO direction_lookup (name, abbrev) VALUES('W', 'W'); -INSERT INTO direction_lookup (name, abbrev) VALUES('SW', 'SW'); -INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH-WEST', 'SW'); -INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTHWEST', 'SW'); -INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH-EAST', 'SE'); -INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTHEAST', 'SE'); -INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH_WEST', 'SW'); -INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH_EAST', 'SE'); -INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH', 'S'); -INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH WEST', 'SW'); -INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH EAST', 'SE'); -INSERT INTO direction_lookup (name, abbrev) VALUES('SE', 'SE'); -INSERT INTO direction_lookup (name, abbrev) VALUES('S', 'S'); -INSERT INTO direction_lookup (name, abbrev) VALUES('NW', 'NW'); -INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH-WEST', 'NW'); -INSERT INTO direction_lookup (name, abbrev) VALUES('NORTHWEST', 'NW'); -INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH-EAST', 'NE'); -INSERT INTO direction_lookup (name, abbrev) VALUES('NORTHEAST', 'NE'); -INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH_WEST', 'NW'); -INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH_EAST', 'NE'); -INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH', 'N'); -INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH WEST', 'NW'); -INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH EAST', 'NE'); -INSERT INTO direction_lookup (name, abbrev) VALUES('NE', 'NE'); -INSERT INTO direction_lookup (name, abbrev) VALUES('N', 'N'); -INSERT INTO direction_lookup (name, abbrev) VALUES('EAST', 'E'); -INSERT INTO direction_lookup (name, abbrev) VALUES('E', 'E'); -CREATE INDEX direction_lookup_abbrev_idx ON direction_lookup (abbrev); - - - --- Create secondary unit lookup table -DROP TABLE IF EXISTS secondary_unit_lookup; -CREATE TABLE secondary_unit_lookup (name VARCHAR(20) PRIMARY KEY, abbrev VARCHAR(5)); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('APARTMENT', 'APT'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('APT', 'APT'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BASEMENT', 'BSMT'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BSMT', 'BSMT'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BUILDING', 'BLDG'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BLDG', 'BLDG'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('DEPARTMENT', 'DEPT'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('DEPT', 'DEPT'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FLOOR', 'FL'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FL', 'FL'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FRONT', 'FRNT'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FRNT', 'FRNT'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('HANGAR', 'HNGR'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('HNGR', 'HNGR'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOBBY', 'LBBY'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LBBY', 'LBBY'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOT', 'LOT'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOWER', 'LOWR'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOWR', 'LOWR'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('OFFICE', 'OFC'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('OFC', 'OFC'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('PENTHOUSE', 'PH'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('PH', 'PH'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('PIER', 'PIER'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('REAR', 'REAR'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('ROOM', 'RM'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('RM', 'RM'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SIDE', 'SIDE'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SLIP', 'SLIP'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SPACE', 'SPC'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SPC', 'SPC'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('STOP', 'STOP'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SUITE', 'STE'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('STE', 'STE'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('TRAILER', 'TRLR'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('TRLR', 'TRLR'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('UNIT', 'UNIT'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('UPPER', 'UPPR'); -INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('UPPR', 'UPPR'); -CREATE INDEX secondary_unit_lookup_abbrev_idx ON secondary_unit_lookup (abbrev); - - - --- Create state lookup table -DROP TABLE IF EXISTS state_lookup; -CREATE TABLE state_lookup (st_code INTEGER PRIMARY KEY, name VARCHAR(40) UNIQUE, abbrev VARCHAR(3) UNIQUE); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Alabama', 'AL', '01'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Alaska', 'AK', '02'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('American Samoa', 'AS', -1); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Arizona', 'AZ', '04'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Arkansas', 'AR', '05'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('California', 'CA', '06'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Colorado', 'CO', '08'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Connecticut', 'CT', '09'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Delaware', 'DE', '10'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('District of Columbia', 'DC', '11'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Federated States of Micronesia', 'FM', -2); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Florida', 'FL', '12'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Georgia', 'GA', '13'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Guam', 'GU', -7); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Hawaii', 'HI', '15'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Idaho', 'ID', '16'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Illinois', 'IL', '17'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Indiana', 'IN', '18'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Iowa', 'IA', '19'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Kansas', 'KS', '20'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Kentucky', 'KY', '21'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Louisiana', 'LA', '22'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Maine', 'ME', '23'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Marshall Islands', 'MH', -3); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Maryland', 'MD', '24'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Massachusetts', 'MA', '25'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Michigan', 'MI', '26'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Minnesota', 'MN', '27'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Mississippi', 'MS', '28'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Missouri', 'MO', '29'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Montana', 'MT', '30'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Nebraska', 'NE', '31'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Nevada', 'NV', '32'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New Hampshire', 'NH', '33'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New Jersey', 'NJ', '34'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New Mexico', 'NM', '35'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New York', 'NY', '36'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('North Carolina', 'NC', '37'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('North Dakota', 'ND', '38'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Northern Mariana Islands', 'MP', -4); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Ohio', 'OH', '39'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Oklahoma', 'OK', '40'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Oregon', 'OR', '41'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Palau', 'PW', -5); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Pennsylvania', 'PA', '42'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Puerto Rico', 'PR', '72'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Rhode Island', 'RI', '44'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('South Carolina', 'SC', '45'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('South Dakota', 'SD', '46'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Tennessee', 'TN', '47'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Texas', 'TX', '48'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Utah', 'UT', '49'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Vermont', 'VT', '50'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Virgin Islands', 'VI', -6); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Virginia', 'VA', '51'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Washington', 'WA', '53'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('West Virginia', 'WV', '54'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Wisconsin', 'WI', '55'); -INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Wyoming', 'WY', '56'); - - --- Create street type lookup table -DROP TABLE IF EXISTS street_type_lookup; -CREATE TABLE street_type_lookup (name VARCHAR(20) PRIMARY KEY, abbrev VARCHAR(4)); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALLEE', 'Aly'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALLEY', 'Aly'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALLY', 'Aly'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALY', 'Aly'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANEX', 'Anx'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANNEX', 'Anx'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANNX', 'Anx'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANX', 'Anx'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ARC', 'Arc'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ARCADE', 'Arc'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('AV', 'Ave'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVE', 'Ave'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVEN', 'Ave'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVENU', 'Ave'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVENUE', 'Ave'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVN', 'Ave'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVNUE', 'Ave'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BAYOO', 'Byu'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BAYOU', 'Byu'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BCH', 'Bch'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BEACH', 'Bch'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BEND', 'Bnd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BND', 'Bnd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLF', 'Blf'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLUF', 'Blf'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLUFF', 'Blf'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLUFFS', 'Blfs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOT', 'Btm'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOTTM', 'Btm'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOTTOM', 'Btm'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BTM', 'Btm'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLVD', 'Blvd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOUL', 'Blvd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOULEVARD', 'Blvd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOULV', 'Blvd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BR', 'Br'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRANCH', 'Br'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRNCH', 'Br'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRDGE', 'Brg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRG', 'Brg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRIDGE', 'Brg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRK', 'Brk'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BROOK', 'Brk'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BROOKS', 'Brks'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BURG', 'Bg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BURGS', 'Bgs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYP', 'Byp'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPA', 'Byp'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPAS', 'Byp'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPASS', 'ByP'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPS', 'Byp'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAMP', 'Cp'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CMP', 'Cp'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CP', 'Cp'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CANYN', 'Cyn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CANYON', 'Cyn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CNYN', 'Cyn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CYN', 'Cyn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAPE', 'Cpe'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CPE', 'Cpe'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAUSEWAY', 'Cswy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAUSWAY', 'Cswy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CSWY', 'Cswy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CEN', 'Ctr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENT', 'Ctr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTER', 'Ctr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTR', 'Ctr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTRE', 'Ctr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CNTER', 'Ctr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CNTR', 'Ctr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CTR', 'Ctr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTERS', 'Ctrs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIR', 'Cir'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRC', 'Cir'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRCL', 'Cir'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRCLE', 'Cir'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRCL', 'Cir'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRCLE', 'Cir'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRCLES', 'Cirs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLF', 'Clf'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLIFF', 'Clf'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLFS', 'Clfs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLIFFS', 'Clfs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLB', 'Clb'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLUB', 'Clb'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('COMMON', 'Cmn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('COR', 'Cor'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CORNER', 'Cor'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CORNERS', 'Cors'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CORS', 'Cors'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('COURSE', 'Crse'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSE', 'Crse'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('COURT', 'Ct'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRT', 'Ct'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CT', 'Ct'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('COURTS', 'Cts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('COVE', 'Cv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CV', 'Cv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('COVES', 'Cvs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CK', 'Crk'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CR', 'Crk'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CREEK', 'Crk'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRK', 'Crk'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRECENT', 'Cres'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRES', 'Cres'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRESCENT', 'Cres'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRESENT', 'Cres'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSCNT', 'Cres'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSENT', 'Cres'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSNT', 'Cres'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CREST', 'Crst'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CROSSING', 'Xing'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSSING', 'Xing'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSSNG', 'Xing'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('XING', 'Xing'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CROSSROAD', 'Xrd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CURVE', 'Curv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DALE', 'Dl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DL', 'Dl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DAM', 'Dm'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DM', 'Dm'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DIV', 'Dv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DIVIDE', 'Dv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DV', 'Dv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DVD', 'Dv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DR', 'Dr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRIV', 'Dr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRIVE', 'Dr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRV', 'Dr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRIVES', 'Drs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EST', 'Est'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ESTATE', 'Est'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ESTATES', 'Ests'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ESTS', 'Ests'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXP', 'Expy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPR', 'Expy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPRESS', 'Expy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPRESSWAY', 'Expy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPW', 'Expy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPY', 'Expy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXT', 'Ext'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTENSION', 'Ext'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTN', 'Ext'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTNSN', 'Ext'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTENSIONS', 'Exts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTS', 'Exts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FALL', 'Fall'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FALLS', 'Fls'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLS', 'Fls'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FERRY', 'Fry'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRRY', 'Fry'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRY', 'Fry'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FIELD', 'Fld'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLD', 'Fld'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FIELDS', 'Flds'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLDS', 'Flds'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLAT', 'Flt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLT', 'Flt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLATS', 'Flts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLTS', 'Flts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORD', 'Frd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRD', 'Frd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORDS', 'Frds'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FOREST', 'Frst'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORESTS', 'Frst'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRST', 'Frst'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORG', 'Frg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORGE', 'Frg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRG', 'Frg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORGES', 'Frgs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORK', 'Frk'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRK', 'Frk'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORKS', 'Frks'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRKS', 'Frks'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORT', 'Ft'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRT', 'Ft'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FT', 'Ft'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FREEWAY', 'Fwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FREEWY', 'Fwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRWAY', 'Fwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRWY', 'Fwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FWY', 'Fwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GARDEN', 'Gdn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GARDN', 'Gdn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GDN', 'Gdn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRDEN', 'Gdn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRDN', 'Gdn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GARDENS', 'Gdns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GDNS', 'Gdns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRDNS', 'Gdns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GATEWAY', 'Gtwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GATEWY', 'Gtwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GATWAY', 'Gtwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GTWAY', 'Gtwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GTWY', 'Gtwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLEN', 'Gln'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLN', 'Gln'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLENS', 'Glns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GREEN', 'Grn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRN', 'Grn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GREENS', 'Grns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GROV', 'Grv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GROVE', 'Grv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRV', 'Grv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GROVES', 'Grvs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARB', 'Hbr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARBOR', 'Hbr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARBR', 'Hbr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HBR', 'Hbr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HRBOR', 'Hbr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARBORS', 'Hbrs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HAVEN', 'Hvn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HAVN', 'Hvn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HVN', 'Hvn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HEIGHT', 'Hts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HEIGHTS', 'Hts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HGTS', 'Hts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HT', 'Hts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HTS', 'Hts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HIGHWAY', 'Hwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HIGHWY', 'Hwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HIWAY', 'Hwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HIWY', 'Hwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HWAY', 'Hwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HWY', 'Hwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HILL', 'Hl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HL', 'Hl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HILLS', 'Hls'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HLS', 'Hls'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HLLW', 'Holw'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLLOW', 'Holw'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLLOWS', 'Holw'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLW', 'Holw'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLWS', 'Holw'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('INLET', 'Inlt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('INLT', 'Inlt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('IS', 'Is'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLAND', 'Is'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLND', 'Is'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLANDS', 'Iss'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLNDS', 'Iss'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISS', 'Iss'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLE', 'Isle'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLES', 'Isle'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCT', 'Jct'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTION', 'Jct'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTN', 'Jct'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTION', 'Jct'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTN', 'Jct'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTON', 'Jct'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTNS', 'Jcts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTS', 'Jcts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTIONS', 'Jcts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('KEY', 'Ky'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('KY', 'Ky'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('KEYS', 'Kys'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('KYS', 'Kys'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNL', 'Knl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNOL', 'Knl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNOLL', 'Knl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNLS', 'Knls'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNOLLS', 'Knls'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LAKE', 'Lk'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LK', 'Lk'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LAKES', 'Lks'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LKS', 'Lks'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LAND', 'Land'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LANDING', 'Lndg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LNDG', 'Lndg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LNDNG', 'Lndg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LA', 'Ln'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LANE', 'Ln'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LANES', 'Ln'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LN', 'Ln'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LGT', 'Lgt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LIGHT', 'Lgt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LIGHTS', 'Lgts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LF', 'Lf'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOAF', 'Lf'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LCK', 'Lck'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOCK', 'Lck'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LCKS', 'Lcks'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOCKS', 'Lcks'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LDG', 'Ldg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LDGE', 'Ldg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LODG', 'Ldg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LODGE', 'Ldg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOOP', 'Loop'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOOPS', 'Loop'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MALL', 'Mall'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MANOR', 'Mnr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNR', 'Mnr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MANORS', 'Mnrs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNRS', 'Mnrs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MDW', 'Mdw'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEADOW', 'Mdw'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MDWS', 'Mdws'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEADOWS', 'Mdws'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEDOWS', 'Mdws'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEWS', 'Mews'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MILL', 'Ml'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ML', 'Ml'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MILLS', 'Mls'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MLS', 'Mls'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MISSION', 'Msn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MISSN', 'Msn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MSN', 'Msn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MSSN', 'Msn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOTORWAY', 'Mtwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNT', 'Mt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNT', 'Mt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MT', 'Mt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNTAIN', 'Mtn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNTN', 'Mtn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNTAIN', 'Mtn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNTIN', 'Mtn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTIN', 'Mtn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTN', 'Mtn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNTNS', 'Mtns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNTAINS', 'Mtns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('NCK', 'Nck'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('NECK', 'Nck'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ORCH', 'Orch'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ORCHARD', 'Orch'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ORCHRD', 'Orch'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('OVAL', 'Oval'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('OVL', 'Oval'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('OVERPASS', 'Opas'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARK', 'Park'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PK', 'Park'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRK', 'Park'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKS', 'Park'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKWAY', 'Pkwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKWY', 'Pkwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKWAY', 'Pkwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKWY', 'Pkwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKY', 'Pkwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKWAYS', 'Pkwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKWYS', 'Pkwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PASS', 'Pass'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PASSAGE', 'Psge'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PATH', 'Path'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PATHS', 'Path'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PIKE', 'Pike'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PIKES', 'Pike'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PINE', 'Pne'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PINES', 'Pnes'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PNES', 'Pnes'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PL', 'Pl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLACE', 'Pl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAIN', 'Pln'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLN', 'Pln'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAINES', 'Plns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAINS', 'Plns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLNS', 'Plns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAZA', 'Plz'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLZ', 'Plz'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLZA', 'Plz'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('POINT', 'Pt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PT', 'Pt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('POINTS', 'Pts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PTS', 'Pts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PORT', 'Prt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRT', 'Prt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PORTS', 'Prts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRTS', 'Prts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PR', 'Pr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRAIRIE', 'Pr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRARIE', 'Pr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRR', 'Pr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAD', 'Radl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RADIAL', 'Radl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RADIEL', 'Radl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RADL', 'Radl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAMP', 'Ramp'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RANCH', 'Rnch'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RANCHES', 'Rnch'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RNCH', 'Rnch'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RNCHS', 'Rnch'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAPID', 'Rpd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RPD', 'Rpd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAPIDS', 'Rpds'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RPDS', 'Rpds'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('REST', 'Rst'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RST', 'Rst'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDG', 'Rdg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDGE', 'Rdg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIDGE', 'Rdg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDGS', 'Rdgs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIDGES', 'Rdgs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIV', 'Riv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIVER', 'Riv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIVR', 'Riv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RVR', 'Riv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RD', 'Rd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROAD', 'Rd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDS', 'Rds'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROADS', 'Rds'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROUTE', 'Rte'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROW', 'Row'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RUE', 'Rue'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RUN', 'Run'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHL', 'Shl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOAL', 'Shl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHLS', 'Shls'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOALS', 'Shls'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOAR', 'Shr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHORE', 'Shr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHR', 'Shr'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOARS', 'Shrs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHORES', 'Shrs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHRS', 'Shrs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SKYWAY', 'Skwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPG', 'Spg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPNG', 'Spg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRING', 'Spg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRNG', 'Spg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPGS', 'Spgs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPNGS', 'Spgs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRINGS', 'Spgs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRNGS', 'Spgs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPUR', 'Spur'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPURS', 'Spur'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQ', 'Sq'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQR', 'Sq'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQRE', 'Sq'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQU', 'Sq'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQUARE', 'Sq'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQRS', 'Sqs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQUARES', 'Sqs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STA', 'Sta'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STATION', 'Sta'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STATN', 'Sta'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STN', 'Sta'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRA', 'Stra'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAV', 'Stra'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVE', 'Stra'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVEN', 'Stra'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVENUE', 'Stra'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVN', 'Stra'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRVN', 'Stra'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRVNUE', 'Stra'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREAM', 'Strm'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREME', 'Strm'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRM', 'Strm'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('ST', 'St'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STR', 'St'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREET', 'St'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRT', 'St'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREETS', 'Sts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SMT', 'Smt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SUMIT', 'Smt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SUMITT', 'Smt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SUMMIT', 'Smt'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TER', 'Ter'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TERR', 'Ter'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TERRACE', 'Ter'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('THROUGHWAY', 'Trwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACE', 'Trce'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACES', 'Trce'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRCE', 'Trce'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACK', 'Trak'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACKS', 'Trak'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAK', 'Trak'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRK', 'Trak'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRKS', 'Trak'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAFFICWAY', 'Trfy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRFY', 'Trfy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TR', 'Trl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAIL', 'Trl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAILS', 'Trl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRL', 'Trl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRLS', 'Trl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNEL', 'Tunl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNL', 'Tunl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNLS', 'Tunl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNNEL', 'Tunl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNNELS', 'Tunl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNNL', 'Tunl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TPK', 'Tpke'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TPKE', 'Tpke'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRNPK', 'Tpke'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRPK', 'Tpke'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TURNPIKE', 'Tpke'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TURNPK', 'Tpke'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNDERPASS', 'Upas'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('UN', 'Un'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNION', 'Un'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNIONS', 'Uns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VALLEY', 'Vly'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VALLY', 'Vly'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLLY', 'Vly'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLY', 'Vly'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VALLEYS', 'Vlys'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLYS', 'Vlys'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VDCT', 'Via'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIA', 'Via'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIADCT', 'Via'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIADUCT', 'Via'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIEW', 'Vw'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VW', 'Vw'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIEWS', 'Vws'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VWS', 'Vws'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILL', 'Vlg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLAG', 'Vlg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLAGE', 'Vlg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLG', 'Vlg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLIAGE', 'Vlg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLG', 'Vlg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLAGES', 'Vlgs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLGS', 'Vlgs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLE', 'Vl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VL', 'Vl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIS', 'Vis'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIST', 'Vis'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VISTA', 'Vis'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VST', 'Vis'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('VSTA', 'Vis'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('WALK', 'Walk'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('WALKS', 'Walk'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('WALL', 'Wall'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('WAY', 'Way'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('WY', 'Way'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('WAYS', 'Ways'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('WELL', 'Wl'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('WELLS', 'Wls'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('WLS', 'Wls'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYU', 'Byu'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLFS', 'Blfs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRKS', 'Brks'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BG', 'Bg'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('BGS', 'Bgs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CTRS', 'Ctrs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRS', 'Cirs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CMN', 'Cmn'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CTS', 'Cts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CVS', 'Cvs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRST', 'Crst'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('XRD', 'Xrd'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('CURV', 'Curv'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRS', 'Drs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRDS', 'Frds'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRGS', 'Frgs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLNS', 'Glns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRNS', 'Grns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRVS', 'Grvs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('HBRS', 'Hbrs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('LGTS', 'Lgts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTWY', 'Mtwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTNS', 'Mtns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('OPAS', 'Opas'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PSGE', 'Psge'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('PNE', 'Pne'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('RTE', 'Rte'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SKWY', 'Skwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQS', 'Sqs'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('STS', 'Sts'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRWY', 'Trwy'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('UPAS', 'Upas'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNS', 'Uns'); -INSERT INTO street_type_lookup (name, abbrev) VALUES ('WL', 'Wl'); -CREATE INDEX street_type_lookup_abbrev_idx ON street_type_lookup (abbrev); - --- Create place and countysub lookup tables -DROP TABLE IF EXISTS place_lookup; -CREATE TABLE place_lookup ( - st_code INTEGER, - state VARCHAR(2), - pl_code INTEGER, - name VARCHAR(90), - PRIMARY KEY (st_code,pl_code) -); - -/** -INSERT INTO place_lookup - SELECT - pl.state::integer as st_code, - sl.abbrev as state, - pl.placefp::integer as pl_code, - pl.name as name - FROM - pl99_d00 pl - JOIN state_lookup sl ON (pl.state = lpad(sl.st_code,2,'0')) - GROUP BY pl.state, sl.abbrev, pl.placefp, pl.name; -**/ -CREATE INDEX place_lookup_name_idx ON place_lookup (soundex(name)); -CREATE INDEX place_lookup_state_idx ON place_lookup (state); - -DROP TABLE IF EXISTS county_lookup; -CREATE TABLE county_lookup ( - st_code INTEGER, - state VARCHAR(2), - co_code INTEGER, - name VARCHAR(90), - PRIMARY KEY (st_code, co_code) -); - -/** -INSERT INTO county_lookup - SELECT - co.state::integer as st_code, - sl.abbrev as state, - co.county::integer as co_code, - co.name as name - FROM - co99_d00 co - JOIN state_lookup sl ON (co.state = lpad(sl.st_code,2,'0')) - GROUP BY co.state, sl.abbrev, co.county, co.name; -**/ -CREATE INDEX county_lookup_name_idx ON county_lookup (soundex(name)); -CREATE INDEX county_lookup_state_idx ON county_lookup (state); - -DROP TABLE IF EXISTS countysub_lookup; -CREATE TABLE countysub_lookup ( - st_code INTEGER, - state VARCHAR(2), - co_code INTEGER, - county VARCHAR(90), - cs_code INTEGER, - name VARCHAR(90), - PRIMARY KEY (st_code, co_code, cs_code) -); - -/** -INSERT INTO countysub_lookup - SELECT - cs.state::integer as st_code, - sl.abbrev as state, - cs.county::integer as co_code, - cl.name as county, - cs.cousubfp::integer as cs_code, - cs.name as name - FROM - cs99_d00 cs - JOIN state_lookup sl ON (cs.state = lpad(sl.st_code,2,'0')) - JOIN county_lookup cl ON (cs.state = lpad(cl.st_code,2,'0') AND cs.county = cl.co_code) - GROUP BY cs.state, sl.abbrev, cs.county, cl.name, cs.cousubfp, cs.name; -**/ -CREATE INDEX countysub_lookup_name_idx ON countysub_lookup (soundex(name)); -CREATE INDEX countysub_lookup_state_idx ON countysub_lookup (state); - -DROP TABLE IF EXISTS zip_lookup_all; -CREATE TABLE zip_lookup_all ( - zip INTEGER, - st_code INTEGER, - state VARCHAR(2), - co_code INTEGER, - county VARCHAR(90), - cs_code INTEGER, - cousub VARCHAR(90), - pl_code INTEGER, - place VARCHAR(90), - cnt INTEGER -); - -/** SET work_mem = '2GB'; - -INSERT INTO zip_lookup_all - SELECT *,count(*) as cnt FROM - (SELECT - zipl as zip, - rl.statel as st_code, - sl.abbrev as state, - rl.countyl as co_code, - cl.name as county, - rl.cousubl as cs_code, - cs.name as countysub, - rl.placel as pl_code, - pl.name as place - FROM - roads_local rl - JOIN state_lookup sl ON (rl.statel = lpad(sl.st_code,2,'0')) - LEFT JOIN county_lookup cl ON (rl.statel = lpad(cl.st_code,2,'0') AND rl.countyl = cl.co_code) - LEFT JOIN countysub_lookup cs ON (rl.statel = lpad(cs.st_code,2,'0') AND rl.countyl = cs.co_code AND rl.cousubl = cs.cs_code) - LEFT JOIN place_lookup pl ON (rl.statel = lpad(pl.st_code,2,'0') AND rl.placel = pl.pl_code) - WHERE zipl IS NOT NULL - UNION ALL - SELECT - zipr as zip, - rl.stater as st_code, - sl.abbrev as state, - rl.countyr as co_code, - cl.name as county, - rl.cousubr as cs_code, - cs.name as countysub, - rl.placer as pl_code, - pl.name as place - FROM - roads_local rl - JOIN state_lookup sl ON (rl.stater = lpad(sl.st_code,2,'0')) - LEFT JOIN county_lookup cl ON (rl.stater = lpad(cl.st_code,2,'0') AND rl.countyr = cl.co_code) - LEFT JOIN countysub_lookup cs ON (rl.stater = lpad(cs.st_code,2,'0') AND rl.countyr = cs.co_code AND rl.cousubr = cs.cs_code) - LEFT JOIN place_lookup pl ON (rl.stater = lpad(pl.st_code,2,'0') AND rl.placer = pl.pl_code) - WHERE zipr IS NOT NULL - ) as subquery - GROUP BY zip, st_code, state, co_code, county, cs_code, countysub, pl_code, place; -**/ -DROP TABLE IF EXISTS zip_lookup_base; -CREATE TABLE zip_lookup_base ( - zip varchar(5), - state VARCHAR(40), - county VARCHAR(90), - city VARCHAR(90), - statefp varchar(2), - PRIMARY KEY (zip) -); - --- INSERT INTO zip_lookup_base --- Populate through magic --- If anyone knows of a good, public, free, place to pull this information from, that'd be awesome to have... - -DROP TABLE IF EXISTS zip_lookup; -CREATE TABLE zip_lookup ( - zip INTEGER, - st_code INTEGER, - state VARCHAR(2), - co_code INTEGER, - county VARCHAR(90), - cs_code INTEGER, - cousub VARCHAR(90), - pl_code INTEGER, - place VARCHAR(90), - cnt INTEGER, - PRIMARY KEY (zip) -); - -DROP TABLE IF EXISTS zcta500; -CREATE TABLE zcta500 -( - gid serial NOT NULL PRIMARY KEY, - zcta5ce character varying(5), - classfp character varying(2), - mtfcc character varying(5), - funcstat character varying(1), - aland numeric(14), - awater numeric(14), - intptlat character varying(11), - intptlon character varying(12), - the_geom geometry, - CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), - CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), - CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) -); -/** -INSERT INTO zip_lookup - SELECT - DISTINCT ON (zip) - zip, - st_code, - state, - co_code, - county, - cs_code, - cousub, - pl_code, - place, - cnt - FROM zip_lookup_all - ORDER BY zip,cnt desc; - **/ -DROP TABLE IF EXISTS county; -CREATE TABLE county -( - gid SERIAL NOT NULL, - statefp character varying(2), - countyfp character varying(3), - countyns character varying(8), - cntyidfp character varying(5) PRIMARY KEY NOT NULL, - "name" character varying(100), - namelsad character varying(100), - lsad character varying(2), - classfp character varying(2), - mtfcc character varying(5), - csafp character varying(3), - cbsafp character varying(5), - metdivfp character varying(5), - funcstat character varying(1), - aland bigint, - awater double precision, - intptlat character varying(11), - intptlon character varying(12), - the_geom geometry, - CONSTRAINT uidx_county_gid UNIQUE (gid), - CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2), - CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), - CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269) -); - -DROP TABLE IF EXISTS state; -CREATE TABLE state -( - gid serial NOT NULL, - region character varying(2), - division character varying(2), - statefp character varying(2), - statens character varying(8), - stusps character varying(2) NOT NULL, - "name" character varying(100), - lsad character varying(2), - mtfcc character varying(5), - funcstat character varying(1), - aland bigint, - awater bigint, - intptlat character varying(11), - intptlon character varying(12), - the_geom geometry, - CONSTRAINT state_pkey PRIMARY KEY (stusps), - CONSTRAINT uidx_tiger_state_gid UNIQUE (gid), - CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), - CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), - CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) -); - -DROP TABLE IF EXISTS place; -CREATE TABLE place -( - gid serial NOT NULL, - statefp character varying(2), - placefp character varying(5), - placens character varying(8), - plcidfp character varying(7) PRIMARY KEY, - "name" character varying(100), - namelsad character varying(100), - lsad character varying(2), - classfp character varying(2), - cpi character varying(1), - pcicbsa character varying(1), - pcinecta character varying(1), - mtfcc character varying(5), - funcstat character varying(1), - aland bigint, - awater bigint, - intptlat character varying(11), - intptlon character varying(12), - the_geom geometry, - CONSTRAINT uidx_tiger_place_gid UNIQUE (gid), - CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), - CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), - CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) -); - -DROP TABLE IF EXISTS zip_state; -CREATE TABLE zip_state -( - zip character varying(5) NOT NULL, - stusps character varying(2) NOT NULL, - statefp character varying(2), - CONSTRAINT zip_state_pkey PRIMARY KEY (zip, stusps) -); - -DROP TABLE IF EXISTS zip_state_loc; -CREATE TABLE zip_state_loc -( - zip character varying(5) NOT NULL, - stusps character varying(2) NOT NULL, - statefp character varying(2), - place varchar(100), - CONSTRAINT zip_state_loc_pkey PRIMARY KEY (zip, stusps, place) -); - -DROP TABLE IF EXISTS cousub; -CREATE TABLE cousub -( - gid serial NOT NULL, - statefp character varying(2), - countyfp character varying(3), - cousubfp character varying(5), - cousubns character varying(8), - cosbidfp character varying(10) NOT NULL PRIMARY KEY, - "name" character varying(100), - namelsad character varying(100), - lsad character varying(2), - classfp character varying(2), - mtfcc character varying(5), - cnectafp character varying(3), - nectafp character varying(5), - nctadvfp character varying(5), - funcstat character varying(1), - aland numeric(14), - awater numeric(14), - intptlat character varying(11), - intptlon character varying(12), - the_geom geometry, - CONSTRAINT uidx_cousub_gid UNIQUE (gid), - CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), - CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), - CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) -); - -CREATE INDEX tige_cousub_the_geom_gist ON cousub USING gist(the_geom); - -DROP TABLE IF EXISTS edges; -CREATE TABLE edges -( - gid SERIAL NOT NULL PRIMARY KEY, - statefp character varying(2), - countyfp character varying(3), - tlid numeric(10), - tfidl numeric(10), - tfidr numeric(10), - mtfcc character varying(5), - fullname character varying(100), - smid character varying(22), - lfromadd character varying(12), - ltoadd character varying(12), - rfromadd character varying(12), - rtoadd character varying(12), - zipl character varying(5), - zipr character varying(5), - featcat character varying(1), - hydroflg character varying(1), - railflg character varying(1), - roadflg character varying(1), - olfflg character varying(1), - passflg character varying(1), - divroad character varying(1), - exttyp character varying(1), - ttyp character varying(1), - deckedroad character varying(1), - artpath character varying(1), - persist character varying(1), - gcseflg character varying(1), - offsetl character varying(1), - offsetr character varying(1), - tnidf numeric(10), - tnidt numeric(10), - the_geom geometry, - CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), - CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL), - CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) -); -CREATE INDEX idx_edges_tlid ON edges USING btree(tlid); - -DROP TABLE IF EXISTS faces; - -CREATE TABLE faces -( -gid serial NOT NULL PRIMARY KEY, - tfid numeric(10,0), - statefp00 varchar(2), - countyfp00 varchar(3), - tractce00 varchar(6), - blkgrpce00 varchar(1), - blockce00 varchar(4), - cousubfp00 varchar(5), - submcdfp00 varchar(5), - conctyfp00 varchar(5), - placefp00 varchar(5), - aiannhfp00 varchar(5), - aiannhce00 varchar(4), - comptyp00 varchar(1), - trsubfp00 varchar(5), - trsubce00 varchar(3), - anrcfp00 varchar(5), - elsdlea00 varchar(5), - scsdlea00 varchar(5), - unsdlea00 varchar(5), - uace00 varchar(5), - cd108fp varchar(2), - sldust00 varchar(3), - sldlst00 varchar(3), - vtdst00 varchar(6), - zcta5ce00 varchar(5), - tazce00 varchar(6), - ugace00 varchar(5), - puma5ce00 varchar(5), - statefp varchar(2), - countyfp varchar(3), - tractce varchar(6), - blkgrpce varchar(1), - blockce varchar(4), - cousubfp varchar(5), - submcdfp varchar(5), - conctyfp varchar(5), - placefp varchar(5), - aiannhfp varchar(5), - aiannhce varchar(4), - comptyp varchar(1), - trsubfp varchar(5), - trsubce varchar(3), - anrcfp varchar(5), - ttractce varchar(6), - tblkgpce varchar(1), - elsdlea varchar(5), - scsdlea varchar(5), - unsdlea varchar(5), - uace varchar(5), - cd111fp varchar(2), - sldust varchar(3), - sldlst varchar(3), - vtdst varchar(6), - zcta5ce varchar(5), - tazce varchar(6), - ugace varchar(5), - puma5ce varchar(5), - csafp varchar(3), - cbsafp varchar(5), - metdivfp varchar(5), - cnectafp varchar(3), - nectafp varchar(5), - nctadvfp varchar(5), - lwflag varchar(1), - "offset" varchar(1), - atotal double precision, - intptlat varchar(11), - intptlon varchar(12), - the_geom geometry, - CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), - CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), - CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) -); -CREATE INDEX idx_tiger_faces_tfid ON faces USING btree (tfid); - - --- Index: tiger.faces_the_geom_gist - --- DROP INDEX tiger.faces_the_geom_gist; - -CREATE INDEX tiger_faces_the_geom_gist - ON faces - USING gist - (the_geom); - -CREATE TABLE featnames -( - gid SERIAL NOT NULL, - tlid bigint, - fullname character varying(100), - "name" character varying(100), - predirabrv character varying(15), - pretypabrv character varying(50), - prequalabr character varying(15), - sufdirabrv character varying(15), - suftypabrv character varying(50), - sufqualabr character varying(15), - predir character varying(2), - pretyp character varying(3), - prequal character varying(2), - sufdir character varying(2), - suftyp character varying(3), - sufqual character varying(2), - linearid character varying(22), - mtfcc character varying(5), - paflag character varying(1), - CONSTRAINT featnames_pkey PRIMARY KEY (gid) -); -ALTER TABLE featnames ADD COLUMN statefp character varying(2); -CREATE INDEX idx_tiger_featnames_snd_name ON featnames USING btree (soundex(name)); -CREATE INDEX idx_tiger_featnames_lname ON featnames USING btree (lower(name)); -CREATE INDEX idx_tiger_featnames_tlid_statefp ON featnames USING btree (tlid,statefp); - -CREATE TABLE addr -( - gid SERIAL NOT NULL, - tlid bigint, - fromhn character varying(12), - tohn character varying(12), - side character varying(1), - zip character varying(5), - plus4 character varying(4), - fromtyp character varying(1), - totyp character varying(1), - fromarmid integer, - toarmid integer, - arid character varying(22), - mtfcc character varying(5), - CONSTRAINT addr_pkey PRIMARY KEY (gid) -); -ALTER TABLE addr ADD COLUMN statefp character varying(2); - -CREATE INDEX idx_tiger_addr_tlid_statefp ON addr USING btree(tlid,statefp); -CREATE INDEX idx_tiger_addr_zip ON addr USING btree (zip); - - +--$Id$ +SET search_path TO tiger, public; +-- Create direction lookup table +DROP TABLE IF EXISTS direction_lookup; +CREATE TABLE direction_lookup (name VARCHAR(20) PRIMARY KEY, abbrev VARCHAR(3)); +INSERT INTO direction_lookup (name, abbrev) VALUES('WEST', 'W'); +INSERT INTO direction_lookup (name, abbrev) VALUES('W', 'W'); +INSERT INTO direction_lookup (name, abbrev) VALUES('SW', 'SW'); +INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH-WEST', 'SW'); +INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTHWEST', 'SW'); +INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH-EAST', 'SE'); +INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTHEAST', 'SE'); +INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH_WEST', 'SW'); +INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH_EAST', 'SE'); +INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH', 'S'); +INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH WEST', 'SW'); +INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH EAST', 'SE'); +INSERT INTO direction_lookup (name, abbrev) VALUES('SE', 'SE'); +INSERT INTO direction_lookup (name, abbrev) VALUES('S', 'S'); +INSERT INTO direction_lookup (name, abbrev) VALUES('NW', 'NW'); +INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH-WEST', 'NW'); +INSERT INTO direction_lookup (name, abbrev) VALUES('NORTHWEST', 'NW'); +INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH-EAST', 'NE'); +INSERT INTO direction_lookup (name, abbrev) VALUES('NORTHEAST', 'NE'); +INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH_WEST', 'NW'); +INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH_EAST', 'NE'); +INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH', 'N'); +INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH WEST', 'NW'); +INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH EAST', 'NE'); +INSERT INTO direction_lookup (name, abbrev) VALUES('NE', 'NE'); +INSERT INTO direction_lookup (name, abbrev) VALUES('N', 'N'); +INSERT INTO direction_lookup (name, abbrev) VALUES('EAST', 'E'); +INSERT INTO direction_lookup (name, abbrev) VALUES('E', 'E'); +CREATE INDEX direction_lookup_abbrev_idx ON direction_lookup (abbrev); + + + +-- Create secondary unit lookup table +DROP TABLE IF EXISTS secondary_unit_lookup; +CREATE TABLE secondary_unit_lookup (name VARCHAR(20) PRIMARY KEY, abbrev VARCHAR(5)); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('APARTMENT', 'APT'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('APT', 'APT'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BASEMENT', 'BSMT'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BSMT', 'BSMT'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BUILDING', 'BLDG'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BLDG', 'BLDG'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('DEPARTMENT', 'DEPT'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('DEPT', 'DEPT'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FLOOR', 'FL'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FL', 'FL'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FRONT', 'FRNT'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FRNT', 'FRNT'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('HANGAR', 'HNGR'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('HNGR', 'HNGR'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOBBY', 'LBBY'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LBBY', 'LBBY'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOT', 'LOT'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOWER', 'LOWR'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOWR', 'LOWR'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('OFFICE', 'OFC'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('OFC', 'OFC'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('PENTHOUSE', 'PH'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('PH', 'PH'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('PIER', 'PIER'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('REAR', 'REAR'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('ROOM', 'RM'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('RM', 'RM'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SIDE', 'SIDE'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SLIP', 'SLIP'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SPACE', 'SPC'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SPC', 'SPC'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('STOP', 'STOP'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SUITE', 'STE'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('STE', 'STE'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('TRAILER', 'TRLR'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('TRLR', 'TRLR'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('UNIT', 'UNIT'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('UPPER', 'UPPR'); +INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('UPPR', 'UPPR'); +CREATE INDEX secondary_unit_lookup_abbrev_idx ON secondary_unit_lookup (abbrev); + + + +-- Create state lookup table +DROP TABLE IF EXISTS state_lookup; +CREATE TABLE state_lookup (st_code INTEGER PRIMARY KEY, name VARCHAR(40) UNIQUE, abbrev VARCHAR(3) UNIQUE); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Alabama', 'AL', '01'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Alaska', 'AK', '02'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('American Samoa', 'AS', -1); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Arizona', 'AZ', '04'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Arkansas', 'AR', '05'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('California', 'CA', '06'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Colorado', 'CO', '08'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Connecticut', 'CT', '09'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Delaware', 'DE', '10'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('District of Columbia', 'DC', '11'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Federated States of Micronesia', 'FM', -2); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Florida', 'FL', '12'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Georgia', 'GA', '13'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Guam', 'GU', -7); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Hawaii', 'HI', '15'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Idaho', 'ID', '16'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Illinois', 'IL', '17'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Indiana', 'IN', '18'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Iowa', 'IA', '19'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Kansas', 'KS', '20'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Kentucky', 'KY', '21'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Louisiana', 'LA', '22'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Maine', 'ME', '23'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Marshall Islands', 'MH', -3); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Maryland', 'MD', '24'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Massachusetts', 'MA', '25'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Michigan', 'MI', '26'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Minnesota', 'MN', '27'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Mississippi', 'MS', '28'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Missouri', 'MO', '29'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Montana', 'MT', '30'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Nebraska', 'NE', '31'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Nevada', 'NV', '32'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New Hampshire', 'NH', '33'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New Jersey', 'NJ', '34'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New Mexico', 'NM', '35'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New York', 'NY', '36'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('North Carolina', 'NC', '37'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('North Dakota', 'ND', '38'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Northern Mariana Islands', 'MP', -4); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Ohio', 'OH', '39'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Oklahoma', 'OK', '40'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Oregon', 'OR', '41'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Palau', 'PW', -5); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Pennsylvania', 'PA', '42'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Puerto Rico', 'PR', '72'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Rhode Island', 'RI', '44'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('South Carolina', 'SC', '45'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('South Dakota', 'SD', '46'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Tennessee', 'TN', '47'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Texas', 'TX', '48'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Utah', 'UT', '49'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Vermont', 'VT', '50'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Virgin Islands', 'VI', -6); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Virginia', 'VA', '51'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Washington', 'WA', '53'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('West Virginia', 'WV', '54'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Wisconsin', 'WI', '55'); +INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Wyoming', 'WY', '56'); + + +-- Create street type lookup table +DROP TABLE IF EXISTS street_type_lookup; +CREATE TABLE street_type_lookup (name VARCHAR(20) PRIMARY KEY, abbrev VARCHAR(4)); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALLEE', 'Aly'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALLEY', 'Aly'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALLY', 'Aly'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALY', 'Aly'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANEX', 'Anx'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANNEX', 'Anx'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANNX', 'Anx'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANX', 'Anx'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ARC', 'Arc'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ARCADE', 'Arc'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('AV', 'Ave'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVE', 'Ave'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVEN', 'Ave'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVENU', 'Ave'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVENUE', 'Ave'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVN', 'Ave'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVNUE', 'Ave'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BAYOO', 'Byu'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BAYOU', 'Byu'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BCH', 'Bch'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BEACH', 'Bch'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BEND', 'Bnd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BND', 'Bnd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLF', 'Blf'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLUF', 'Blf'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLUFF', 'Blf'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLUFFS', 'Blfs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOT', 'Btm'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOTTM', 'Btm'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOTTOM', 'Btm'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BTM', 'Btm'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLVD', 'Blvd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOUL', 'Blvd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOULEVARD', 'Blvd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOULV', 'Blvd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BR', 'Br'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRANCH', 'Br'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRNCH', 'Br'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRDGE', 'Brg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRG', 'Brg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRIDGE', 'Brg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRK', 'Brk'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BROOK', 'Brk'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BROOKS', 'Brks'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BURG', 'Bg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BURGS', 'Bgs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYP', 'Byp'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPA', 'Byp'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPAS', 'Byp'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPASS', 'ByP'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPS', 'Byp'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAMP', 'Cp'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CMP', 'Cp'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CP', 'Cp'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CANYN', 'Cyn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CANYON', 'Cyn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CNYN', 'Cyn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CYN', 'Cyn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAPE', 'Cpe'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CPE', 'Cpe'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAUSEWAY', 'Cswy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAUSWAY', 'Cswy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CSWY', 'Cswy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CEN', 'Ctr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENT', 'Ctr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTER', 'Ctr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTR', 'Ctr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTRE', 'Ctr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CNTER', 'Ctr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CNTR', 'Ctr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CTR', 'Ctr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTERS', 'Ctrs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIR', 'Cir'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRC', 'Cir'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRCL', 'Cir'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRCLE', 'Cir'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRCL', 'Cir'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRCLE', 'Cir'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRCLES', 'Cirs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLF', 'Clf'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLIFF', 'Clf'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLFS', 'Clfs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLIFFS', 'Clfs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLB', 'Clb'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLUB', 'Clb'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('COMMON', 'Cmn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('COR', 'Cor'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CORNER', 'Cor'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CORNERS', 'Cors'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CORS', 'Cors'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('COURSE', 'Crse'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSE', 'Crse'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('COURT', 'Ct'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRT', 'Ct'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CT', 'Ct'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('COURTS', 'Cts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('COVE', 'Cv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CV', 'Cv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('COVES', 'Cvs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CK', 'Crk'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CR', 'Crk'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CREEK', 'Crk'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRK', 'Crk'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRECENT', 'Cres'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRES', 'Cres'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRESCENT', 'Cres'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRESENT', 'Cres'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSCNT', 'Cres'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSENT', 'Cres'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSNT', 'Cres'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CREST', 'Crst'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CROSSING', 'Xing'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSSING', 'Xing'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSSNG', 'Xing'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('XING', 'Xing'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CROSSROAD', 'Xrd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CURVE', 'Curv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DALE', 'Dl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DL', 'Dl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DAM', 'Dm'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DM', 'Dm'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DIV', 'Dv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DIVIDE', 'Dv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DV', 'Dv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DVD', 'Dv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DR', 'Dr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRIV', 'Dr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRIVE', 'Dr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRV', 'Dr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRIVES', 'Drs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EST', 'Est'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ESTATE', 'Est'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ESTATES', 'Ests'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ESTS', 'Ests'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXP', 'Expy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPR', 'Expy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPRESS', 'Expy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPRESSWAY', 'Expy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPW', 'Expy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPY', 'Expy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXT', 'Ext'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTENSION', 'Ext'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTN', 'Ext'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTNSN', 'Ext'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTENSIONS', 'Exts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTS', 'Exts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FALL', 'Fall'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FALLS', 'Fls'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLS', 'Fls'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FERRY', 'Fry'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRRY', 'Fry'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRY', 'Fry'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FIELD', 'Fld'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLD', 'Fld'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FIELDS', 'Flds'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLDS', 'Flds'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLAT', 'Flt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLT', 'Flt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLATS', 'Flts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLTS', 'Flts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORD', 'Frd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRD', 'Frd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORDS', 'Frds'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FOREST', 'Frst'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORESTS', 'Frst'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRST', 'Frst'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORG', 'Frg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORGE', 'Frg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRG', 'Frg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORGES', 'Frgs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORK', 'Frk'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRK', 'Frk'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORKS', 'Frks'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRKS', 'Frks'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORT', 'Ft'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRT', 'Ft'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FT', 'Ft'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FREEWAY', 'Fwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FREEWY', 'Fwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRWAY', 'Fwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRWY', 'Fwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FWY', 'Fwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GARDEN', 'Gdn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GARDN', 'Gdn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GDN', 'Gdn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRDEN', 'Gdn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRDN', 'Gdn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GARDENS', 'Gdns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GDNS', 'Gdns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRDNS', 'Gdns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GATEWAY', 'Gtwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GATEWY', 'Gtwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GATWAY', 'Gtwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GTWAY', 'Gtwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GTWY', 'Gtwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLEN', 'Gln'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLN', 'Gln'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLENS', 'Glns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GREEN', 'Grn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRN', 'Grn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GREENS', 'Grns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GROV', 'Grv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GROVE', 'Grv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRV', 'Grv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GROVES', 'Grvs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARB', 'Hbr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARBOR', 'Hbr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARBR', 'Hbr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HBR', 'Hbr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HRBOR', 'Hbr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARBORS', 'Hbrs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HAVEN', 'Hvn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HAVN', 'Hvn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HVN', 'Hvn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HEIGHT', 'Hts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HEIGHTS', 'Hts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HGTS', 'Hts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HT', 'Hts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HTS', 'Hts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HIGHWAY', 'Hwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HIGHWY', 'Hwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HIWAY', 'Hwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HIWY', 'Hwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HWAY', 'Hwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HWY', 'Hwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HILL', 'Hl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HL', 'Hl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HILLS', 'Hls'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HLS', 'Hls'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HLLW', 'Holw'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLLOW', 'Holw'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLLOWS', 'Holw'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLW', 'Holw'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLWS', 'Holw'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('INLET', 'Inlt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('INLT', 'Inlt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('IS', 'Is'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLAND', 'Is'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLND', 'Is'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLANDS', 'Iss'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLNDS', 'Iss'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISS', 'Iss'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLE', 'Isle'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLES', 'Isle'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCT', 'Jct'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTION', 'Jct'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTN', 'Jct'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTION', 'Jct'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTN', 'Jct'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTON', 'Jct'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTNS', 'Jcts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTS', 'Jcts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTIONS', 'Jcts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('KEY', 'Ky'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('KY', 'Ky'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('KEYS', 'Kys'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('KYS', 'Kys'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNL', 'Knl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNOL', 'Knl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNOLL', 'Knl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNLS', 'Knls'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNOLLS', 'Knls'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LAKE', 'Lk'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LK', 'Lk'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LAKES', 'Lks'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LKS', 'Lks'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LAND', 'Land'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LANDING', 'Lndg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LNDG', 'Lndg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LNDNG', 'Lndg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LA', 'Ln'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LANE', 'Ln'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LANES', 'Ln'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LN', 'Ln'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LGT', 'Lgt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LIGHT', 'Lgt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LIGHTS', 'Lgts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LF', 'Lf'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOAF', 'Lf'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LCK', 'Lck'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOCK', 'Lck'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LCKS', 'Lcks'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOCKS', 'Lcks'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LDG', 'Ldg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LDGE', 'Ldg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LODG', 'Ldg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LODGE', 'Ldg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOOP', 'Loop'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOOPS', 'Loop'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MALL', 'Mall'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MANOR', 'Mnr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNR', 'Mnr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MANORS', 'Mnrs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNRS', 'Mnrs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MDW', 'Mdw'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEADOW', 'Mdw'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MDWS', 'Mdws'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEADOWS', 'Mdws'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEDOWS', 'Mdws'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEWS', 'Mews'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MILL', 'Ml'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ML', 'Ml'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MILLS', 'Mls'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MLS', 'Mls'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MISSION', 'Msn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MISSN', 'Msn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MSN', 'Msn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MSSN', 'Msn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOTORWAY', 'Mtwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNT', 'Mt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNT', 'Mt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MT', 'Mt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNTAIN', 'Mtn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNTN', 'Mtn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNTAIN', 'Mtn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNTIN', 'Mtn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTIN', 'Mtn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTN', 'Mtn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNTNS', 'Mtns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNTAINS', 'Mtns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('NCK', 'Nck'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('NECK', 'Nck'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ORCH', 'Orch'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ORCHARD', 'Orch'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ORCHRD', 'Orch'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('OVAL', 'Oval'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('OVL', 'Oval'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('OVERPASS', 'Opas'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARK', 'Park'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PK', 'Park'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRK', 'Park'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKS', 'Park'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKWAY', 'Pkwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKWY', 'Pkwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKWAY', 'Pkwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKWY', 'Pkwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKY', 'Pkwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKWAYS', 'Pkwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKWYS', 'Pkwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PASS', 'Pass'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PASSAGE', 'Psge'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PATH', 'Path'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PATHS', 'Path'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PIKE', 'Pike'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PIKES', 'Pike'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PINE', 'Pne'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PINES', 'Pnes'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PNES', 'Pnes'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PL', 'Pl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLACE', 'Pl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAIN', 'Pln'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLN', 'Pln'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAINES', 'Plns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAINS', 'Plns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLNS', 'Plns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAZA', 'Plz'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLZ', 'Plz'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLZA', 'Plz'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('POINT', 'Pt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PT', 'Pt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('POINTS', 'Pts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PTS', 'Pts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PORT', 'Prt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRT', 'Prt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PORTS', 'Prts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRTS', 'Prts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PR', 'Pr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRAIRIE', 'Pr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRARIE', 'Pr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRR', 'Pr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAD', 'Radl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RADIAL', 'Radl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RADIEL', 'Radl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RADL', 'Radl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAMP', 'Ramp'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RANCH', 'Rnch'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RANCHES', 'Rnch'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RNCH', 'Rnch'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RNCHS', 'Rnch'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAPID', 'Rpd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RPD', 'Rpd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAPIDS', 'Rpds'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RPDS', 'Rpds'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('REST', 'Rst'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RST', 'Rst'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDG', 'Rdg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDGE', 'Rdg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIDGE', 'Rdg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDGS', 'Rdgs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIDGES', 'Rdgs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIV', 'Riv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIVER', 'Riv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIVR', 'Riv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RVR', 'Riv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RD', 'Rd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROAD', 'Rd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDS', 'Rds'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROADS', 'Rds'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROUTE', 'Rte'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROW', 'Row'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RUE', 'Rue'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RUN', 'Run'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHL', 'Shl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOAL', 'Shl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHLS', 'Shls'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOALS', 'Shls'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOAR', 'Shr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHORE', 'Shr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHR', 'Shr'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOARS', 'Shrs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHORES', 'Shrs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHRS', 'Shrs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SKYWAY', 'Skwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPG', 'Spg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPNG', 'Spg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRING', 'Spg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRNG', 'Spg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPGS', 'Spgs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPNGS', 'Spgs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRINGS', 'Spgs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRNGS', 'Spgs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPUR', 'Spur'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPURS', 'Spur'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQ', 'Sq'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQR', 'Sq'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQRE', 'Sq'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQU', 'Sq'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQUARE', 'Sq'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQRS', 'Sqs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQUARES', 'Sqs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STA', 'Sta'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STATION', 'Sta'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STATN', 'Sta'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STN', 'Sta'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRA', 'Stra'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAV', 'Stra'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVE', 'Stra'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVEN', 'Stra'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVENUE', 'Stra'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVN', 'Stra'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRVN', 'Stra'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRVNUE', 'Stra'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREAM', 'Strm'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREME', 'Strm'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRM', 'Strm'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('ST', 'St'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STR', 'St'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREET', 'St'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRT', 'St'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREETS', 'Sts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SMT', 'Smt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SUMIT', 'Smt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SUMITT', 'Smt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SUMMIT', 'Smt'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TER', 'Ter'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TERR', 'Ter'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TERRACE', 'Ter'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('THROUGHWAY', 'Trwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACE', 'Trce'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACES', 'Trce'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRCE', 'Trce'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACK', 'Trak'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACKS', 'Trak'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAK', 'Trak'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRK', 'Trak'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRKS', 'Trak'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAFFICWAY', 'Trfy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRFY', 'Trfy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TR', 'Trl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAIL', 'Trl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAILS', 'Trl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRL', 'Trl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRLS', 'Trl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNEL', 'Tunl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNL', 'Tunl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNLS', 'Tunl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNNEL', 'Tunl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNNELS', 'Tunl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNNL', 'Tunl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TPK', 'Tpke'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TPKE', 'Tpke'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRNPK', 'Tpke'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRPK', 'Tpke'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TURNPIKE', 'Tpke'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TURNPK', 'Tpke'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNDERPASS', 'Upas'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('UN', 'Un'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNION', 'Un'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNIONS', 'Uns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VALLEY', 'Vly'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VALLY', 'Vly'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLLY', 'Vly'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLY', 'Vly'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VALLEYS', 'Vlys'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLYS', 'Vlys'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VDCT', 'Via'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIA', 'Via'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIADCT', 'Via'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIADUCT', 'Via'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIEW', 'Vw'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VW', 'Vw'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIEWS', 'Vws'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VWS', 'Vws'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILL', 'Vlg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLAG', 'Vlg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLAGE', 'Vlg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLG', 'Vlg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLIAGE', 'Vlg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLG', 'Vlg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLAGES', 'Vlgs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLGS', 'Vlgs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLE', 'Vl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VL', 'Vl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIS', 'Vis'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIST', 'Vis'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VISTA', 'Vis'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VST', 'Vis'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('VSTA', 'Vis'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('WALK', 'Walk'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('WALKS', 'Walk'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('WALL', 'Wall'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('WAY', 'Way'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('WY', 'Way'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('WAYS', 'Ways'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('WELL', 'Wl'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('WELLS', 'Wls'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('WLS', 'Wls'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYU', 'Byu'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLFS', 'Blfs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRKS', 'Brks'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BG', 'Bg'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('BGS', 'Bgs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CTRS', 'Ctrs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRS', 'Cirs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CMN', 'Cmn'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CTS', 'Cts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CVS', 'Cvs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRST', 'Crst'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('XRD', 'Xrd'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('CURV', 'Curv'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRS', 'Drs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRDS', 'Frds'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRGS', 'Frgs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLNS', 'Glns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRNS', 'Grns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRVS', 'Grvs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('HBRS', 'Hbrs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('LGTS', 'Lgts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTWY', 'Mtwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTNS', 'Mtns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('OPAS', 'Opas'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PSGE', 'Psge'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('PNE', 'Pne'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('RTE', 'Rte'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SKWY', 'Skwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQS', 'Sqs'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('STS', 'Sts'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRWY', 'Trwy'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('UPAS', 'Upas'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNS', 'Uns'); +INSERT INTO street_type_lookup (name, abbrev) VALUES ('WL', 'Wl'); +CREATE INDEX street_type_lookup_abbrev_idx ON street_type_lookup (abbrev); + +-- Create place and countysub lookup tables +DROP TABLE IF EXISTS place_lookup; +CREATE TABLE place_lookup ( + st_code INTEGER, + state VARCHAR(2), + pl_code INTEGER, + name VARCHAR(90), + PRIMARY KEY (st_code,pl_code) +); + +/** +INSERT INTO place_lookup + SELECT + pl.state::integer as st_code, + sl.abbrev as state, + pl.placefp::integer as pl_code, + pl.name as name + FROM + pl99_d00 pl + JOIN state_lookup sl ON (pl.state = lpad(sl.st_code,2,'0')) + GROUP BY pl.state, sl.abbrev, pl.placefp, pl.name; +**/ +CREATE INDEX place_lookup_name_idx ON place_lookup (soundex(name)); +CREATE INDEX place_lookup_state_idx ON place_lookup (state); + +DROP TABLE IF EXISTS county_lookup; +CREATE TABLE county_lookup ( + st_code INTEGER, + state VARCHAR(2), + co_code INTEGER, + name VARCHAR(90), + PRIMARY KEY (st_code, co_code) +); + +/** +INSERT INTO county_lookup + SELECT + co.state::integer as st_code, + sl.abbrev as state, + co.county::integer as co_code, + co.name as name + FROM + co99_d00 co + JOIN state_lookup sl ON (co.state = lpad(sl.st_code,2,'0')) + GROUP BY co.state, sl.abbrev, co.county, co.name; +**/ +CREATE INDEX county_lookup_name_idx ON county_lookup (soundex(name)); +CREATE INDEX county_lookup_state_idx ON county_lookup (state); + +DROP TABLE IF EXISTS countysub_lookup; +CREATE TABLE countysub_lookup ( + st_code INTEGER, + state VARCHAR(2), + co_code INTEGER, + county VARCHAR(90), + cs_code INTEGER, + name VARCHAR(90), + PRIMARY KEY (st_code, co_code, cs_code) +); + +/** +INSERT INTO countysub_lookup + SELECT + cs.state::integer as st_code, + sl.abbrev as state, + cs.county::integer as co_code, + cl.name as county, + cs.cousubfp::integer as cs_code, + cs.name as name + FROM + cs99_d00 cs + JOIN state_lookup sl ON (cs.state = lpad(sl.st_code,2,'0')) + JOIN county_lookup cl ON (cs.state = lpad(cl.st_code,2,'0') AND cs.county = cl.co_code) + GROUP BY cs.state, sl.abbrev, cs.county, cl.name, cs.cousubfp, cs.name; +**/ +CREATE INDEX countysub_lookup_name_idx ON countysub_lookup (soundex(name)); +CREATE INDEX countysub_lookup_state_idx ON countysub_lookup (state); + +DROP TABLE IF EXISTS zip_lookup_all; +CREATE TABLE zip_lookup_all ( + zip INTEGER, + st_code INTEGER, + state VARCHAR(2), + co_code INTEGER, + county VARCHAR(90), + cs_code INTEGER, + cousub VARCHAR(90), + pl_code INTEGER, + place VARCHAR(90), + cnt INTEGER +); + +/** SET work_mem = '2GB'; + +INSERT INTO zip_lookup_all + SELECT *,count(*) as cnt FROM + (SELECT + zipl as zip, + rl.statel as st_code, + sl.abbrev as state, + rl.countyl as co_code, + cl.name as county, + rl.cousubl as cs_code, + cs.name as countysub, + rl.placel as pl_code, + pl.name as place + FROM + roads_local rl + JOIN state_lookup sl ON (rl.statel = lpad(sl.st_code,2,'0')) + LEFT JOIN county_lookup cl ON (rl.statel = lpad(cl.st_code,2,'0') AND rl.countyl = cl.co_code) + LEFT JOIN countysub_lookup cs ON (rl.statel = lpad(cs.st_code,2,'0') AND rl.countyl = cs.co_code AND rl.cousubl = cs.cs_code) + LEFT JOIN place_lookup pl ON (rl.statel = lpad(pl.st_code,2,'0') AND rl.placel = pl.pl_code) + WHERE zipl IS NOT NULL + UNION ALL + SELECT + zipr as zip, + rl.stater as st_code, + sl.abbrev as state, + rl.countyr as co_code, + cl.name as county, + rl.cousubr as cs_code, + cs.name as countysub, + rl.placer as pl_code, + pl.name as place + FROM + roads_local rl + JOIN state_lookup sl ON (rl.stater = lpad(sl.st_code,2,'0')) + LEFT JOIN county_lookup cl ON (rl.stater = lpad(cl.st_code,2,'0') AND rl.countyr = cl.co_code) + LEFT JOIN countysub_lookup cs ON (rl.stater = lpad(cs.st_code,2,'0') AND rl.countyr = cs.co_code AND rl.cousubr = cs.cs_code) + LEFT JOIN place_lookup pl ON (rl.stater = lpad(pl.st_code,2,'0') AND rl.placer = pl.pl_code) + WHERE zipr IS NOT NULL + ) as subquery + GROUP BY zip, st_code, state, co_code, county, cs_code, countysub, pl_code, place; +**/ +DROP TABLE IF EXISTS zip_lookup_base; +CREATE TABLE zip_lookup_base ( + zip varchar(5), + state VARCHAR(40), + county VARCHAR(90), + city VARCHAR(90), + statefp varchar(2), + PRIMARY KEY (zip) +); + +-- INSERT INTO zip_lookup_base +-- Populate through magic +-- If anyone knows of a good, public, free, place to pull this information from, that'd be awesome to have... + +DROP TABLE IF EXISTS zip_lookup; +CREATE TABLE zip_lookup ( + zip INTEGER, + st_code INTEGER, + state VARCHAR(2), + co_code INTEGER, + county VARCHAR(90), + cs_code INTEGER, + cousub VARCHAR(90), + pl_code INTEGER, + place VARCHAR(90), + cnt INTEGER, + PRIMARY KEY (zip) +); + +DROP TABLE IF EXISTS zcta500; +CREATE TABLE zcta500 +( + gid serial NOT NULL PRIMARY KEY, + zcta5ce character varying(5), + classfp character varying(2), + mtfcc character varying(5), + funcstat character varying(1), + aland numeric(14), + awater numeric(14), + intptlat character varying(11), + intptlon character varying(12), + the_geom geometry, + CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), + CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), + CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) +); +/** +INSERT INTO zip_lookup + SELECT + DISTINCT ON (zip) + zip, + st_code, + state, + co_code, + county, + cs_code, + cousub, + pl_code, + place, + cnt + FROM zip_lookup_all + ORDER BY zip,cnt desc; + **/ +DROP TABLE IF EXISTS county; +CREATE TABLE county +( + gid SERIAL NOT NULL, + statefp character varying(2), + countyfp character varying(3), + countyns character varying(8), + cntyidfp character varying(5) PRIMARY KEY NOT NULL, + "name" character varying(100), + namelsad character varying(100), + lsad character varying(2), + classfp character varying(2), + mtfcc character varying(5), + csafp character varying(3), + cbsafp character varying(5), + metdivfp character varying(5), + funcstat character varying(1), + aland bigint, + awater double precision, + intptlat character varying(11), + intptlon character varying(12), + the_geom geometry, + CONSTRAINT uidx_county_gid UNIQUE (gid), + CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2), + CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), + CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269) +); + +DROP TABLE IF EXISTS state; +CREATE TABLE state +( + gid serial NOT NULL, + region character varying(2), + division character varying(2), + statefp character varying(2), + statens character varying(8), + stusps character varying(2) NOT NULL, + "name" character varying(100), + lsad character varying(2), + mtfcc character varying(5), + funcstat character varying(1), + aland bigint, + awater bigint, + intptlat character varying(11), + intptlon character varying(12), + the_geom geometry, + CONSTRAINT state_pkey PRIMARY KEY (stusps), + CONSTRAINT uidx_tiger_state_gid UNIQUE (gid), + CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), + CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), + CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) +); + +DROP TABLE IF EXISTS place; +CREATE TABLE place +( + gid serial NOT NULL, + statefp character varying(2), + placefp character varying(5), + placens character varying(8), + plcidfp character varying(7) PRIMARY KEY, + "name" character varying(100), + namelsad character varying(100), + lsad character varying(2), + classfp character varying(2), + cpi character varying(1), + pcicbsa character varying(1), + pcinecta character varying(1), + mtfcc character varying(5), + funcstat character varying(1), + aland bigint, + awater bigint, + intptlat character varying(11), + intptlon character varying(12), + the_geom geometry, + CONSTRAINT uidx_tiger_place_gid UNIQUE (gid), + CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), + CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), + CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) +); + +DROP TABLE IF EXISTS zip_state; +CREATE TABLE zip_state +( + zip character varying(5) NOT NULL, + stusps character varying(2) NOT NULL, + statefp character varying(2), + CONSTRAINT zip_state_pkey PRIMARY KEY (zip, stusps) +); + +DROP TABLE IF EXISTS zip_state_loc; +CREATE TABLE zip_state_loc +( + zip character varying(5) NOT NULL, + stusps character varying(2) NOT NULL, + statefp character varying(2), + place varchar(100), + CONSTRAINT zip_state_loc_pkey PRIMARY KEY (zip, stusps, place) +); + +DROP TABLE IF EXISTS cousub; +CREATE TABLE cousub +( + gid serial NOT NULL, + statefp character varying(2), + countyfp character varying(3), + cousubfp character varying(5), + cousubns character varying(8), + cosbidfp character varying(10) NOT NULL PRIMARY KEY, + "name" character varying(100), + namelsad character varying(100), + lsad character varying(2), + classfp character varying(2), + mtfcc character varying(5), + cnectafp character varying(3), + nectafp character varying(5), + nctadvfp character varying(5), + funcstat character varying(1), + aland numeric(14), + awater numeric(14), + intptlat character varying(11), + intptlon character varying(12), + the_geom geometry, + CONSTRAINT uidx_cousub_gid UNIQUE (gid), + CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), + CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), + CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) +); + +CREATE INDEX tige_cousub_the_geom_gist ON cousub USING gist(the_geom); + +DROP TABLE IF EXISTS edges; +CREATE TABLE edges +( + gid SERIAL NOT NULL PRIMARY KEY, + statefp character varying(2), + countyfp character varying(3), + tlid numeric(10), + tfidl numeric(10), + tfidr numeric(10), + mtfcc character varying(5), + fullname character varying(100), + smid character varying(22), + lfromadd character varying(12), + ltoadd character varying(12), + rfromadd character varying(12), + rtoadd character varying(12), + zipl character varying(5), + zipr character varying(5), + featcat character varying(1), + hydroflg character varying(1), + railflg character varying(1), + roadflg character varying(1), + olfflg character varying(1), + passflg character varying(1), + divroad character varying(1), + exttyp character varying(1), + ttyp character varying(1), + deckedroad character varying(1), + artpath character varying(1), + persist character varying(1), + gcseflg character varying(1), + offsetl character varying(1), + offsetr character varying(1), + tnidf numeric(10), + tnidt numeric(10), + the_geom geometry, + CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), + CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL), + CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) +); +CREATE INDEX idx_edges_tlid ON edges USING btree(tlid); + +DROP TABLE IF EXISTS faces; + +CREATE TABLE faces +( +gid serial NOT NULL PRIMARY KEY, + tfid numeric(10,0), + statefp00 varchar(2), + countyfp00 varchar(3), + tractce00 varchar(6), + blkgrpce00 varchar(1), + blockce00 varchar(4), + cousubfp00 varchar(5), + submcdfp00 varchar(5), + conctyfp00 varchar(5), + placefp00 varchar(5), + aiannhfp00 varchar(5), + aiannhce00 varchar(4), + comptyp00 varchar(1), + trsubfp00 varchar(5), + trsubce00 varchar(3), + anrcfp00 varchar(5), + elsdlea00 varchar(5), + scsdlea00 varchar(5), + unsdlea00 varchar(5), + uace00 varchar(5), + cd108fp varchar(2), + sldust00 varchar(3), + sldlst00 varchar(3), + vtdst00 varchar(6), + zcta5ce00 varchar(5), + tazce00 varchar(6), + ugace00 varchar(5), + puma5ce00 varchar(5), + statefp varchar(2), + countyfp varchar(3), + tractce varchar(6), + blkgrpce varchar(1), + blockce varchar(4), + cousubfp varchar(5), + submcdfp varchar(5), + conctyfp varchar(5), + placefp varchar(5), + aiannhfp varchar(5), + aiannhce varchar(4), + comptyp varchar(1), + trsubfp varchar(5), + trsubce varchar(3), + anrcfp varchar(5), + ttractce varchar(6), + tblkgpce varchar(1), + elsdlea varchar(5), + scsdlea varchar(5), + unsdlea varchar(5), + uace varchar(5), + cd111fp varchar(2), + sldust varchar(3), + sldlst varchar(3), + vtdst varchar(6), + zcta5ce varchar(5), + tazce varchar(6), + ugace varchar(5), + puma5ce varchar(5), + csafp varchar(3), + cbsafp varchar(5), + metdivfp varchar(5), + cnectafp varchar(3), + nectafp varchar(5), + nctadvfp varchar(5), + lwflag varchar(1), + "offset" varchar(1), + atotal double precision, + intptlat varchar(11), + intptlon varchar(12), + the_geom geometry, + CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), + CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), + CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) +); +CREATE INDEX idx_tiger_faces_tfid ON faces USING btree (tfid); + + +-- Index: tiger.faces_the_geom_gist + +-- DROP INDEX tiger.faces_the_geom_gist; + +CREATE INDEX tiger_faces_the_geom_gist + ON faces + USING gist + (the_geom); + +CREATE TABLE featnames +( + gid SERIAL NOT NULL, + tlid bigint, + fullname character varying(100), + "name" character varying(100), + predirabrv character varying(15), + pretypabrv character varying(50), + prequalabr character varying(15), + sufdirabrv character varying(15), + suftypabrv character varying(50), + sufqualabr character varying(15), + predir character varying(2), + pretyp character varying(3), + prequal character varying(2), + sufdir character varying(2), + suftyp character varying(3), + sufqual character varying(2), + linearid character varying(22), + mtfcc character varying(5), + paflag character varying(1), + CONSTRAINT featnames_pkey PRIMARY KEY (gid) +); +ALTER TABLE featnames ADD COLUMN statefp character varying(2); +CREATE INDEX idx_tiger_featnames_snd_name ON featnames USING btree (soundex(name)); +CREATE INDEX idx_tiger_featnames_lname ON featnames USING btree (lower(name)); +CREATE INDEX idx_tiger_featnames_tlid_statefp ON featnames USING btree (tlid,statefp); + +CREATE TABLE addr +( + gid SERIAL NOT NULL, + tlid bigint, + fromhn character varying(12), + tohn character varying(12), + side character varying(1), + zip character varying(5), + plus4 character varying(4), + fromtyp character varying(1), + totyp character varying(1), + fromarmid integer, + toarmid integer, + arid character varying(22), + mtfcc character varying(5), + CONSTRAINT addr_pkey PRIMARY KEY (gid) +); +ALTER TABLE addr ADD COLUMN statefp character varying(2); + +CREATE INDEX idx_tiger_addr_tlid_statefp ON addr USING btree(tlid,statefp); +CREATE INDEX idx_tiger_addr_zip ON addr USING btree (zip); + + -- 2.50.1