Latest Contributions by RichardFairhurst

It is possible to create an ArcMap Python Addin tool that simultaneously captures clicked map coordinates stamped with a user name and date time in an ArcMap layer displayed on a map and collect them into a table in a Microsoft Access database housed on a shared network drive.  The tool outlined in this blog only keeps track of the last coordinate clicked by each user in the Access coordinate table with the expectation that these records would be used like a clipboard by custom Access forms that control the creation or editing of records within Access.  However, the tool code presented here could be modified to directly update any Access table from within ArcMap if prompts are added to the tool that let the user choose the Access records they want to create or edit as a result of each mouse click. The pictures below shows the result of using the tool to capture the location of two different STOP markings on a road.  The table records in ArcMap and Access match completely after simply activating the tool and clicking on each marking. The tool only activates when the left mouse button is clicked within a map and the Alt, Ctrl and Shift keys are not being pressed.  I created the Access tblGIS_COORDINATE table manually, but the ArcMap GIS_COORDINATE feature class will automatically be created by the tool if it doesn't exist in the default database and be added to the map as a layer and made visible if it is not currently a layer in the map or visible when the mouse is clicked.  The tool ensures that the cache is cleared if the GIS_COORDINATE table view is open so that it will refresh to match the point location on the map and you may have to make Access the currently active program to see its table refresh if it is open. Technical Notes: To build this tool you should download the addin_assistant tool from https://www.arcgis.com/home/item.html?id=5f3aefe77f6b4f61ad3e4c62f30bff3b.  If you are running Python 3 or higher you will have to fix one line of code in the makeaddin.py file created by the addin assistant by changing it from: print archive_file to print(archive_file) to build the tool use the wizard to first create a toolbar and then create a tool under it. For the tool icon I used the EditingRemoteEditSynchronize32.png file in the C:\Program Files (x86)\ArcGIS\Desktop10.6\bin\Icons directory, but you are welcome to design your own. . To install pyodbc you can do the following: pyodbc list here is a list of whl files. Download the right one for you. Open cmd as administrator and go to the directory containing the file. Run  pip install pyodbc-xxxxx.whl . If you get an error about the odbcji32.dll required to use the {Microsoft Access Driver (*.mdb, *.accdb)} you can follow the instructions at Unable to load odbcji32.dll (MS Access ODBC driver)  The tool captures coordinates in both the State Plane Spatial Reference preferred by my jurisdiction and in the GCS WGS 1984 Spatial Reference used by Google maps.  You should change the State Plane Spatial Reference to your own preferred Spatial Reference.  You can find the WKID of your preferred Spatial Reference by adding a layer that uses that Spatial Reference to your map and opening the Data Frame Properties dialog, choosing the Coordinate System tab and choosing the Spatial Reference under the Layers group at the bottom of the list. I am not an ArcGIS Pro user, so I have not explored whether or not a similar addin can be built for use in ArcGIS Pro, but the development of a tool for Pro shouldn't affect the pyodbc code that makes communication with Microsoft Access possible. I hope many of you will find this useful and intriguing.  I am also open to suggestions for other ways that pyodbc and python code might be used to create tools that may integrate Access and ArcMap more fully. import arcpy import pythonaddins import getpass from datetime import datetime import pyodbc class CaptureCoordinateTool(object): """Implementation for Capture_Coordinate2_addin.tool (Tool)""" def __init__(self): self.enabled = True self.shape = "NONE" # Can set to "Line", "Circle" or "Rectangle" for interactive shape drawing and to activate the onLine/Polygon/Circle event sinks. def onMouseDownMap(self, x, y, button, shift): # Determine if the button and shift states are correct for activating the tool if button != 1 or shift != 0: # The left mouse button was not pressed or the Alt, Ctrl and/or Shift keys were pressed pass # Do default ArcMap behavior and exit else: # The left mouse button was pressed and the Alt, Ctrl and Shift keys were not pressed # Get information about the settings of the map clicked mxd = arcpy.mapping.MapDocument("CURRENT") # get the current map where the mouse was pressed df = arcpy.mapping.ListDataFrames(mxd)[0] # Assume the first data frame in the map was clicked sr = df.spatialReference # get the spatial reference of the data frame if sr.factoryCode != 2230 and sr.factoryCode != 4326: # Spatial Reference is invalid if it is not 2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet) or 4326 (GCS_WGS_1984) message = "Invalid Spatial Reference " + str(sr.factoryCode) + " - " + sr.name + " detected.\nPlease set the Spatial Reference to\n2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet)\nor 4326 (GSC_WGS_1984)" pythonaddins.MessageBox(message, "Map Spatial Reference Is Invalid") # Display a message alerting user that the map has an invalid spatial reference and exit else: # Spatial Reference is either 2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet) or 4326 (GCS_WGS_1984) and therefore valid try: # Get the GIS_COORDINATE layer and if necessary Create the feature class or add the layer to the map user_name = getpass.getuser() # get the login name of the user out_path = "C:\Users\{}\Documents\ArcGIS\Default.gdb".format(user_name) # ArcMap Default geodartbase out_name = "GIS_COORDINATE" # FC name coordinate_fc = out_path + "\\" + out_name # Full path and name of FC geometry_type = "POINT" # Features will be points template = "" has_m = "DISABLED" has_z = "DISABLED" spatial_ref = arcpy.SpatialReference(2230) # Set Spatial Reference to 2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet) preferred by Riverside County if not arcpy.Exists(coordinate_fc): # Create an FC for displaying the clicked point in the preferred spatial reference and add fields to match the Access table schema arcpy.CreateFeatureclass_management(out_path, out_name, geometry_type, template, has_m, has_z, spatial_ref) arcpy.AddField_management(coordinate_fc, "USER_NAME", "TEXT", field_length=50) arcpy.AddField_management(coordinate_fc, "LONGITUDE", "DOUBLE") arcpy.AddField_management(coordinate_fc, "LATITUDE", "DOUBLE") arcpy.AddField_management(coordinate_fc, "X_COORDINATE", "DOUBLE") arcpy.AddField_management(coordinate_fc, "Y_COORDINATE", "DOUBLE") arcpy.AddField_management(coordinate_fc, "COORDINATE_DATE", "DATE") coordinate_lyr = None # Create an unassigned variable for a layer that will display the coordinate for lyr in arcpy.mapping.ListLayers(mxd, "", df): # Check all existing layers in the dataframe clicked if lyr.dataSource.lower() == coordinate_fc.lower(): # Check if any layer has the coordinate fc as its datasource coordinate_lyr = lyr # set the coordinate layer variable to the map layer that has the coordinate feature class coordinate_lyr.visible = True # make sure the layer is visible if coordinate_lyr == None: # Check if no layer was found in the map arcpy.MakeFeatureLayer_management(coordinate_fc, out_name) # Make a layer from the map, which should automatically be added to the map for lyr in arcpy.mapping.ListLayers(mxd, "", df): # Recheck all existing layers in the dataframe clicked if lyr.dataSource.lower() == coordinate_fc.lower(): # Find the created layer that has the coordinate fc as its datasource coordinate_lyr = lyr # set the coordinate layer variable to the map layer that has the coordinate feature class coordinate_lyr.visible = True # make sure the layer is visible # Capture date for the point geometries and coordinates of both spatial references, the user name and the date time when the data was captured x_stateplane, y_stateplane, longitude, latitude, state_plane_PtGeom, wgs_1984_PtGeom = 0, 0, 0, 0, None, None # Initialize variables for state plain and wgs 1984 coordinates and points point = arcpy.Point() # Create a point object point.X, point.Y = x, y # Set the x, y of the point object to the coordinates clicked pointGeom = arcpy.PointGeometry(point, sr) # create a PointGeometry based on the point coordinates and spatial reference of the map click if sr.factoryCode == 2230: # Spatial Reference is 2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet) state_plane_PtGeom = pointGeom # set state_plane_PtGeom to pointGeom clicked x_stateplane, y_stateplane = x, y # x_stateplane and y_stateplane to x and y clicked srOut = arcpy.SpatialReference(4326) # Create a 4326 (GCS_WGS_1984) Spatial Reference wgs_1984_PtGeom = pointGeom.projectAs(srOut) # Project the point clicked to 4326 (GCS_WGS_1984) spatial reference longitude, latitude = wgs_1984_PtGeom.centroid.X, wgs_1984_PtGeom.centroid.Y # set longitude and latitude to projected X and Y elif sr.factoryCode == 4326: # Spatial Reference is 4326 (GCS_WGS_1984) wgs_1984_PtGeom = pointGeom # set wgs_1984_PtGeom to pointGeom clicked longitude, latitude = x, y # set longitude to latitude to x and y clicked srOut = arcpy.SpatialReference(2230) # Create a 2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet) Spatial Reference state_plane_PtGeom = pointGeom.projectAs(srOut) # Project the point clicked to 2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet) spatial reference x_stateplane, y_stateplane = state_plane_PtGeom.centroid.X, state_plane_PtGeom.centroid.Y # set x_stateplane and y_stateplane to projected X and Y dtnow = datetime.now() # Capture the current datetime # Cutput the captured data conn_str = ( r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' r'DBQ=\\agency\agencydfs\Annex\Files\TRAFFIC\TRAFFICDB\TRAFFICDB\TRAFFICDB.accdb;' ) # set up a connection string for connecting to an Access accdb database cnxn = pyodbc.connect(conn_str) # connect to the Access accdb database crsr = cnxn.cursor() # create a cursor from the connection crsr.execute("SELECT USER_NAME FROM tblGIS_COORDINATE WHERE USER_NAME = ?", user_name) # Execute a query to find any records that match the user_name that clicked the point row = crsr.fetchone() # fetch the first record (there should only be none or one record) if row: # If a row was found update the record with the coordinates and date time of the user click crsr.execute("UPDATE tblGIS_COORDINATE SET LONGITUDE = ?, LATITUDE = ?, X_COORDINATE = ?, Y_COORDINATE = ?, COORDINATE_DATE = ? WHERE USER_NAME = ?", longitude, latitude, x_stateplane, y_stateplane, dtnow, user_name) cnxn.commit() else: # If no row was found insert a record for the current user_name with the coordinates and date time of the user click crsr.execute("INSERT INTO tblGIS_COORDINATE(USER_NAME, LONGITUDE, LATITUDE, X_COORDINATE, Y_COORDINATE, COORDINATE_DATE) values (?, ?, ?, ?, ?, ?)", user_name, longitude, latitude, x_stateplane, y_stateplane, dtnow) cnxn.commit() crsr.close() # close the accdb cursor cnxn.close() # close the accdb connection arcpy.SelectLayerByAttribute_management(coordinate_lyr.name, "CLEAR_SELECTION") # Make sure no records are selected in the layer displaying the clicked coordinate fields = ['SHAPE@', 'USER_NAME', 'LONGITUDE', 'LATITUDE', 'X_COORDINATE', 'Y_COORDINATE', 'COORDINATE_DATE'] # create a list of fields for the layer to be updated or inserted count = 0 # Create a counter to determine if a record exists already or needs to be inserted with arcpy.da.UpdateCursor(coordinate_lyr.name, fields) as cursor: # process an update cursor on the layer for row in cursor: # iterate through all records if row[1] == user_name: # only update a record if it matches the user_name of the the user that clicked the map row[0] = state_plane_PtGeom # create the point shape using the point geomtery with preferred spatial reference row[2] = longitude # update the longitude row[3] = latitude # update the latitude row[4] = x_stateplane # update the x coordinate in the preferred spatial reference row[5] = y_stateplane # update the y coordinate in the preferred spatial reference row[6] = dtnow # update the coordinatte date to the datetime of the map click cursor.updateRow(row) # post the update to the row count += 1 # increment the counter to so that another record will not be inserted if count == 0: # determine if no feature with the user name exists cursor = arcpy.da.InsertCursor(coordinate_lyr.name, fields) # if none exists create an insert cursor cursor.insertRow((state_plane_PtGeom, user_name, longitude, latitude, x_stateplane, y_stateplane, dtnow)) # insert a new feature for the user with the coordinate and date time of the click del cursor # delete the insert cursor arcpy.RefreshActiveView() # refresh the active dataframe to show the point located at the position the user just clicked except Exception as e: # catch any errors generated by the tool pythonaddins.MessageBox("An Error Occurred Attempting to Capture a Coordinate\n" + str(e), "Error Capturing Coordinate") # Display a message showing that an error occurred executing the tool ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ ... View more