forked from ncss-tech/NASIS-Pedons
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Extract_Pedons_from_NASIS_updateTableAliases.py
250 lines (188 loc) · 10.5 KB
/
Extract_Pedons_from_NASIS_updateTableAliases.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
#-------------------------------------------------------------------------------
# Name: Extract Pedons from NASIS - update Table Aliases
# Purpose: This script will update the Table and field aliases for the FGDB whose schema
# will be used in an XML workspace. All aliases are coming from the 'MetadataTable'
# and 'MetadataTableColumn'tables. They must be present. This schema reflects the
# nasis 7.3.3 model. If everything goes haywire use the 'NASIS_Schema_ODBC.mdb'
# access database that was created using and ODBC connection to NASIS.
#
# Relationships will also be created. The MetadataRelationshipDetail is used for this.
# 4 columns were added to this table in order to simplify the process. This table had to
# be joined with the MetadataTableColumn and MetadataTable tables in order to populate
# the 4 new fields.
#
# This script is not intended for distribution. It is intended for prepartion of the
# XML workspace
#
# Author: Adolfo.Diaz
# e-mail: [email protected]
# phone: 608.662.4422 ext. 216
#
# Created: 9/29/2016
# Last Updated: 10/4/2016
# Copyright: (c) Adolfo.Diaz 2016
#-------------------------------------------------------------------------------
## ===================================================================================
class ExitError(Exception):
pass
## ===================================================================================
def AddMsgAndPrint(msg, severity=0):
# prints message to screen if run as a python script
# Adds tool message to the geoprocessor
#
#Split the message on \n first, so that if it's multiple lines, a GPMessage will be added for each line
try:
print msg
f = open(textFilePath,'a+')
f.write(msg + " \n")
f.close
del f
#for string in msg.split('\n'):
#Add a geoprocessing message (in case this is run as a tool)
if severity == 0:
arcpy.AddMessage(msg)
elif severity == 1:
arcpy.AddWarning(msg)
elif severity == 2:
arcpy.AddError("\n" + msg)
except:
pass
# ===================================================================================
def FindField(layer,chkField):
# Check table or featureclass to see if specified field exists
# If fully qualified name is found, return that name; otherwise return ""
# Set workspace before calling FindField
try:
if arcpy.Exists(layer):
theDesc = arcpy.Describe(layer)
theFields = theDesc.fields
theField = theFields[0]
for theField in theFields:
# Parses a fully qualified field name into its components (database, owner name, table name, and field name)
parseList = arcpy.ParseFieldName(theField.name) # (null), (null), (null), MUKEY
# choose the last component which would be the field name
theFieldname = parseList.split(",")[len(parseList.split(','))-1].strip() # MUKEY
if theFieldname.upper() == chkField.upper():
return theField.name
return False
else:
AddMsgAndPrint("\tInput layer not found", 0)
return False
except:
errorMsg()
return False
# =========================================================== Main Body =============================================================================
import sys, string, os, traceback, urllib, re, arcpy
from arcpy import env
if __name__ == '__main__':
nasisPedons = os.path.dirname(sys.argv[0]) + os.sep + "NasisPedonsTemplate.gdb"
aliasTable = nasisPedons + os.sep + "MetadataTable"
aliasFieldTbl = nasisPedons + os.sep + "MetadataTableColumn"
relateTable = nasisPedons + os.sep + "MetadataRelationshipDetail"
if not arcpy.Exists(aliasTable):
raise ExitError, "\nTable to extract table aliases does NOT exist: Metadata Table"
if not arcpy.Exists(aliasFieldTbl):
raise ExitError, "\nTable to extract field aliases does NOT exist: Metadata Table Column"
if not arcpy.Exists(relateTable):
raise ExitError, "\nTable to establish Relationships does NOT exist: Metadata Relationship Detail"
arcpy.env.workspace = nasisPedons
nasisPedonsTables = arcpy.ListTables()
# manually add the site table b/c ListTables does not recognize it.
if arcpy.Exists(nasisPedons + os.sep + "pedon"):
nasisPedonsTables.append("pedon")
textFilePath = os.path.dirname(sys.argv[0]) + os.sep + "NASIS_Pedons_Table_Field_Aliases.txt"
if os.path.exists(textFilePath):
os.remove(textFilePath)
""" --------------------------------------- Create a dictionary of Table Aliases --------------------------------"""
tblName = FindField(aliasTable,"TablePhysicalName")
tblAlias = FindField(aliasTable,"TableLabel")
tblID = FindField(aliasTable,"TableID")
if not tblName or not tblAlias:
raise ExitError, "\nNecessary fields are missing from alias table " + tblName + " OR " + tblAlias
tblAliasDict = dict() # i.e.{u'MetadataDomainMaster': (u'Domain Master Metadata', 4987)}
with arcpy.da.SearchCursor(aliasTable, [tblName,tblAlias]) as cursor:
for row in cursor:
if not tblAliasDict.has_key(row[0]):
tblAliasDict[row[0]] = (row[1])
""" --------------------------------------- Create a dictionary of Field Aliases --------------------------------"""
fldName = FindField(aliasFieldTbl,"ColumnPhysicalName")
fldAlias = FindField(aliasFieldTbl,"ColumnLabel")
if not fldName or not fldAlias:
raise ExitError, "\nNecessary fields are missing from field alias table: " + fldName + " OR " + fldAlias
fldAliasDict = dict()
with arcpy.da.SearchCursor(aliasFieldTbl, [fldName,fldAlias]) as cursor:
for row in cursor:
if not fldAliasDict.has_key(row[0]):
fldAliasDict[row[0]] = row[1]
""" ------------------------------------------ Update Table and Field Aliases ------------------------------------"""
missingAliases = dict()
for table in nasisPedonsTables:
if tblAliasDict.has_key(table):
arcpy.AlterAliasName(table,tblAliasDict.get(table))
AddMsgAndPrint("\n" + table + ": " + tblAliasDict.get(table))
else:
AddMsgAndPrint("\n" + table + ": No alias found!")
i = 1
fields = arcpy.ListFields(table)
for field in fields:
if field.name == "OBJECTID" or field.name == "SHAPE":continue
if fldAliasDict.has_key(field.name):
try:
alias = fldAliasDict.get(field.name)
arcpy.AlterField_management(table,field.name,"#",alias)
AddMsgAndPrint("\t\t" + str(i) + ". " + field.name + " - " + alias)
except:
pass
#AddMsgAndPrint(table + "," + tblAliasDict.get(table) + "," + field.name + "," + alias + "," + str(i)) # Use this line to create a comma seperated file
i += 1
else:
AddMsgAndPrint("\t\t" + str(i) + ". " + field.name + " - NO ALIAS FOUND")
#AddMsgAndPrint(table + "," + tblAliasDict.get(table) + "," + field.name + ",NO ALIAS FOUND," + str(i)) # Use this line to create a comma seperated file
if not missingAliases.has_key(table):
missingAliases[table] = [field.name]
else:
missingAliases[table].append(field.name)
i+=1
if len(missingAliases):
AddMsgAndPrint("\nSummary of Missing aliases:")
AddMsgAndPrint("\t" + str(missingAliases),2)
""" ------------------------------------------ Establish Relationships ------------------------------------"""
for table in nasisPedonsTables:
fields = ["LeftTable_TableName","LeftTable_FieldName","RightTable_TableName","RightTable_FieldName"]
for field in fields:
if not arcpy.ListFields(relateTable,field):
raise ExitError, field + " NOT Found in " + relateTable + " table!"
AddMsgAndPrint("\n" + table + " Table")
expression = arcpy.AddFieldDelimiters(relateTable,"LeftTable_TableName") + ' = \'' + table + "\'"
with arcpy.da.SearchCursor(relateTable, (fields),where_clause=expression) as cursor:
for row in cursor:
destinationTable = row[2]
if destinationTable in nasisPedonsTables:
originTable = row[0]
originPKey = row[1]
originFKey = row[3]
originTablePath = nasisPedons + os.sep + originTable
destinationTablePath = nasisPedons + os.sep + destinationTable
if not arcpy.ListFields(originTablePath,originPKey):
AddMsgAndPrint("\t" + originPKey + " NOT FOUND")
continue
if not arcpy.ListFields(destinationTablePath,originFKey):
AddMsgAndPrint("\t" + originFKey + " NOT FOUND")
continue
relName = "x" + originTable.capitalize() + "_" + destinationTable.capitalize()
theCardinality = "ONE_TO_MANY"
# create Forward Label i.e. "> Horizon AASHTO Table"
if tblAliasDict.has_key(destinationTable):
fwdLabel = "> " + tblAliasDict.get(destinationTable) + " Table"
else:
fwdLabel = destinationTable + " Table"
# create Backward Label i.e. "< Horizon Table"
if tblAliasDict.has_key(originTable):
backLabel = "< " + tblAliasDict.get(originTable) + " Table"
else:
backLabel = "< " + originTable + " Table"
if not arcpy.Exists(relName):
arcpy.CreateRelationshipClass_management(originTablePath, destinationTablePath, relName, "SIMPLE", fwdLabel, backLabel, "NONE", theCardinality, "NONE", originPKey, originFKey, "","")
AddMsgAndPrint("\t" + relName)
else:
AddMsgAndPrint("\t" + relName)