using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Threading.Tasks; using System.IO; using System.Xml; using System.Net; namespace ApnTools { // // The ApnResolver class takes an APN, which may be in any number of different formats and attempts to // find the parcel, represented by a unique Location_Id, or property, represented by a unique Tax_Id, // for that APN. If there are multiple possibilities for that APN they will all be returned. // public class ApnParams { public string[] sHosts; public string sCandy; public string ApnLookupLayer; public string ParcelLayer; public string PropertyLayer; public string[] fipsCodes; // The Fips Code of the county where the APNs are located. } // The results can be // None: That APN nor any that are on the same page of the assessor's book were found. Error_Text will be filled // out with any additional details. // Parcel: The APN matched to a parcel and the LOCATION_ID of that parcel in the ParcelDetail layer is returned. // Property: The APN matched to a property (and didn't match to a parcel) and the TAX_ID of that property in the // the PropertyDetaul layer is returned. // MultiParcel:The APN didn't match to a property but the page of the assessor's book that that property would have // been on had other parcels. Since the missing parcel will be in this area if the APN changed for // some reason the latitude and longitude of the center of a rectangle bounding all the parcels will be // returned along with the radius in feet from that center of a circle that will encompass the whole // area (as Accuracy). public enum MatchType { None, Parcel, Property, MultiParcel }; public class ApnResult { public MatchType Match_Type = MatchType.None; public string Error_Text = String.Empty; public string Trimmed_Apn = String.Empty; public string Location_Id = String.Empty; public string Tax_Id = String.Empty; public double Latitude = 0; public double Longitude = 0; public int Accuracy = 0; // Constuctors public ApnResult(string locationId, string taxId, string trimmedApn) { if (locationId != String.Empty) Match_Type = MatchType.Parcel; else Match_Type = MatchType.Property; Trimmed_Apn = trimmedApn; Location_Id = locationId; Tax_Id = taxId; } public ApnResult(double lat, double lon, int acc, string locIds, string trimmedApn) { Trimmed_Apn = trimmedApn; Match_Type = MatchType.MultiParcel; Latitude = lat; Longitude = lon; Accuracy = acc; Location_Id = locIds; } public ApnResult(string err, string trimmedApn) { Trimmed_Apn = trimmedApn; Match_Type = MatchType.None; Error_Text = err; } } // // ApnResolver is the class that has the logic to try to resolve APNs to a particular parcel or property record. // public class ApnResolver { private ApnParams _params; private string _apnTrimmed; private string _fipsQuery; StreamWriter _logFS; bool _verbose; // APNResolver Constructor. // p: Various parameters needed for calling ParcelStream // logFS: The stream for a file used to record the results of the APN Lookups. // verbose: If false, only information for None and MultiParcel results will be written to the log file. public ApnResolver(ApnParams p, StreamWriter logFS, bool verbose) { _params = p; _logFS = logFS; _verbose = verbose; _fipsQuery = " AND FIPS_CODE IN ("; for (var i = 0; i < _params.fipsCodes.Length; i++) _fipsQuery += "'" + _params.fipsCodes[i] + "',"; _fipsQuery = _fipsQuery.Remove(_fipsQuery.Length - 1); // strip off the last comma _fipsQuery = _fipsQuery + ")"; // add the final paren } // GetXML makes the http request in sQueryString and returns the response. private static XmlDocument GetXML(string sQueryString) { HttpWebRequest oRequest = (HttpWebRequest)WebRequest.Create(sQueryString); HttpWebResponse oResponse = (HttpWebResponse)oRequest.GetResponse(); XmlDocument oResponseXML = new XmlDocument(); oResponseXML.Load(oResponse.GetResponseStream()); return (oResponseXML); } private ApnResult reportError(string errText) { if (!_verbose) // write the APN if we didn't do it above _logFS.WriteLine("APN: {0}", _apnTrimmed); _logFS.WriteLine(" Lookup Failed: {0}", errText); return new ApnResult(errText, _apnTrimmed); } private string buildUrl(string query) { string url = _params.sHosts[0] + "getquery.aspx?"; url += "datasource=" + _params.ApnLookupLayer; url += "&query=" + query; url += "&fields=APN_TRMD,LOCATION_ID,TAX_ID"; url += "&showSchema=false"; url += "&maxRecords=300"; url += "&output=xml"; url += "&SS_CANDY=" + _params.sCandy; return url; } // // lookupApn attempts to locate the parcel or property record for the APN in apnInput. It does this using the // ApnLookup table which links trimmed APNs to ParcelDetail and PropertyDetail tables. The APN is trimmed by removing // leading and trailing zeroes and removing all non-alphanumeric characters. This is done because people often leave // out or add extra or otherwise mangle these characters, so matching on only the trimmed version makes it more likely // that a match will be found. // // However, it also makes it more likely that a false match will be found, so there has to be code to try to determine // what the real match is. // // If no match is still found it tries to determine the approximate location by assuming that // it should be near other parcels found on the same page of the assessor's book. // public ApnResult lookupApn(string apnInput) { //TODO: use StringBuilder for all string manipulation _apnTrimmed = Regex.Replace(apnInput, @"[^A-Za-z0-9]", String.Empty); // replace all non alphanumeric with null _apnTrimmed = Regex.Replace(_apnTrimmed, @"^0+", String.Empty); // replace leading zeroes with null _apnTrimmed = Regex.Replace(_apnTrimmed, @"0+$", String.Empty); // replace trailing zeroes with null if (_verbose) _logFS.WriteLine("Resolve apn {0}, trimmed {1}", apnInput, _apnTrimmed); if (apnInput.Length <= 6) // the number of digits in a book and page for Orange County return reportError("Too short to be an apn"); // // First look for an exact (trimmed) match in the APNLookup table. // string query = "(APN_TRMD = '" + _apnTrimmed + "')"; query += _fipsQuery; string url = buildUrl(query); XmlDocument oResponseXML = GetXML(url); XmlNode resultsNode = oResponseXML.SelectSingleNode("Response/Results"); if (int.Parse(resultsNode.Attributes["totalRecords"].Value) > 0) { return onFoundResults(resultsNode, apnInput, false); } else { // // Couldn't find any matches so find all the parcels on the same page // // in OC the format is xxx-xxx-xx, so strip off the last two digits. If there are more than 8 // digits, just trim it back to 6 and hope for the best. string bookPageApn; if (_apnTrimmed.Length > 6) bookPageApn = _apnTrimmed.Remove(6); else bookPageApn = _apnTrimmed; if (_verbose) _logFS.WriteLine(" No matches found in LookupAPN table, try book/page, {0}", bookPageApn); query = "(APN_TRMD LIKE '" + bookPageApn + "%')"; query += _fipsQuery; url = buildUrl(query); oResponseXML = GetXML(url); resultsNode = oResponseXML.SelectSingleNode("Response/Results"); if (int.Parse(resultsNode.Attributes["totalRecords"].Value) > 0) return onFoundResults(resultsNode, bookPageApn, true); else return reportError("No matches found"); } } // // If a single result is found, return it. Otherwise, the APN was either truncated in the previous step to match on book and page, // in which case multiple matches will be returned, or the APN got multiple matches because some zeroes were trimmed from the // beginning or end. For example, the APN 001-234-56 when trimmed would match to 012-345-60 or 123-456-00 if those APNs exist. // To attempt to match to the correct APN, the results are compared to the input APN, before it was trimmed. But the APNLookup // table doesn't have a field for fully formatted APN, however the LOCATION_ID corresponding to that APN contains a fully // formatted APN as part of it. So, if the input APN is correctly formatted then it will match the APN pulled from the LOCATION_ID // field for one of the results. // // TODO: Often the APN is entered with all the digits, but with the separators left out. To match in this case a second // check should be added where the input APN is compared to the APN extracted from LOCATION_ID but with all non-alphanumeric // characters removed. // private ApnResult onFoundResults(XmlNode results, string apnInput, bool apnTruncated) { int totalRecords = int.Parse(results.Attributes["totalRecords"].Value); if (totalRecords == 1) { if (_verbose) _logFS.Write(" Found single result in LookupAPN table, "); return onSingleResult(results.SelectSingleNode("Data/Row"), !apnTruncated); } else { if (!apnTruncated) { // if the apn wasn't truncated // search through the results looking for an APN pulled from the LOCATION_ID field that matches apnInput // TODO: Also check if input is numeric only if derivedApn matches if all non-numeric is stripped out StringBuilder s = new StringBuilder(" Multiple matches found, checking for exact match to input apn, "); int matchFoundAt = -1; for (int i = 0; i < totalRecords; i++) { string derivedApn = ""; string locId = results.SelectSingleNode("Data/Row[" + (i + 1) + "]").Attributes["LOCATION_ID"].Value; string taxId = results.SelectSingleNode("Data/Row[" + (i + 1) + "]").Attributes["TAX_ID"].Value; s.Append("(" + locId + ", " + taxId + "), "); if (locId != "") derivedApn = locId.Substring(10); if (derivedApn == apnInput) { matchFoundAt = i; break; } } s.Remove(s.Length - 2, 2); // remove the last ", " if (_verbose) _logFS.WriteLine(s); if (matchFoundAt != -1) { if (_verbose) _logFS.Write(" Found a match so stopped loop, "); return onSingleResult(results.SelectSingleNode("Data/Row[" + (matchFoundAt + 1) + "]"), false); } } if (_verbose) _logFS.WriteLine(" Multiple results found, check for patterns"); return cleanTheResults(results.SelectSingleNode("Data"), totalRecords, apnInput, apnTruncated); } } // onFoundResults // // When multiple results are found it is possible that after all the trimming results from multiple // pages were found. So 123456 could match 123-456-xx or xx1-234-56 where xx could be any two digits. // cleanTheResults finds the different patterns of results based on where the formatting // characters are in the string and return the group matching the most numerous pattern. // private class apnPattern { public int count; public int index; public apnPattern(int c, int i) { count = c; index = i; } } private ApnResult cleanTheResults(XmlNode rows, int totalRecords, string apnInput, bool apnTruncated) { Dictionary patterns; string regexString; // find patterns based on what the user actually input (including leading and trailing zeroes) but if there are // no matches try again using apnTrimmed instead of apnInput. // if apnTruncated is true though, then apnInput has already been trimmed, so there is no need for the second try. if (findPatterns(rows, totalRecords, apnInput, out regexString, out patterns)) return findBestPattern(rows, totalRecords, regexString, patterns); else if (!apnTruncated && findPatterns(rows, totalRecords, _apnTrimmed, out regexString, out patterns)) return findBestPattern(rows, totalRecords, regexString, patterns); else return reportError("No patterns found"); } private bool findPatterns(XmlNode rows, int totalRecords, string apn, out string regexString, out Dictionary patternOut) { Regex alphanumeric = new Regex("[A-Za-z0-9]"); Regex nonalphanumeric = new Regex("[^A-Za-z0-9]"); Dictionary patterns = new Dictionary(); bool foundOne = false; // Match any number of zeroes or non-alphabetic characters followed by each digit in apn possibly interspersed with non-alphanumeric // characters, followed by anything. regexString = "^([^A-Za-z1-9]*)"; for (int i=0; i patterns) { // Determine the most common pattern int winningIndex = -1; int winningCount = 0; string winningPattern = ""; foreach (KeyValuePair p in patterns) { if (p.Value.count > winningCount) { winningCount = p.Value.count; winningIndex = p.Value.index; winningPattern = p.Key; } } if (winningIndex != -1) { if (_verbose) _logFS.WriteLine(" The winning pattern is {0}", winningPattern); if (winningCount == 1) { if (_verbose) _logFS.Write(" Single result with winning pattern found, "); return onSingleResult(rows.SelectSingleNode("Row[" + (winningIndex+1) + "]"), false); } else { if (_verbose) _logFS.WriteLine(" Multiple results matching pattern, return all results"); // construct an apn prefix based on a record of the winning pattern string locId = rows.SelectSingleNode("Row[" + (winningIndex+1) + "]").Attributes["LOCATION_ID"].Value; string derivedApn = locId.Substring(10); Regex regex = new Regex(regexString); Match result = regex.Match(derivedApn); int lastOffset = result.Groups[0].Index + result.Groups[0].Length - 1; string apnPrefix = derivedApn.Substring(0, lastOffset+1); string fips = locId.Substring(0, 10); // Search for fips + apnPrefix + * in rows instead of the db table Regex locIdPattern = new Regex("^"+fips+apnPrefix+"*"); return onMultipleResults(rows, totalRecords, locIdPattern); } } else return reportError("No matching results found"); } private ApnResult onSingleResult(XmlNode apnLookupRow, bool exactMatch) { string locId = String.Empty; string taxId = String.Empty; if (apnLookupRow.Attributes["LOCATION_ID"] != null) locId = apnLookupRow.Attributes["LOCATION_ID"].Value; if (apnLookupRow.Attributes["TAX_ID"] != null) taxId = apnLookupRow.Attributes["TAX_ID"].Value; ApnResult rslt = new ApnResult(locId, taxId, _apnTrimmed); if (_verbose) _logFS.WriteLine(" Location ID = {0}, Tax ID = {1}", locId, taxId); return rslt; } // onSingleResult private ApnResult onMultipleResults(XmlNode rows, int totalRecords, Regex pattern) { StringBuilder locations = new StringBuilder(); bool firstTime = true; for (int i = 0; i < totalRecords; i++) { string locId = rows.SelectSingleNode("Row[" + (i + 1) + "]").Attributes["LOCATION_ID"].Value; if (pattern.IsMatch(locId)) { if (firstTime) firstTime = false; else locations.Append(","); locations.Append(locId); } } if (!_verbose) // write the APN if we didn't do it above _logFS.WriteLine("APN: {0}", _apnTrimmed); _logFS.WriteLine(" Matching Location IDs = {0}", locations.ToString()); // determine the center point and the radius of a circle covering all the parcels string url = _params.sHosts[0] + "getbykey.aspx?"; url += "datasource=" + _params.ParcelLayer; url += "&keyName=" + "LOCATION_ID"; url += "&keyValue=" + locations.ToString(); url += "&fields=GEOMETRY"; // just want geometry url += "&showSchema=false"; url += "&output=xml"; url += "&returnGeoType=2"; url += "&SS_CANDY=" + _params.sCandy; XmlDocument oResponseXML = GetXML(url); XmlNode resultsNode = oResponseXML.SelectSingleNode("Response/Results"); double xMin = double.Parse(resultsNode.Attributes["xMin"].Value); double yMin = double.Parse(resultsNode.Attributes["yMin"].Value); double xMax = double.Parse(resultsNode.Attributes["xMax"].Value); double yMax = double.Parse(resultsNode.Attributes["yMax"].Value); if (_verbose) _logFS.WriteLine(" Bounding Box upper left = ({0},{1}). lower right = ({2}{3})", xMin, yMin, xMax, yMax); double xCenter = (xMax + xMin)/2; double yCenter = (yMax + yMin)/2; int radius = 100; // temporary if (_verbose) _logFS.WriteLine(" Center = ({0},{1}), Radius = {2}", xCenter, yCenter, radius); ApnResult rslt = new ApnResult(yCenter, xCenter, radius, locations.ToString(), _apnTrimmed); return rslt; } // onMultipleResults } }