rfairhur24

Capturing Map Coordinates Simultaneously In Both Microsoft Access and ArcMap

Blog Post created by rfairhur24 on Jan 9, 2020

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:

  1. pyodbc list here is a list of whl files. Download the right one for you.
  2. Open cmd as administrator and go to the directory containing the file.
  3. 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

Outcomes