Capturing Map Coordinates Simultaneously In Both Microsoft Access and ArcMap
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