From 90eccab361d28b6c2e36e5b3031cf95334cf9e7e Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Mon, 21 May 2012 20:29:31 +0000 Subject: [PATCH] get rid of old stuff can always be pulled from prior versions git-svn-id: http://svn.osgeo.org/postgis/trunk@9770 b70326c6-7e19-0410-871a-916f4a2858ee --- .../tiger_2006andbefore/INSTALL | 76 - .../tiger_geocoder/tiger_2006andbefore/README | 350 --- .../tiger_2006andbefore/create_geocode.sql | 59 - .../tiger_2006andbefore/geocode/geocode.sql | 111 - .../geocode/geocode_address.sql | 94 - .../geocode_address_countysub_exact.sql | 161 - .../geocode_address_countysub_fuzzy.sql | 162 - .../geocode/geocode_address_place_exact.sql | 156 - .../geocode/geocode_address_place_fuzzy.sql | 155 - .../geocode/geocode_address_state.sql | 81 - .../geocode/geocode_address_zip.sql | 83 - .../geocode/geocode_get_point.sql | 10 - .../geocode/geocode_location.sql | 67 - .../geocode/geocode_zip.sql | 41 - .../geocode/includes_address.sql | 93 - .../geocode/interpolate_from_address.sql | 123 - .../geocode/rate_attributes.sql | 58 - .../tiger_2006andbefore/import/load_tiger.sh | 9 - .../tiger_2006andbefore/import/newdb.sh | 6 - .../tiger_2006andbefore/import/tigerimport.sh | 130 - .../tiger_2006andbefore/import/tigerpoly2.py | 333 --- .../normalize/count_words.sql | 32 - .../normalize/end_soundex.sql | 17 - .../normalize/get_last_words.sql | 29 - .../normalize/location_extract.sql | 36 - .../location_extract_countysub_exact.sql | 54 - .../location_extract_countysub_fuzzy.sql | 87 - .../location_extract_place_exact.sql | 57 - .../location_extract_place_fuzzy.sql | 86 - .../normalize/normalize_address.sql | 493 --- .../normalize/pprint_addy.sql | 36 - .../normalize/state_extract.sql | 82 - .../orig/tiger_geocoder.sql | 2657 ----------------- .../tables/lookup_tables.sql | 885 ------ .../tables/roads_local.sql | 92 - .../tables/tiger_geocode_roads.sql | 78 - .../tiger_2006andbefore/utility/cull_null.sql | 8 - .../utility/levenshtein_ignore_case.sql | 10 - .../utility/nullable_levenshtein.sql | 27 - .../tiger_2006andbefore/utility/utmzone.sql | 17 - 40 files changed, 7141 deletions(-) delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/INSTALL delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/README delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/create_geocode.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_countysub_exact.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_countysub_fuzzy.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_place_exact.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_place_fuzzy.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_state.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_zip.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_get_point.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_location.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_zip.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/includes_address.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/interpolate_from_address.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/geocode/rate_attributes.sql delete mode 100755 extras/tiger_geocoder/tiger_2006andbefore/import/load_tiger.sh delete mode 100755 extras/tiger_geocoder/tiger_2006andbefore/import/newdb.sh delete mode 100755 extras/tiger_geocoder/tiger_2006andbefore/import/tigerimport.sh delete mode 100755 extras/tiger_geocoder/tiger_2006andbefore/import/tigerpoly2.py delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/normalize/count_words.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/normalize/end_soundex.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/normalize/get_last_words.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_countysub_exact.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_countysub_fuzzy.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_place_exact.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_place_fuzzy.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/normalize/normalize_address.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/normalize/pprint_addy.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/normalize/state_extract.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/orig/tiger_geocoder.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/tables/lookup_tables.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/tables/roads_local.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/tables/tiger_geocode_roads.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/utility/cull_null.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/utility/levenshtein_ignore_case.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/utility/nullable_levenshtein.sql delete mode 100644 extras/tiger_geocoder/tiger_2006andbefore/utility/utmzone.sql diff --git a/extras/tiger_geocoder/tiger_2006andbefore/INSTALL b/extras/tiger_geocoder/tiger_2006andbefore/INSTALL deleted file mode 100644 index 53e162b48..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/INSTALL +++ /dev/null @@ -1,76 +0,0 @@ -TIGER Geocoder - -2007/07/03 install instructions: - -- Install postgresql-8.2-postgis (For various GIS bits) -- Install PostGIS functions and spatial ref definitions: - \i /usr/share/postgresql-8.2-postgis/postgis.sql - \i /usr/share/postgresql-8.2-postgis/spatial_ref_sys.sql -- Install postgresql-contrib-8.2 for soundex (from fuzzystrmatch) -- Install functions into PG using: - \i /usr/share/postgresql/8.2/contrib/fuzzystrmatch.sql -- Modify and run import/load_tiger.sh for TIGER/Line data - (May wish to run the CompleteChain import seperate from the - Polygon import, since the latter takes an enourmous amount of time) -- Rename tables to be lower-case and whatnot -- Run ogr2ogr (example from tigerimport.sh called from load_tiger.sh) - for the TIGER Boundary SHP files (state boundaries, county boundaries, etc). - Might want download them into boundary/ - - Single-file import something like this: - ogr2ogr -append -f PostgreSQL -a_srs EPSG:4269 "PG:dbname=mydb user=tiger password=XXXXXXXX" WHATEVER.shp WHATEVER -lco DIM=2 - - Script to import the multi-file sets into a single table: - for file in *; do - mkdir x - cd x - unzip ../$file - BASE=`echo *.shp | cut -f1 -d.` - ogr2ogr -append -f PostgreSQL -a_srs EPSG:4269 "PG:dbname=mydb user=tiger password=XXXXXXXX" $BASE.shp $BASE -nln vt99_d00 -lco DIM=2 - cd .. - rm -rf x - done - -- Create geometry indexes on all the tables using GiST, eg: - CREATE INDEX completechain_geom_idx ON completechain - USING GIST (wkb_geometry public.gist_geometry_ops); -- Create lookup tables using lookup_tables.sql -- Create roads_local using roads_local.sql -- Create 'tiger_geocode_roads' using tiger_geocode_roads.sql -- Run create_geocode.sql to load the functions -- Profit! - -Old install instructions: - - 2004/10/28 - - A plpgsql based geocoder written for TIGER census data. - -Installation instructions: - -- If the database being used is new, ensure the following scripts have - been loaded: - - /opt/pgsql74/share/postgis.sql - /opt/pgsql74/share/contrib/spatial_ref_sys.sql - -- Load the fuzzy string matching functions. These must first be compiled, - and may be found in the contrib directory of the postgres source directory. - - psql [-p (port)] (database) < /usr/local/src/postgresql-7.4.5/contrib/fuzzystrmatch/fuzzystrmatch.sql - -- Ensure that the TIGER data is loaded into the target database. - -- Load the lookup tables. This creates the lookup tables and loads predefined - data into them. If the standardized TIGER data is not being used, this - script will need to be altered to reflect the actual data. Since the place - and countysub lookup tables are generated to reflect the data in use, the - database must be populated before this script is run. Indicies are also - created in this script. - - psql [-p (port)] (database) < lookup_tables.sql - -- Load the function scripts. This script loads the geocode function, and all - support modules and functions required by it. - - psql [-p (port)] (database) < tiger_geocoder.sql diff --git a/extras/tiger_geocoder/tiger_2006andbefore/README b/extras/tiger_geocoder/tiger_2006andbefore/README deleted file mode 100644 index a476d735f..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/README +++ /dev/null @@ -1,350 +0,0 @@ -$Id: README 6731 2011-01-25 18:08:57Z robe $ -TIGER Geocoder - - 2004/10/28 - - A plpgsql based geocoder written for TIGER census data. - -Design: - -There are two components to the geocoder, the address normalizer and the -address geocoder. These two components are described seperatly below. - -The goal of this project is to build a fully functional geocoder that can -process an arbitrary address string and, using normalized TIGER censes data, -produce a point geometry reflecting the location of the given address. - -- The geocoder should be simple for anyone familiar with PostGIS to install - and use. -- It should be robust enough to function properly despite formatting and - spelling errors. -- It should be extensible enough to be used with future data updates, or - alternate data sources with a minimum of coding changes. - -Installation: - - Refer to the INSTALL file for installation instructions. - -Usage: - - refcursor geocode(refcursor, 'address string'); - -Notes: - -- The assumed format for the address is the US Postal Service standard: - () indicates a field required by the geocoder, [] indicates an optional field. - - (address) [dirPrefix] (streetName) [streetType] [dirSuffix] - [internalAddress] [location] [state] [zipCode] - - - -Address Normalizer: - -The goal of the address normalizer is to provide a robust function to break a -given address string down into the components of an address. While the -normalizer is built specifically for the normalized US TIGER Census data, it -has been designed to be reasonably extensible to other data sets and localities. - -Usage: - - normalize_address('address string'); - -Support functions: - - location_extract_countysub_exact('partial address string', 'state abbreviation') - location_extract_countysub_fuzzy('partial address string', 'state abbreviation') - location_extract_place_exact('partial address string', 'state abbreviation') - location_extract_place_fuzzy('partial address string', 'state abbreviation') - cull_null('string') - count_words('string') - get_last_words('string') - state_extract('partial address string') - levenshtein_ignore_case('string', 'string') - -Notes: - -- A set of lookup tables, listed below, is used to provide street type, - secondary unit and direction abbreviation standards for a given set - of data. These are provided with the geocoder, but will need to be - customized for the data used. - - direction_lookup - secondary_unit_lookup - street_type_lookup - -- Additional lookup tables are required to perform matching for state - and location extraction. The state lookup is derived from the - US Postal Service standards, while the place and county subdivision - lookups are generated from the dataset. The creation statements for - the place and countysub tables are given in the INSTALL file. - - state_lookup - place_lookup - countysub_lookup - -- The use of lookup tables is intended to provide a versatile way of applying - the normalizer to data sets and localities other than the US Census TIGER - data. However, due to the need for matching based extraction in the event - of poorly formatted or incomplete address strings, assumptions are made about - the data available. Most notably the division of place and county - subdivision. For data sets without exactly two logical divisions in location - precision, code changes will be required. - -- The normalizer will perform better the more information is provided. - -- The process for normalization is roughly as follows: - - Extract the address from the beginning. - Extract the zipCode from the end. - Extract the state, using a fuzzy search if exact matching fails. - Attempt to extract the location by parsing the punctuation - of the address. - Find and remove any internal address. - If internal address was found: - Set location as everything between internal address and state. - Extract the street type from the string. - If multiple potential street types are found: - If internal address was found: - Extract the last street type that preceeds the internal address. - Else: - Extract the last street type. - If street type was found: - If a word beginning with a number follows the street type. - This indicates the street type is part of the street name, - eg. 'State Hwy 92a'. - Set street type to NULL. - Else if location not yet found: - Set location as everything between street type and state. - Extract direction prefix from start of street name. - If internal address was found: - Extract direction suffix from end of street name. - Else: - Extract direction suffix from start of location. - Set street name as everything that is not the address, direction - prefix or suffix, internal address, location, state or - zip code. - Else: - If internal address was found: - Extract direction prefix from beginning of string. - Extract direction suffix before internal address. - Set street name as everything that is not the address, direction - prefix or suffix, internal address, location, state or - zip code. - Else: - Extract direction suffix. - If direction suffix is found: - Set location as everything between direction suffix and state, - zip or end of string as appropriate. - Extract direction prefix from beginning of string. - Set street name as everything that is not the address, direction - prefix or suffix, internal address, location, state or - zip code. - Else: - Attempt to determine the location via exact comparison against - the places lookup. - Attempt to determine the location via exact comparison against - the countysub lookup. - Attempt to determine the location via fuzzy comparison against - the places lookup. - Attempt to determine the location via fuzzy comparison against - the countysub lookup. - Extract direction prefix. - Set street name as everything that is not the address, direction - prefix or suffix, internal address, location, state or - zip code. - - - -Address Geocoder: - -The goal of the address geocoder is to provide a robust means of searching -the database for a match to whatever data the user provides. To accomplish -this, the coder uses a series of checks and fallthrough cases. Starting with -the most specific combination of parameters, the algorithm works outwards -towards the most vague combination, until valid results are found. The result -of this is that the more accurate information that is provided, the faster the -algorithm will return. - -Usage: - - normalize_address('address string'); - -Support functions: - - geocode_address(cursor, address, 'dirPrefix', 'streetName', 'streetType', - 'dirSuffix', 'location', 'state', zipCode) - geocode_address_zip(cursor, address, 'dirPrefix', 'streetName', - 'streetType', 'dirSuffix', zipCode) - geocode_address_countysub_exact(cursor, address, 'dirPrefix', 'streetName', - 'streetType', 'dirSuffix', 'location', 'state') - geocode_address_countysub_fuzzy(cursor, address, 'dirPrefix', 'streetName', - 'streetType', 'dirSuffix', 'location', 'state') - geocode_address_place_exact(cursor, address, 'dirPrefix', 'streetName', - 'streetType', 'dirSuffix', 'location', 'state') - geocode_address_place_fuzzy(cursor, address, 'dirPrefix', 'streetName', - 'streetType', 'dirSuffix', 'location', 'state') - rate_attributes('dirPrefixA', 'dirPrefixB', 'streetNameA', 'streetNameB', - 'streetTypeA', 'streetTypeB', 'dirSuffixA', 'dirSuffixB') - rate_attributes('dirPrefixA', 'dirPrefixB', 'streetNameA', 'streetNameB', - 'streetTypeA', 'streetTypeB', 'dirSuffixA', 'dirSuffixB', - 'locationA', 'locationB') - location_extract_countysub_exact('partial address string', 'state abbreviation') - location_extract_countysub_fuzzy('partial address string', 'state abbreviation') - location_extract_place_exact('partial address string', 'state abbreviation') - location_extract_place_fuzzy('partial address string', 'state abbreviation') - cull_null('string') - count_words('string') - get_last_words('string') - state_extract('partial address string') - levenshtein_ignore_case('string', 'string') - interpolate_from_address(given address, from address L, to address L, - from address R, to address R, street segment) - interpolate_from_address(given address, 'from address L', 'to address L', - 'from address R', 'to address R', street segment) - includes_address(given address, from address L, to address L, - from address R, to address R) - includes_address(given address, 'from address L', 'to address L', - 'from address R', 'to address R') - -Notes: - -- The geocoder is quite dependent on the address normalizer. The direction - prefix and suffix, streetType and state are all expected to be standard - abbreviations that will match exactly to the database. - -- Either a zip code, or a location must be provided. No exception will be - thrown, but the result will be null. If the zip code or location cannot - be matched, with the other information provided, against the database - the result is null. - -- The process is as follows: - - If a zipCode is provided: - Check if the zipCode, streetName and optionally state match any roads. - If they do: - Check if the given address fits any of the roads. - If it does: - Return the matching road segment information, rating and - interpolated geographic point. - If location exactly matches a place: - Check if the place, streetName and optionally state match any roads. - If they do: - Check if the given address fits any of the roads. - If it does: - Return the matching road segment information, rating and - interpolated geographic point. - If location exactly matches a countySubdivision: - Check if the countySubdivision, streetName and optionally state - match any roads. - If they do: - Check if the given address fits any of the roads. - If it does: - Return the matching road segment information, rating and - interpolated geographic point. - If location approximately matches a place: - Check if the place, streetName and optionally state match any roads. - If they do: - Check if the given address fits any of the roads. - If it does: - Return the matching road segment information, rating and - interpolated geographic point. - If location approximately matches a countySubdivision: - Check if the countySubdivision, streetName and optionally state - match any roads. - If they do: - Check if the given address fits any of the roads. - If it does: - Return the matching road segment information, rating and - interpolated geographic point. - - -Current Issues / Known Failures: - -- If a location starts with a direction, eg. East Seattle, and no suffix - direction is given, the direction from the location will be interpreted - as the streets suffix direction. - - '18196 68th Ave East Seattle Washington' - address = 18196 - dirPrefix = NULL - streetName = '68th' - streetType = 'Ave' - dirSuffix = 'E' - location = 'Seattle' - state = 'WA' - zip = NULL - -- The last possible street type in the string is interpreted as the street type - to allow street names to contain type words. As a result, any location - containing a street type will have the type interpreted as the street type. - - '29645 7th Street SW Federal Way 98023' - address = 29645 - dirPrefix = NULL - streetName = 7th Street SW Federal - streetType = Way - dirSuffix = NULL - location = NULL - state = NULL - zip = 98023 - -- While some state misspellings will be picked up by the fuzzy searches, - misspelled or non-standard abbreviations may not be picked up, due to - the length (soundex uses an intial character plus three codeable - characters) - - '2554 E Highland Dr Seatel Wash' - address = 2554 - dirPrefix = 'E' - streetName = 'Highland' - streetType = 'Dr' - dirSuffix = NULL - location = 'Seatel Wash' - state = NULL - zip = NULL - -- If neither a location or a zip code are found by the normalizer, no search - is performed. - -- If neither street type, direction suffix nor location are given in the - address string, the street name is generally misclassified as the - location. - - '98 E Main Washington 98012' - address = 98 - dirPrefix = 'E' - streetName = NULL - streetType = NULL - dirSuffix = NULL - location = 'Main' - state = 'WA' - zip = 98012 - -- If no street type is given and the street name contains a type word, then the - type in the street name is interpreted as the street type. - - '1348 SW Orchard Seattle wa 98106' - 1348::SW:Orch::Seattle:WA:98106 - address = 1348 - dirPrefix = NULL - streetName = SW - streetType = Orch - dirSuffix = NULL - location = Seattle - state = WA - zip = 98106 - -- Misspellings of words are only handled so far as their soundex values match. - - 'Hiland' will not be matched with 'Highland' - soundex('Hiland') = 'H453' - soundex('Highland') = 'H245' - -- Missing words in location or street name are not handled. - - 'Redmond Fall' will not be matched with 'Redmond Fall City' - -- Unacceptable failure cases: - The street name is parsed out as 'West Central Park' - '500 South West Central Park Ave Chicago Illinois 60624' diff --git a/extras/tiger_geocoder/tiger_2006andbefore/create_geocode.sql b/extras/tiger_geocoder/tiger_2006andbefore/create_geocode.sql deleted file mode 100644 index a5d19887b..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/create_geocode.sql +++ /dev/null @@ -1,59 +0,0 @@ - --- Tiger is where we're going to create the functions, but we need --- the PostGIS functions/types which are in public. -SET search_path TO tiger,public; - --- Type used to pass around a normalized address between functions -DROP TYPE IF EXISTS norm_addy CASCADE; -CREATE TYPE norm_addy AS ( - address INTEGER, - preDirAbbrev VARCHAR, - streetName VARCHAR, - streetTypeAbbrev VARCHAR, - postDirAbbrev VARCHAR, - internal VARCHAR, - location VARCHAR, - stateAbbrev VARCHAR, - zip INTEGER, - parsed BOOLEAN); - --- System/General helper functions -\i utility/utmzone.sql -\i utility/cull_null.sql -\i utility/nullable_levenshtein.sql -\i utility/levenshtein_ignore_case.sql - ----- Address normalizer --- General helpers -\i normalize/end_soundex.sql -\i normalize/count_words.sql -\i normalize/state_extract.sql -\i normalize/get_last_words.sql --- Location extraction/normalization helpers -\i normalize/location_extract_countysub_exact.sql -\i normalize/location_extract_countysub_fuzzy.sql -\i normalize/location_extract_place_exact.sql -\i normalize/location_extract_place_fuzzy.sql -\i normalize/location_extract.sql --- Normalization API, called by geocode mainly. -\i normalize/normalize_address.sql -\i normalize/pprint_addy.sql - ----- Geocoder functions --- General helpers -\i geocode/rate_attributes.sql -\i geocode/includes_address.sql -\i geocode/interpolate_from_address.sql --- Actual lookups/geocoder helpers -\i geocode/geocode_address_countysub_exact.sql -\i geocode/geocode_address_countysub_fuzzy.sql -\i geocode/geocode_address_place_exact.sql -\i geocode/geocode_address_place_fuzzy.sql -\i geocode/geocode_address.sql -\i geocode/geocode_address_state.sql -\i geocode/geocode_address_zip.sql -\i geocode/geocode_zip.sql -\i geocode/geocode_location.sql --- Geocode API, called by user -\i geocode/geocode_get_point.sql -\i geocode/geocode.sql diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode.sql deleted file mode 100644 index c7ab8bad1..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode.sql +++ /dev/null @@ -1,111 +0,0 @@ -CREATE OR REPLACE FUNCTION geocode( - input VARCHAR, - OUT ADDY NORM_ADDY, - OUT GEOMOUT GEOMETRY, - OUT RATING INTEGER -) RETURNS SETOF RECORD -AS $_$ -DECLARE - result REFCURSOR; - rec RECORD; -BEGIN - - IF input IS NULL THEN - RETURN; - END IF; - - -- Pass the input string into the address normalizer - ADDY := normalize_address(input); - IF NOT ADDY.parsed THEN - RETURN; - END IF; - - OPEN result FOR SELECT * FROM geocode(ADDY); - - LOOP - FETCH result INTO rec; - - IF NOT FOUND THEN - RETURN; - END IF; - - ADDY := rec.addy; - GEOMOUT := rec.geomout; - RATING := rec.rating; - - RETURN NEXT; - END LOOP; - - RETURN; - -END; -$_$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION geocode( - IN_ADDY NORM_ADDY, - OUT ADDY NORM_ADDY, - OUT GEOMOUT GEOMETRY, - OUT RATING INTEGER -) RETURNS SETOF RECORD -AS $_$ -DECLARE - result REFCURSOR; - rec RECORD; -BEGIN - - IF NOT IN_ADDY.parsed THEN - RETURN; - END IF; - - -- Go for the full monty if we've got enough info - IF IN_ADDY.address IS NOT NULL AND - IN_ADDY.streetName IS NOT NULL AND - (IN_ADDY.zip IS NOT NULL OR IN_ADDY.stateAbbrev IS NOT NULL) THEN - - result := geocode_address(IN_ADDY); - END IF; - - -- Next best is zipcode, if we've got it - IF result IS NULL AND IN_ADDY.zip IS NOT NULL THEN - result := geocode_zip(IN_ADDY); - END IF; - - -- No zip code, try state/location, need both or we'll get too much stuffs. - IF result IS NULL AND IN_ADDY.stateAbbrev IS NOT NULL AND IN_ADDY.location IS NOT NULL THEN - result := geocode_location(IN_ADDY); - END IF; - - IF result IS NULL THEN - RETURN; - END IF; - - ADDY.address := IN_ADDY.address; - ADDY.internal := IN_ADDY.internal; - - LOOP - FETCH result INTO rec; - - IF NOT FOUND THEN - RETURN; - END IF; - - ADDY.preDirAbbrev := rec.fedirp; - ADDY.streetName := rec.fename; - ADDY.streetTypeAbbrev := rec.fetype; - ADDY.postDirAbbrev := rec.fedirs; - ADDY.location := rec.place; - ADDY.stateAbbrev := rec.state; - ADDY.zip := rec.zip; - ADDY.parsed := TRUE; - - GEOMOUT := rec.address_geom; - RATING := rec.rating; - - RETURN NEXT; - END LOOP; - - RETURN; - -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address.sql deleted file mode 100644 index c17f26cae..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address.sql +++ /dev/null @@ -1,94 +0,0 @@ --- geocode(cursor, address, directionPrefix, streetName, --- streetTypeAbbreviation, directionSuffix, location, stateAbbreviation, --- zipCode) -CREATE OR REPLACE FUNCTION geocode_address( - parsed NORM_ADDY -) RETURNS REFCURSOR -AS $_$ -DECLARE - result REFCURSOR; - tempString VARCHAR; - ziplookup RECORD; -BEGIN - -- The first step is to determine what weve been given, and if its enough. - IF parsed.address IS NULL THEN - -- The address is manditory. - -- Without it, wed be wandering into strangers homes all the time. - RETURN NULL; - END IF; - - IF parsed.streetName IS NULL THEN - -- A street name must be given. Think about it. - RETURN NULL; - END IF; - - IF parsed.zip IS NOT NULL THEN - -- If the zip code is given, it is the most useful way to narrow the - -- search. We will try it first, and if no results match, we will move - -- on to a location search. There is no fuzzy searching on zip codes. - result := geocode_address_zip(result, parsed); - IF result IS NOT NULL THEN - RETURN result; - END IF; - -- If we weren't able to find one using the zip code, but the zip code - -- exists, and location is null, then fill in the location and/or state - -- based on the zip code so that the location lookup has a chance. - IF parsed.stateAbbrev IS NULL OR parsed.location IS NULL THEN - SELECT INTO ziplookup * FROM zip_lookup_base JOIN state_lookup ON (state = name) WHERE zip = parsed.zip; - IF FOUND THEN - parsed.stateAbbrev := coalesce(parsed.stateAbbrev,ziplookup.abbrev); - parsed.location := coalesce(parsed.location,ziplookup.city); - END IF; - END IF; - END IF; - - -- After now, the location becomes manditory. - IF parsed.location IS NOT NULL THEN - -- location may be useful, it may not. The first step is to determine if - -- there are any potenial matches in the place and countysub fields. - -- This is done against the lookup tables, and will save us time on much - -- larger queries if they dont match. - tempString := location_extract_place_exact(parsed.location, parsed.stateAbbrev); - IF tempString IS NOT NULL THEN - result := geocode_address_place_exact(result, parsed); - IF result IS NOT NULL THEN - RETURN result; - END IF; - END IF; - - tempString := location_extract_countysub_exact(parsed.location, parsed.stateAbbrev); - IF tempString IS NOT NULL THEN - result := geocode_address_countysub_exact(result, parsed); - IF result IS NOT NULL THEN - RETURN result; - END IF; - END IF; - - tempString := location_extract_place_fuzzy(parsed.location, parsed.stateAbbrev); - IF tempString IS NOT NULL THEN - result := geocode_address_place_fuzzy(result, parsed); - IF result IS NOT NULL THEN - RETURN result; - END IF; - END IF; - - tempString := location_extract_countysub_fuzzy(parsed.location, parsed.stateAbbrev); - IF tempString IS NOT NULL THEN - result := geocode_address_countysub_fuzzy(result, parsed); - IF result IS NOT NULL THEN - RETURN result; - END IF; - END IF; - END IF; - - -- Try with just the state if we can't find the location - IF parsed.stateAbbrev IS NOT NULL THEN - result := geocode_address_state(result, parsed); - IF result IS NOT NULL THEN - RETURN result; - END IF; - END IF; - - RETURN NULL; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_countysub_exact.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_countysub_exact.sql deleted file mode 100644 index 4c866f8a3..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_countysub_exact.sql +++ /dev/null @@ -1,161 +0,0 @@ -CREATE OR REPLACE FUNCTION geocode_address_countysub_exact( - result REFCURSOR, - parsed NORM_ADDY -) RETURNS REFCURSOR -AS $_$ -DECLARE - result REFCURSOR; - tempString VARCHAR; - tempInt INTEGER; -BEGIN - IF parsed.location IS NULL THEN - -- location is manditory. This is the location geocoder after all. - RETURN NULL; - END IF; - - -- Check to see if the road name can be matched. - IF parsed.stateAbbrev IS NOT NULL THEN - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE parsed.location = tiger_geocode_roads.cousub - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state; - ELSE - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE parsed.location = tiger_geocode_roads.cousub - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename); - END IF; - - IF tempInt = 0 THEN - RETURN NULL; - ELSE - -- The road name matches, now we check to see if the addresses match - IF parsed.stateAbbrev IS NOT NULL THEN - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE parsed.location = tiger_geocode_roads.cousub - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state - ) AS subquery, roads_local - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.tlid = roads_local.tlid; - ELSE - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE parsed.location = tiger_geocode_roads.cousub - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, roads_local - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.tlid = roads_local.tlid; - END IF; - - IF tempInt = 0 THEN - RETURN NULL; - ELSE - IF parsed.stateAbbrev IS NOT NULL THEN - OPEN result FOR - SELECT - roads_local.fedirp as fedirp, - roads_local.fename as fename, - roads_local.fetype as fetype, - roads_local.fedirs as fedirs, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN coalesce(pl.name,zipl.city,csl.name,col.name) ELSE coalesce(pr.name,zipr.city,csr.name,cor.name) END as place, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN sl.abbrev ELSE sr.abbrev END as state, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN zipl ELSE zipr END as zip, - interpolate_from_address(parsed.address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom, - subquery.rating as rating - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs, parsed.location, - tiger_geocode_roads.cousub) as rating - FROM tiger_geocode_roads - WHERE parsed.location = tiger_geocode_roads.cousub - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state - ) AS subquery - JOIN roads_local ON (subquery.tlid = roads_local.tlid) - JOIN state_lookup sl ON (roads_local.statel = sl.st_code) - JOIN state_lookup sr ON (roads_local.stater = sr.st_code) - LEFT JOIN place_lookup pl ON (roads_local.statel = pl.st_code AND roads_local.placel = pl.pl_code) - LEFT JOIN place_lookup pr ON (roads_local.stater = pr.st_code AND roads_local.placer = pr.pl_code) - LEFT JOIN county_lookup col ON (roads_local.statel = col.st_code AND roads_local.countyl = col.co_code) - LEFT JOIN county_lookup cor ON (roads_local.stater = cor.st_code AND roads_local.countyr = cor.co_code) - LEFT JOIN countysub_lookup csl ON (roads_local.statel = csl.st_code AND roads_local.countyl = csl.co_code AND roads_local.cousubl = csl.cs_code) - LEFT JOIN countysub_lookup csr ON (roads_local.stater = csr.st_code AND roads_local.countyr = csr.co_code AND roads_local.cousubr = csr.cs_code) - LEFT JOIN zip_lookup_base zipl ON (roads_local.zipl = zipl.zip) - LEFT JOIN zip_lookup_base zipr ON (roads_local.zipr = zipr.zip) - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - ORDER BY subquery.rating; - RETURN result; - ELSE - OPEN result FOR - SELECT - roads_local.fedirp as fedirp, - roads_local.fename as fename, - roads_local.fetype as fetype, - roads_local.fedirs as fedirs, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN coalesce(pl.name,zipl.city,csl.name,col.name) ELSE coalesce(pr.name,zipr.city,csr.name,cor.name) END as place, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN sl.abbrev ELSE sr.abbrev END as state, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN zipl ELSE zipr END as zip, - interpolate_from_address(parsed.address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom, - subquery.rating as rating - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs, parsed.location, - tiger_geocode_roads.cousub) as rating - FROM tiger_geocode_roads - WHERE parsed.location = tiger_geocode_roads.cousub - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery - JOIN roads_local ON (subquery.tlid = roads_local.tlid) - JOIN state_lookup sl ON (roads_local.statel = sl.st_code) - JOIN state_lookup sr ON (roads_local.stater = sr.st_code) - LEFT JOIN place_lookup pl ON (roads_local.statel = pl.st_code AND roads_local.placel = pl.pl_code) - LEFT JOIN place_lookup pr ON (roads_local.stater = pr.st_code AND roads_local.placer = pr.pl_code) - LEFT JOIN county_lookup col ON (roads_local.statel = col.st_code AND roads_local.countyl = col.co_code) - LEFT JOIN county_lookup cor ON (roads_local.stater = cor.st_code AND roads_local.countyr = cor.co_code) - LEFT JOIN countysub_lookup csl ON (roads_local.statel = csl.st_code AND roads_local.countyl = csl.co_code AND roads_local.cousubl = csl.cs_code) - LEFT JOIN countysub_lookup csr ON (roads_local.stater = csr.st_code AND roads_local.countyr = csr.co_code AND roads_local.cousubr = csr.cs_code) - LEFT JOIN zip_lookup_base zipl ON (roads_local.zipl = zipl.zip) - LEFT JOIN zip_lookup_base zipr ON (roads_local.zipr = zipr.zip) - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - ORDER BY subquery.rating; - RETURN result; - END IF; - END IF; - END IF; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_countysub_fuzzy.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_countysub_fuzzy.sql deleted file mode 100644 index 89c70038a..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_countysub_fuzzy.sql +++ /dev/null @@ -1,162 +0,0 @@ -CREATE OR REPLACE FUNCTION geocode_address_countysub_fuzzy( - result REFCURSOR, - parsed NORM_ADDY -) RETURNS REFCURSOR -AS $_$ -DECLARE - result REFCURSOR; - tempString VARCHAR; - tempInt INTEGER; -BEGIN - -- The first step is to determine what weve been given, and if its enough. - IF parsed.location IS NULL THEN - -- location is manditory. This is the location geocoder after all. - RETURN NULL; - END IF; - - -- Check to see if the road name can be matched. - IF parsed.stateAbbrev IS NOT NULL THEN - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE soundex(parsed.location) = soundex(tiger_geocode_roads.cousub) - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state; - ELSE - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE soundex(parsed.location) = soundex(tiger_geocode_roads.cousub) - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename); - END IF; - - IF tempInt = 0 THEN - RETURN NULL; - END IF; - - -- The road name matches, now we check to see if the addresses match - IF parsed.stateAbbrev IS NOT NULL THEN - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE soundex(parsed.location) = soundex(tiger_geocode_roads.cousub) - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state - ) AS subquery, roads_local - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.tlid = roads_local.tlid; - ELSE - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE soundex(parsed.location) = soundex(tiger_geocode_roads.cousub) - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, roads_local - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.tlid = roads_local.tlid; - END IF; - - IF tempInt = 0 THEN - RETURN NULL; - END IF; - - IF parsed.stateAbbrev IS NOT NULL THEN - OPEN result FOR - SELECT - roads_local.fedirp as fedirp, - roads_local.fename as fename, - roads_local.fetype as fetype, - roads_local.fedirs as fedirs, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN coalesce(pl.name,zipl.city,csl.name,col.name) ELSE coalesce(pr.name,zipr.city,csr.name,cor.name) END as place, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN sl.abbrev ELSE sr.abbrev END as state, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN zipl ELSE zipr END as zip, - interpolate_from_address(parsed.address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom, - subquery.rating as rating - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs, parsed.location, - tiger_geocode_roads.cousub) as rating - FROM tiger_geocode_roads - WHERE soundex(parsed.location) = soundex(tiger_geocode_roads.cousub) - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state - ) AS subquery - JOIN roads_local ON (subquery.tlid = roads_local.tlid) - JOIN state_lookup sl ON (roads_local.statel = sl.st_code) - JOIN state_lookup sr ON (roads_local.stater = sr.st_code) - LEFT JOIN place_lookup pl ON (roads_local.statel = pl.st_code AND roads_local.placel = pl.pl_code) - LEFT JOIN place_lookup pr ON (roads_local.stater = pr.st_code AND roads_local.placer = pr.pl_code) - LEFT JOIN county_lookup col ON (roads_local.statel = col.st_code AND roads_local.countyl = col.co_code) - LEFT JOIN county_lookup cor ON (roads_local.stater = cor.st_code AND roads_local.countyr = cor.co_code) - LEFT JOIN countysub_lookup csl ON (roads_local.statel = csl.st_code AND roads_local.countyl = csl.co_code AND roads_local.cousubl = csl.cs_code) - LEFT JOIN countysub_lookup csr ON (roads_local.stater = csr.st_code AND roads_local.countyr = csr.co_code AND roads_local.cousubr = csr.cs_code) - LEFT JOIN zip_lookup_base zipl ON (roads_local.zipl = zipl.zip) - LEFT JOIN zip_lookup_base zipr ON (roads_local.zipr = zipr.zip) - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - ORDER BY subquery.rating; - RETURN result; - ELSE - OPEN result FOR - SELECT - roads_local.fedirp as fedirp, - roads_local.fename as fename, - roads_local.fetype as fetype, - roads_local.fedirs as fedirs, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN coalesce(pl.name,zipl.city,csl.name,col.name) ELSE coalesce(pr.name,zipr.city,csr.name,cor.name) END as place, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN sl.abbrev ELSE sr.abbrev END as state, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN zipl ELSE zipr END as zip, - interpolate_from_address(parsed.address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom, - subquery.rating as rating - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs, parsed.location, - tiger_geocode_roads.cousub) as rating - FROM tiger_geocode_roads - WHERE soundex(parsed.location) = soundex(tiger_geocode_roads.cousub) - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery - JOIN roads_local ON (subquery.tlid = roads_local.tlid) - JOIN state_lookup sl ON (roads_local.statel = sl.st_code) - JOIN state_lookup sr ON (roads_local.stater = sr.st_code) - LEFT JOIN place_lookup pl ON (roads_local.statel = pl.st_code AND roads_local.placel = pl.pl_code) - LEFT JOIN place_lookup pr ON (roads_local.stater = pr.st_code AND roads_local.placer = pr.pl_code) - LEFT JOIN county_lookup col ON (roads_local.statel = col.st_code AND roads_local.countyl = col.co_code) - LEFT JOIN county_lookup cor ON (roads_local.stater = cor.st_code AND roads_local.countyr = cor.co_code) - LEFT JOIN countysub_lookup csl ON (roads_local.statel = csl.st_code AND roads_local.countyl = csl.co_code AND roads_local.cousubl = csl.cs_code) - LEFT JOIN countysub_lookup csr ON (roads_local.stater = csr.st_code AND roads_local.countyr = csr.co_code AND roads_local.cousubr = csr.cs_code) - LEFT JOIN zip_lookup_base zipl ON (roads_local.zipl = zipl.zip) - LEFT JOIN zip_lookup_base zipr ON (roads_local.zipr = zipr.zip) - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - ORDER BY subquery.rating; - RETURN result; - END IF; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_place_exact.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_place_exact.sql deleted file mode 100644 index 29c3dc013..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_place_exact.sql +++ /dev/null @@ -1,156 +0,0 @@ -CREATE OR REPLACE FUNCTION geocode_address_place_exact( - result REFCURSOR, - parsed NORM_ADDY -) RETURNS REFCURSOR -AS $_$ -DECLARE - result REFCURSOR; - tempString VARCHAR; - tempInt INTEGER; -BEGIN - -- Check to see if the road name can be matched. - IF parsed.stateAbbrev IS NOT NULL THEN - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE parsed.location = tiger_geocode_roads.place - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state; - ELSE - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE parsed.location = tiger_geocode_roads.place - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename); - END IF; - - IF tempInt = 0 THEN - RETURN NULL; - END IF; - - -- The road name matches, now we check to see if the addresses match - IF parsed.stateAbbrev IS NOT NULL THEN - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE parsed.location = tiger_geocode_roads.place - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state - ) AS subquery, roads_local - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.tlid = roads_local.tlid; - ELSE - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE parsed.location = tiger_geocode_roads.place - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, roads_local - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.tlid = roads_local.tlid; - END IF; - - IF tempInt = 0 THEN - return NULL; - END IF; - - IF parsed.stateAbbrev IS NOT NULL THEN - OPEN result FOR - SELECT - roads_local.fedirp as fedirp, - roads_local.fename as fename, - roads_local.fetype as fetype, - roads_local.fedirs as fedirs, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN coalesce(pl.name,zipl.city,csl.name,col.name) ELSE coalesce(pr.name,zipr.city,csr.name,cor.name) END as place, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN sl.abbrev ELSE sr.abbrev END as state, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN zipl ELSE zipr END as zip, - interpolate_from_address(parsed.address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom, - subquery.rating as rating - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs, parsed.location, - tiger_geocode_roads.place) as rating - FROM tiger_geocode_roads - WHERE parsed.location = tiger_geocode_roads.place - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state - ) AS subquery - JOIN roads_local ON (subquery.tlid = roads_local.tlid) - JOIN state_lookup sl ON (roads_local.statel = sl.st_code) - JOIN state_lookup sr ON (roads_local.stater = sr.st_code) - LEFT JOIN place_lookup pl ON (roads_local.statel = pl.st_code AND roads_local.placel = pl.pl_code) - LEFT JOIN place_lookup pr ON (roads_local.stater = pr.st_code AND roads_local.placer = pr.pl_code) - LEFT JOIN county_lookup col ON (roads_local.statel = col.st_code AND roads_local.countyl = col.co_code) - LEFT JOIN county_lookup cor ON (roads_local.stater = cor.st_code AND roads_local.countyr = cor.co_code) - LEFT JOIN countysub_lookup csl ON (roads_local.statel = csl.st_code AND roads_local.countyl = csl.co_code AND roads_local.cousubl = csl.cs_code) - LEFT JOIN countysub_lookup csr ON (roads_local.stater = csr.st_code AND roads_local.countyr = csr.co_code AND roads_local.cousubr = csr.cs_code) - LEFT JOIN zip_lookup_base zipl ON (roads_local.zipl = zipl.zip) - LEFT JOIN zip_lookup_base zipr ON (roads_local.zipr = zipr.zip) - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - ORDER BY subquery.rating; - RETURN result; - ELSE - OPEN result FOR - SELECT - roads_local.fedirp as fedirp, - roads_local.fename as fename, - roads_local.fetype as fetype, - roads_local.fedirs as fedirs, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN coalesce(pl.name,zipl.city,csl.name,col.name) ELSE coalesce(pr.name,zipr.city,csr.name,cor.name) END as place, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN sl.abbrev ELSE sr.abbrev END as state, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN zipl ELSE zipr END as zip, - interpolate_from_address(parsed.address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom, - subquery.rating as rating - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs, parsed.location, - tiger_geocode_roads.place) as rating - FROM tiger_geocode_roads - WHERE parsed.location = tiger_geocode_roads.place - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery - JOIN roads_local ON (subquery.tlid = roads_local.tlid) - JOIN state_lookup sl ON (roads_local.statel = sl.st_code) - JOIN state_lookup sr ON (roads_local.stater = sr.st_code) - LEFT JOIN place_lookup pl ON (roads_local.statel = pl.st_code AND roads_local.placel = pl.pl_code) - LEFT JOIN place_lookup pr ON (roads_local.stater = pr.st_code AND roads_local.placer = pr.pl_code) - LEFT JOIN county_lookup col ON (roads_local.statel = col.st_code AND roads_local.countyl = col.co_code) - LEFT JOIN county_lookup cor ON (roads_local.stater = cor.st_code AND roads_local.countyr = cor.co_code) - LEFT JOIN countysub_lookup csl ON (roads_local.statel = csl.st_code AND roads_local.countyl = csl.co_code AND roads_local.cousubl = csl.cs_code) - LEFT JOIN countysub_lookup csr ON (roads_local.stater = csr.st_code AND roads_local.countyr = csr.co_code AND roads_local.cousubr = csr.cs_code) - LEFT JOIN zip_lookup_base zipl ON (roads_local.zipl = zipl.zip) - LEFT JOIN zip_lookup_base zipr ON (roads_local.zipr = zipr.zip) - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - ORDER BY subquery.rating; - RETURN result; - END IF; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_place_fuzzy.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_place_fuzzy.sql deleted file mode 100644 index f4334f386..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_place_fuzzy.sql +++ /dev/null @@ -1,155 +0,0 @@ -CREATE OR REPLACE FUNCTION geocode_address_place_fuzzy( - result REFCURSOR, - parsed NORM_ADDY -) RETURNS REFCURSOR -AS $_$ -DECLARE - tempString VARCHAR; - tempInt INTEGER; -BEGIN - -- Check to see if the road name can be matched. - IF parsed.stateAbbrev IS NOT NULL THEN - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE soundex(parsed.location) = soundex(tiger_geocode_roads.place) - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state; - ELSE - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE soundex(parsed.location) = soundex(tiger_geocode_roads.place) - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename); - END IF; - - IF tempInt = 0 THEN - RETURN NULL; - END IF; - - -- The road name matches, now we check to see if the addresses match - IF parsed.stateAbbrev IS NOT NULL THEN - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE soundex(parsed.location) = soundex(tiger_geocode_roads.place) - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state - ) AS subquery, roads_local - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.tlid = roads_local.tlid; - ELSE - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE soundex(parsed.location) = soundex(tiger_geocode_roads.place) - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, roads_local - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.tlid = roads_local.tlid; - END IF; - - IF tempInt = 0 THEN - RETURN NULL; - END IF; - - IF parsed.stateAbbrev IS NOT NULL THEN - OPEN result FOR - SELECT - roads_local.fedirp as fedirp, - roads_local.fename as fename, - roads_local.fetype as fetype, - roads_local.fedirs as fedirs, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN coalesce(pl.name,zipl.city,csl.name,col.name) ELSE coalesce(pr.name,zipr.city,csr.name,cor.name) END as place, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN sl.abbrev ELSE sr.abbrev END as state, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN zipl ELSE zipr END as zip, - interpolate_from_address(parsed.address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom, - subquery.rating as rating - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs, parsed.location, - tiger_geocode_roads.place) as rating - FROM tiger_geocode_roads - WHERE soundex(parsed.location) = soundex(tiger_geocode_roads.place) - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state - ) AS subquery - JOIN roads_local ON (subquery.tlid = roads_local.tlid) - JOIN state_lookup sl ON (roads_local.statel = sl.st_code) - JOIN state_lookup sr ON (roads_local.stater = sr.st_code) - LEFT JOIN place_lookup pl ON (roads_local.statel = pl.st_code AND roads_local.placel = pl.pl_code) - LEFT JOIN place_lookup pr ON (roads_local.stater = pr.st_code AND roads_local.placer = pr.pl_code) - LEFT JOIN county_lookup col ON (roads_local.statel = col.st_code AND roads_local.countyl = col.co_code) - LEFT JOIN county_lookup cor ON (roads_local.stater = cor.st_code AND roads_local.countyr = cor.co_code) - LEFT JOIN countysub_lookup csl ON (roads_local.statel = csl.st_code AND roads_local.countyl = csl.co_code AND roads_local.cousubl = csl.cs_code) - LEFT JOIN countysub_lookup csr ON (roads_local.stater = csr.st_code AND roads_local.countyr = csr.co_code AND roads_local.cousubr = csr.cs_code) - LEFT JOIN zip_lookup_base zipl ON (roads_local.zipl = zipl.zip) - LEFT JOIN zip_lookup_base zipr ON (roads_local.zipr = zipr.zip) - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - ORDER BY subquery.rating; - RETURN result; - ELSE - OPEN result FOR - SELECT - roads_local.fedirp as fedirp, - roads_local.fename as fename, - roads_local.fetype as fetype, - roads_local.fedirs as fedirs, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN coalesce(pl.name,zipl.city,csl.name,col.name) ELSE coalesce(pr.name,zipr.city,csr.name,cor.name) END as place, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN sl.abbrev ELSE sr.abbrev END as state, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN zipl ELSE zipr END as zip, - interpolate_from_address(parsed.address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom, - subquery.rating as rating - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs, parsed.location, - tiger_geocode_roads.place) as rating - FROM tiger_geocode_roads - WHERE soundex(parsed.location) = soundex(tiger_geocode_roads.place) - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery - JOIN roads_local ON (subquery.tlid = roads_local.tlid) - JOIN state_lookup sl ON (roads_local.statel = sl.st_code) - JOIN state_lookup sr ON (roads_local.stater = sr.st_code) - LEFT JOIN place_lookup pl ON (roads_local.statel = pl.st_code AND roads_local.placel = pl.pl_code) - LEFT JOIN place_lookup pr ON (roads_local.stater = pr.st_code AND roads_local.placer = pr.pl_code) - LEFT JOIN county_lookup col ON (roads_local.statel = col.st_code AND roads_local.countyl = col.co_code) - LEFT JOIN county_lookup cor ON (roads_local.stater = cor.st_code AND roads_local.countyr = cor.co_code) - LEFT JOIN countysub_lookup csl ON (roads_local.statel = csl.st_code AND roads_local.countyl = csl.co_code AND roads_local.cousubl = csl.cs_code) - LEFT JOIN countysub_lookup csr ON (roads_local.stater = csr.st_code AND roads_local.countyr = csr.co_code AND roads_local.cousubr = csr.cs_code) - LEFT JOIN zip_lookup_base zipl ON (roads_local.zipl = zipl.zip) - LEFT JOIN zip_lookup_base zipr ON (roads_local.zipr = zipr.zip) - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - ORDER BY subquery.rating; - RETURN result; - END IF; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_state.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_state.sql deleted file mode 100644 index d21585161..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_state.sql +++ /dev/null @@ -1,81 +0,0 @@ -CREATE OR REPLACE FUNCTION geocode_address_state( - result REFCURSOR, - parsed NORM_ADDY -) RETURNS REFCURSOR -AS $_$ -DECLARE - tempInt INTEGER; -BEGIN - -- Check to see if the road name can be matched. - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state; - - IF tempInt = 0 THEN - RETURN NULL; - END IF; - - -- The road name matches, now we check to see if the addresses match - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state - ) AS subquery, roads_local - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.tlid = roads_local.tlid; - - IF tempInt = 0 THEN - return NULL; - END IF; - - OPEN result FOR - SELECT - roads_local.fedirp as fedirp, - roads_local.fename as fename, - roads_local.fetype as fetype, - roads_local.fedirs as fedirs, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN coalesce(pl.name,zipl.city,csl.name,col.name) ELSE coalesce(pr.name,zipr.city,csr.name,cor.name) END as place, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN sl.abbrev ELSE sr.abbrev END as state, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN zipl ELSE zipr END as zip, - interpolate_from_address(parsed.address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom, - subquery.rating as rating - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - AND parsed.stateAbbrev = tiger_geocode_roads.state - ) AS subquery - JOIN roads_local ON (subquery.tlid = roads_local.tlid) - JOIN state_lookup sl ON (roads_local.statel = sl.st_code) - JOIN state_lookup sr ON (roads_local.stater = sr.st_code) - LEFT JOIN place_lookup pl ON (roads_local.statel = pl.st_code AND roads_local.placel = pl.pl_code) - LEFT JOIN place_lookup pr ON (roads_local.stater = pr.st_code AND roads_local.placer = pr.pl_code) - LEFT JOIN county_lookup col ON (roads_local.statel = col.st_code AND roads_local.countyl = col.co_code) - LEFT JOIN county_lookup cor ON (roads_local.stater = cor.st_code AND roads_local.countyr = cor.co_code) - LEFT JOIN countysub_lookup csl ON (roads_local.statel = csl.st_code AND roads_local.countyl = csl.co_code AND roads_local.cousubl = csl.cs_code) - LEFT JOIN countysub_lookup csr ON (roads_local.stater = csr.st_code AND roads_local.countyr = csr.co_code AND roads_local.cousubr = csr.cs_code) - LEFT JOIN zip_lookup_base zipl ON (roads_local.zipl = zipl.zip) - LEFT JOIN zip_lookup_base zipr ON (roads_local.zipr = zipr.zip) - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - ORDER BY subquery.rating; - RETURN result; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_zip.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_zip.sql deleted file mode 100644 index ac72a9ed3..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_address_zip.sql +++ /dev/null @@ -1,83 +0,0 @@ -CREATE OR REPLACE FUNCTION geocode_address_zip( - result REFCURSOR, - parsed NORM_ADDY -) RETURNS REFCURSOR -AS $_$ -DECLARE - tempString VARCHAR; - tempInt INTEGER; -BEGIN - -- Check to see if the road name can be matched. - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE parsed.zip = tiger_geocode_roads.zip - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename); - - IF tempInt = 0 THEN - RETURN NULL; - END IF; - - -- The road name matches, now we check to see if the addresses match - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE parsed.zip = tiger_geocode_roads.zip - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, roads_local - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.tlid = roads_local.tlid; - - IF tempInt = 0 THEN - RETURN NULL; - END IF; - - OPEN result FOR - SELECT - roads_local.fedirp as fedirp, - roads_local.fename as fename, - roads_local.fetype as fetype, - roads_local.fedirs as fedirs, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN coalesce(pl.name,zipl.city,csl.name,col.name) ELSE coalesce(pr.name,zipr.city,csr.name,cor.name) END as place, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN sl.abbrev ELSE sr.abbrev END as state, - CASE WHEN (parsed.address % 2) = roads_local.fraddl - OR (parsed.address % 2) = roads_local.toaddl - THEN zipl ELSE zipr END as zip, - interpolate_from_address(parsed.address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom, - subquery.rating as rating - FROM ( - SELECT *, rate_attributes(parsed.preDirAbbrev, tiger_geocode_roads.fedirp, - parsed.streetName, tiger_geocode_roads.fename, parsed.streetTypeAbbrev, - tiger_geocode_roads.fetype, parsed.postDirAbbrev, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE parsed.zip = tiger_geocode_roads.zip - AND soundex(parsed.streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery - JOIN roads_local ON (subquery.tlid = roads_local.tlid) - JOIN state_lookup sl ON (roads_local.statel = sl.st_code) - JOIN state_lookup sr ON (roads_local.stater = sr.st_code) - LEFT JOIN place_lookup pl ON (roads_local.statel = pl.st_code AND roads_local.placel = pl.pl_code) - LEFT JOIN place_lookup pr ON (roads_local.stater = pr.st_code AND roads_local.placer = pr.pl_code) - LEFT JOIN county_lookup col ON (roads_local.statel = col.st_code AND roads_local.countyl = col.co_code) - LEFT JOIN county_lookup cor ON (roads_local.stater = cor.st_code AND roads_local.countyr = cor.co_code) - LEFT JOIN countysub_lookup csl ON (roads_local.statel = csl.st_code AND roads_local.countyl = csl.co_code AND roads_local.cousubl = csl.cs_code) - LEFT JOIN countysub_lookup csr ON (roads_local.stater = csr.st_code AND roads_local.countyr = csr.co_code AND roads_local.cousubr = csr.cs_code) - LEFT JOIN zip_lookup_base zipl ON (roads_local.zipl = zipl.zip) - LEFT JOIN zip_lookup_base zipr ON (roads_local.zipr = zipr.zip) - WHERE includes_address(parsed.address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - ORDER BY subquery.rating; - - RETURN result; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_get_point.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_get_point.sql deleted file mode 100644 index eee323188..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_get_point.sql +++ /dev/null @@ -1,10 +0,0 @@ -CREATE OR REPLACE FUNCTION geocode_get_point(VARCHAR) RETURNS GEOMETRY -AS $_$ -DECLARE - ans RECORD; -BEGIN - ans := geocode(NULL, $1); - - RETURN centroid(ans.geom); -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_location.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_location.sql deleted file mode 100644 index 44d1a6a81..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_location.sql +++ /dev/null @@ -1,67 +0,0 @@ -CREATE OR REPLACE FUNCTION geocode_location( - parsed NORM_ADDY -) RETURNS REFCURSOR -AS $_$ -DECLARE - result REFCURSOR; - tempString VARCHAR; - tempInt INTEGER; -BEGIN - -- Try to match the city/state to a zipcode first - SELECT INTO tempInt count(*) - FROM zip_lookup_base zip - JOIN state_lookup sl ON (zip.state = sl.name) - JOIN zt99_d00 zl ON (zip.zip = zl.zcta::integer) - WHERE soundex(zip.city) = soundex(parsed.location) and sl.abbrev = parsed.stateAbbrev; - - -- If that worked, just use the zipcode lookup - IF tempInt > 0 THEN - OPEN result FOR - SELECT - NULL::varchar(2) as fedirp, - NULL::varchar(30) as fename, - NULL::varchar(4) as fetype, - NULL::varchar(2) as fedirs, - coalesce(zip.city) as place, - sl.abbrev as state, - parsed.zip as zip, - centroid(wkb_geometry) as address_geom, - 100::integer + levenshtein_ignore_case(coalesce(zip.city), parsed.location) as rating - FROM - zip_lookup_base zip - JOIN state_lookup sl on (zip.state = sl.name) - JOIN zt99_d00 zl ON (zip.zip = zl.zcta::integer) - WHERE - soundex(zip.city) = soundex(parsed.location) and sl.abbrev = parsed.stateAbbrev; - - RETURN result; - END IF; - - -- Try to match the city/state to a place next - SELECT INTO tempInt count(*) - FROM pl99_d00 pl - JOIN state_lookup sl ON (pl.state::integer = sl.st_code) - WHERE soundex(pl.name) = soundex(parsed.location) and sl.abbrev = parsed.stateAbbrev; - - -- If that worked then use it - IF tempInt > 0 THEN - OPEN result FOR - SELECT - NULL::varchar(2) as fedirp, - NULL::varchar(30) as fename, - NULL::varchar(4) as fetype, - NULL::varchar(2) as fedirs, - pl.name as place, - sl.abbrev as state, - NULL::integer as zip, - centroid(wkb_geometry) as address_geom, - 100::integer + levenshtein_ignore_case(coalesce(zip.city), parsed.location) as rating - FROM pl99_d00 pl - JOIN state_lookup sl ON (pl.state::integer = sl.st_code) - WHERE soundex(pl.name) = soundex(parsed.location) and sl.abbrev = parsed.stateAbbrev; - - RETURN result; - END IF; - RETURN result; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_zip.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_zip.sql deleted file mode 100644 index 85a86519f..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/geocode_zip.sql +++ /dev/null @@ -1,41 +0,0 @@ -CREATE OR REPLACE FUNCTION geocode_zip( - parsed NORM_ADDY -) RETURNS REFCURSOR -AS $_$ -DECLARE - result REFCURSOR; - tempString VARCHAR; - tempInt INTEGER; -BEGIN - -- Check to see if the road name can be matched. - SELECT INTO tempInt count(*) - FROM zip_lookup_base zip - JOIN state_lookup sl on (zip.state = sl.name) - JOIN zt99_d00 zl ON (zip.zip = zl.zcta::integer) - WHERE zip = parsed.zip; - - IF tempInt = 0 THEN - RETURN NULL; - END IF; - - OPEN result FOR - SELECT - NULL::varchar(2) as fedirp, - NULL::varchar(30) as fename, - NULL::varchar(4) as fetype, - NULL::varchar(2) as fedirs, - coalesce(zip.city) as place, - sl.abbrev as state, - parsed.zip as zip, - centroid(wkb_geometry) as address_geom, - 100::integer as rating - FROM - zip_lookup_base zip - JOIN state_lookup sl on (zip.state = sl.name) - JOIN zt99_d00 zl ON (zip.zip = zl.zcta::integer) - WHERE - zip.zip = parsed.zip; - - RETURN result; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/includes_address.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/includes_address.sql deleted file mode 100644 index df962c58b..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/includes_address.sql +++ /dev/null @@ -1,93 +0,0 @@ --- This function requires the addresses to be grouped, such that the second and --- third arguments are from one side of the street, and the fourth and fifth --- from the other. -CREATE OR REPLACE FUNCTION includes_address( - given_address INTEGER, - addr1 INTEGER, - addr2 INTEGER, - addr3 INTEGER, - addr4 INTEGER -) RETURNS BOOLEAN -AS $_$ -DECLARE - lmaxaddr INTEGER := -1; - rmaxaddr INTEGER := -1; - lminaddr INTEGER := -1; - rminaddr INTEGER := -1; - maxaddr INTEGER := -1; - minaddr INTEGER := -1; - verbose BOOLEAN := false; -BEGIN - IF addr1 IS NOT NULL THEN - maxaddr := addr1; - minaddr := addr1; - lmaxaddr := addr1; - lminaddr := addr1; - END IF; - - IF addr2 IS NOT NULL THEN - IF addr2 < minaddr OR minaddr = -1 THEN - minaddr := addr2; - END IF; - IF addr2 > maxaddr OR maxaddr = -1 THEN - maxaddr := addr2; - END IF; - IF addr2 > lmaxaddr OR lmaxaddr = -1 THEN - lmaxaddr := addr2; - END IF; - IF addr2 < lminaddr OR lminaddr = -1 THEN - lminaddr := addr2; - END IF; - END IF; - - IF addr3 IS NOT NULL THEN - IF addr3 < minaddr OR minaddr = -1 THEN - minaddr := addr3; - END IF; - IF addr3 > maxaddr OR maxaddr = -1 THEN - maxaddr := addr3; - END IF; - rmaxaddr := addr3; - rminaddr := addr3; - END IF; - - IF addr4 IS NOT NULL THEN - IF addr4 < minaddr OR minaddr = -1 THEN - minaddr := addr4; - END IF; - IF addr4 > maxaddr OR maxaddr = -1 THEN - maxaddr := addr4; - END IF; - IF addr4 > rmaxaddr OR rmaxaddr = -1 THEN - rmaxaddr := addr4; - END IF; - IF addr4 < rminaddr OR rminaddr = -1 THEN - rminaddr := addr4; - END IF; - END IF; - - IF minaddr = -1 OR maxaddr = -1 THEN - -- No addresses were non-null, return FALSE (arbitrary) - RETURN FALSE; - ELSIF given_address >= minaddr AND given_address <= maxaddr THEN - -- The address is within the given range - IF given_address >= lminaddr AND given_address <= lmaxaddr THEN - -- This checks to see if the address is on this side of the - -- road, ie if the address is even, the street range must be even - IF (given_address % 2) = (lminaddr % 2) - OR (given_address % 2) = (lmaxaddr % 2) THEN - RETURN TRUE; - END IF; - END IF; - IF given_address >= rminaddr AND given_address <= rmaxaddr THEN - -- See above - IF (given_address % 2) = (rminaddr % 2) - OR (given_address % 2) = (rmaxaddr % 2) THEN - RETURN TRUE; - END IF; - END IF; - END IF; - -- The address is not within the range - RETURN FALSE; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/interpolate_from_address.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/interpolate_from_address.sql deleted file mode 100644 index bd47c810d..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/interpolate_from_address.sql +++ /dev/null @@ -1,123 +0,0 @@ --- This function converts string addresses to integers and passes them to --- the other interpolate_from_address function. -CREATE OR REPLACE FUNCTION interpolate_from_address(INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, GEOMETRY) RETURNS GEOMETRY -AS $_$ -DECLARE - given_address INTEGER; - addr1 INTEGER; - addr2 INTEGER; - addr3 INTEGER; - addr4 INTEGER; - road GEOMETRY; - result GEOMETRY; -BEGIN - given_address := $1; - addr1 := to_number($2, '999999'); - addr2 := to_number($3, '999999'); - addr3 := to_number($4, '999999'); - addr4 := to_number($5, '999999'); - road := $6; - result = interpolate_from_address(given_address, addr1, addr2, addr3, addr4, road); - RETURN result; -END -$_$ LANGUAGE plpgsql; - --- interpolate_from_address(local_address, from_address_l, to_address_l, from_address_r, to_address_r, local_road) --- This function returns a point along the given geometry (must be linestring) --- corresponding to the given address. If the given address is not within --- the address range of the road, null is returned. --- This function requires that the address be grouped, such that the second and --- third arguments are from one side of the street, while the fourth and --- fifth are from the other. -CREATE OR REPLACE FUNCTION interpolate_from_address(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, GEOMETRY) RETURNS GEOMETRY -AS $_$ -DECLARE - given_address INTEGER; - lmaxaddr INTEGER := -1; - rmaxaddr INTEGER := -1; - lminaddr INTEGER := -1; - rminaddr INTEGER := -1; - lfrgreater BOOLEAN; - rfrgreater BOOLEAN; - frgreater BOOLEAN; - addrwidth INTEGER; - part DOUBLE PRECISION; - road GEOMETRY; - result GEOMETRY; -BEGIN - IF $1 IS NULL THEN - RETURN NULL; - ELSE - given_address := $1; - END IF; - - IF $6 IS NULL THEN - RETURN NULL; - ELSE - IF geometrytype($6) = 'LINESTRING' THEN - road := $6; - ELSIF geometrytype($6) = 'MULTILINESTRING' THEN - road := geometryn($6,1); - ELSE - RETURN NULL; - END IF; - END IF; - - IF $2 IS NOT NULL THEN - lfrgreater := TRUE; - lmaxaddr := $2; - lminaddr := $2; - END IF; - - IF $3 IS NOT NULL THEN - IF $3 > lmaxaddr OR lmaxaddr = -1 THEN - lmaxaddr := $3; - lfrgreater := FALSE; - END IF; - IF $3 < lminaddr OR lminaddr = -1 THEN - lminaddr := $3; - END IF; - END IF; - - IF $4 IS NOT NULL THEN - rmaxaddr := $4; - rminaddr := $4; - rfrgreater := TRUE; - END IF; - - IF $5 IS NOT NULL THEN - IF $5 > rmaxaddr OR rmaxaddr = -1 THEN - rmaxaddr := $5; - rfrgreater := FALSE; - END IF; - IF $5 < rminaddr OR rminaddr = -1 THEN - rminaddr := $5; - END IF; - END IF; - - IF given_address >= lminaddr AND given_address <= lmaxaddr THEN - IF (given_address % 2) = (lminaddr % 2) - OR (given_address % 2) = (lmaxaddr % 2) THEN - addrwidth := lmaxaddr - lminaddr; - part := (given_address - lminaddr) / trunc(addrwidth, 1); - frgreater := lfrgreater; - END IF; - END IF; - - IF given_address >= rminaddr AND given_address <= rmaxaddr THEN - IF (given_address % 2) = (rminaddr % 2) - OR (given_address % 2) = (rmaxaddr % 2) THEN - addrwidth := rmaxaddr - rminaddr; - part := (given_address - rminaddr) / trunc(addrwidth, 1); - frgreater := rfrgreater; - END IF; - END IF; - - IF frgreater THEN - part := 1 - part; - END IF; - - result = line_interpolate_point(road, part); - RETURN result; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/geocode/rate_attributes.sql b/extras/tiger_geocoder/tiger_2006andbefore/geocode/rate_attributes.sql deleted file mode 100644 index 2a9e7edaf..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/geocode/rate_attributes.sql +++ /dev/null @@ -1,58 +0,0 @@ --- rate_attributes(dirpA, dirpB, streetNameA, streetNameB, streetTypeA, --- streetTypeB, dirsA, dirsB, locationA, locationB) --- Rates the street based on the given attributes. The locations must be --- non-null. The other eight values are handled by the other rate_attributes --- function, so it's requirements must also be met. -CREATE OR REPLACE FUNCTION rate_attributes(VARCHAR, VARCHAR, VARCHAR, VARCHAR, - VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR) RETURNS INTEGER -AS $_$ -DECLARE - result INTEGER := 0; - locationWeight INTEGER := 14; - verbose BOOLEAN := FALSE; -BEGIN - IF $9 IS NOT NULL AND $10 IS NOT NULL THEN - result := levenshtein_ignore_case($9, $10); - ELSE - IF verbose THEN - RAISE NOTICE 'rate_attributes() - Location names cannot be null!'; - END IF; - RETURN NULL; - END IF; - result := result + rate_attributes($1, $2, $3, $4, $5, $6, $7, $8); - RETURN result; -END; -$_$ LANGUAGE plpgsql; - --- rate_attributes(dirpA, dirpB, streetNameA, streetNameB, streetTypeA, --- streetTypeB, dirsA, dirsB) --- Rates the street based on the given attributes. Only streetNames are --- required. If any others are null (either A or B) they are treated as --- empty strings. -CREATE OR REPLACE FUNCTION rate_attributes(VARCHAR, VARCHAR, VARCHAR, VARCHAR, - VARCHAR, VARCHAR, VARCHAR, VARCHAR) RETURNS INTEGER -AS $_$ -DECLARE - result INTEGER := 0; - directionWeight INTEGER := 2; - nameWeight INTEGER := 10; - typeWeight INTEGER := 5; - verbose BOOLEAN := FALSE; -BEGIN - result := result + levenshtein_ignore_case(cull_null($1), cull_null($2)) * - directionWeight; - IF $3 IS NOT NULL AND $4 IS NOT NULL THEN - result := result + levenshtein_ignore_case($3, $4) * nameWeight; - ELSE - IF verbose THEN - RAISE NOTICE 'rate_attributes() - Street names cannot be null!'; - END IF; - RETURN NULL; - END IF; - result := result + levenshtein_ignore_case(cull_null($5), cull_null($6)) * - typeWeight; - result := result + levenshtein_ignore_case(cull_null($7), cull_null($7)) * - directionWeight; - return result; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/import/load_tiger.sh b/extras/tiger_geocoder/tiger_2006andbefore/import/load_tiger.sh deleted file mode 100755 index cfa135275..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/import/load_tiger.sh +++ /dev/null @@ -1,9 +0,0 @@ -#!/bin/sh - -FIRST="" -for file in /data/tiger/www2.census.gov/geo/tiger/tiger2006se/*/*.ZIP; do - ./tigerimport.sh append mydb $file $FIRST - if [ -z "$FIRST" ]; then - FIRST="-a" - fi -done diff --git a/extras/tiger_geocoder/tiger_2006andbefore/import/newdb.sh b/extras/tiger_geocoder/tiger_2006andbefore/import/newdb.sh deleted file mode 100755 index 8abedcd0a..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/import/newdb.sh +++ /dev/null @@ -1,6 +0,0 @@ -#!/bin/bash - -dropdb $1 -createdb $1 -createlang plpgsql $1 -psql $1 -# Last updated Fri Mar 4 08:13:28 PST 2005 -# -# tigerimport.sh imports the CompleteChain, Landmarks, AreaLandMarks, and -# Polygon layers from a TIGER/Line .ZIP file into a PostGIS database. You -# must have ogr2ogr from GDAL (http://gdal.org), as well as the GDAL -# Python extensions. -# -# This program is free software; you can redistribute it and/or -# modify it under the terms of the GNU General Public License -# as published by the Free Software Foundation; either version 2 -# of the License, or (at your option) any later version. -# -# This program is distributed in the hope that it will be useful, -# but WITHOUT ANY WARRANTY; without even the implied warranty of -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -# GNU General Public License for more details. -# -# You can download a copy of the GPL from http://www.gnu.org/copyleft/gpl.html, -# or write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330, -# Boston, MA 02111-1307, USA. - -MODE=$1 -DATABASE=$2 -ZIP=$3 -APPEND=$4 - -TIGERPOLY=tigerpoly2.py - -case $MODE in - append) PRE="-append"; POST="" ;; - update) PRE="-update"; POST="" ;; - create) PRE=""; POST="-lco OVERWRITE=YES" ;; - *) echo "Usage: $0 [append|update|create] "; - exit -1;; -esac - -FILE=$(basename $ZIP) -FILE=${FILE#TGR} -FILE=${FILE%.ZIP} -TIGER=TGR$FILE.RT1 - -SRID=4269 -#cat > nad83_srs.txt < -# -# Permission is hereby granted, free of charge, to any person obtaining a -# copy of this software and associated documentation files (the "Software"), -# to deal in the Software without restriction, including without limitation -# the rights to use, copy, modify, merge, publish, distribute, sublicense, -# and/or sell copies of the Software, and to permit persons to whom the -# Software is furnished to do so, subject to the following conditions: -# -# The above copyright notice and this permission notice shall be included -# in all copies or substantial portions of the Software. -# -# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS -# OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, -# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL -# THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER -# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING -# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER -# DEALINGS IN THE SOFTWARE. -############################################################################### -# -# $Log: tigerpoly.py,v $ -# Revision 1.3 2003/07/11 14:52:13 warmerda -# Added logic to replicate all source polygon fields onto output file. -# -# Revision 1.2 2003/07/11 14:31:17 warmerda -# Use provided input filename. -# -# Revision 1.1 2003/03/03 05:17:06 warmerda -# New -# -# - -import osr -import ogr -import sys, os - -############################################################################# -class Module: - - def __init__( self ): - self.lines = {} - self.poly_line_links = {} - -############################################################################# -def Usage(): - print 'Usage: tigerpoly.py infile [outfile].shp' - print - sys.exit(1) - -############################################################################# -# Argument processing. - -infile = None -outfile = None - -i = 1 -while i < len(sys.argv): - arg = sys.argv[i] - - if infile is None: - infile = arg - - elif outfile is None: - outfile = arg - - else: - Usage() - - i = i + 1 - -if infile is None: - Usage() - -############################################################################# -# Open the datasource to operate on. - -ds = ogr.Open( infile, update = 0 ) - -poly_layer = ds.GetLayerByName( 'Polygon' ) -lm_layer = ds.GetLayerByName( 'Landmarks' ) - -############################################################################# -# Create output file for the composed polygons. - -shp_driver = ogr.GetDriverByName( 'ESRI Shapefile' ) - -shp_ds_cache = [] -def create_layer (outfile, layer, type): - if os.access( outfile, os.W_OK ): - shp_driver.DeleteDataSource( outfile ) - - defn = layer.GetLayerDefn() - shp_ds = shp_driver.CreateDataSource( outfile ) - shp_layer = shp_ds.CreateLayer( 'out', geom_type = type ) - field_count = defn.GetFieldCount() - - for fld_index in range(field_count): - src_fd = defn.GetFieldDefn( fld_index ) - - fd = ogr.FieldDefn( src_fd.GetName(), src_fd.GetType() ) - fd.SetWidth( src_fd.GetWidth() ) - fd.SetPrecision( src_fd.GetPrecision() ) - shp_layer.CreateField( fd ) - - shp_ds_cache.append(shp_ds) - return shp_layer - -poly_out = create_layer("Polygon.shp", poly_layer, ogr.wkbPolygon) -area_out = create_layer("AreaLandmarks.shp", lm_layer, ogr.wkbPolygon) - -############################################################################# -# Read all features in the line layer, holding just the geometry in a hash -# for fast lookup by TLID. - -line_layer = ds.GetLayerByName( 'CompleteChain' ) -line_count = 0 - -modules_hash = {} - -feat = line_layer.GetNextFeature() -geom_id_field = feat.GetFieldIndex( 'TLID' ) -tile_ref_field = feat.GetFieldIndex( 'MODULE' ) -while feat is not None: - geom_id = feat.GetField( geom_id_field ) - tile_ref = feat.GetField( tile_ref_field ) - - try: - module = modules_hash[tile_ref] - except: - module = Module() - modules_hash[tile_ref] = module - - module.lines[geom_id] = feat.GetGeometryRef().Clone() - line_count = line_count + 1 - - feat.Destroy() - - feat = line_layer.GetNextFeature() - -print 'Got %d lines in %d modules.' % (line_count,len(modules_hash)) - -############################################################################# -# Read all polygon/chain links and build a hash keyed by POLY_ID listing -# the chains (by TLID) attached to it. - -link_layer = ds.GetLayerByName( 'PolyChainLink' ) - -feat = link_layer.GetNextFeature() -geom_id_field = feat.GetFieldIndex( 'TLID' ) -tile_ref_field = feat.GetFieldIndex( 'MODULE' ) -lpoly_field = feat.GetFieldIndex( 'POLYIDL' ) -lcenid_field = feat.GetFieldIndex( 'CENIDL' ) -rpoly_field = feat.GetFieldIndex( 'POLYIDR' ) -rcenid_field = feat.GetFieldIndex( 'CENIDR' ) - -link_count = 0 - -while feat is not None: - module = modules_hash[feat.GetField( tile_ref_field )] - - tlid = feat.GetField( geom_id_field ) - - lpoly_id = feat.GetField( lpoly_field ) - lcenid_id = feat.GetField( lcenid_field ) - rpoly_id = feat.GetField( rpoly_field ) - rcenid_id = feat.GetField( rcenid_field ) - - if lcenid_id == rcenid_id and lpoly_id == rpoly_id: - feat.Destroy() - feat = link_layer.GetNextFeature() - continue - - for cenid, polyid in ((rcenid_id, rpoly_id), (lcenid_id, lpoly_id)): - if module.poly_line_links.has_key((cenid, polyid)): - module.poly_line_links[cenid, polyid].append( tlid ) - else: - module.poly_line_links[cenid, polyid] = [ tlid ] - - link_count = link_count + 1 - - feat.Destroy() - - feat = link_layer.GetNextFeature() - -print 'Processed %d links.' % link_count - -############################################################################# -# Process all polygon features. - -feat = poly_layer.GetNextFeature() -tile_ref_field = feat.GetFieldIndex( 'MODULE' ) -polyid_field = feat.GetFieldIndex( 'POLYID' ) -cenid_field = feat.GetFieldIndex( 'CENID' ) - -poly_count = 0 -degenerate_count = 0 - -while feat is not None: - module = modules_hash[feat.GetField( tile_ref_field )] - polyid = feat.GetField( polyid_field ) - cenid = feat.GetField( cenid_field ) - - link_coll = ogr.Geometry( type = ogr.wkbGeometryCollection ) - tlid_list = module.poly_line_links[cenid, polyid] - for tlid in tlid_list: - geom = module.lines[tlid] - link_coll.AddGeometry( geom ) - - try: - poly = ogr.BuildPolygonFromEdges( link_coll, 0, 0 ) - - if poly.GetGeometryRef(0).GetPointCount() < 4: - degenerate_count = degenerate_count + 1 - poly.Destroy() - feat.Destroy() - feat = poly_layer.GetNextFeature() - continue - - #print poly.ExportToWkt() - #feat.SetGeometryDirectly( poly ) - - feat2 = ogr.Feature(feature_def=poly_out.GetLayerDefn()) - - for fld_index in range(poly_out.GetLayerDefn().GetFieldCount()): - feat2.SetField( fld_index, feat.GetField( fld_index ) ) - - feat2.SetGeometryDirectly( poly ) - poly_out.CreateFeature( feat2 ) - feat2.Destroy() - poly_count += 1 - - except: - raise - print 'BuildPolygonFromEdges failed.' - - feat.Destroy() - feat = poly_layer.GetNextFeature() - -if degenerate_count: - print 'Discarded %d degenerate polygons.' % degenerate_count - -print 'Built %d polygons.' % poly_count - -############################################################################# -# Extract landmarks... - -area_layer = ds.GetLayerByName( 'AreaLandmarks' ) -feat = area_layer.GetNextFeature() -land_field = feat.GetFieldIndex( 'LAND' ) -polyid_field = feat.GetFieldIndex( 'POLYID' ) -cenid_field = feat.GetFieldIndex( 'CENID' ) - -area = {} -while feat is not None: - land = feat.GetField( land_field ) - polyid = feat.GetField( polyid_field ) - cenid = feat.GetField( cenid_field ) - - if area.has_key(land): - area[land].append(( cenid, polyid )) - else: - area[land] = [(cenid, polyid)] - - feat.Destroy() - feat = area_layer.GetNextFeature() - -feat = lm_layer.GetNextFeature() -land_field = feat.GetFieldIndex( 'LAND' ) -tile_ref_field = feat.GetFieldIndex( 'MODULE' ) -area_count = 0 - -while feat is not None: - module = modules_hash[feat.GetField( tile_ref_field )] - land = feat.GetField( land_field ) - if not area.has_key(land): - # print "Can't find LAND %s in Landmarks" % land - feat.Destroy() - feat = lm_layer.GetNextFeature() - continue - - link_coll = ogr.Geometry( type = ogr.wkbGeometryCollection ) - for cenid, polyid in area[land]: - seen = {} - tlid_list = module.poly_line_links[cenid, polyid] - for tlid in tlid_list: - if seen.has_key(tlid): - seen[tlid] += 1 - else: - seen[tlid] = 1 - - for tlid in seen.keys(): - if seen[tlid] == 1: - geom = module.lines[tlid] - link_coll.AddGeometry( geom ) - - try: - poly = ogr.BuildPolygonFromEdges( link_coll, 1, 1 ) - except: - print 'BuildPolygonFromEdges failed.' - feat.Destroy() - feat = lm_layer.GetNextFeature() - - feat2 = ogr.Feature(feature_def=area_out.GetLayerDefn()) - for fld_index in range(area_out.GetLayerDefn().GetFieldCount()): - feat2.SetField( fld_index, feat.GetField( fld_index ) ) - - feat2.SetGeometryDirectly( poly ) - area_out.CreateFeature( feat2 ) - feat2.Destroy() - - area_count += 1 - - feat.Destroy() - feat = lm_layer.GetNextFeature() - -print "Built %d area landmarks." % area_count - -############################################################################# -# Cleanup - -for shpds in shp_ds_cache: - shpds.Destroy() -ds.Destroy() diff --git a/extras/tiger_geocoder/tiger_2006andbefore/normalize/count_words.sql b/extras/tiger_geocoder/tiger_2006andbefore/normalize/count_words.sql deleted file mode 100644 index 166d4e32e..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/normalize/count_words.sql +++ /dev/null @@ -1,32 +0,0 @@ --- 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_2006andbefore/normalize/end_soundex.sql b/extras/tiger_geocoder/tiger_2006andbefore/normalize/end_soundex.sql deleted file mode 100644 index 02500ddb1..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/normalize/end_soundex.sql +++ /dev/null @@ -1,17 +0,0 @@ --- 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_2006andbefore/normalize/get_last_words.sql b/extras/tiger_geocoder/tiger_2006andbefore/normalize/get_last_words.sql deleted file mode 100644 index 54058a288..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/normalize/get_last_words.sql +++ /dev/null @@ -1,29 +0,0 @@ --- 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_2006andbefore/normalize/location_extract.sql b/extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract.sql deleted file mode 100644 index 6026291ec..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract.sql +++ /dev/null @@ -1,36 +0,0 @@ --- 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 - location VARCHAR; -BEGIN - IF fullStreet IS NULL THEN - RETURN NULL; - END IF; - - location := location_extract_place_exact(fullStreet, stateAbbrev); - IF location IS NULL THEN - location := location_extract_countysub_exact(fullStreet, stateAbbrev); - IF location IS NULL THEN - location := location_extract_place_fuzzy(fullStreet, stateAbbrev); - IF location IS NULL THEN - location := location_extract_countysub_fuzzy(fullStreet, stateAbbrev); - END IF; - END IF; - END IF; - - RETURN location; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_countysub_exact.sql b/extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_countysub_exact.sql deleted file mode 100644 index b63f246ca..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_countysub_exact.sql +++ /dev/null @@ -1,54 +0,0 @@ --- 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; - rec RECORD; -BEGIN - ws := E'[ ,.\n\f\t]'; - - -- No hope of determining the location from place. Try countysub. - IF stateAbbrev IS NOT NULL THEN - SELECT INTO tempInt count(*) FROM countysub_lookup - WHERE countysub_lookup.state = stateAbbrev - AND texticregexeq(fullStreet, '(?i)' || name || '$'); - ELSE - SELECT INTO tempInt count(*) FROM countysub_lookup - 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 countysub_lookup - WHERE countysub_lookup.state = stateAbbrev - 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 countysub_lookup - 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_2006andbefore/normalize/location_extract_countysub_fuzzy.sql b/extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_countysub_fuzzy.sql deleted file mode 100644 index 01d0107c1..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_countysub_fuzzy.sql +++ /dev/null @@ -1,87 +0,0 @@ --- 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; -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 - SELECT INTO tempInt count(*) FROM countysub_lookup - WHERE countysub_lookup.state = stateAbbrev - AND soundex(tempString) = end_soundex(name); - ELSE - SELECT INTO tempInt count(*) FROM countysub_lookup - 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 countysub_lookup - WHERE countysub_lookup.state = stateAbbrev - AND soundex(tempString) = end_soundex(name) LOOP - word_count := count_words(rec.name); - test := TRUE; - tempString := get_last_words(fullStreet, word_count); - FOR i IN 1..word_count LOOP - IF soundex(split_part(tempString, ' ', i)) != - soundex(split_part(rec.name, ' ', i)) THEN - test := FALSE; - END IF; - END LOOP; - IF test THEN - -- The soundex matched, determine if the distance is better. - IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN - location := tempString; - tempInt := levenshtein_ignore_case(rec.name, tempString); - END IF; - END IF; - END LOOP; - ELSE - FOR rec IN SELECT name FROM countysub_lookup - WHERE soundex(tempString) = end_soundex(name) LOOP - word_count := count_words(rec.name); - test := TRUE; - tempString := get_last_words(fullStreet, word_count); - FOR i IN 1..word_count LOOP - IF soundex(split_part(tempString, ' ', i)) != - soundex(split_part(rec.name, ' ', i)) THEN - test := FALSE; - END IF; - END LOOP; - IF test THEN - -- The soundex matched, determine if the distance is better. - IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN - location := tempString; - tempInt := levenshtein_ignore_case(rec.name, tempString); - END IF; - END IF; - END LOOP; - END IF; - END IF; -- If no fuzzys were found, leave location null. - - RETURN location; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_place_exact.sql b/extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_place_exact.sql deleted file mode 100644 index 4711e1a09..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_place_exact.sql +++ /dev/null @@ -1,57 +0,0 @@ --- 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; - rec RECORD; -BEGIN - ws := E'[ ,.\n\f\t]'; - - -- Try for an exact match against places - IF stateAbbrev IS NOT NULL THEN - SELECT INTO tempInt count(*) FROM place_lookup - WHERE place_lookup.state = stateAbbrev - AND texticregexeq(fullStreet, '(?i)' || name || '$'); - ELSE - SELECT INTO tempInt count(*) FROM place_lookup - 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_lookup - WHERE place_lookup.state = stateAbbrev - 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_lookup - 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_2006andbefore/normalize/location_extract_place_fuzzy.sql b/extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_place_fuzzy.sql deleted file mode 100644 index f57e45b3e..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/normalize/location_extract_place_fuzzy.sql +++ /dev/null @@ -1,86 +0,0 @@ --- location_extract_place_fuzzy(string, stateAbbrev) --- This function checks the place_lookup table to find a potential match to --- the location described at the end of the given string. If an exact match --- fails, a fuzzy match is performed. The location as found in the given --- string is returned. -CREATE OR REPLACE FUNCTION location_extract_place_fuzzy( - fullStreet VARCHAR, - stateAbbrev VARCHAR -) RETURNS VARCHAR -AS $_$ -DECLARE - ws VARCHAR; - tempString VARCHAR; - location VARCHAR; - tempInt INTEGER; - word_count INTEGER; - rec RECORD; - test BOOLEAN; -BEGIN - ws := E'[ ,.\n\f\t]'; - - tempString := substring(fullStreet, '(?i)' || ws - || '([a-zA-Z0-9]+)$'); - IF tempString IS NULL THEN - tempString := fullStreet; - END IF; - - IF stateAbbrev IS NOT NULL THEN - SELECT into tempInt count(*) FROM place_lookup - WHERE place_lookup.state = stateAbbrev - AND soundex(tempString) = end_soundex(name); - ELSE - SELECT into tempInt count(*) FROM place_lookup - WHERE soundex(tempString) = end_soundex(name); - END IF; - - IF tempInt > 0 THEN - -- Some potentials were found. Begin a word-by-word soundex on each. - tempInt := 50; - IF stateAbbrev IS NOT NULL THEN - FOR rec IN SELECT name FROM place_lookup - WHERE place_lookup.state = stateAbbrev - AND soundex(tempString) = end_soundex(name) LOOP - word_count := count_words(rec.name); - test := TRUE; - tempString := get_last_words(fullStreet, word_count); - FOR i IN 1..word_count LOOP - IF soundex(split_part(tempString, ' ', i)) != - soundex(split_part(rec.name, ' ', i)) THEN - test := FALSE; - END IF; - END LOOP; - IF test THEN - -- The soundex matched, determine if the distance is better. - IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN - location := tempString; - tempInt := levenshtein_ignore_case(rec.name, tempString); - END IF; - END IF; - END LOOP; - ELSE - FOR rec IN SELECT name FROM place_lookup - WHERE soundex(tempString) = end_soundex(name) LOOP - word_count := count_words(rec.name); - test := TRUE; - tempString := get_last_words(fullStreet, word_count); - FOR i IN 1..word_count LOOP - IF soundex(split_part(tempString, ' ', i)) != - soundex(split_part(rec.name, ' ', i)) THEN - test := FALSE; - END IF; - END LOOP; - IF test THEN - -- The soundex matched, determine if the distance is better. - IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN - location := tempString; - tempInt := levenshtein_ignore_case(rec.name, tempString); - END IF; - END IF; - END LOOP; - END IF; - END IF; - - RETURN location; -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/normalize/normalize_address.sql b/extras/tiger_geocoder/tiger_2006andbefore/normalize/normalize_address.sql deleted file mode 100644 index 67d5bb4e1..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/normalize/normalize_address.sql +++ /dev/null @@ -1,493 +0,0 @@ --- 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( - rawInput VARCHAR -) RETURNS norm_addy -AS $_$ -DECLARE - 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; -BEGIN - result.parsed := FALSE; - - IF rawInput IS NULL THEN - RETURN result; - END IF; - - ws := E'[ ,.\t\n\f\r]'; - - -- Assume that the address begins with a digit, and extract it from - -- the input string. - addressString := substring(rawInput from '^([0-9].*?)[ ,/.]'); - - -- 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 := to_number(zipString, '99999'); - result.parsed := TRUE; - RETURN result; - END IF; - END IF; - END IF; - - IF zipString IS NOT NULL THEN - fullStreet := substring(rawInput from '(.*)' - || ws || '+' || cull_null(zipString) || '[- ]?([0-9]{4})?$'); - ELSE - fullStreet := rawInput; - 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; - - -- 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; - - -- 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; - 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 - SELECT INTO 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 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; - - -- Pull potential street types from the full street information - SELECT INTO 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; - - -- 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 - 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; - - 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); - SELECT INTO tempInt count(*) FROM direction_lookup WHERE - texticregexeq(reducedStreet, '(?i)' || ws || name || '$'); - IF tempInt > 0 THEN - SELECT INTO postDir substring(reducedStreet, '(?i)' || ws || '(' - || name || ')' || '$') FROM direction_lookup - WHERE texticregexeq(reducedStreet, '(?i)' || ws || name || '$'); - SELECT INTO result.postDirAbbrev abbrev FROM direction_lookup - WHERE texticregexeq(reducedStreet, '(?i)' || ws || name || '$'); - END IF; - 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, '(?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. - SELECT INTO 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. - SELECT INTO postDir substring(fullStreet, '(?i)' || ws || '(' - || name || ')(?:' || ws || '|$)') FROM direction_lookup WHERE - texticregexeq(fullStreet, '(?i)' || ws || name || '(?:' - || ws || '|$)'); - SELECT INTO 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); - 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; - 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 - result.location := location_extract(fullStreet, result.stateAbbrev); - -- If the location was found, remove it from fullStreet - fullStreet := substring(fullStreet, '(?i)(.*),' || ws || '+' || - result.location); - 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 := to_number(zipString, '99999'); - - result.parsed := TRUE; - RETURN result; -END -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/normalize/pprint_addy.sql b/extras/tiger_geocoder/tiger_2006andbefore/normalize/pprint_addy.sql deleted file mode 100644 index 392e9dc82..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/normalize/pprint_addy.sql +++ /dev/null @@ -1,36 +0,0 @@ -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(lpad(input.zip::text,5,'0')); - - RETURN result; - -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/normalize/state_extract.sql b/extras/tiger_geocoder/tiger_2006andbefore/normalize/state_extract.sql deleted file mode 100644 index 4390b696c..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/normalize/state_extract.sql +++ /dev/null @@ -1,82 +0,0 @@ --- 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; -BEGIN - ws := E'[ ,.\t\n\f\r]'; - - -- 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(rawInput from ws || E'+([^ ,.\t\n\f\r0-9]*?)$'); - 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_2006andbefore/orig/tiger_geocoder.sql b/extras/tiger_geocoder/tiger_2006andbefore/orig/tiger_geocoder.sql deleted file mode 100644 index 0d016dbf9..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/orig/tiger_geocoder.sql +++ /dev/null @@ -1,2657 +0,0 @@ --- 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, ''[ ,\.\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; - --- Returns the value passed, or an empty string if null. --- This is used to concatinate values that may be null. -CREATE OR REPLACE FUNCTION cull_null(VARCHAR) RETURNS VARCHAR -AS ' -BEGIN - IF $1 IS NULL THEN - return ''''; - ELSE - return $1; - END IF; -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; - - - -CREATE OR REPLACE FUNCTION geocode(VARCHAR) RETURNS REFCURSOR -AS ' -BEGIN - return geocode(NULL, $1); -END; -' LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION geocode(REFCURSOR, VARCHAR) RETURNS REFCURSOR -AS ' -DECLARE - result REFCURSOR; - input VARCHAR; - parsed VARCHAR; - addressString VARCHAR; - address INTEGER; - directionPrefix VARCHAR; - streetName VARCHAR; - streetType VARCHAR; - directionSuffix VARCHAR; - location VARCHAR; - state VARCHAR; - zipCodeString VARCHAR; - zipCode INTEGER; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''geocode()''; - END IF; - -- Check inputs. - IF $1 IS NOT NULL THEN - result := $1; - END IF; - IF $2 IS NULL THEN - -- The address string is manditory. - RAISE EXCEPTION ''geocode() - No address string provided.''; - ELSE - input := $2; - END IF; - - -- Pass the input string into the address normalizer - parsed := normalize_address(input); - IF parsed IS NULL THEN - RAISE EXCEPTION ''geocode() - address string failed to parse.''; - END IF; - - addressString := split_part(parsed, '':'', 1); - directionPrefix := split_part(parsed, '':'', 2); - streetName := split_part(parsed, '':'', 3); - streetType := split_part(parsed, '':'', 4); - directionSuffix := split_part(parsed, '':'', 5); - location := split_part(parsed, '':'', 6); - state := split_part(parsed, '':'', 7); - zipCodeString := split_part(parsed, '':'', 8); - - -- Empty strings must be converted to nulls; - IF addressString = '''' THEN - addressString := NULL; - END IF; - IF directionPrefix = '''' THEN - directionPrefix := NULL; - END IF; - IF streetName = '''' THEN - streetName := NULL; - END IF; - IF streetType = '''' THEN - streetType := NULL; - END IF; - IF directionSuffix = '''' THEN - directionSuffix := NULL; - END IF; - IF location = '''' THEN - location := NULL; - END IF; - IF state = '''' THEN - state := NULL; - END IF; - IF zipCodeString = '''' THEN - zipCodeString := NULL; - END IF; - - -- address and zipCode must be integers - IF addressString IS NOT NULL THEN - address := to_number(addressString, ''99999999999''); - END IF; - IF zipCodeString IS NOT NULL THEN - zipCode := to_number(zipCodeString, ''99999''); - END IF; - - IF verbose THEN - RAISE NOTICE ''geocode() - address %'', address; - RAISE NOTICE ''geocode() - directionPrefix %'', directionPrefix; - RAISE NOTICE ''geocode() - streetName "%"'', streetName; - RAISE NOTICE ''geocode() - streetType %'', streetType; - RAISE NOTICE ''geocode() - directionSuffix %'', directionSuffix; - RAISE NOTICE ''geocode() - location "%"'', location; - RAISE NOTICE ''geocode() - state %'', state; - RAISE NOTICE ''geocode() - zipCode %'', zipCode; - END IF; - -- This is where any validation above the geocode_address functions would go. - - -- Call geocode_address - result := geocode_address(result, address, directionPrefix, streetName, - streetType, directionSuffix, location, state, zipCode); - RETURN result; -END; -' LANGUAGE plpgsql; - - - --- geocode(cursor, address, directionPrefix, streetName, --- streetTypeAbbreviation, directionSuffix, location, stateAbbreviation, --- zipCode) -CREATE OR REPLACE FUNCTION geocode_address(refcursor, INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, INTEGER) RETURNS REFCURSOR -AS ' -DECLARE - result REFCURSOR; - address INTEGER; - directionPrefix VARCHAR; - streetName VARCHAR; - streetTypeAbbrev VARCHAR; - directionSuffix VARCHAR; - location VARCHAR; - stateAbbrev VARCHAR; - state VARCHAR; - zipCode INTEGER; - tempString VARCHAR; - tempInt VARCHAR; - locationPlaceExact BOOLEAN := FALSE; - locationPlaceFuzzy BOOLEAN := FALSE; - locationCountySubExact BOOLEAN := FALSE; - locationCountySubFuzzy BOOLEAN := FALSE; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''geocode_address()''; - END IF; - -- The first step is to determine what weve been given, and if its enough. - IF $1 IS NOT NULL THEN - -- The result was not provided. No matter, we can use an unnamed one. - result := $1; - END IF; - IF $2 IS NULL THEN - -- The address is manditory. - -- Without it, wed be wandering into strangers homes all the time. - RAISE EXCEPTION ''geocode_address() - No address provided!''; - ELSE - address := $2; - END IF; - IF $3 IS NOT NULL THEN - -- The direction prefix really isnt important. - -- It will be used for rating if provided. - directionPrefix := $3; - END IF; - IF $4 IS NULL THEN - -- A street name must be given. Think about it. - RAISE EXCEPTION ''geocode_address() - No street name provided!''; - ELSE - streetName := $4; - END IF; - IF $5 IS NOT NULL THEN - -- A street type will be used for rating if provided, but isnt required. - streetTypeAbbrev := $5; - END IF; - IF $6 IS NOT NULL THEN - -- Same as direction prefix, only later. - directionSuffix := $6; - END IF; - IF $7 IS NOT NULL THEN - -- Location is not needed iff a zip is given. The check occurs after - -- the geocode_address_zip call. - location := $7; - END IF; - IF $8 IS NULL THEN - -- State abbreviation is manditory. It is also assumed to be valid. - ELSE - stateAbbrev := $8; - END IF; - IF $9 IS NOT NULL THEN - -- Zip code is optional, but nice. - zipCode := $9; - END IF; - - -- The geocoding tables store the state name rather than the abbreviation. - -- We can validate the abbreviation while retrieving the name. - IF stateAbbrev IS NOT NULL THEN - SELECT INTO state name FROM state_lookup - WHERE state_lookup.abbrev = stateAbbrev; - IF state IS NULL THEN - END IF; - END IF; - - IF zipCode IS NOT NULL THEN - IF verbose THEN - RAISE NOTICE ''geocode_address() - calling geocode_address_zip()''; - END IF; - -- If the zip code is given, it is the most useful way to narrow the - -- search. We will try it first, and if no results match, we will move - -- on to a location search. There is no fuzzy searching on zip codes. - result := geocode_address_zip(result, address, directionPrefix, streetName, - streetTypeAbbrev, directionSuffix, zipCode); - IF result IS NOT NULL THEN - RETURN result; - ELSE - result := $1; - END IF; - END IF; - -- After now, the location becomes manditory. - IF location IS NOT NULL THEN - -- location may be useful, it may not. The first step is to determine if - -- there are any potenial matches in the place and countysub fields. - -- This is done against the lookup tables, and will save us time on much - -- larger queries if they dont match. - IF verbose THEN - RAISE NOTICE ''geocode_address() - calling location_extract_place_*()''; - END IF; - tempString := location_extract_place_exact(location, stateAbbrev); - IF tempString IS NOT NULL THEN - locationPlaceExact := TRUE; - ELSE - locationPlaceExact := FALSE; - END IF; - tempString := location_extract_place_fuzzy(location, stateAbbrev); - IF tempString IS NOT NULL THEN - locationPlaceFuzzy := true; - ELSE - locationPlaceFuzzy := false; - END IF; - IF verbose THEN - RAISE NOTICE ''geocode_address() - calling location_extract_countysub_*()''; - END IF; - tempString := location_extract_countysub_exact(location, stateAbbrev); - IF tempString IS NOT NULL THEN - locationCountySubExact := TRUE; - ELSE - locationCountySubExact := FALSE; - END IF; - tempString := location_extract_countysub_fuzzy(location, stateAbbrev); - IF tempString IS NOT NULL THEN - locationCountySubFuzzy := true; - ELSE - locationCountySubFuzzy := false; - END IF; - END IF; - IF locationPlaceExact THEN - IF verbose THEN - RAISE NOTICE ''geocode_address() - calling geocode_address_place_exact()''; - END IF; - result := geocode_address_place_exact(result, address, directionPrefix, - streetName, streetTypeAbbrev, directionSuffix, location, state); - IF result IS NOT NULL THEN - RETURN result; - ELSE - result := $1; - END IF; - END IF; - IF locationCountySubExact THEN - IF verbose THEN - RAISE NOTICE ''geocode_address() - calling geocode_address_countysub_exact()''; - END IF; - result := geocode_address_countysub_exact(result, address, directionPrefix, - streetName, streetTypeAbbrev, directionSuffix, location, state); - IF result IS NOT NULL THEN - RETURN result; - ELSE - result := $1; - END IF; - END IF; - IF locationPlaceFuzzy THEN - IF verbose THEN - RAISE NOTICE ''geocode_address() - calling geocode_address_place_fuzzy()''; - END IF; - result := geocode_address_place_fuzzy(result, address, directionPrefix, - streetName, streetTypeAbbrev, directionSuffix, location, state); - IF result IS NOT NULL THEN - RETURN result; - ELSE - result := $1; - END IF; - END IF; - IF locationCountySubFuzzy THEN - IF verbose THEN - RAISE NOTICE ''geocode_address() - calling geocode_address_countysub_fuzzy()''; - END IF; - result := geocode_address_countysub_fuzzy(result, address, directionPrefix, - streetName, streetTypeAbbrev, directionSuffix, location, state); - IF result IS NOT NULL THEN - RETURN result; - ELSE - result := $1; - END IF; - END IF; - IF state IS NOT NULL THEN - IF verbose THEN - RAISE NOTICE ''geocode_address() - calling geocode_address_state()''; - END IF; - result := geocode_address_state(result, address, directionPrefix, - streetName, streetTypeAbbrev, directionSuffix, state); - IF result IS NOT NULL THEN - RETURN result; - ELSE - result := $1; - END IF; - END IF; - RETURN NULL; -END; -' LANGUAGE plpgsql; - - - -CREATE OR REPLACE FUNCTION geocode_address_countysub_exact(REFCURSOR, INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR) RETURNS REFCURSOR -AS ' -DECLARE - result REFCURSOR; - address INTEGER; - directionPrefix VARCHAR; - streetName VARCHAR; - streetTypeAbbrev VARCHAR; - directionSuffix VARCHAR; - state VARCHAR; - location VARCHAR; - tempString VARCHAR; - tempInt VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''geocode_address_countysub_exact()''; - END IF; - -- The first step is to determine what weve been given, and if its enough. - IF $1 IS NOT NULL THEN - -- The cursor was not provided. No matter, we can use an unnamed one. - result := $1; - END IF; - IF $2 IS NULL THEN - -- The address is manditory. - -- Without it, wed be wandering into strangers homes all the time. - RAISE EXCEPTION ''geocode_address_countysub_exact() - No address provided!''; - ELSE - address := $2; - END IF; - IF $3 IS NOT NULL THEN - -- The direction prefix really isnt important. - -- It will be used for rating if provided. - directionPrefix := $3; - END IF; - IF $4 IS NULL THEN - -- A street name must be given. Think about it. - RAISE EXCEPTION ''geocode_address_countysub_exact() - No street name provided!''; - ELSE - streetName := $4; - END IF; - IF $5 IS NOT NULL THEN - -- A street type will be used for rating if provided, but isnt required. - streetTypeAbbrev := $5; - END IF; - IF $6 IS NOT NULL THEN - -- Same as direction prefix, only later. - directionSuffix := $6; - END IF; - IF $7 IS NULL THEN - -- location is manditory. This is the location geocoder after all. - RAISE EXCEPTION ''geocode_address_countysub_exact() - No location provided!''; - ELSE - location := $7; - END IF; - IF $8 IS NOT NULL THEN - state := $8; - END IF; - - -- Check to see if the road name can be matched. - IF state IS NOT NULL THEN - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE location = tiger_geocode_roads.cousub - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state; - ELSE - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE location = tiger_geocode_roads.cousub - AND soundex(streetName) = soundex(tiger_geocode_roads.fename); - END IF; - IF verbose THEN - RAISE NOTICE ''geocode_address_countysub_exact() - % potential matches.'', tempInt; - END IF; - IF tempInt = 0 THEN - RETURN NULL; - ELSE - -- The road name matches, now we check to see if the addresses match - IF state IS NOT NULL THEN - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE location = tiger_geocode_roads.cousub - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid; - ELSE - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE location = tiger_geocode_roads.cousub - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid; - END IF; - IF verbose THEN - RAISE NOTICE ''geocode_address_countysub_exact() - % address matches.'', tempInt; - END IF; - IF tempInt = 0 THEN - return NULL; - ELSE - IF state IS NOT NULL THEN - OPEN result FOR - SELECT *, interpolate_from_address(address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs, location, - tiger_geocode_roads.cousub) as rating - FROM tiger_geocode_roads - WHERE location = tiger_geocode_roads.cousub - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid - ORDER BY subquery.rating; - return result; - ELSE - OPEN result FOR - SELECT *, interpolate_from_address(address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs, location, - tiger_geocode_roads.cousub) as rating - FROM tiger_geocode_roads - WHERE location = tiger_geocode_roads.cousub - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid - ORDER BY subquery.rating; - RETURN result; - END IF; - END IF; - END IF; -END; -' LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION geocode_address_countysub_fuzzy(REFCURSOR, INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR) RETURNS REFCURSOR -AS ' -DECLARE - result REFCURSOR; - address INTEGER; - directionPrefix VARCHAR; - streetName VARCHAR; - streetTypeAbbrev VARCHAR; - directionSuffix VARCHAR; - state VARCHAR; - location VARCHAR; - tempString VARCHAR; - tempInt VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''geocode_address_countysub_fuzzy()''; - END IF; - -- The first step is to determine what weve been given, and if its enough. - IF $1 IS NOT NULL THEN - -- The cursor was not provided. No matter, we can use an unnamed one. - result := $1; - END IF; - IF $2 IS NULL THEN - -- The address is manditory. - -- Without it, wed be wandering into strangers homes all the time. - RAISE EXCEPTION ''geocode_address_countysub_fuzzy() - No address provided!''; - ELSE - address := $2; - END IF; - IF $3 IS NOT NULL THEN - -- The direction prefix really isnt important. - -- It will be used for rating if provided. - directionPrefix := $3; - END IF; - IF $4 IS NULL THEN - -- A street name must be given. Think about it. - RAISE EXCEPTION ''geocode_address_countysub_fuzzy() - No street name provided!''; - ELSE - streetName := $4; - END IF; - IF $5 IS NOT NULL THEN - -- A street type will be used for rating if provided, but isnt required. - streetTypeAbbrev := $5; - END IF; - IF $6 IS NOT NULL THEN - -- Same as direction prefix, only later. - directionSuffix := $6; - END IF; - IF $7 IS NULL THEN - -- location is manditory. This is the location geocoder after all. - RAISE EXCEPTION ''geocode_address_countysub_fuzzy() - No location provided!''; - ELSE - location := $7; - END IF; - IF $8 IS NOT NULL THEN - state := $8; - END IF; - - -- Check to see if the road name can be matched. - IF state IS NOT NULL THEN - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE soundex(location) = soundex(tiger_geocode_roads.cousub) - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state; - ELSE - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE soundex(location) = soundex(tiger_geocode_roads.cousub) - AND soundex(streetName) = soundex(tiger_geocode_roads.fename); - END IF; - IF verbose THEN - RAISE NOTICE ''geocode_address_countysub_fuzzy() - % potential matches.'', tempInt; - END IF; - IF tempInt = 0 THEN - RETURN NULL; - ELSE - -- The road name matches, now we check to see if the addresses match - IF state IS NOT NULL THEN - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE soundex(location) = soundex(tiger_geocode_roads.cousub) - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid; - ELSE - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE soundex(location) = soundex(tiger_geocode_roads.cousub) - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid; - END IF; - IF verbose THEN - RAISE NOTICE ''geocode_address_countysub_fuzzy() - % address matches.'', tempInt; - END IF; - IF tempInt = 0 THEN - return NULL; - ELSE - IF state IS NOT NULL THEN - OPEN result FOR - SELECT *, interpolate_from_address(address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs, location, - tiger_geocode_roads.cousub) as rating - FROM tiger_geocode_roads - WHERE soundex(location) = soundex(tiger_geocode_roads.cousub) - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid - ORDER BY subquery.rating; - return result; - ELSE - OPEN result FOR - SELECT *, interpolate_from_address(address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs, location, - tiger_geocode_roads.cousub) as rating - FROM tiger_geocode_roads - WHERE soundex(location) = soundex(tiger_geocode_roads.cousub) - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid - ORDER BY subquery.rating; - RETURN result; - END IF; - END IF; - END IF; -END; -' LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION geocode_address_place_exact(REFCURSOR, INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR) RETURNS REFCURSOR -AS ' -DECLARE - result REFCURSOR; - address INTEGER; - directionPrefix VARCHAR; - streetName VARCHAR; - streetTypeAbbrev VARCHAR; - directionSuffix VARCHAR; - state VARCHAR; - location VARCHAR; - tempString VARCHAR; - tempInt VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''geocode_address_place_exact()''; - END IF; - -- The first step is to determine what weve been given, and if its enough. - IF $1 IS NOT NULL THEN - -- The cursor was not provided. No matter, we can use an unnamed one. - result := $1; - END IF; - IF $2 IS NULL THEN - -- The address is manditory. - -- Without it, wed be wandering into strangers homes all the time. - RAISE EXCEPTION ''geocode_address_place_exact() - No address provided!''; - ELSE - address := $2; - END IF; - IF $3 IS NOT NULL THEN - -- The direction prefix really isnt important. - -- It will be used for rating if provided. - directionPrefix := $3; - END IF; - IF $4 IS NULL THEN - -- A street name must be given. Think about it. - RAISE EXCEPTION ''geocode_address_place_exact() - No street name provided!''; - ELSE - streetName := $4; - END IF; - IF $5 IS NOT NULL THEN - -- A street type will be used for rating if provided, but isnt required. - streetTypeAbbrev := $5; - END IF; - IF $6 IS NOT NULL THEN - -- Same as direction prefix, only later. - directionSuffix := $6; - END IF; - IF $7 IS NULL THEN - -- location is manditory. This is the location geocoder after all. - RAISE EXCEPTION ''geocode_address_place_exact() - No location provided!''; - ELSE - location := $7; - END IF; - IF $8 IS NOT NULL THEN - state := $8; - END IF; - - -- Check to see if the road name can be matched. - IF state IS NOT NULL THEN - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE location = tiger_geocode_roads.place - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state; - ELSE - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE location = tiger_geocode_roads.place - AND soundex(streetName) = soundex(tiger_geocode_roads.fename); - END IF; - IF verbose THEN - RAISE NOTICE ''geocode_address_place_exact() - % potential matches.'', tempInt; - END IF; - IF tempInt = 0 THEN - RETURN NULL; - ELSE - -- The road name matches, now we check to see if the addresses match - IF state IS NOT NULL THEN - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE location = tiger_geocode_roads.place - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid; - ELSE - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE location = tiger_geocode_roads.place - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid; - END IF; - IF verbose THEN - RAISE NOTICE ''geocode_address_place_exact() - % address matches.'', tempInt; - END IF; - IF tempInt = 0 THEN - return NULL; - ELSE - IF state IS NOT NULL THEN - OPEN result FOR - SELECT *, interpolate_from_address(address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs, location, - tiger_geocode_roads.place) as rating - FROM tiger_geocode_roads - WHERE location = tiger_geocode_roads.place - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid - ORDER BY subquery.rating; - return result; - ELSE - OPEN result FOR - SELECT *, interpolate_from_address(address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs, location, - tiger_geocode_roads.place) as rating - FROM tiger_geocode_roads - WHERE location = tiger_geocode_roads.place - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid - ORDER BY subquery.rating; - RETURN result; - END IF; - END IF; - END IF; -END; -' LANGUAGE plpgsql; - - - -CREATE OR REPLACE FUNCTION geocode_address_place_fuzzy(REFCURSOR, INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR) RETURNS REFCURSOR -AS ' -DECLARE - result REFCURSOR; - address INTEGER; - directionPrefix VARCHAR; - streetName VARCHAR; - streetTypeAbbrev VARCHAR; - directionSuffix VARCHAR; - state VARCHAR; - location VARCHAR; - tempString VARCHAR; - tempInt VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''geocode_address_place_fuzzy()''; - END IF; - -- The first step is to determine what weve been given, and if its enough. - IF $1 IS NOT NULL THEN - -- The cursor was not provided. No matter, we can use an unnamed one. - result := $1; - END IF; - IF $2 IS NULL THEN - -- The address is manditory. - -- Without it, wed be wandering into strangers homes all the time. - RAISE EXCEPTION ''geocode_address_place_fuzzy() - No address provided!''; - ELSE - address := $2; - END IF; - IF $3 IS NOT NULL THEN - -- The direction prefix really isnt important. - -- It will be used for rating if provided. - directionPrefix := $3; - END IF; - IF $4 IS NULL THEN - -- A street name must be given. Think about it. - RAISE EXCEPTION ''geocode_address_place_fuzzy() - No street name provided!''; - ELSE - streetName := $4; - END IF; - IF $5 IS NOT NULL THEN - -- A street type will be used for rating if provided, but isnt required. - streetTypeAbbrev := $5; - END IF; - IF $6 IS NOT NULL THEN - -- Same as direction prefix, only later. - directionSuffix := $6; - END IF; - IF $7 IS NULL THEN - -- location is manditory. This is the location geocoder after all. - RAISE EXCEPTION ''geocode_address_place_fuzzy() - No location provided!''; - ELSE - location := $7; - END IF; - IF $8 IS NOT NULL THEN - state := $8; - END IF; - - -- Check to see if the road name can be matched. - IF state IS NOT NULL THEN - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE soundex(location) = soundex(tiger_geocode_roads.place) - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state; - ELSE - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE soundex(location) = soundex(tiger_geocode_roads.place) - AND soundex(streetName) = soundex(tiger_geocode_roads.fename); - END IF; - IF verbose THEN - RAISE NOTICE ''geocode_address_place_fuzzy() - % potential matches.'', tempInt; - END IF; - IF tempInt = 0 THEN - RETURN NULL; - ELSE - -- The road name matches, now we check to see if the addresses match - IF state IS NOT NULL THEN - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE soundex(location) = soundex(tiger_geocode_roads.place) - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid; - ELSE - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE soundex(location) = soundex(tiger_geocode_roads.place) - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid; - END IF; - IF verbose THEN - RAISE NOTICE ''geocode_address_place_fuzzy() - % address matches.'', tempInt; - END IF; - IF tempInt = 0 THEN - return NULL; - ELSE - IF state IS NOT NULL THEN - OPEN result FOR - SELECT *, interpolate_from_address(address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs, location, - tiger_geocode_roads.place) as rating - FROM tiger_geocode_roads - WHERE soundex(location) = soundex(tiger_geocode_roads.place) - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid - ORDER BY subquery.rating; - return result; - ELSE - OPEN result FOR - SELECT *, interpolate_from_address(address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs, location, - tiger_geocode_roads.place) as rating - FROM tiger_geocode_roads - WHERE soundex(location) = soundex(tiger_geocode_roads.place) - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid - ORDER BY subquery.rating; - RETURN result; - END IF; - END IF; - END IF; -END; -' LANGUAGE plpgsql; - - - -CREATE OR REPLACE FUNCTION geocode_address_state(REFCURSOR, INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR) RETURNS REFCURSOR -AS ' -DECLARE - result REFCURSOR; - address INTEGER; - directionPrefix VARCHAR; - streetName VARCHAR; - streetTypeAbbrev VARCHAR; - directionSuffix VARCHAR; - state VARCHAR; - tempString VARCHAR; - tempInt VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''geocode_address_state()''; - END IF; - -- The first step is to determine what weve been given, and if its enough. - IF $1 IS NOT NULL THEN - -- The cursor was not provided. No matter, we can use an unnamed one. - result := $1; - END IF; - IF $2 IS NULL THEN - -- The address is manditory. - -- Without it, wed be wandering into strangers homes all the time. - RAISE EXCEPTION ''geocode_address_state() - No address provided!''; - ELSE - address := $2; - END IF; - IF $3 IS NOT NULL THEN - -- The direction prefix really isnt important. - -- It will be used for rating if provided. - directionPrefix := $3; - END IF; - IF $4 IS NULL THEN - -- A street name must be given. Think about it. - RAISE EXCEPTION ''geocode_address_state() - No street name provided!''; - ELSE - streetName := $4; - END IF; - IF $5 IS NOT NULL THEN - -- A street type will be used for rating if provided, but isnt required. - streetTypeAbbrev := $5; - END IF; - IF $6 IS NOT NULL THEN - -- Same as direction prefix, only later. - directionSuffix := $6; - END IF; - IF $7 IS NOT NULL THEN - state := $7; - ELSE - -- It is unreasonable to do a country wide search. State is already - -- pretty sketchy. No state, no search. - RAISE EXCEPTION ''geocode_address_state() - No state name provided!''; - END IF; - - -- Check to see if the road name can be matched. - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state; - IF verbose THEN - RAISE NOTICE ''geocode_address_state() - % potential matches.'', tempInt; - END IF; - IF tempInt = 0 THEN - RETURN NULL; - ELSE - -- The road name matches, now we check to see if the addresses match - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid; - IF verbose THEN - RAISE NOTICE ''geocode_address_state() - % address matches.'', tempInt; - END IF; - IF tempInt = 0 THEN - return NULL; - ELSE - OPEN result FOR - SELECT *, interpolate_from_address(address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE soundex(streetName) = soundex(tiger_geocode_roads.fename) - AND state = tiger_geocode_roads.state - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid - ORDER BY subquery.rating; - return result; - END IF; - END IF; -END; -' LANGUAGE plpgsql; - - - -CREATE OR REPLACE FUNCTION geocode_address_zip(REFCURSOR, INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, INTEGER) RETURNS REFCURSOR -AS ' -DECLARE - result REFCURSOR; - address INTEGER; - directionPrefix VARCHAR; - streetName VARCHAR; - streetTypeAbbrev VARCHAR; - directionSuffix VARCHAR; - zipCode INTEGER; - tempString VARCHAR; - tempInt VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''geocode_address_zip()''; - END IF; - -- The first step is to determine what weve been given, and if its enough. - IF $1 IS NOT NULL THEN - -- The cursor was not provided. No matter, we can use an unnamed one. - result := $1; - END IF; - IF $2 IS NULL THEN - -- The address is manditory. - -- Without it, wed be wandering into strangers homes all the time. - RAISE EXCEPTION ''geocode_address_zip() - No address provided!''; - ELSE - address := $2; - END IF; - IF $3 IS NOT NULL THEN - -- The direction prefix really isnt important. - -- It will be used for rating if provided. - directionPrefix := $3; - END IF; - IF $4 IS NULL THEN - -- A street name must be given. Think about it. - RAISE EXCEPTION ''geocode_address_zip() - No street name provided!''; - ELSE - streetName := $4; - END IF; - IF $5 IS NOT NULL THEN - -- A street type will be used for rating if provided, but isnt required. - streetTypeAbbrev := $5; - END IF; - IF $6 IS NOT NULL THEN - -- Same as direction prefix, only later. - directionSuffix := $6; - END IF; - IF $7 IS NULL THEN - -- Zip code is not optional. - RAISE EXCEPTION ''geocode_address_zip() - No zip provided!''; - ELSE - zipCode := $7; - END IF; - - -- Check to see if the road name can be matched. - SELECT INTO tempInt count(*) FROM tiger_geocode_roads - WHERE zipCode = tiger_geocode_roads.zip - AND soundex(streetName) = soundex(tiger_geocode_roads.fename); - IF tempInt = 0 THEN - return NULL; - ELSE - -- The road name matches, now we check to see if the addresses match - SELECT INTO tempInt count(*) - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE zipCode = tiger_geocode_roads.zip - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid; - IF tempInt = 0 THEN - return NULL; - ELSE - OPEN result FOR - SELECT *, interpolate_from_address(address, roads_local.fraddl, - roads_local.toaddl, roads_local.fraddr, roads_local.toaddr, - roads_local.geom) as address_geom - FROM ( - SELECT *, rate_attributes(directionPrefix, tiger_geocode_roads.fedirp, - streetName, tiger_geocode_roads.fename, streetTypeAbbrev, - tiger_geocode_roads.fetype, directionSuffix, - tiger_geocode_roads.fedirs) as rating - FROM tiger_geocode_roads - WHERE zipCode = tiger_geocode_roads.zip - AND soundex(streetName) = soundex(tiger_geocode_roads.fename) - ) AS subquery, tiger_geocode_join, roads_local - WHERE includes_address(address, roads_local.fraddl, roads_local.toaddl, - roads_local.fraddr, roads_local.toaddr) - AND subquery.id = tiger_geocode_join.id - AND tiger_geocode_join.tlid = roads_local.tlid - ORDER BY subquery.rating; - return result; - END IF; - END IF; -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(VARCHAR, INTEGER) RETURNS VARCHAR -AS ' -DECLARE - inputString VARCHAR; - tempString VARCHAR; - count VARCHAR; - result VARCHAR := ''''; -BEGIN - IF $1 IS NULL THEN - return NULL; - ELSE - inputString := $1; - END IF; - IF $2 IS NULL THEN - RAISE EXCEPTION ''get_last_words() - word count is null!''; - ELSE - count := $2; - END IF; - 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; - - - --- This function converts the string addresses to integers and passes them --- to the other includes_address function. -CREATE OR REPLACE FUNCTION includes_address(INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR) RETURNS BOOLEAN -AS ' -DECLARE - given_address INTEGER; - addr1 INTEGER; - addr2 INTEGER; - addr3 INTEGER; - addr4 INTEGER; - result BOOLEAN; -BEGIN - given_address = $1; - addr1 = to_number($2, ''999999''); - addr2 = to_number($3, ''999999''); - addr3 = to_number($4, ''999999''); - addr4 = to_number($5, ''999999''); - result = includes_address(given_address, addr1, addr2, addr3, addr4); - RETURN result; -END -' LANGUAGE plpgsql; - - - --- This function requires the addresses to be grouped, such that the second and --- third arguments are from one side of the street, and the fourth and fifth --- from the other. -CREATE OR REPLACE FUNCTION includes_address(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) RETURNS BOOLEAN -AS ' -DECLARE - given_address INTEGER; - addr1 INTEGER; - addr2 INTEGER; - addr3 INTEGER; - addr4 INTEGER; - lmaxaddr INTEGER := -1; - rmaxaddr INTEGER := -1; - lminaddr INTEGER := -1; - rminaddr INTEGER := -1; - maxaddr INTEGER := -1; - minaddr INTEGER := -1; -BEGIN - IF $1 IS NULL THEN - RAISE EXCEPTION ''includes_address() - local address is NULL!''; - ELSE - given_address := $1; - END IF; - - IF $2 IS NOT NULL THEN - addr1 := $2; - maxaddr := addr1; - minaddr := addr1; - lmaxaddr := addr1; - lminaddr := addr1; - END IF; - - IF $3 IS NOT NULL THEN - addr2 := $3; - IF addr2 < minaddr OR minaddr = -1 THEN - minaddr := addr2; - END IF; - IF addr2 > maxaddr OR maxaddr = -1 THEN - maxaddr := addr2; - END IF; - IF addr2 > lmaxaddr OR lmaxaddr = -1 THEN - lmaxaddr := addr2; - END IF; - IF addr2 < lminaddr OR lminaddr = -1 THEN - lminaddr := addr2; - END IF; - END IF; - - IF $4 IS NOT NULL THEN - addr3 := $4; - IF addr3 < minaddr OR minaddr = -1 THEN - minaddr := addr3; - END IF; - IF addr3 > maxaddr OR maxaddr = -1 THEN - maxaddr := addr3; - END IF; - rmaxaddr := addr3; - rminaddr := addr3; - END IF; - - IF $5 IS NOT NULL THEN - addr4 := $5; - IF addr4 < minaddr OR minaddr = -1 THEN - minaddr := addr4; - END IF; - IF addr4 > maxaddr OR maxaddr = -1 THEN - maxaddr := addr4; - END IF; - IF addr4 > rmaxaddr OR rmaxaddr = -1 THEN - rmaxaddr := addr4; - END IF; - IF addr4 < rminaddr OR rminaddr = -1 THEN - rminaddr := addr4; - END IF; - END IF; - - IF minaddr = -1 OR maxaddr = -1 THEN - -- No addresses were non-null, return FALSE (arbitrary) - RETURN FALSE; - ELSIF given_address >= minaddr AND given_address <= maxaddr THEN - -- The address is within the given range - IF given_address >= lminaddr AND given_address <= lmaxaddr THEN - -- This checks to see if the address is on this side of the - -- road, ie if the address is even, the street range must be even - IF (given_address % 2) = (lminaddr % 2) - OR (given_address % 2) = (lmaxaddr % 2) THEN - RETURN TRUE; - END IF; - END IF; - IF given_address >= rminaddr AND given_address <= rmaxaddr THEN - -- See above - IF (given_address % 2) = (rminaddr % 2) - OR (given_address % 2) = (rmaxaddr % 2) THEN - RETURN TRUE; - END IF; - END IF; - END IF; - -- The address is not within the range - RETURN FALSE; -END; - -' LANGUAGE plpgsql; - - - --- This function converts string addresses to integers and passes them to --- the other interpolate_from_address function. -CREATE OR REPLACE FUNCTION interpolate_from_address(INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, GEOMETRY) RETURNS GEOMETRY -AS ' -DECLARE - given_address INTEGER; - addr1 INTEGER; - addr2 INTEGER; - addr3 INTEGER; - addr4 INTEGER; - road GEOMETRY; - result GEOMETRY; -BEGIN - given_address := $1; - addr1 := to_number($2, ''999999''); - addr2 := to_number($3, ''999999''); - addr3 := to_number($4, ''999999''); - addr4 := to_number($5, ''999999''); - road := $6; - result = interpolate_from_address(given_address, addr1, addr2, addr3, addr4, road); - RETURN result; -END -' LANGUAGE plpgsql; - --- interpolate_from_address(local_address, from_address_l, to_address_l, from_address_r, to_address_r, local_road) --- This function returns a point along the given geometry (must be linestring) --- corresponding to the given address. If the given address is not within --- the address range of the road, null is returned. --- This function requires that the address be grouped, such that the second and --- third arguments are from one side of the street, while the fourth and --- fifth are from the other. -CREATE OR REPLACE FUNCTION interpolate_from_address(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, GEOMETRY) RETURNS GEOMETRY -AS ' -DECLARE - given_address INTEGER; - lmaxaddr INTEGER := -1; - rmaxaddr INTEGER := -1; - lminaddr INTEGER := -1; - rminaddr INTEGER := -1; - lfrgreater BOOLEAN; - rfrgreater BOOLEAN; - frgreater BOOLEAN; - addrwidth INTEGER; - part DOUBLE PRECISION; - road GEOMETRY; - result GEOMETRY; -BEGIN - IF $1 IS NULL THEN - RAISE EXCEPTION ''interpolate_from_address() - local address is NULL!''; - ELSE - given_address := $1; - END IF; - - IF $6 IS NULL THEN - RAISE EXCEPTION ''interpolate_from_address() - local road is NULL!''; - ELSE - IF geometrytype($6) = ''LINESTRING'' THEN - road := $6; - ELSIF geometrytype($6) = ''MULTILINESTRING'' THEN - road := geometryn($6,1); - ELSE - RAISE EXCEPTION ''interpolate_from_address() - local road is not a line!''; - END IF; - END IF; - - IF $2 IS NOT NULL THEN - lfrgreater := TRUE; - lmaxaddr := $2; - lminaddr := $2; - END IF; - - IF $3 IS NOT NULL THEN - IF $3 > lmaxaddr OR lmaxaddr = -1 THEN - lmaxaddr := $3; - lfrgreater := FALSE; - END IF; - IF $3 < lminaddr OR lminaddr = -1 THEN - lminaddr := $3; - END IF; - END IF; - - IF $4 IS NOT NULL THEN - rmaxaddr := $4; - rminaddr := $4; - rfrgreater := TRUE; - END IF; - - IF $5 IS NOT NULL THEN - IF $5 > rmaxaddr OR rmaxaddr = -1 THEN - rmaxaddr := $5; - rfrgreater := FALSE; - END IF; - IF $5 < rminaddr OR rminaddr = -1 THEN - rminaddr := $5; - END IF; - END IF; - - IF given_address >= lminaddr AND given_address <= lmaxaddr THEN - IF (given_address % 2) = (lminaddr % 2) - OR (given_address % 2) = (lmaxaddr % 2) THEN - addrwidth := lmaxaddr - lminaddr; - part := (given_address - lminaddr) / trunc(addrwidth, 1); - frgreater := lfrgreater; - END IF; - END IF; - IF given_address >= rminaddr AND given_address <= rmaxaddr THEN - IF (given_address % 2) = (rminaddr % 2) - OR (given_address % 2) = (rmaxaddr % 2) THEN - addrwidth := rmaxaddr - rminaddr; - part := (given_address - rminaddr) / trunc(addrwidth, 1); - frgreater := rfrgreater; - END IF; - ELSE - RETURN null; - END IF; - - IF frgreater THEN - part := 1 - part; - END IF; - - result = line_interpolate_point(road, part); - RETURN result; -END; -' LANGUAGE plpgsql; - - --- This function determines the levenshtein distance irespective of case. -CREATE OR REPLACE FUNCTION levenshtein_ignore_case(VARCHAR, VARCHAR) RETURNS INTEGER -AS ' -DECLARE - result INTEGER; -BEGIN - result := levenshtein(upper($1), upper($2)); - RETURN result; -END -' LANGUAGE plpgsql; - --- This function take two arguements. The first is the "given string" and --- must not be null. The second arguement is the "compare string" and may --- or may not be null. If the second string is null, the value returned is --- 3, otherwise it is the levenshtein difference between the two. -CREATE OR REPLACE FUNCTION nullable_levenshtein(VARCHAR, VARCHAR) RETURNS INTEGER -AS ' -DECLARE - given_string VARCHAR; - result INTEGER := 3; -BEGIN - IF $1 IS NULL THEN - RAISE EXCEPTION ''nullable_levenshtein - given string is NULL!''; - ELSE - given_string := $1; - END IF; - - IF $2 IS NOT NULL AND $2 != '''' THEN - result := levenshtein_ignore_case(given_string, $2); - END IF; - - RETURN result; -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(VARCHAR, VARCHAR) RETURNS VARCHAR -AS ' -DECLARE - fullStreet VARCHAR; - stateAbbrev VARCHAR; - location VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''location_extract()''; - END IF; - IF $1 IS NULL THEN - RAISE EXCEPTION ''location_extract() - No input given!''; - ELSE - fullStreet := $1; - END IF; - IF $2 IS NULL THEN - ELSE - stateAbbrev := $2; - END IF; - location := location_extract_place_exact(fullStreet, stateAbbrev); - IF location IS NULL THEN - location := location_extract_countysub_exact(fullStreet, stateAbbrev); - IF location IS NULL THEN - location := location_extract_place_fuzzy(fullStreet, stateAbbrev); - IF location IS NULL THEN - location := location_extract_countysub_fuzzy(fullStreet, stateAbbrev); - END IF; - 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(VARCHAR, VARCHAR) RETURNS VARCHAR -AS ' -DECLARE - fullStreet VARCHAR; - ws VARCHAR; - tempString VARCHAR; - location VARCHAR; - tempInt INTEGER; - word_count INTEGER; - stateAbbrev VARCHAR; - rec RECORD; - test BOOLEAN; - result VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''location_extract_countysub_exact()''; - END IF; - IF $1 IS NULL THEN - RAISE EXCEPTION ''location_extract_countysub_exact() - No input given!''; - ELSE - fullStreet := $1; - END IF; - IF $2 IS NOT NULL THEN - stateAbbrev := $2; - END IF; - ws := ''[ ,\.\n\f\t]''; - -- No hope of determining the location from place. Try countysub. - IF stateAbbrev IS NOT NULL THEN - SELECT INTO tempInt count(*) FROM countysub_lookup - WHERE countysub_lookup.state = stateAbbrev - AND texticregexeq(fullStreet, ''(?i)'' || name || ''$''); - ELSE - SELECT INTO tempInt count(*) FROM countysub_lookup - 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 countysub_lookup - WHERE countysub_lookup.state = stateAbbrev - 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 countysub_lookup - 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_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(VARCHAR, VARCHAR) RETURNS VARCHAR -AS ' -DECLARE - fullStreet VARCHAR; - ws VARCHAR; - tempString VARCHAR; - location VARCHAR; - tempInt INTEGER; - word_count INTEGER; - stateAbbrev VARCHAR; - rec RECORD; - test BOOLEAN; - result VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''location_extract_countysub_fuzzy()''; - END IF; - IF $1 IS NULL THEN - RAISE EXCEPTION ''location_extract_countysub_fuzzy() - No input given!''; - ELSE - fullStreet := $1; - END IF; - IF $2 IS NOT NULL THEN - stateAbbrev := $2; - END IF; - ws := ''[ ,\.\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 - SELECT INTO tempInt count(*) FROM countysub_lookup - WHERE countysub_lookup.state = stateAbbrev - AND soundex(tempString) = end_soundex(name); - ELSE - SELECT INTO tempInt count(*) FROM countysub_lookup - 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 countysub_lookup - WHERE countysub_lookup.state = stateAbbrev - AND soundex(tempString) = end_soundex(name) LOOP - word_count := count_words(rec.name); - test := TRUE; - tempString := get_last_words(fullStreet, word_count); - FOR i IN 1..word_count LOOP - IF soundex(split_part(tempString, '' '', i)) != - soundex(split_part(rec.name, '' '', i)) THEN - test := FALSE; - END IF; - END LOOP; - IF test THEN - -- The soundex matched, determine if the distance is better. - IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN - location := tempString; - tempInt := levenshtein_ignore_case(rec.name, tempString); - END IF; - END IF; - END LOOP; - ELSE - FOR rec IN SELECT name FROM countysub_lookup - WHERE soundex(tempString) = end_soundex(name) LOOP - word_count := count_words(rec.name); - test := TRUE; - tempString := get_last_words(fullStreet, word_count); - FOR i IN 1..word_count LOOP - IF soundex(split_part(tempString, '' '', i)) != - soundex(split_part(rec.name, '' '', i)) THEN - test := FALSE; - END IF; - END LOOP; - IF test THEN - -- The soundex matched, determine if the distance is better. - IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN - location := tempString; - tempInt := levenshtein_ignore_case(rec.name, tempString); - END IF; - END IF; - END LOOP; - END IF; - END IF; -- If no fuzzys were found, leave location null. - 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(VARCHAR, VARCHAR) RETURNS VARCHAR -AS ' -DECLARE - fullStreet VARCHAR; - ws VARCHAR; - tempString VARCHAR; - location VARCHAR; - tempInt INTEGER; - word_count INTEGER; - stateAbbrev VARCHAR; - rec RECORD; - test BOOLEAN; - result VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''location_extract_place_exact()''; - END IF; - IF $1 IS NULL THEN - RAISE EXCEPTION ''location_extract_place_exact() - No input given!''; - ELSE - fullStreet := $1; - END IF; - IF verbose THEN - RAISE NOTICE ''location_extract_place_exact() - input: "%"'', fullStreet; - END IF; - IF $2 IS NOT NULL THEN - stateAbbrev := $2; - END IF; - ws := ''[ ,\.\n\f\t]''; - -- Try for an exact match against places - IF stateAbbrev IS NOT NULL THEN - SELECT INTO tempInt count(*) FROM place_lookup - WHERE place_lookup.state = stateAbbrev - AND texticregexeq(fullStreet, ''(?i)'' || name || ''$''); - ELSE - SELECT INTO tempInt count(*) FROM place_lookup - WHERE texticregexeq(fullStreet, ''(?i)'' || name || ''$''); - END IF; - IF verbose THEN - RAISE NOTICE ''location_extract_place_exact() - Exact Matches %'', tempInt; - 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_lookup - WHERE place_lookup.state = stateAbbrev - 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_lookup - 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_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(VARCHAR, VARCHAR) RETURNS VARCHAR -AS ' -DECLARE - fullStreet VARCHAR; - ws VARCHAR; - tempString VARCHAR; - location VARCHAR; - tempInt INTEGER; - word_count INTEGER; - stateAbbrev VARCHAR; - rec RECORD; - test BOOLEAN; - result VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''location_extract_place_fuzzy()''; - END IF; - IF $1 IS NULL THEN - RAISE EXCEPTION ''location_extract_place_fuzzy() - No input given!''; - ELSE - fullStreet := $1; - END IF; - IF verbose THEN - RAISE NOTICE ''location_extract_place_fuzzy() - input: "%"'', fullStreet; - END IF; - IF $2 IS NOT NULL THEN - stateAbbrev := $2; - END IF; - ws := ''[ ,\.\n\f\t]''; - - tempString := substring(fullStreet, ''(?i)'' || ws - || ''([a-zA-Z0-9]+)$''); - IF tempString IS NULL THEN - tempString := fullStreet; - END IF; - IF stateAbbrev IS NOT NULL THEN - SELECT into tempInt count(*) FROM place_lookup - WHERE place_lookup.state = stateAbbrev - AND soundex(tempString) = end_soundex(name); - ELSE - SELECT into tempInt count(*) FROM place_lookup - WHERE soundex(tempString) = end_soundex(name); - END IF; - IF verbose THEN - RAISE NOTICE ''location_extract_place_fuzzy() - Fuzzy matches %'', tempInt; - END IF; - IF tempInt > 0 THEN - -- Some potentials were found. Begin a word-by-word soundex on each. - tempInt := 50; - IF stateAbbrev IS NOT NULL THEN - FOR rec IN SELECT name FROM place_lookup - WHERE place_lookup.state = stateAbbrev - AND soundex(tempString) = end_soundex(name) LOOP - IF verbose THEN - RAISE NOTICE ''location_extract_place_fuzzy() - Fuzzy: "%"'', rec.name; - END IF; - 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 - IF verbose THEN - RAISE NOTICE ''location_extract_place_fuzzy() - No Match.''; - END IF; - test := FALSE; - END IF; - END LOOP; - IF test THEN - -- The soundex matched, determine if the distance is better. - IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN - location := tempString; - tempInt := levenshtein_ignore_case(rec.name, tempString); - END IF; - END IF; - END LOOP; - ELSE - FOR rec IN SELECT name FROM place_lookup - WHERE soundex(tempString) = end_soundex(name) LOOP - word_count := count_words(rec.name); - test := TRUE; - tempString := get_last_words(fullStreet, word_count); - FOR i IN 1..word_count LOOP - IF soundex(split_part(tempString, '' '', i)) != - soundex(split_part(rec.name, '' '', i)) THEN - test := FALSE; - END IF; - END LOOP; - IF test THEN - -- The soundex matched, determine if the distance is better. - IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN - location := tempString; - tempInt := levenshtein_ignore_case(rec.name, tempString); - END IF; - END IF; - END LOOP; - END IF; - END IF; - RETURN location; -END; -' LANGUAGE plpgsql; - - - --- 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(VARCHAR) RETURNS VARCHAR -AS ' -DECLARE - rawInput VARCHAR; - address VARCHAR; - preDir VARCHAR; - preDirAbbrev VARCHAR; - postDir VARCHAR; - postDirAbbrev VARCHAR; - fullStreet VARCHAR; - reducedStreet VARCHAR; - streetName VARCHAR; - streetType VARCHAR; - streetTypeAbbrev VARCHAR; - internal VARCHAR; - location VARCHAR; - state VARCHAR; - stateAbbrev VARCHAR; - tempString VARCHAR; - tempInt INTEGER; - result VARCHAR; - zip VARCHAR; - test BOOLEAN; - working REFCURSOR; - rec RECORD; - ws VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''normalize_address()''; - END IF; - IF $1 IS NULL THEN - RAISE EXCEPTION ''normalise_address() - address string is null!''; - ELSE - rawInput := $1; - END IF; - ws := ''[ ,\.\t\n\f\r]''; - - -- Assume that the address begins with a digit, and extract it from - -- the input string. - address := substring(rawInput from ''^([0-9].*?)[ ,/.]''); - - -- There are two formats for zip code, the normal 5 digit, and - -- the nine digit zip-4. It may also not exist. - zip := substring(rawInput from ws || ''([0-9]{5})$''); - IF zip IS NULL THEN - zip := substring(rawInput from ws || ''([0-9]{5})-[0-9]{4}$''); - END IF; - - IF zip IS NOT NULL THEN - fullStreet := substring(rawInput from ''(.*)'' - || ws || ''+'' || cull_null(zip) || ''[- ]?([0-9]{4})?$''); - ELSE - fullStreet := rawInput; - END IF; - IF verbose THEN - RAISE NOTICE ''normalize_address() - after zip extract "%"'', fullStreet; - END IF; - tempString := state_extract(fullStreet); - IF tempString IS NOT NULL THEN - state := split_part(tempString, '':'', 1); - stateAbbrev := split_part(tempString, '':'', 2); - 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 IS NOT NULL THEN - location := tempString; - IF address IS NOT NULL THEN - fullStreet := substring(fullStreet, ''(?i)'' || address || ws || - ''+(.*),'' || ws || ''+'' || location); - ELSE - fullStreet := substring(fullStreet, ''(?i)(.*),'' || ws || ''+'' || - location); - END IF; - IF verbose THEN - RAISE NOTICE ''normalize_address() - Parsed by punctuation.''; - RAISE NOTICE ''normalize_address() - Location "%"'', location; - RAISE NOTICE ''normalize_address() - FullStreet "%"'', fullStreet; - END IF; - 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 location IS NULL THEN - IF address IS NOT NULL THEN - IF state IS NOT NULL THEN - fullStreet := substring(fullStreet, ''(?i)'' || address || - ws || ''+(.*?)'' || ws || ''+'' || state); - ELSE - fullStreet := substring(fullStreet, ''(?i)'' || address || - 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; - END IF; - IF verbose THEN - RAISE NOTICE ''normalize_address() - after addy extract "%"'', fullStreet; - 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 - SELECT INTO 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); - internal := rec.value; - END IF; - END LOOP; - END IF; - - IF verbose THEN - RAISE NOTICE ''normalize_address() - internal: "%"'', internal; - END IF; - - IF location IS NULL THEN - -- If the internal address is given, the location is everything after it. - location := substring(fullStreet, internal || ws || ''+(.*)$''); - END IF; - - -- Pull potential street types from the full street information - SELECT INTO 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; - 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 internal IS NOT NULL THEN - IF position(rec.given IN fullStreet) < - position(internal IN fullStreet) THEN - IF tempInt < position(rec.given IN fullStreet) THEN - streetType := rec.given; - streetTypeAbbrev := rec.abbrev; - tempInt := position(rec.given IN fullStreet); - END IF; - END IF; - ELSIF tempInt < position(rec.given IN fullStreet) THEN - streetType := rec.given; - streetTypeAbbrev := rec.abbrev; - tempInt := position(rec.given IN fullStreet); - END IF; - END LOOP; - END IF; - IF verbose THEN - RAISE NOTICE ''normalize_address() - street Type: "%"'', streetType; - 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 Im sad. - IF streetType IS NOT NULL THEN - tempString := substring(fullStreet, streetType || ws || - ''+([0-9][^ ,\.\t\r\n\f]*?)'' || ws); - IF tempString IS NOT NULL THEN - IF location IS NULL THEN - location := substring(fullStreet, streetType || ws || ''+'' - || tempString || ws || ''+(.*)$''); - END IF; - reducedStreet := substring(fullStreet, ''(.*)'' || ws || ''+'' - || location || ''$''); - streetType := NULL; - streetTypeAbbrev := NULL; - ELSE - IF location IS NULL THEN - 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 - 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 preDirAbbrev abbrev FROM direction_lookup - where texticregexeq(reducedStreet, ''(?i)(^'' || name || '')'' || ws) - ORDER BY length(name) DESC; - streetName := substring(reducedStreet, ''^'' || preDir || ws || ''(.*)''); - ELSE - streetName := reducedStreet; - END IF; - - IF texticregexeq(location, ''(?i)'' || 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(location, ''(?i)^('' || name || '')'' || ws) as value - FROM direction_lookup WHERE texticregexeq(location, ''(?i)^'' - || name || ws) ORDER BY length(name) desc; - IF rec.value IS NOT NULL THEN - postDir := rec.value; - postDirAbbrev := rec.abbrev; - END IF; - location := null; - ELSIF 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(location) = upper(name); - IF tempInt != 0 THEN - postDir := location; - SELECT INTO postDirAbbrev abbrev FROM direction_lookup WHERE - upper(postDir) = upper(name); - location := NULL; - ELSE - -- postDirection is not equal location, but may be contained in it. - SELECT INTO tempString substring(location, ''(?i)(^'' || name - || '')'' || ws) FROM direction_lookup WHERE - texticregexeq(location, ''(?i)(^'' || name || '')'' || ws) - ORDER BY length(name) desc; - IF tempString IS NOT NULL THEN - postDir := tempString; - SELECT INTO postDirAbbrev abbrev FROM direction_lookup - where texticregexeq(location, ''(?i)(^'' || name || '')'' || ws); - location := substring(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 || ''+'' || internal) - FROM direction_lookup WHERE texticregexeq(fullStreet, ''(?i)'' - || ws || name || ws || ''+'' || internal) ORDER BY length(name) desc; - IF tempString IS NOT NULL THEN - postDir := tempString; - SELECT INTO 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 internal IS NOT NULL THEN - reducedStreet := substring(fullStreet, ''(?i)^(.*?)'' || ws || ''+'' - || internal); - SELECT INTO tempInt count(*) FROM direction_lookup WHERE - texticregexeq(reducedStreet, ''(?i)'' || ws || name || ''$''); - IF tempInt > 0 THEN - SELECT INTO postDir substring(reducedStreet, ''(?i)'' || ws || ''('' - || name || '')'' || ''$'') FROM direction_lookup - WHERE texticregexeq(reducedStreet, ''(?i)'' || ws || name || ''$''); - SELECT INTO postDirAbbrev abbrev FROM direction_lookup - WHERE texticregexeq(reducedStreet, ''(?i)'' || ws || name || ''$''); - END IF; - 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 preDirAbbrev abbrev FROM direction_lookup WHERE - texticregexeq(reducedStreet, ''(?i)(^'' || name || '')'' || ws) - ORDER BY length(name) DESC; - streetName := substring(reducedStreet, ''(?i)^'' || preDir || ws - || ''+(.*?)(?:'' || ws || ''+'' || cull_null(postDir) || ''|$)''); - ELSE - 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. - SELECT INTO 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. - SELECT INTO postDir substring(fullStreet, ''(?i)'' || ws || ''('' - || name || '')(?:'' || ws || ''|$)'') FROM direction_lookup WHERE - texticregexeq(fullStreet, ''(?i)'' || ws || name || ''(?:'' - || ws || ''|$)''); - SELECT INTO postDirAbbrev abbrev FROM direction_lookup - WHERE texticregexeq(fullStreet, ''(?i)'' || ws || name - || ''(?:'' || ws || ''|$)''); - IF location IS NULL THEN - location := substring(fullStreet, ''(?i)'' || ws || postDir - || ws || ''+(.*?)$''); - END IF; - reducedStreet := substring(fullStreet, ''^(.*?)'' || 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 preDirAbbrev abbrev FROM direction_lookup WHERE - texticregexeq(reducedStreet, ''(?i)(^'' || name || '')'' || ws) - ORDER BY length(name) DESC; - streetName := substring(reducedStreet, ''^'' || preDir || ws - || ''+(.*)''); - ELSE - 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; - postDirAbbrev := rec.abbrev; - ELSIF NOT texticregexeq(postDir, ''(?i)'' || rec.value) THEN - tempInt := position(rec.value in fullStreet); - postDir := rec.value; - postDirAbbrev := rec.abbrev; - END IF; - END IF; - END LOOP; - IF location IS NULL THEN - 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 preDirAbbrev abbrev FROM direction_lookup WHERE - texticregexeq(reducedStreet, ''(?i)(^'' || name || '')'' || ws) - ORDER BY length(name) DESC; - streetName := substring(reducedStreet, ''^'' || preDir || ws - || ''+(.*)''); - ELSE - streetName := reducedStreet; - END IF; - ELSE - - -- There is no street type, directional suffix or internal address - -- to allow distinction between street name and location. - IF location IS NULL THEN - location := location_extract(fullStreet, stateAbbrev); - 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); - RAISE NOTICE ''DEBUG 1''; - IF tempString IS NOT NULL THEN - preDir := tempString; - SELECT INTO preDirAbbrev abbrev FROM direction_lookup WHERE - texticregexeq(fullStreet, ''(?i)(^'' || name || '')'' || ws) - ORDER BY length(name) DESC; - IF location IS NOT NULL THEN - -- The location may still be in the fullStreet, or may - -- have been removed already - streetName := substring(fullStreet, ''^'' || preDir || ws - || ''+(.*?)('' || ws || ''+'' || location || ''|$)''); - RAISE NOTICE ''DEBUG 2.1 "%", "%"'', streetName, fullStreet; - ELSE - streetName := substring(fullStreet, ''^'' || preDir || ws - || ''+(.*?)'' || ws || ''*''); - END IF; - ELSE - IF location IS NOT NULL THEN - -- The location may still be in the fullStreet, or may - -- have been removed already - streetName := substring(fullStreet, ''^(.*?)('' || ws - || ''+'' || location || ''|$)''); - RAISE NOTICE ''DEBUG 2.2 "%", "%"'', streetName, fullStreet; - ELSE - streetName := fullStreet; - END IF; - END IF; - END IF; - END IF; - END IF; - - - - RAISE NOTICE ''normalize_address() - final internal "%"'', internal; - RAISE NOTICE ''normalize_address() - prefix_direction "%"'', preDir; - RAISE NOTICE ''normalize_address() - street_type "%"'', streetType; - RAISE NOTICE ''normalize_address() - suffix_direction "%"'', postDir; - RAISE NOTICE ''normalize_address() - state "%"'', state; - - -- This is useful for scripted checking. It returns what was entered - -- for each field, rather than what should be used by the geocoder. - --result := cull_null(internal) || '':'' || cull_null(address) || '':'' - --|| cull_null(preDir) || '':'' || cull_null(streetName) || '':'' - --|| cull_null(streetType) || '':'' || cull_null(postDir) - --|| '':'' || cull_null(location) || '':'' || cull_null(state) || '':'' - --|| cull_null(zip); - - -- This is the standardized return. - result := cull_null(address) || '':'' || cull_null(preDirAbbrev) || '':'' - || cull_null(streetName) || '':'' || cull_null(streetTypeAbbrev) || '':'' - || cull_null(postDirAbbrev) || '':'' || cull_null(location) || '':'' - || cull_null(stateAbbrev) || '':'' || cull_null(zip); - return result; -END -' LANGUAGE plpgsql; - - - --- rate_attributes(dirpA, dirpB, streetNameA, streetNameB, streetTypeA, --- streetTypeB, dirsA, dirsB, locationA, locationB) --- Rates the street based on the given attributes. The locations must be --- non-null. The other eight values are handled by the other rate_attributes --- function, so it's requirements must also be met. -CREATE OR REPLACE FUNCTION rate_attributes(VARCHAR, VARCHAR, VARCHAR, VARCHAR, - VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR) RETURNS INTEGER -AS ' -DECLARE - result INTEGER := 0; - locationWeight INTEGER := 14; -BEGIN - IF $9 IS NOT NULL AND $10 IS NOT NULL THEN - result := levenshtein_ignore_case($9, $10); - ELSE - RAISE EXCEPTION ''rate_attributes() - Location names cannot be null!''; - END IF; - result := result + rate_attributes($1, $2, $3, $4, $5, $6, $7, $8); - RETURN result; -END; -' LANGUAGE plpgsql; - --- rate_attributes(dirpA, dirpB, streetNameA, streetNameB, streetTypeA, --- streetTypeB, dirsA, dirsB) --- Rates the street based on the given attributes. Only streetNames are --- required. If any others are null (either A or B) they are treated as --- empty strings. -CREATE OR REPLACE FUNCTION rate_attributes(VARCHAR, VARCHAR, VARCHAR, VARCHAR, - VARCHAR, VARCHAR, VARCHAR, VARCHAR) RETURNS INTEGER -AS ' -DECLARE - result INTEGER := 0; - directionWeight INTEGER := 2; - nameWeight INTEGER := 10; - typeWeight INTEGER := 5; -BEGIN - result := result + levenshtein_ignore_case(cull_null($1), cull_null($2)) * - directionWeight; - IF $3 IS NOT NULL AND $4 IS NOT NULL THEN - result := result + levenshtein_ignore_case($3, $4) * nameWeight; - ELSE - RAISE EXCEPTION ''rate_attributes() - Street names cannot be null!''; - END IF; - result := result + levenshtein_ignore_case(cull_null($5), cull_null($6)) * - typeWeight; - result := result + levenshtein_ignore_case(cull_null($7), cull_null($7)) * - directionWeight; - 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(VARCHAR) RETURNS VARCHAR -AS ' -DECLARE - tempInt INTEGER; - tempString VARCHAR; - rawInput VARCHAR; - state VARCHAR; - stateAbbrev VARCHAR; - result VARCHAR; - rec RECORD; - test BOOLEAN; - ws VARCHAR; - verbose BOOLEAN := TRUE; -BEGIN - IF verbose THEN - RAISE NOTICE ''state_extract()''; - END IF; - IF $1 IS NULL THEN - RAISE EXCEPTION ''state_extract() - no input''; - ELSE - rawInput := $1; - END IF; - ws := ''[ ,\.\t\n\f\r]''; - - -- 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(rawInput from ws || ''+([^ ,\.\t\n\f\r0-9]*?)$''); - 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_2006andbefore/tables/lookup_tables.sql b/extras/tiger_geocoder/tiger_2006andbefore/tables/lookup_tables.sql deleted file mode 100644 index 96fff14de..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/tables/lookup_tables.sql +++ /dev/null @@ -1,885 +0,0 @@ --- 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::integer = sl.st_code) - 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::integer = sl.st_code) - 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::integer = sl.st_code) - JOIN county_lookup cl ON (cs.state::integer = cl.st_code AND cs.county::integer = 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::integer = sl.st_code) - LEFT JOIN county_lookup cl ON (rl.statel::integer = cl.st_code AND rl.countyl::integer = cl.co_code) - LEFT JOIN countysub_lookup cs ON (rl.statel::integer = cs.st_code AND rl.countyl::integer = cs.co_code AND rl.cousubl = cs.cs_code) - LEFT JOIN place_lookup pl ON (rl.statel::integer = pl.st_code AND rl.placel::integer = 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 = sl.st_code) - LEFT JOIN county_lookup cl ON (rl.stater = cl.st_code AND rl.countyr = cl.co_code) - LEFT JOIN countysub_lookup cs ON (rl.stater = cs.st_code AND rl.countyr = cs.co_code AND rl.cousubr = cs.cs_code) - LEFT JOIN place_lookup pl ON (rl.stater = pl.st_code 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 INTEGER, - state VARCHAR(40), - county VARCHAR(90), - city VARCHAR(90), - 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) -); - -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; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/tables/roads_local.sql b/extras/tiger_geocoder/tiger_2006andbefore/tables/roads_local.sql deleted file mode 100644 index a1be54ef2..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/tables/roads_local.sql +++ /dev/null @@ -1,92 +0,0 @@ -create table roads_local ( - ogc_fid INTEGER, - geom GEOMETRY, - module VARCHAR(8), - tlid INTEGER, - side1 INTEGER, - source CHAR(1), - fedirp VARCHAR(2), - fename VARCHAR(30), - fetype VARCHAR(4), - fedirs VARCHAR(2), - cfcc VARCHAR(3), - fraddl INTEGER, - toaddl INTEGER, - fraddr INTEGER, - toaddr INTEGER, - friaddl CHAR(1), - toiaddl CHAR(1), - friaddr CHAR(1), - toiaddr CHAR(1), - zipl INTEGER, - zipr INTEGER, - aianhhfpl INTEGER, - aianhhfpr INTEGER, - aihhtlil CHAR(1), - aihhtlir CHAR(1), - census1 CHAR(1), - census2 CHAR(1), - statel INTEGER, - stater INTEGER, - countyl INTEGER, - countyr INTEGER, - cousubl INTEGER, - cousubr INTEGER, - submcdl INTEGER, - submcdr INTEGER, - placel INTEGER, - placer INTEGER, - tractl INTEGER, - tractr INTEGER, - blockl INTEGER, - blockr INTEGER -); - -insert into roads_local -select - ogc_fid, - wkb_geometry, - trim(module), - tlid, - side1, - trim(source), - trim(fedirp), - trim(fename), - trim(fetype), - trim(fedirs), - trim(cfcc), - to_number(fraddl,'99999999999'), - to_number(toaddl,'99999999999'), - to_number(fraddr,'99999999999'), - to_number(toaddr,'99999999999'), - trim(friaddl), - trim(toiaddl), - trim(friaddr), - trim(toiaddr), - zipl, - zipr, - aianhhfpl, - aianhhfpr, - trim(aihhtlil), - trim(aihhtlir), - trim(census1), - trim(census2), - statel, - stater, - countyl, - countyr, - cousubl, - cousubr, - submcdl, - submcdr, - placel, - placer, - tractl, - tractr, - blockl, - blockr -from completechain; - -CREATE INDEX roads_local_tlid_idx ON roads_local (tlid); -CREATE INDEX roads_local_geom_idx ON roads_local USING GIST (geom public.gist_geometry_ops); -CREATE INDEX roads_local_cfcc_idx ON roads_local (cfcc); diff --git a/extras/tiger_geocoder/tiger_2006andbefore/tables/tiger_geocode_roads.sql b/extras/tiger_geocoder/tiger_2006andbefore/tables/tiger_geocode_roads.sql deleted file mode 100644 index b1eaff233..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/tables/tiger_geocode_roads.sql +++ /dev/null @@ -1,78 +0,0 @@ -DROP SEQUENCE IF EXISTS tiger_geocode_roads_seq; -CREATE SEQUENCE tiger_geocode_roads_seq; - -DROP TABLE IF EXISTS tiger_geocode_roads; -CREATE TABLE tiger_geocode_roads ( - id INTEGER, - tlid INTEGER, - fedirp VARCHAR(2), - fename VARCHAR(30), - fetype VARCHAR(4), - fedirs VARCHAR(2), - zip INTEGER, - state VARCHAR(2), - county VARCHAR(90), - cousub VARCHAR(90), - place VARCHAR(90) -); - -INSERT INTO tiger_geocode_roads - SELECT - nextval('tiger_geocode_roads_seq'), - tlid, - fedirp, - fename, - fetype, - fedirs, - zip, - state, - county, - cousub, - place - FROM - (SELECT - tlid, - fedirp, - fename, - fetype, - fedirs, - zipl as zip, - sl.abbrev as state, - co.name as county, - cs.name as cousub, - pl.name as place - FROM - roads_local rl - JOIN state_lookup sl on (rl.statel = sl.st_code) - LEFT JOIN county_lookup co on (rl.statel = co.st_code AND rl.countyl = co.co_code) - LEFT JOIN countysub_lookup cs on (rl.statel = cs.st_code AND rl.countyl = cs.co_code AND rl.cousubl = cs.cs_code) - LEFT JOIN place_lookup pl on (rl.statel = pl.st_code AND rl.placel = pl.pl_code) - WHERE fename IS NOT NULL - UNION - SELECT - tlid, - fedirp, - fename, - fetype, - fedirs, - zipr as zip, - sl.abbrev as state, - co.name as county, - cs.name as cousub, - pl.name as place - FROM - roads_local rl - JOIN state_lookup sl on (rl.stater = sl.st_code) - LEFT JOIN county_lookup co on (rl.stater = co.st_code AND rl.countyr = co.co_code) - LEFT JOIN countysub_lookup cs on (rl.stater = cs.st_code AND rl.countyr = cs.co_code AND rl.cousubr = cs.cs_code) - LEFT JOIN place_lookup pl on (rl.stater = pl.st_code AND rl.placer = pl.pl_code) - WHERE fename IS NOT NULL - ) AS sub; - -CREATE INDEX tiger_geocode_roads_zip_soundex_idx ON tiger_geocode_roads (soundex(fename), zip, state); -CREATE INDEX tiger_geocode_roads_place_soundex_idx ON tiger_geocode_roads (soundex(fename), place, state); -CREATE INDEX tiger_geocode_roads_cousub_soundex_idx ON tiger_geocode_roads (soundex(fename), cousub, state); -CREATE INDEX tiger_geocode_roads_place_more_soundex_idx ON tiger_geocode_roads (soundex(fename), soundex(place), state); -CREATE INDEX tiger_geocode_roads_cousub_more_soundex_idx ON tiger_geocode_roads (soundex(fename), soundex(cousub), state); -CREATE INDEX tiger_geocode_roads_state_soundex_idx ON tiger_geocode_roads (soundex(fename), state); - diff --git a/extras/tiger_geocoder/tiger_2006andbefore/utility/cull_null.sql b/extras/tiger_geocoder/tiger_2006andbefore/utility/cull_null.sql deleted file mode 100644 index dd11ced6b..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/utility/cull_null.sql +++ /dev/null @@ -1,8 +0,0 @@ --- Returns the value passed, or an empty string if null. --- This is used to concatinate values that may be null. -CREATE OR REPLACE FUNCTION cull_null(VARCHAR) RETURNS VARCHAR -AS $_$ -BEGIN - RETURN coalesce($1,''); -END; -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/utility/levenshtein_ignore_case.sql b/extras/tiger_geocoder/tiger_2006andbefore/utility/levenshtein_ignore_case.sql deleted file mode 100644 index 7e1fa9fa2..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/utility/levenshtein_ignore_case.sql +++ /dev/null @@ -1,10 +0,0 @@ --- This function determines the levenshtein distance irespective of case. -CREATE OR REPLACE FUNCTION levenshtein_ignore_case(VARCHAR, VARCHAR) RETURNS INTEGER -AS $_$ -DECLARE - result INTEGER; -BEGIN - result := levenshtein(upper($1), upper($2)); - RETURN result; -END -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/utility/nullable_levenshtein.sql b/extras/tiger_geocoder/tiger_2006andbefore/utility/nullable_levenshtein.sql deleted file mode 100644 index 53aeac57a..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/utility/nullable_levenshtein.sql +++ /dev/null @@ -1,27 +0,0 @@ --- This function take two arguements. The first is the "given string" and --- must not be null. The second arguement is the "compare string" and may --- or may not be null. If the second string is null, the value returned is --- 3, otherwise it is the levenshtein difference between the two. -CREATE OR REPLACE FUNCTION nullable_levenshtein(VARCHAR, VARCHAR) RETURNS INTEGER -AS $_$ -DECLARE - given_string VARCHAR; - result INTEGER := 3; - verbose BOOLEAN := FALSE; -BEGIN - IF $1 IS NULL THEN - IF verbose THEN - RAISE NOTICE 'nullable_levenshtein - given string is NULL!'; - END IF; - RETURN NULL; - ELSE - given_string := $1; - END IF; - - IF $2 IS NOT NULL AND $2 != '' THEN - result := levenshtein_ignore_case(given_string, $2); - END IF; - - RETURN result; -END -$_$ LANGUAGE plpgsql; diff --git a/extras/tiger_geocoder/tiger_2006andbefore/utility/utmzone.sql b/extras/tiger_geocoder/tiger_2006andbefore/utility/utmzone.sql deleted file mode 100644 index 485e0b7ce..000000000 --- a/extras/tiger_geocoder/tiger_2006andbefore/utility/utmzone.sql +++ /dev/null @@ -1,17 +0,0 @@ -CREATE OR REPLACE FUNCTION utmzone(geometry) RETURNS integer AS -$BODY$ -DECLARE - geomgeog geometry; - zone int; - pref int; -BEGIN - geomgeog:=transform($1,4326); - IF (y(geomgeog))>0 THEN - pref:=32600; - ELSE - pref:=32700; - END IF; - zone:=floor((x(geomgeog)+180)/6)+1; - RETURN zone+pref; -END; -$BODY$ LANGUAGE 'plpgsql' immutable; -- 2.50.1