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"
def onMouseDownMap(self, x, y, button, shift):
if button != 1 or shift != 0:
pass
else:
mxd = arcpy.mapping.MapDocument("CURRENT")
df = arcpy.mapping.ListDataFrames(mxd)[0]
sr = df.spatialReference
if sr.factoryCode != 2230 and sr.factoryCode != 4326:
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")
else:
try:
user_name = getpass.getuser()
out_path = "C:\Users\{}\Documents\ArcGIS\Default.gdb".format(user_name)
out_name = "GIS_COORDINATE"
coordinate_fc = out_path + "\\" + out_name
geometry_type = "POINT"
template = ""
has_m = "DISABLED"
has_z = "DISABLED"
spatial_ref = arcpy.SpatialReference(2230)
if not arcpy.Exists(coordinate_fc):
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
for lyr in arcpy.mapping.ListLayers(mxd, "", df):
if lyr.dataSource.lower() == coordinate_fc.lower():
coordinate_lyr = lyr
coordinate_lyr.visible = True
if coordinate_lyr == None:
arcpy.MakeFeatureLayer_management(coordinate_fc, out_name)
for lyr in arcpy.mapping.ListLayers(mxd, "", df):
if lyr.dataSource.lower() == coordinate_fc.lower():
coordinate_lyr = lyr
coordinate_lyr.visible = True
x_stateplane, y_stateplane, longitude, latitude, state_plane_PtGeom, wgs_1984_PtGeom = 0, 0, 0, 0, None, None
point = arcpy.Point()
point.X, point.Y = x, y
pointGeom = arcpy.PointGeometry(point, sr)
if sr.factoryCode == 2230:
state_plane_PtGeom = pointGeom
x_stateplane, y_stateplane = x, y
srOut = arcpy.SpatialReference(4326)
wgs_1984_PtGeom = pointGeom.projectAs(srOut)
longitude, latitude = wgs_1984_PtGeom.centroid.X, wgs_1984_PtGeom.centroid.Y
elif sr.factoryCode == 4326:
wgs_1984_PtGeom = pointGeom
longitude, latitude = x, y
srOut = arcpy.SpatialReference(2230)
state_plane_PtGeom = pointGeom.projectAs(srOut)
x_stateplane, y_stateplane = state_plane_PtGeom.centroid.X, state_plane_PtGeom.centroid.Y
dtnow = datetime.now()
conn_str = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=\\agency\agencydfs\Annex\Files\TRAFFIC\TRAFFICDB\TRAFFICDB\TRAFFICDB.accdb;'
)
cnxn = pyodbc.connect(conn_str)
crsr = cnxn.cursor()
crsr.execute("SELECT USER_NAME FROM tblGIS_COORDINATE WHERE USER_NAME = ?", user_name)
row = crsr.fetchone()
if row:
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:
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()
cnxn.close()
arcpy.SelectLayerByAttribute_management(coordinate_lyr.name, "CLEAR_SELECTION")
fields = ['SHAPE@', 'USER_NAME', 'LONGITUDE', 'LATITUDE', 'X_COORDINATE', 'Y_COORDINATE', 'COORDINATE_DATE']
count = 0
with arcpy.da.UpdateCursor(coordinate_lyr.name, fields) as cursor:
for row in cursor:
if row[1] == user_name:
row[0] = state_plane_PtGeom
row[2] = longitude
row[3] = latitude
row[4] = x_stateplane
row[5] = y_stateplane
row[6] = dtnow
cursor.updateRow(row)
count += 1
if count == 0:
cursor = arcpy.da.InsertCursor(coordinate_lyr.name, fields)
cursor.insertRow((state_plane_PtGeom, user_name, longitude, latitude, x_stateplane, y_stateplane, dtnow))
del cursor
arcpy.RefreshActiveView()
except Exception as e:
pythonaddins.MessageBox("An Error Occurred Attempting to Capture a Coordinate\n" + str(e), "Error Capturing Coordinate")