/// <summary> /// Convert Standard Address to Postal Address /// </summary> /// <param name="standardAddress">Standard Address</param> /// <returns>Postal Address</returns> public PostalAddress ConvertStandardAddressToPostal(StandardAddress standardAddress) { PostalAddress postalAddress = new PostalAddress(); postalAddress.BuildingOrFirmName = standardAddress.BuildingOrFirmName; // Address Line 1 // <Address Number> <Street Pre Direction> <Street Name> <Street Suffix> <Street Post Direction> string buildingAddressLine1 = string.Empty; buildingAddressLine1 = Helper.AppendStringWithChar(buildingAddressLine1, standardAddress.PrimaryHighAddress, enmAppendCharacter.Space); buildingAddressLine1 = Helper.AppendStringWithChar(buildingAddressLine1, standardAddress.StreetPreDirection, enmAppendCharacter.Space); buildingAddressLine1 = Helper.AppendStringWithChar(buildingAddressLine1, standardAddress.StreetName, enmAppendCharacter.Space); buildingAddressLine1 = Helper.AppendStringWithChar(buildingAddressLine1, standardAddress.StreetSuffix, enmAppendCharacter.Space); buildingAddressLine1 = Helper.AppendStringWithChar(buildingAddressLine1, standardAddress.StreetPostDirection, enmAppendCharacter.Space); postalAddress.AddressLine1 = buildingAddressLine1; // Address Line 2 // <Secondary Address Type> <Secondary Address> string buildingAddressLine2 = string.Empty; buildingAddressLine2 = Helper.AppendStringWithChar(buildingAddressLine2, standardAddress.SecondaryAddressType, enmAppendCharacter.Space); buildingAddressLine2 = Helper.AppendStringWithChar(buildingAddressLine2, standardAddress.SecondaryHighAddress, enmAppendCharacter.Space); postalAddress.AddressLine2 = buildingAddressLine2; postalAddress.City = standardAddress.CityStateKey; // TODO: Need to retrieve from city-state mapping postalAddress.State = standardAddress.StateCode; postalAddress.Zip5 = standardAddress.ZipCode; postalAddress.Zip4 = standardAddress.Zip4AddonHigh; postalAddress.UspsAddressId = standardAddress.UspsFileAddressId; return postalAddress; }
/// <summary> /// Get Address Line 1 from Postal Address /// Address Line 1 is split into: /// PrimaryHighAddress /// StreetPreDirection /// StreetName /// StreetSuffix /// StreetPostDirection /// </summary> /// <param name="postalAddress">Postal Address</param> private void GetAddress1Fields(PostalAddress postalAddress) { if (StdAddress == null) StdAddress = new StandardAddress(); Regex lregex = new Regex(@"(?<streetno>(\d{1,5}))\s+(?<streetpre>(("+ Constants.StreetDirections +@")\s+)?)(?<streetname>(\S+(?:\s+\S+)*?)\s+)(?<streetsuffix>(("+ Constants.StreetSuffixCommonUsage + @"))(\s+)?)(?<streetpost>(("+ Constants.StreetDirections + @")+)?)"); Match lregexMatch = lregex.Match(postalAddress.AddressLine1); if (lregexMatch.Success) { StdAddress.PrimaryHighAddress = lregexMatch.Groups["streetno"].Value.Trim(); StdAddress.PrimaryLowAddress = lregexMatch.Groups["streetno"].Value.Trim(); //TODO: Apply Standardization like Suffix for Street Pre and Post Direction. StdAddress.StreetPreDirection = lregexMatch.Groups["streetpre"].Value.Trim(); StdAddress.StreetName = lregexMatch.Groups["streetname"].Value.Trim(); // Get Standard Suffix. if (!string.IsNullOrEmpty(lregexMatch.Groups["streetsuffix"].Value)) StdAddress.StreetSuffix = GetStandardStreetSuffix(lregexMatch.Groups["streetsuffix"].Value); else StdAddress.StreetSuffix = string.Empty; StdAddress.StreetPostDirection = lregexMatch.Groups["streetpost"].Value.Trim(); } }
/// <summary> /// Convert Postal Address to Standard Address /// </summary> /// <param name="postalAddress">Postal Address</param> /// <returns>Standard Address</returns> public StandardAddress ConvertPostalAddressToStandardAddress(PostalAddress postalAddress) { StdAddress = new StandardAddress(); GetAddress1Fields(postalAddress); GetAddress2Fields(postalAddress); GetCityStateFields(postalAddress); GetZipFields(postalAddress); return StdAddress; }
/// <summary> /// Get Matching address from Database /// </summary> /// <param name="addressToBeStandardized">Address to be Standardized</param> /// <returns>Matching Addresses</returns> public Collection<StandardAddress> GetMatchingAddressFromDB(StandardAddress addressToBeStandardized) { // Get Matches by Address Parts. stateAndZipMatches = MatchesByStateAndZip(addressToBeStandardized); // TODO: get by fields and combine //streetNumberMatches = MatchesByDBField("primary_high_address", addressToBeStandardized.PrimaryHighAddress); //streetNameMatches = MatchesByDBField("street_name", addressToBeStandardized.StreetName); // Filter results using Levenshtein Distance. icombinedResult = GetCorrectedAddresses(stateAndZipMatches, addressToBeStandardized); return icombinedResult; }
/// <summary> /// Get Address 2 Fields /// Address Line 2 is split up into: /// SecondaryHighAddress, SecondaryLowAddress (both are same mostly) /// SecondaryAddressType /// </summary> /// <param name="postalAddress">Postal Address</param> private void GetAddress2Fields(PostalAddress postalAddress) { if (StdAddress == null) StdAddress = new StandardAddress(); Regex lregex = new Regex(@"(?:(?<addrtype>("+ Constants.AddressTypes + @"))?(?:(?:\s+|-)(?<doorno>\d+)))$"); Match lregexMatch = lregex.Match(postalAddress.AddressLine2); if (lregexMatch.Success) { StdAddress.SecondaryAddressType = lregexMatch.Groups["addrtype"].Value; StdAddress.SecondaryHighAddress = lregexMatch.Groups["doorno"].Value; StdAddress.SecondaryLowAddress = lregexMatch.Groups["doorno"].Value; } }
/// <summary> /// Get Zip Code and Zip+4 /// Zip 5 is Zip Code /// Zip 4 is Zip4AddonHigh and Zip4AddonLow /// </summary> /// <param name="postalAddress">Postal Address</param> private void GetZipFields(PostalAddress postalAddress) { if (StdAddress == null) StdAddress = new StandardAddress(); StdAddress.ZipCode = postalAddress.Zip5; StdAddress.Zip4AddonHigh = postalAddress.Zip4; StdAddress.Zip4AddonLow = postalAddress.Zip4; }
/// <summary> /// Calculate Levenshtein Distance /// </summary> /// <param name="standardAddress">Standard Address</param> /// <param name="addressToBeStandardized">Address to be Standardized</param> private void CalculateLevenshteinDistance(StandardAddress standardAddress, StandardAddress addressToBeStandardized) { standardAddress.LevenDistStreetNo = Helper.ComputeLevenshteinDistance(standardAddress.PrimaryHighAddress, addressToBeStandardized.PrimaryHighAddress); standardAddress.LevenDistBuildingOrFirmName = Helper.ComputeLevenshteinDistance(standardAddress.BuildingOrFirmName, standardAddress.BuildingOrFirmName); standardAddress.LevenDistStreetPreDirection = Helper.ComputeLevenshteinDistance(standardAddress.StreetPreDirection, addressToBeStandardized.StreetPreDirection); standardAddress.LevenDistStreetName = Helper.ComputeLevenshteinDistance(standardAddress.StreetName, addressToBeStandardized.StreetName); standardAddress.LevenDistStreetSuffix = Helper.ComputeLevenshteinDistance(standardAddress.StreetSuffix, addressToBeStandardized.StreetSuffix); standardAddress.LevenDistStreetPostDirection = Helper.ComputeLevenshteinDistance(standardAddress.StreetPostDirection, addressToBeStandardized.StreetPostDirection); standardAddress.LevenDistSecondaryAddrType = Helper.ComputeLevenshteinDistance(standardAddress.SecondaryAddressType, addressToBeStandardized.SecondaryAddressType); standardAddress.LevenDistSecondaryHighAddress = Helper.ComputeLevenshteinDistance(standardAddress.SecondaryHighAddress, addressToBeStandardized.SecondaryHighAddress); standardAddress.LevenDistSecondaryLowAddress = Helper.ComputeLevenshteinDistance(standardAddress.SecondaryLowAddress, addressToBeStandardized.SecondaryLowAddress); standardAddress.LevenDistZipPlusfour = Helper.ComputeLevenshteinDistance(standardAddress.Zip4AddonHigh, addressToBeStandardized.Zip4AddonHigh); }
/// <summary> /// Match by State Code and Zip Code /// </summary> /// <param name="addressToBeStandardized">Address to be Standardized</param> /// <returns>State Zip Matches.</returns> private Collection<StandardAddress> MatchesByStateAndZip(StandardAddress addressToBeStandardized) { // Select query to be used. string lstrQuery = string.Empty; // Get matches by State. if (!string.IsNullOrEmpty(addressToBeStandardized.StateCode) && !string.IsNullOrEmpty(addressToBeStandardized.ZipCode)) lstrQuery = "select * from sgs_usps_file_address with(nolock) where state_code = '" + addressToBeStandardized.StateCode + "' and zipcode = '" + addressToBeStandardized.ZipCode + "'"; else if (!string.IsNullOrEmpty(addressToBeStandardized.ZipCode)) lstrQuery = "select * from sgs_usps_file_address with(nolock) where zipcode = '" + addressToBeStandardized.ZipCode + "'"; else if (!string.IsNullOrEmpty(addressToBeStandardized.StateCode)) lstrQuery = "select * from sgs_usps_file_address with(nolock) where state_code = '" + addressToBeStandardized.StateCode + "'"; else return null; DataSet ldsMatches = database.ExecuteSelect(lstrQuery); return GetFileAddressFromDataSet(ldsMatches); }
/// <summary> /// Get Possible Matches Filtered on Field /// </summary> /// <param name="matchesFromDB">Matches from DB</param> /// <param name="addressToBeStandardized">Address to be Standardized</param> /// <returns>Possible Matches</returns> private Collection<StandardAddress> GetPossibleMatchFiltered(Collection<StandardAddress> levenshteinFiltered, StandardAddress addressToBeStandardized) { // Filter on Street Number if (!string.IsNullOrEmpty(addressToBeStandardized.PrimaryHighAddress)) { var minimumDistanceOnStreetNumber = levenshteinFiltered.Where(address => address.LevenDistStreetNo == levenshteinFiltered.Min(addr => addr.LevenDistStreetNo)); if (minimumDistanceOnStreetNumber != null && minimumDistanceOnStreetNumber.Count() > 0) return new Collection<StandardAddress>(minimumDistanceOnStreetNumber.ToList<StandardAddress>()); } // No Filter Matched; Return the original. return levenshteinFiltered; }
/// <summary> /// Get File Address from DataSet /// </summary> /// <param name="adsFileAddresses">File Address DataSet</param> /// <returns>Collection of File Addresses</returns> private Collection<StandardAddress> GetFileAddressFromDataSet(DataSet adsFileAddresses) { Collection<StandardAddress> lFileAddresses = new Collection<StandardAddress>(); foreach (DataRow ldrRow in adsFileAddresses.Tables[0].Rows) { StandardAddress lFileAddress = new StandardAddress(); lFileAddress.UspsFileAddressId = ldrRow["usps_file_address_id"] != DBNull.Value ? Convert.ToInt32(ldrRow["usps_file_address_id"]) : 0; lFileAddress.DetailCode = ldrRow["detail_code"] != DBNull.Value ? ldrRow["detail_code"].ToString() : string.Empty; lFileAddress.ActionCode = ldrRow["action_code"] != DBNull.Value ? ldrRow["action_code"].ToString() : string.Empty; lFileAddress.ZipCode = ldrRow["zipcode"] != DBNull.Value ? ldrRow["zipcode"].ToString() : string.Empty; lFileAddress.UpdateKeyNumber = ldrRow["update_key_number"] != DBNull.Value ? ldrRow["update_key_number"].ToString() : string.Empty; lFileAddress.RecordType = ldrRow["record_type"] != DBNull.Value ? ldrRow["record_type"].ToString() : string.Empty; lFileAddress.CarrierRouteId = ldrRow["carrier_route_id"] != DBNull.Value ? ldrRow["carrier_route_id"].ToString() : string.Empty; lFileAddress.StreetPreDirection = ldrRow["street_pre_direction"] != DBNull.Value ? ldrRow["street_pre_direction"].ToString() : string.Empty; lFileAddress.StreetName = ldrRow["street_name"] != DBNull.Value ? ldrRow["street_name"].ToString() : string.Empty; lFileAddress.StreetSuffix = ldrRow["street_suffix"] != DBNull.Value ? ldrRow["street_suffix"].ToString() : string.Empty; lFileAddress.StreetPostDirection = ldrRow["street_post_direction"] != DBNull.Value ? ldrRow["street_post_direction"].ToString() : string.Empty; lFileAddress.PrimaryLowAddress = ldrRow["primary_low_address"] != DBNull.Value ? ldrRow["primary_low_address"].ToString() : string.Empty; lFileAddress.PrimaryHighAddress = ldrRow["primary_high_address"] != DBNull.Value ? ldrRow["primary_high_address"].ToString() : string.Empty; lFileAddress.PrimaryOddEvenBothFlag = ldrRow["primary_odd_even_both_flag"] != DBNull.Value ? ldrRow["primary_odd_even_both_flag"].ToString() : string.Empty; lFileAddress.BuildingOrFirmName = ldrRow["building_or_firm_name"] != DBNull.Value ? ldrRow["building_or_firm_name"].ToString() : string.Empty; lFileAddress.SecondaryAddressType = ldrRow["secondary_address_type"] != DBNull.Value ? ldrRow["secondary_address_type"].ToString() : string.Empty; lFileAddress.SecondaryLowAddress = ldrRow["secondary_low_address"] != DBNull.Value ? ldrRow["secondary_low_address"].ToString() : string.Empty; lFileAddress.SecondaryHighAddress = ldrRow["secondary_high_address"] != DBNull.Value ? ldrRow["secondary_high_address"].ToString() : string.Empty; lFileAddress.SecondaryOddEvenBothFlag = ldrRow["secondary_odd_even_both_flag"] != DBNull.Value ? ldrRow["secondary_odd_even_both_flag"].ToString() : string.Empty; lFileAddress.Zip4AddonLow = ldrRow["zip4_addon_low"] != DBNull.Value ? ldrRow["zip4_addon_low"].ToString() : string.Empty; lFileAddress.Zip4AddonHigh = ldrRow["zip4_addon_high"] != DBNull.Value ? ldrRow["zip4_addon_high"].ToString() : string.Empty; lFileAddress.BaseAltFlag = ldrRow["base_alt_flag"] != DBNull.Value ? ldrRow["base_alt_flag"].ToString() : string.Empty; lFileAddress.LACSStatusFlag = ldrRow["lacs_status_flag"] != DBNull.Value ? ldrRow["lacs_status_flag"].ToString() : string.Empty; lFileAddress.GovtBuildingFlag = ldrRow["govt_building_flag"] != DBNull.Value ? ldrRow["govt_building_flag"].ToString() : string.Empty; lFileAddress.USPSFinanceNumber = ldrRow["usps_financial_number"] != DBNull.Value ? ldrRow["usps_financial_number"].ToString() : string.Empty; lFileAddress.StateCode = ldrRow["state_code"] != DBNull.Value ? ldrRow["state_code"].ToString() : string.Empty; lFileAddress.CountyFIPSCode = ldrRow["county_fips_code"] != DBNull.Value ? ldrRow["county_fips_code"].ToString() : string.Empty; lFileAddress.CongressionalDistrict = ldrRow["congressional_district"] != DBNull.Value ? ldrRow["congressional_district"].ToString() : string.Empty; lFileAddress.MunicipalityKey = ldrRow["municipality_key"] != DBNull.Value ? ldrRow["municipality_key"].ToString() : string.Empty; lFileAddress.PRUrbanizationKey = ldrRow["pr_urbanization_flag"] != DBNull.Value ? ldrRow["pr_urbanization_flag"].ToString() : string.Empty; lFileAddress.CityStateKey = ldrRow["city_state_key"] != DBNull.Value ? ldrRow["city_state_key"].ToString() : string.Empty; lFileAddresses.Add(lFileAddress); } return lFileAddresses; }
/// <summary> /// Get Exact Match /// </summary> /// <param name="matchesFromDB">Match From DB</param> /// <param name="addressToBeStandardized">Address to be Standardized</param> /// <returns>Exact Match</returns> private Collection<StandardAddress> GetExactMatch(Collection<StandardAddress> matchesFromDB, StandardAddress addressToBeStandardized) { // Account for Zip+4 if (!string.IsNullOrEmpty(addressToBeStandardized.Zip4AddonHigh)) { var matches = matchesFromDB.Where (addr => addr.LevenDistStreetNo == 0 && addr.LevenDistStreetPreDirection == 0 && addr.LevenDistStreetName == 0 && addr.LevenDistStreetPostDirection == 0 && addr.LevenDistStreetSuffix == 0 && addr.LevenDistSecondaryAddrType == 0 && addr.LevenDistSecondaryHighAddress == 0 && addr.LevenDistSecondaryLowAddress == 0 && addr.LevenDistZipPlusfour == 0); if (matches != null && matches.Count() > 0) return new Collection<StandardAddress>(matches.ToList<StandardAddress>()); else return null; } else { var matches = matchesFromDB.Where (addr => addr.LevenDistStreetNo == 0 && addr.LevenDistStreetPreDirection == 0 && addr.LevenDistStreetName == 0 && addr.LevenDistStreetPostDirection == 0 && addr.LevenDistStreetSuffix == 0 && addr.LevenDistSecondaryAddrType == 0 && addr.LevenDistSecondaryHighAddress == 0 && addr.LevenDistSecondaryLowAddress == 0); if (matches != null && matches.Count() > 0) return new Collection<StandardAddress>(matches.ToList<StandardAddress>()); else return null; } }
/// <summary> /// Apply Levenshtein Distance to get the most probable matches. /// </summary> /// <param name="matchesFromDB">Matches from DB</param> /// <param name="addressToBeStandardized">Address to be Standardized</param> /// <returns>File Address Matches.</returns> private Collection<StandardAddress> GetCorrectedAddresses(Collection<StandardAddress> matchesFromDB, StandardAddress addressToBeStandardized) { foreach (StandardAddress match in matchesFromDB) CalculateLevenshteinDistance(match, addressToBeStandardized); // Get exact match using Levenshtein Distance Collection<StandardAddress> resultingAddress = GetExactMatch(matchesFromDB, addressToBeStandardized); // Exact Match found. if (resultingAddress != null) return resultingAddress; // Get Possible Matches var leastLevenshteinDistantAddresses = matchesFromDB.Where (address => address.LevenDistStreetNo <= Constants.MaxStreetNoTolerance && address.LevenDistStreetPreDirection <= Constants.MaxStreetPreDirectionTolerance && address.LevenDistStreetName <= Constants.MaxStreetNameTolerance && address.LevenDistStreetPostDirection <= Constants.MaxStreetPostDirectionTolerance && address.LevenDistStreetSuffix <= Constants.MaxStreetSuffixTolerance && address.LevenDistSecondaryAddrType <= Constants.MaxSecondaryAddrTypeTolerance && address.LevenDistSecondaryHighAddress <= Constants.MaxSecondaryHighAddrTolerance && address.LevenDistSecondaryLowAddress <= Constants.MaxSecondaryLowAddrTolerance); if (leastLevenshteinDistantAddresses != null && leastLevenshteinDistantAddresses.Count() > 0) { return GetPossibleMatchFiltered(new Collection<StandardAddress>(leastLevenshteinDistantAddresses.ToList<StandardAddress>()), addressToBeStandardized); } else return new Collection<StandardAddress>(); }