API tools faq
paste
Guest User

Untitled

a guest
Feb 21st, 2018
109
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.67 KB | None | 0 0
raw download clone embed print report
  1. import arcpy
  2. import sys
  3. import pyodbc
  4. import datetime
  5.  
  6. # field names
  7. X_fld = 'X'
  8. Y_fld = 'Y'
  9. PRECINCT_ID_fld = 'PRECINCT_ID'
  10. COUNTY_ID_fld = 'COUNTY_ID'
  11. RESIDENCE_ID_fld = 'RESIDENCE_ID'
  12. VistaID_fld = 'VistaID'
  13.  
  14. try:
  15. db = sys.argv[1]
  16. db_username = sys.argv[2]
  17. db_password = sys.argv[3]
  18. db_server = sys.argv[4]
  19. county_num = sys.argv[5]
  20. res_id = sys.argv[6]
  21. except IndexError:
  22. db = raw_input('Database Instance (e.g. test, live, dev, etc..): ')
  23. db_username = raw_input('Database Username: ')
  24. db_password = raw_input('Database Password: ')
  25. db_server = raw_input('Database Server:Port/ServiceName: ')
  26. county_num = raw_input('County Number: ')
  27. res_id = raw_input('Residence ID: ')
  28.  
  29. log_file_location = r'\\<machine name>\v1\Apps\VISTA\Working Directory\Services\ResidencePrecinctUpdate' + r'\\' + county_num+ '_' + db + '_' + datetime.datetime.now().strftime("%Y%m%d") + '.log'
  30. ResCounty_file_location = r'\\<machine name>\v1\Apps\VISTA\Working Directory\Services\ResidencePrecinctUpdate' + r'\\' + county_num + '_' + db + '_Residence-County_Issues' + '_' + datetime.datetime.now().strftime("%Y%m%d") + '.log'
  31.  
  32. with open(log_file_location, "a+") as log_file:
  33. log_file.write("Beginning process - " + datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") + " \n")
  34.  
  35. counties = r'SGID10.sde\SGID10.BOUNDARIES.Counties'
  36. vista_ballot_areas = r'SGID10.sde\SGID10.POLITICAL.VistaBallotAreas'
  37. precincts_table = r'vista_' + db + '.odc\GV_VISTA.PRECINCTS'
  38. residences_table = r'vista_' + db + '.odc\GV_VISTA.RESIDENCES'
  39.  
  40. print('Running GIS update process.')
  41. log_file.write('Running GIS update process. \n')
  42. #print('Making temporary Residences table view.')
  43. log_file.write('Making temporary Residences table view. \n')
  44. query = '{} = {}'.format(COUNTY_ID_fld, county_num)
  45. resquery = query + ' AND ' + '{} IN ({})'.format(RESIDENCE_ID_fld, res_id)
  46. xy_table = arcpy.MakeQueryTable_management([residences_table], 'xy_table', 'USE_KEY_FIELDS', 'GV_VISTA.RESIDENCES.RESIDENCE_ID', where_clause=resquery)
  47. log_file.write('Query: ' + resquery + " \n")
  48.  
  49. #print('Setting precinct name to id')
  50. log_file.write('Setting precinct name to id \n')
  51. precinct_table = arcpy.MakeQueryTable_management([precincts_table], 'precinct_table', 'USE_KEY_FIELDS', 'GV_VISTA.PRECINCTS.PRECINCT_ID', where_clause=query)
  52. precinct_name_to_id = {}
  53. with arcpy.da.SearchCursor(precinct_table, [PRECINCT_ID_fld, 'PRECINCT']) as scur:
  54. for row in scur:
  55. if row[0] is not None:
  56. if row[0] > 0:
  57. log_file.write(' Setting ' + str(row[1]) + ' to ' + str(row[0]) + ' \n')
  58. precinct_name_to_id[row[1]] = row[0]
  59. else:
  60. log_file.write(' Cannot set ' + str(row[0]) + ' to ' + str(row[0]) + ' \n')
  61. else:
  62. log_file.write(' Setting NONE to ' + str(row[0]) + ' \n')
  63.  
  64. print('Creating the [XY] layer')
  65. log_file.write('Creating the [XY] layer \n')
  66. xy_layer = arcpy.MakeXYEventLayer_management(xy_table, X_fld, Y_fld, 'xy_layer', arcpy.SpatialReference(26912))
  67.  
  68. print('Setting GIS identity')
  69. log_file.write('Setting GIS identity for counties. \n')
  70. identityCounties = arcpy.Identity_analysis(xy_layer, counties, 'in_memory\identity_counties')
  71.  
  72. i = 0
  73. print("Validating Residnce and County IDs.")
  74. log_file.write('Validating Residnce and County IDs. \n')
  75. with open(ResCounty_file_location, "a+") as ResCounty_file:
  76. with arcpy.da.SearchCursor(identityCounties, ['COUNTYNBR', COUNTY_ID_fld, RESIDENCE_ID_fld]) as cur:
  77. for row in cur:
  78. if row[0] is not None:
  79. resID = str(row[2])
  80. countyID = str(row[0])
  81. if countyID.startswith('0'):
  82. countyID = countyID.replace('0','')
  83. if countyID != '':
  84. gisCountyID = str(row[1]).replace('.0','')
  85. if countyID != gisCountyID:
  86. print('Residence ID [' + resID + '] belongs to County ['+ countyID + ']' + ' - GIS County [' + gisCountyID + '].')
  87. log_file.write('Residence ID [' + resID + '] belongs to County ['+ countyID + '] \n')
  88. ResCounty_file.write('Residence ID [' + resID + '] belongs to County ['+ countyID + '] \n')
  89. i = i + 1
  90. else:
  91. print('Residence ID [' + resID + '] has invalid X and/or Y coordinates.')
  92. log_file.write('Residence ID [' + resID + '] has invalid X and/or Y coordinates. \n')
  93. ResCounty_file.write('Residence ID [' + resID + '] has invalid X and/or Y coordinates. \n')
  94. i = i + 1
  95. ResCounty_file.close()
  96.  
  97. if i == 0:
  98. print('Setting GIS identity')
  99. log_file.write('Setting GIS identity for vista ballot areas. \n')
  100. identity = arcpy.Identity_analysis(xy_layer, vista_ballot_areas, 'in_memory\identity')
  101.  
  102. print('Building GIS Residence to Precinct Mapping table.')
  103. log_file.write('Building GIS Residence to Precinct Mapping table. \n')
  104. res_to_precinct_dict = {}
  105. with arcpy.da.SearchCursor(identity, [RESIDENCE_ID_fld, VistaID_fld]) as scur:
  106. for row in scur:
  107. if row[1] is not None:
  108. if str(row[1]) <> '':
  109. log_file.write(' Mapping ' + str(row[0]) + ' to ' + str(row[1]) + ' \n')
  110. res_to_precinct_dict[row[0]] = row[1]
  111. else:
  112. log_file.write(' Cannot map ' + str(row[0]) + ' to ' + str(row[1]) + ' \n')
  113. else:
  114. log_file.write(' Mapping NONE to ' + str(row[0]) + ' \n')
  115.  
  116.  
  117. print('Updating Precinct IDs')
  118. log_file.write('Updating VISTA Database \n')
  119. with arcpy.da.SearchCursor(xy_table, [RESIDENCE_ID_fld]) as cur:
  120. connection = pyodbc.connect('Driver={Microsoft ODBC for Oracle};UID=' + db_username + ';PWD=' + db_password + ';SERVER='+ db_server)
  121. cursor = connection.cursor()
  122. for row in cur:
  123. log_file.write(' \n')
  124. if row[0] is not None:
  125. log_file.write('Residence ID: ' + str(row[0]) + ' \n')
  126. if row[0] > 0:
  127. res_statement = """
  128. SELECT PRECINCT_ID, COUNTY_ID
  129. FROM GV_VISTA.RESIDENCES
  130. WHERE RESIDENCE_ID = {}
  131. AND COUNTY_ID = {}
  132. """.format(row[0],county_num)
  133. log_file.write(' Query: ' + str(res_statement) + ' \n')
  134. res_rows = cursor.execute(res_statement).fetchone()
  135. if res_rows:
  136. if str(county_num) == str(res_rows.COUNTY_ID):
  137. log_file.write(' Updating Precinct ID: \n')
  138. log_file.write(' Old: ' + str(res_rows.PRECINCT_ID) + ' \n')
  139. try:
  140. new_precinct_id = precinct_name_to_id[res_to_precinct_dict[row[0]]]
  141. except KeyError as error:
  142. print('ERROR: issue with res id: {}. error: {}'.format(row[0], error) + ' \n')
  143. log_file.write('ERROR: issue with res id: {}. \n Message: {}'.format(row[0], error) + ' \n')
  144. continue
  145. log_file.write(' New: ' + str(new_precinct_id) + ' \n')
  146. statement = """
  147. UPDATE GV_VISTA.RESIDENCES
  148. SET PRECINCT_ID = {}
  149. WHERE RESIDENCE_ID = {}
  150. """.format(new_precinct_id, row[0])
  151. log_file.write(' Query: ' + str(statement) + ' \n')
  152. cursor.execute(statement)
  153. connection.commit()
  154. log_file.write(' Commit Successful \n')
  155. log_file.write(' Recording only changes to repository \n')
  156. if res_rows.PRECINCT_ID != new_precinct_id:
  157. evr_connection = pyodbc.connect('Driver={Microsoft ODBC for Oracle};UID=GV_EVR;PWD=<password>;SERVER=<server>:1521/tgvdv')
  158. evr_cursor = evr_connection.cursor()
  159. evr_statement = """INSERT INTO GV_EVR.RESIDENCE_CHANGE_DETAILS
  160. (KEY,TABLE_NAME,FIELD_NAME,TRIGGERED_ACTION,FROM_VALUE,TO_VALUE,CHANGED_BY,RESIDENCE_ID)
  161. VALUES({},'RESIDENCES','PRECINCT_ID','INSERT',{},{},'VISTA.Services.Residence.PrecinctUpdate',{})""".format(row[0],res_rows.PRECINCT_ID,new_precinct_id,row[0])
  162. evr_cursor.execute(evr_statement)
  163. log_file.write(' Query: ' + str(statement) + ' \n')
  164. evr_connection.commit()
  165. log_file.write(' Commit Successful. \n')
  166. else:
  167. print('Residence ID [' + str(row[0]) + '] belongs to County ['+ str(res_rows.COUNTY_ID) + ']')
  168. log_file.write('Residence ID [' + str(row[0]) + '] belongs to County ['+ str(res_rows.COUNTY_ID) + '] \n')
  169. with open(ResCounty_file_location, "a+") as ResCounty_file2:
  170. ResCounty_file2.write('Residence ID [' + str(row[0]) + '] belongs to County ['+ str(res_rows.COUNTY_ID) + '] \n')
  171. ResCounty_file2.close()
  172. else:
  173. print('Residence ID not found in VISTA.')
  174. log_file.write('Residence ID not found in VISTA. \n')
  175. else:
  176. print('Residence ID not found in VISTA. \n')
  177. log_file.write('Residence ID not found in VISTA. \n')
  178. else:
  179. print("No rows to update \n")
  180. log_file.write("No rows to update \n")
  181. else:
  182. log_file.write("County and residence validation issues. \n")
  183.  
  184. log_file.write("Ending process - " + datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") +" \n")
  185. log_file.close()
  186. print('*** Precinct IDs updated for this batch. ***')
  187. print(' ')
  188. print(' ')
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!