public IWorkspace CreateOutput()
{
// create the file geodatabase for the derived geocoding data
var output = EsriHelper.CreateFileGdbWorkspace(_options.OutputGeodatabase, "RoadGrinder");
// releaser.ManageLifetime(output);
var outputFeatureWorkspace = (IFeatureWorkspace)output;
var outputWorkspace2 = (IWorkspace2)output;
// check if the feature class and table exist in the file geodatabase - if so rename them before adding new data
if (EsriHelper.NameExists(outputWorkspace2, GeocodeRoadsFeatureClassName, esriDatasetType.esriDTFeatureClass))
{
// rename existing fc
var renameFeatureClass = outputFeatureWorkspace.OpenFeatureClass(GeocodeRoadsFeatureClassName);
// releaser.ManageLifetime(renameFeatureClass);
var outputDataset = (IDataset)renameFeatureClass;
outputDataset.Rename(string.Format("{0}{1}{2:yyyyMMdd}", GeocodeRoadsFeatureClassName, "ReplacedOn", DateTime.Now));
}
if (EsriHelper.NameExists(outputWorkspace2, GeocodeRoadsTableName + "Roads", esriDatasetType.esriDTTable))
{
// rename existing table
var renameTable = outputFeatureWorkspace.OpenTable(GeocodeRoadsTableName + "Roads");
// releaser.ManageLifetime(renameTable);
var outputDataset = (IDataset)renameTable;
outputDataset.Rename(string.Format("{0}{1}{2}", GeocodeRoadsTableName + "Roads", "ReplacedOn", DateTime.Now.ToString("yyyyMMdd")));
}
if (EsriHelper.NameExists(outputWorkspace2, GeocodeRoadsTableName + "AddrPnts", esriDatasetType.esriDTTable))
{
// rename existing table
var renameTable = outputFeatureWorkspace.OpenTable(GeocodeRoadsTableName + "AddrPnts");
// releaser.ManageLifetime(renameTable);
var outputDataset = (IDataset)renameTable;
outputDataset.Rename(string.Format("{0}{1}{2}", GeocodeRoadsTableName + "AddrPnts", "ReplacedOn", DateTime.Now.ToString("yyyyMMdd")));
}
// create a feature class in the newly-created file geodatabase
_geocodeRoads = EsriHelper.CreateFeatureClass(GeocodeRoadsFeatureClassName, null, outputFeatureWorkspace);
// create a roads altnames table in the newly-created file geodatabase
_altnameTableRoads = EsriHelper.CreateTable(GeocodeRoadsTableName + "Roads", null, outputFeatureWorkspace);
// create a address points altnames table in the newly-created file geodatabase
_altnameTableAddrPnts = EsriHelper.CreateTable(GeocodeRoadsTableName + "AddrPnts", null, outputFeatureWorkspace);
// Create the scratch geodatabase for the temp data
CreateScratchFgdWorkspace();
return(output);
}
public void CreateScratchFgdWorkspace()
{
// create the file geodatabase for the derived geocoding data
_scratchFGDB = EsriHelper.CreateFileGdbWorkspace(_options.OutputGeodatabase, "RoadGrinderScratchWS");
var scratchOutputFeatureWorkspace = (IFeatureWorkspace)_scratchFGDB;
var outputWorkspace2 = (IWorkspace2)_scratchFGDB;
var outputFeatureWorkspace = (IFeatureWorkspace)scratchOutputFeatureWorkspace;
//if (EsriHelper.NameExists(outputWorkspace2, GeocodeRoadsTableName + "RoadsScratchData", esriDatasetType.esriDTTable))
//{
// // rename existing table
// var renameTable = outputFeatureWorkspace.OpenTable(GeocodeRoadsTableName + "RoadsScratchData");
// // releaser.ManageLifetime(renameTable);
// var outputDataset = (IDataset)renameTable;
// outputDataset.Rename(string.Format("{0}{1}{2}", GeocodeRoadsTableName + "RoadsScratchData", "ReplacedOn", DateTime.Now.ToString("yyyyMMdd")));
//}
_scratchRoadsFC = EsriHelper.CreateFeatureClass(GeocodeRoadsScratchFeatureClassName, null, scratchOutputFeatureWorkspace);
}
public static void Execute(IWorkspaceEdit outputEditWorkspace, ITable altnameTableRoads, IFeatureClass geocodeRoads)
{
// load records into the altnames table for roads
Console.WriteLine("begin altnames table for roads: " + DateTime.Now);
// get feature cursor of newly-created derived-roads fgdb feature class
using (var comReleaser = new ComReleaser())
{
int consoleCounter = 0;
outputEditWorkspace.StartEditing(false);
outputEditWorkspace.StartEditOperation();
// Set up where clause to omit the records without a predir - as they are already in the database without a predir
//var omitPredirQueryFilter = new QueryFilter {WhereClause = @"PREDIR <> ''"};
// this one only gets the alpha named roads to evaluate for the altnames table
var omitPredirQueryFilter = new QueryFilter {
WhereClause = @"PREDIR <> '' and (UPPER(""NAME"") <> ""NAME"" OR LOWER(""NAME"") <> ""NAME"")"
};
var geocodeRoadsCursor = geocodeRoads.Search(omitPredirQueryFilter, false);
comReleaser.ManageLifetime(geocodeRoadsCursor);
IFeature geocodeRoadFeature;
// loop through the geocode roads feature cursor
while ((geocodeRoadFeature = geocodeRoadsCursor.NextFeature()) != null)
{
// check if this segment is found in another address quad, within the same address grid
using (var comReleaser2 = new ComReleaser())
{
var fieldIndexMapNewSchema = new FindIndexByNameCommand(geocodeRoads, new[]
{
"ADDRSYS_L", "ADDRSYS_R", "FROMADDR_L", "TOADDR_L", "FROMADDR_R", "TOADDR_R",
"PREDIR", "NAME", "POSTTYPE", "POSTDIR", "ZIPCODE_L", "ZIPCODE_R", "GLOBALID_SGID"
}).Execute();
var valueMapNewSchema = new AddValueToIndexFieldMapCommand(fieldIndexMapNewSchema, geocodeRoadFeature).Execute();
// set up query filter
var possibleCandidatesFilter = new QueryFilter
{
WhereClause = @"(ADDRSYS_L = '" + geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField(("ADDRSYS_L"))).ToString() + @"' AND
ADDRSYS_R = '" + geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField(("ADDRSYS_R"))).ToString() + @"' AND
NAME = '" + geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField(("NAME"))).ToString() + @"' AND
POSTTYPE = '" + geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField(("POSTTYPE"))).ToString() + @"' AND
POSTDIR = '" + geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField(("POSTDIR"))).ToString() + @"' AND
PREDIR <> '" + geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField(("PREDIR"))).ToString() + @"')"
};
var possibleCandidatesFeatureCursor = geocodeRoads.Search(possibleCandidatesFilter, false);
comReleaser2.ManageLifetime(possibleCandidatesFeatureCursor);
// Check if matching seg was found.
var possibleCandidateFeature = possibleCandidatesFeatureCursor.NextFeature();
bool foundMatch = false;
// Check if a segment was found in another quad with the same characteristics.
if (possibleCandidateFeature != null)
{
////Console.WriteLine(consoleCounter + ": found in the other grid: " + geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField("OBJECTID")).ToString());
// A feature was found.
while (possibleCandidateFeature != null)
{
using (var comReleaser3 = new ComReleaser())
{
// Check if any of the four numbers from the geocodeRoadsFeature class is contained in any of these returned possible candidates
// Get the lowest and the highest number
int possibleCandidateOID = Convert.ToInt32(possibleCandidateFeature.get_Value(possibleCandidateFeature.Fields.FindField("OBJECTID")));
int fromAddrL = Convert.ToInt32(geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField("FROMADDR_L")));
int fromAddrR = Convert.ToInt32(geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField("FROMADDR_R")));
int toAddrL = Convert.ToInt32(geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField("TOADDR_L")));
int toAddrR = Convert.ToInt32(geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField("TOADDR_R")));
int highNum;
int lowNum;
// Asign the high and low numbers (make sure the low is not zero)
if (fromAddrL == 0 || fromAddrR == 0)
{
if (fromAddrL == 0)
{
lowNum = fromAddrR;
}
else
{
lowNum = fromAddrL;
}
}
else
{
if (fromAddrL < fromAddrR)
{
lowNum = fromAddrL;
}
else
{
lowNum = fromAddrR;
}
}
if (toAddrL > toAddrR)
{
highNum = toAddrL;
}
else
{
highNum = toAddrR;
}
// Create a new query filter to see if the high or low number falls within one of these candidate features
var fitsInLargerSegmentFilter = new QueryFilter
{
WhereClause = @"OBJECTID = " + possibleCandidateOID + @" AND
(((" + lowNum + @" BETWEEN FROMADDR_L AND TOADDR_L ) or (" + highNum + @" BETWEEN FROMADDR_L AND TOADDR_L)) or
((" + lowNum + @" BETWEEN FROMADDR_R AND TOADDR_R ) or (" + highNum + @" BETWEEN FROMADDR_R AND TOADDR_R)))"
};
//Console.WriteLine("for oid: " + geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField("OBJECTID")).ToString() + " " + fitsInLargerSegmentFilter.WhereClause.ToString());
var fitsInLargerSegmentFeatureCursor = geocodeRoads.Search(fitsInLargerSegmentFilter, false);
comReleaser3.ManageLifetime(fitsInLargerSegmentFeatureCursor);
// Check if matching seg was found.
var fitsInLargerFeature = fitsInLargerSegmentFeatureCursor.NextFeature();
if (fitsInLargerFeature != null)
{
// A match was found. So don't write it to the AltNames table
consoleCounter = consoleCounter + 1;
Console.WriteLine(consoleCounter + ": fit in larger: " + fitsInLargerFeature.get_Value(fitsInLargerFeature.Fields.FindField("OBJECTID")).ToString());
foundMatch = true;
break;
}
else
{
// the current segment's address range did not fit within a larger segment
// check if a smaller address ranged segment fits into this larger segment
using (var comReleaser4 = new ComReleaser())
{
// Get the lowest and the highest number of the possible candidate
int geocodeRoadOID_ = Convert.ToInt32(geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField("OBJECTID")));
int fromAddrL_ = Convert.ToInt32(possibleCandidateFeature.get_Value(possibleCandidateFeature.Fields.FindField("FROMADDR_L")));
int fromAddrR_ = Convert.ToInt32(possibleCandidateFeature.get_Value(possibleCandidateFeature.Fields.FindField("FROMADDR_R")));
int toAddrL_ = Convert.ToInt32(possibleCandidateFeature.get_Value(possibleCandidateFeature.Fields.FindField("TOADDR_L")));
int toAddrR_ = Convert.ToInt32(possibleCandidateFeature.get_Value(possibleCandidateFeature.Fields.FindField("TOADDR_R")));
int highNum_;
int lowNum_;
// Asign the high and low numbers (make sure the low is not zero)
if (fromAddrL_ == 0 || fromAddrR_ == 0)
{
if (fromAddrL_ == 0)
{
lowNum_ = fromAddrR_;
}
else
{
lowNum_ = fromAddrL_;
}
}
else
{
if (fromAddrL_ < fromAddrR_)
{
lowNum_ = fromAddrL_;
}
else
{
lowNum_ = fromAddrR_;
}
}
if (toAddrL_ > toAddrR_)
{
highNum_ = toAddrL_;
}
else
{
highNum_ = toAddrR_;
}
var smallerCandidateFitsInFilter = new QueryFilter
{
WhereClause = @"OBJECTID = " + geocodeRoadOID_ + @" AND
(((" + lowNum_ + @" BETWEEN FROMADDR_L AND TOADDR_L ) or (" + highNum_ + @" BETWEEN FROMADDR_L AND TOADDR_L)) or
((" + lowNum_ + @" BETWEEN FROMADDR_R AND TOADDR_R ) or (" + highNum_ + @" BETWEEN FROMADDR_R AND TOADDR_R)))"
};
var smallerCandidateFitsInFeatureCursor = geocodeRoads.Search(smallerCandidateFitsInFilter, false);
comReleaser4.ManageLifetime(smallerCandidateFitsInFeatureCursor);
// Check if matching seg was found.
var smallerCandidateFitsInFeature = smallerCandidateFitsInFeatureCursor.NextFeature();
if (smallerCandidateFitsInFeature != null)
{
// A match was found. So don't write it to the AltNames table
consoleCounter = consoleCounter + 1;
Console.WriteLine(consoleCounter + ": candidate fit within it: " + smallerCandidateFitsInFeature.get_Value(smallerCandidateFitsInFeature.Fields.FindField("OBJECTID")).ToString());
foundMatch = true;
break;
}
}
}
}
// Advance to the next feature in the cursor.
possibleCandidateFeature = possibleCandidatesFeatureCursor.NextFeature();
}
}
else
{
// No possible candidates were found in other quad, so add this segment to the AltNames table
}
// Write to the AltNames table > match was not found
if (foundMatch == false)
{
// A matching feature was not found.
// Add a record to the table without a predir.
// Remove the PREDIR value from the field map
var valueMapNewSchemaNoPredir = valueMapNewSchema;
valueMapNewSchemaNoPredir.Remove("PREDIR");
EsriHelper.InsertRowInto(geocodeRoadFeature, altnameTableRoads, valueMapNewSchema, true, true);
consoleCounter = consoleCounter + 1;
Console.WriteLine(consoleCounter + ": not found in the other grid: " + geocodeRoadFeature.get_Value(geocodeRoadFeature.Fields.FindField("OBJECTID")).ToString());
}
}
}
// stop editing from altnames road table (this allow the roads altnames table to be preserved even if the altnames for addr pnts (below) errors out)
outputEditWorkspace.StopEditOperation();
outputEditWorkspace.StopEditing(true);
}
}
public static void Execute(IWorkspaceEdit outputEditWorkspace, ITable altnameTableAddrPnts, string connectionStringSgid)
{
// load records into the altnames table for address points
//start editing again
outputEditWorkspace.StartEditing(false);
outputEditWorkspace.StartEditOperation();
int counter = 0;
Console.WriteLine("begin altnames table for addr pnts: " + DateTime.Now);
const string getSgidAddrPntsPredirNotNeededQuery = @"select a.*
from (SELECT DISTINCT AddSystem,AddNum,AddNumSuffix,PrefixDir,StreetName,StreetType,SuffixDir,City,ZipCode,CountyID,(ltrim(rtrim(AddSystem)) + ' | ' + ltrim(rtrim(AddNum)) + ' ' + ltrim(rtrim(AddNumSuffix)) + ' ' + ltrim(rtrim(PrefixDir)) + ' ' + ltrim(rtrim(StreetName)) + ' ' + ltrim(rtrim(StreetType + ' ' + ltrim(rtrim(SuffixDir)))))UTAddPtID FROM LOCATION.ADDRESSPOINTS WHERE PrefixDir <> '' AND StreetName LIKE '%[A-Z]%' AND StreetName NOT LIKE 'HIGHWAY %') a
where NOT EXISTS
(
select null
from LOCATION.ADDRESSPOINTS b
where a.AddSystem = b.AddSystem
and a.StreetName = b.StreetName
and a.AddNum = b.AddNum
and a.StreetType = b.StreetType
and a.SuffixDir = b.SuffixDir
and a.AddNumSuffix = b.AddNumSuffix
and a.PrefixDir <> b.PrefixDir
)";
// old way - const string sgidAddrPntstoVerifyQuery = @"SELECT DISTINCT AddSystem,AddNum,AddNumSuffix,PrefixDir,StreetName,StreetType,SuffixDir,City,ZipCode,CountyID FROM LOCATION.ADDRESSPOINTS WHERE PrefixDir <> '' AND StreetName LIKE '%[A-Z]%' AND StreetName NOT LIKE 'HIGHWAY %' AND AddSystem = 'SALT LAKE CITY';";
using (var con = new SqlConnection(connectionStringSgid))
{
con.Open();
var getSgidAddrPntsPredirNotNeeded = con.Query(getSgidAddrPntsPredirNotNeededQuery);
//old way - var sgidAddrPntsToVerifyList = con.Query(sgidAddrPntstoVerifyQuery);
// loop through potential altname address points (from sgid records)
var sgidAddrPntsResults = getSgidAddrPntsPredirNotNeeded as dynamic[] ?? getSgidAddrPntsPredirNotNeeded.ToArray();
foreach (var sgidAddrPnt in sgidAddrPntsResults)
{
// add the addresspoint to the altnames table
var dictRow = sgidAddrPnt as IDictionary <string, object>;
// Remove the PREDIR key/value from the Dictionary.
dictRow.Remove("PrefixDir");
// replace the possible double space in the UTAddPtID field from missing AddNumSuffix
// get the value from the field, and trim it
string stringRemovedDblSpace = dictRow["UTAddPtID"].ToString();
//if (stringRemovedDblSpace != null)
//{
// stringRemovedDblSpace = stringRemovedDblSpace.Trim();
//}
stringRemovedDblSpace = stringRemovedDblSpace.Trim();
stringRemovedDblSpace = Regex.Replace(stringRemovedDblSpace, @"\s+", " ");
if (stringRemovedDblSpace != dictRow["UTAddPtID"].ToString())
{
dictRow.Remove("UTAddPtID");
dictRow.Add("UTAddPtID", stringRemovedDblSpace);
}
// Insert these values into the esri fgdb table.
EsriHelper.InsertRowInto(altnameTableAddrPnts, dictRow);
}
//var sgidAddrPntsList = sgidAddrPntsToVerifyList as dynamic[] ?? sgidAddrPntsToVerifyList.ToArray();
//foreach (var sgidAddrPntToVerify in sgidAddrPntsList)
//{
// // check for matching address point in same address-system with different prefix
// // Check is the SuffixDir is empty or null before creating the query
// string matchingAddrPntQuery = @"SELECT Count(*) Count FROM LOCATION.ADDRESSPOINTS WHERE AddSystem = '" + sgidAddrPntToVerify.AddSystem + @"' AND AddNum = '" + sgidAddrPntToVerify.AddNum + @"' AND PrefixDir <> '" + sgidAddrPntToVerify.PrefixDir + @"' AND StreetName = '" + sgidAddrPntToVerify.StreetName + @"' AND StreetType = '" + sgidAddrPntToVerify.StreetType + @"' AND SuffixDir = '" + sgidAddrPntToVerify.SuffixDir + @"' AND AddNumSuffix = '" + sgidAddrPntToVerify.AddNumSuffix + @"';";
// using (var con1 = new SqlConnection(connectionStringSgid))
// {
// con1.Open();
// var rowCount = con1.ExecuteScalar<int>(matchingAddrPntQuery);
// if (rowCount == 0)
// {
// counter = counter + 1;
// Console.WriteLine(counter + ": AddressPoints: not found with other predir.");
// IDictionary<string, object> dictRow =
// sgidAddrPntToVerify as IDictionary<string, object>;
// //dictRow.Remove("OBJECTID");
// dictRow.Remove("PrefixDir");
// EsriHelper.InsertRowInto(altnameTableAddrPnts, dictRow);
// }
// else
// {
// Console.WriteLine(counter + ": AddressPoints: found with other predir - don't add to altnames table.");
// }
// }
//}
// stop editing from altnames addr pnts table
outputEditWorkspace.StopEditOperation();
outputEditWorkspace.StopEditing(true);
}
}
public void Grind(IWorkspace output)
{
// Check for null values in the SGID Roads and AddressPoints - in the required fields before we begin.
Console.WriteLine("Check for null values in SGID Roads and AddressPoints: " + DateTime.Now);
var connectionStringSgid = @"Data Source=" + _options.SgidServer + @";Initial Catalog=" + _options.SgidDatabase + @";User ID=" + _options.SgidId + @";Password="******"";
const string checkForNullsRoadsQuery = @"select count(*) from Transportation.Roads (nolock) where CARTOCODE not in ('1','7','99') and NAME is null or POSTTYPE is null or ADDRSYS_L is null or ADDRSYS_R is null or PREDIR is null or POSTDIR is null or A1_NAME is null or A1_POSTTYPE is null or A2_NAME is null or A2_POSTTYPE is null or AN_NAME is null or AN_POSTDIR is null;";
const string checkForNullsAddressPointsQuery = @"select count(*) from Location.ADDRESSPOINTS (nolock) where AddNum is null or PrefixDir is null or StreetName is null or StreetType is null or SuffixDir is null or AddNumSuffix is null;";
using (var connection = new SqlConnection(connectionStringSgid))
{
connection.Open();
var rowCount = connection.ExecuteScalar <int>(checkForNullsRoadsQuery);
if (rowCount != 0)
{
Console.WriteLine(rowCount + " Null Values were found in SGID Roads in one of these fields: ADDRSYS_L, ADDRSYS_R PREDIR, NAME, POSTTYPE, POSTDIR, A1_NAME, A1_POSTTYPE, A2_NAME, A2_POSTTYPE, AN_NAME, AN_POSTDIR. Remove nulls from SGID.Transportation.Roads and try again.");
Console.ReadLine();
return;
}
}
using (var connection = new SqlConnection(connectionStringSgid))
{
connection.Open();
var rowCount = connection.ExecuteScalar <int>(checkForNullsAddressPointsQuery);
if (rowCount != 0)
{
Console.WriteLine(rowCount + " Null Values were found in SGID AddressPoints in one of these fields: AddNum, AddNumSuffix, PrefixDir, StreetName, StreetType, SuffixDir. Remove nulls from SGID.Location.AddressPoints and try again.");
//Console.ReadLine();
//return;
}
}
var startTime = DateTime.Now;
Console.WriteLine("Begin creating Geocode FC: " + DateTime.Now);
IWorkspaceEdit outputEditWorkspace = null;
try
{
var outputFeatureWorkspace = (IFeatureWorkspace)output;
// create a feature cursor from the source roads data and loop through this subset
// create the query filter to filter results
const string geocodableRoads = @"CARTOCODE not in ('1','7','99') and
((FROMADDR_L <> 0 and TOADDR_L <> 0) OR (FROMADDR_R <> 0 and TOADDR_R <> 0)) and
NAME <> '' and NAME not like '%ROUNDABOUT%'";
// GO LIVE...
//geocodableRoads = @"CARTOCODE not in ('1','7','99') and ((L_F_ADD <> 0 and L_T_ADD <> 0) OR (R_F_ADD <> 0 and R_T_ADD <> 0)) and STREETNAME <> '' and STREETNAME not like '%ROUNDABOUT%'";
var roadsFilter = new QueryFilter
{
WhereClause = geocodableRoads
};
outputEditWorkspace = (IWorkspaceEdit)outputFeatureWorkspace;
// begin an edit session on the file geodatabase (maybe) that way we can roll back if it errors out
outputEditWorkspace.StartEditing(false);
outputEditWorkspace.StartEditOperation();
// create a ComReleaser for feature cursor's life-cycle management
using (var comReleaser = new ComReleaser())
{
var roadsCursor = _roads.Search(roadsFilter, false);
comReleaser.ManageLifetime(roadsCursor);
// begin an edit session on the file geodatabase (maybe) that way we can roll back if it errors out
//outputEditWorkspace.StartEditing(false);
//outputEditWorkspace.StartEditOperation();
IFeature roadFeature;
var fieldIndexMap = new FindIndexByNameCommand(_roads, new[]
{
"ADDRSYS_L", "ADDRSYS_R", "FROMADDR_L", "TOADDR_L", "FROMADDR_R", "TOADDR_R", "PREDIR",
"NAME", "POSTTYPE", "POSTDIR", "A1_NAME", "A1_POSTTYPE", "A2_NAME",
"A2_POSTTYPE", "AN_NAME", "AN_POSTDIR", "ZIPCODE_L", "ZIPCODE_R", "GlobalID"
}).Execute();
// loop through the sgid roads' feature cursor
while ((roadFeature = roadsCursor.NextFeature()) != null)
{
var valueMap = new AddValueToIndexFieldMapCommand(fieldIndexMap, roadFeature).Execute();
// begin to populate the geocode feature class in the newly-created file geodatabase
// check if this segment has a primary streetname
if (!string.IsNullOrEmpty(valueMap["NAME"].Value.ToString()))
{
// only write necessary key/values
var streetValueMap = valueMap;
//RemoveKeys(streetValueMap);
// create a new feature for the primary street name in the roads feature class
EsriHelper.InsertFeatureInto(roadFeature, _geocodeRoads, streetValueMap, true);
// create a new feature in the scratch roads fc
EsriHelper.InsertFeatureInto(roadFeature, _scratchRoadsFC, streetValueMap, true);
}
// check if this segment has an alias name
if (!string.IsNullOrEmpty(valueMap["A1_NAME"].Value.ToString()))
{
var aliasValueMap = valueMap;
aliasValueMap["NAME"] = aliasValueMap["A1_NAME"];
aliasValueMap["POSTTYPE"] = aliasValueMap["A1_POSTTYPE"];
// check if primary name is acs, if so remove the sufdir for new alias1-based feature
if (!aliasValueMap["NAME"].ToString().Any(char.IsLetter))
{
// acs primary street
aliasValueMap.Remove("POSTDIR");
}
//RemoveKeys(aliasValueMap);
// create a new feature in the altnames table
EsriHelper.InsertRowInto(roadFeature, _altnameTableRoads, aliasValueMap, true, false);
//EsriHelper.InsertFeatureInto(roadFeature, _geocodeRoads, aliasValueMap, true);
// create a new feature in the scratch roads fc
EsriHelper.InsertFeatureInto(roadFeature, _scratchRoadsFC, aliasValueMap, true);
}
// check if this segment has a second alias name
if (!string.IsNullOrEmpty(valueMap["A2_NAME"].Value.ToString()))
{
var aliasValueMap = valueMap;
aliasValueMap["NAME"] = aliasValueMap["A2_NAME"];
aliasValueMap["POSTTYPE"] = aliasValueMap["A2_POSTTYPE"];
// check if primary name is acs, if so remove the sufdir for new alias2-based feature
// check if primary name is acs, if so remove the sufdir for new alias1-based feature
if (!aliasValueMap["NAME"].ToString().Any(char.IsLetter))
{
// acs primary street
aliasValueMap.Remove("POSTDIR");
}
//RemoveKeys(aliasValueMap);
// create a new feature in the altnames table
EsriHelper.InsertRowInto(roadFeature, _altnameTableRoads, aliasValueMap, true, false);
//EsriHelper.InsertFeatureInto(roadFeature, _geocodeRoads, aliasValueMap, true);
// create a new feature in the scratch roads table
EsriHelper.InsertFeatureInto(roadFeature, _scratchRoadsFC, aliasValueMap, true);
}
// check if this segment has an acs alias name
if (!string.IsNullOrEmpty(valueMap["AN_NAME"].Value.ToString()))
{
var acsValueMap = valueMap;
acsValueMap["NAME"] = acsValueMap["AN_NAME"];
acsValueMap["POSTDIR"] = acsValueMap["AN_POSTDIR"];
acsValueMap.Remove("POSTTYPE");
//RemoveKeys(acsValueMap);
// create a new feature in the altnames table
EsriHelper.InsertRowInto(roadFeature, _altnameTableRoads, acsValueMap, false, false);
//EsriHelper.InsertFeatureInto(roadFeature, _geocodeRoads, acsValueMap, false);
// create a new feature in the scratch roads table
EsriHelper.InsertFeatureInto(roadFeature, _scratchRoadsFC, acsValueMap, false);
}
}
outputEditWorkspace.StopEditOperation();
outputEditWorkspace.StopEditing(true);
}
Console.WriteLine("begin indexing fields: " + DateTime.Now);
// Add Indexes to query fields.
string[] fieldsToIndex = { "ADDRSYS_L", "ADDRSYS_R", "FROMADDR_L", "TOADDR_L", "FROMADDR_R", "TOADDR_R",
"PREDIR", "NAME", "POSTTYPE", "POSTDIR" };
foreach (var field in fieldsToIndex)
{
AddIndexToFieldCommand.Execute(_scratchRoadsFC, field + "_IDX", field);
}
Console.WriteLine("finished indexing fields: " + DateTime.Now);
// Create the AltNamesRoads table.
PopulateAltNamesRoadsTableCommand.Execute(outputEditWorkspace, _altnameTableRoads, _scratchRoadsFC);
// Create the AltNamesAddrPnts table.
PopulateAltNamesAddrPntsTableCommand.Execute(outputEditWorkspace, _altnameTableAddrPnts, connectionStringSgid);
Console.WriteLine("Started at: " + startTime);
Console.WriteLine("Finished at: " + DateTime.Now);
Console.WriteLine("Press any key to continue...");
Console.ReadLine();
}
finally
{
// stop editing and don't save the edits
if (outputEditWorkspace != null && outputEditWorkspace.IsBeingEdited())
{
outputEditWorkspace.StopEditOperation();
outputEditWorkspace.StopEditing(false);
}
}
}
