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-xyz.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.
<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> pythonaddins
<SPAN class="keyword token">import</SPAN> getpass
<SPAN class="keyword token">from</SPAN> datetime <SPAN class="keyword token">import</SPAN> datetime
<SPAN class="keyword token">import</SPAN> pyodbc
<SPAN class="keyword token">class</SPAN> <SPAN class="token class-name">CaptureCoordinateTool</SPAN><SPAN class="punctuation token">(</SPAN>object<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
<SPAN class="string token">"""Implementation for Capture_Coordinate2_addin.tool (Tool)"""</SPAN>
<SPAN class="keyword token">def</SPAN> <SPAN class="token function">__init__</SPAN><SPAN class="punctuation token">(</SPAN>self<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
self<SPAN class="punctuation token">.</SPAN>enabled <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN>
self<SPAN class="punctuation token">.</SPAN>shape <SPAN class="operator token">=</SPAN> <SPAN class="string token">"NONE"</SPAN> <SPAN class="comment token"># Can set to "Line", "Circle" or "Rectangle" for interactive shape drawing and to activate the onLine/Polygon/Circle event sinks.</SPAN>
<SPAN class="keyword token">def</SPAN> <SPAN class="token function">onMouseDownMap</SPAN><SPAN class="punctuation token">(</SPAN>self<SPAN class="punctuation token">,</SPAN> x<SPAN class="punctuation token">,</SPAN> y<SPAN class="punctuation token">,</SPAN> button<SPAN class="punctuation token">,</SPAN> shift<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
<SPAN class="comment token"># Determine if the button and shift states are correct for activating the tool</SPAN>
<SPAN class="keyword token">if</SPAN> button <SPAN class="operator token">!=</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="operator token">or</SPAN> shift <SPAN class="operator token">!=</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># The left mouse button was not pressed or the Alt, Ctrl and/or Shift keys were pressed</SPAN>
<SPAN class="keyword token">pass</SPAN> <SPAN class="comment token"># Do default ArcMap behavior and exit</SPAN>
<SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># The left mouse button was pressed and the Alt, Ctrl and Shift keys were not pressed</SPAN>
<SPAN class="comment token"># Get information about the settings of the map clicked</SPAN>
mxd <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>mapping<SPAN class="punctuation token">.</SPAN>MapDocument<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"CURRENT"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># get the current map where the mouse was pressed</SPAN>
df <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>mapping<SPAN class="punctuation token">.</SPAN>ListDataFrames<SPAN class="punctuation token">(</SPAN>mxd<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># Assume the first data frame in the map was clicked</SPAN>
sr <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">.</SPAN>spatialReference <SPAN class="comment token"># get the spatial reference of the data frame</SPAN>
<SPAN class="keyword token">if</SPAN> sr<SPAN class="punctuation token">.</SPAN>factoryCode <SPAN class="operator token">!=</SPAN> <SPAN class="number token">2230</SPAN> <SPAN class="operator token">and</SPAN> sr<SPAN class="punctuation token">.</SPAN>factoryCode <SPAN class="operator token">!=</SPAN> <SPAN class="number token">4326</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># Spatial Reference is invalid if it is not 2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet) or 4326 (GCS_WGS_1984)</SPAN>
message <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Invalid Spatial Reference "</SPAN> <SPAN class="operator token">+</SPAN> str<SPAN class="punctuation token">(</SPAN>sr<SPAN class="punctuation token">.</SPAN>factoryCode<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">" - "</SPAN> <SPAN class="operator token">+</SPAN> sr<SPAN class="punctuation token">.</SPAN>name <SPAN class="operator token">+</SPAN> <SPAN class="string token">" detected.\nPlease set the Spatial Reference to\n2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet)\nor 4326 (GSC_WGS_1984)"</SPAN>
pythonaddins<SPAN class="punctuation token">.</SPAN>MessageBox<SPAN class="punctuation token">(</SPAN>message<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Map Spatial Reference Is Invalid"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Display a message alerting user that the map has an invalid spatial reference and exit</SPAN>
<SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># Spatial Reference is either 2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet) or 4326 (GCS_WGS_1984) and therefore valid</SPAN>
<SPAN class="keyword token">try</SPAN><SPAN class="punctuation token">:</SPAN>
<SPAN class="comment token"># Get the GIS_COORDINATE layer and if necessary Create the feature class or add the layer to the map</SPAN>
user_name <SPAN class="operator token">=</SPAN> getpass<SPAN class="punctuation token">.</SPAN>getuser<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># get the login name of the user</SPAN>
out_path <SPAN class="operator token">=</SPAN> <SPAN class="string token">"C:\Users\{}\Documents\ArcGIS\Default.gdb"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>user_name<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># ArcMap Default geodartbase</SPAN>
out_name <SPAN class="operator token">=</SPAN> <SPAN class="string token">"GIS_COORDINATE"</SPAN> <SPAN class="comment token"># FC name</SPAN>
coordinate_fc <SPAN class="operator token">=</SPAN> out_path <SPAN class="operator token">+</SPAN> <SPAN class="string token">"\\"</SPAN> <SPAN class="operator token">+</SPAN> out_name <SPAN class="comment token"># Full path and name of FC</SPAN>
geometry_type <SPAN class="operator token">=</SPAN> <SPAN class="string token">"POINT"</SPAN> <SPAN class="comment token"># Features will be points</SPAN>
template <SPAN class="operator token">=</SPAN> <SPAN class="string token">""</SPAN>
has_m <SPAN class="operator token">=</SPAN> <SPAN class="string token">"DISABLED"</SPAN>
has_z <SPAN class="operator token">=</SPAN> <SPAN class="string token">"DISABLED"</SPAN>
spatial_ref <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>SpatialReference<SPAN class="punctuation token">(</SPAN><SPAN class="number token">2230</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Set Spatial Reference to 2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet) preferred by Riverside County</SPAN>
<SPAN class="keyword token">if</SPAN> <SPAN class="operator token">not</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>Exists<SPAN class="punctuation token">(</SPAN>coordinate_fc<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
<SPAN class="comment token"># Create an FC for displaying the clicked point in the preferred spatial reference and add fields to match the Access table schema</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>CreateFeatureclass_management<SPAN class="punctuation token">(</SPAN>out_path<SPAN class="punctuation token">,</SPAN> out_name<SPAN class="punctuation token">,</SPAN> geometry_type<SPAN class="punctuation token">,</SPAN> template<SPAN class="punctuation token">,</SPAN> has_m<SPAN class="punctuation token">,</SPAN> has_z<SPAN class="punctuation token">,</SPAN> spatial_ref<SPAN class="punctuation token">)</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>AddField_management<SPAN class="punctuation token">(</SPAN>coordinate_fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"USER_NAME"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"TEXT"</SPAN><SPAN class="punctuation token">,</SPAN> field_length<SPAN class="operator token">=</SPAN><SPAN class="number token">50</SPAN><SPAN class="punctuation token">)</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>AddField_management<SPAN class="punctuation token">(</SPAN>coordinate_fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"LONGITUDE"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"DOUBLE"</SPAN><SPAN class="punctuation token">)</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>AddField_management<SPAN class="punctuation token">(</SPAN>coordinate_fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"LATITUDE"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"DOUBLE"</SPAN><SPAN class="punctuation token">)</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>AddField_management<SPAN class="punctuation token">(</SPAN>coordinate_fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"X_COORDINATE"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"DOUBLE"</SPAN><SPAN class="punctuation token">)</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>AddField_management<SPAN class="punctuation token">(</SPAN>coordinate_fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Y_COORDINATE"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"DOUBLE"</SPAN><SPAN class="punctuation token">)</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>AddField_management<SPAN class="punctuation token">(</SPAN>coordinate_fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"COORDINATE_DATE"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"DATE"</SPAN><SPAN class="punctuation token">)</SPAN>
coordinate_lyr <SPAN class="operator token">=</SPAN> None <SPAN class="comment token"># Create an unassigned variable for a layer that will display the coordinate</SPAN>
<SPAN class="keyword token">for</SPAN> lyr <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>mapping<SPAN class="punctuation token">.</SPAN>ListLayers<SPAN class="punctuation token">(</SPAN>mxd<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">""</SPAN><SPAN class="punctuation token">,</SPAN> df<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># Check all existing layers in the dataframe clicked</SPAN>
<SPAN class="keyword token">if</SPAN> lyr<SPAN class="punctuation token">.</SPAN>dataSource<SPAN class="punctuation token">.</SPAN>lower<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">==</SPAN> coordinate_fc<SPAN class="punctuation token">.</SPAN>lower<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># Check if any layer has the coordinate fc as its datasource</SPAN>
coordinate_lyr <SPAN class="operator token">=</SPAN> lyr <SPAN class="comment token"># set the coordinate layer variable to the map layer that has the coordinate feature class</SPAN>
coordinate_lyr<SPAN class="punctuation token">.</SPAN>visible <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN> <SPAN class="comment token"># make sure the layer is visible</SPAN>
<SPAN class="keyword token">if</SPAN> coordinate_lyr <SPAN class="operator token">==</SPAN> None<SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># Check if no layer was found in the map</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>MakeFeatureLayer_management<SPAN class="punctuation token">(</SPAN>coordinate_fc<SPAN class="punctuation token">,</SPAN> out_name<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Make a layer from the map, which should automatically be added to the map</SPAN>
<SPAN class="keyword token">for</SPAN> lyr <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>mapping<SPAN class="punctuation token">.</SPAN>ListLayers<SPAN class="punctuation token">(</SPAN>mxd<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">""</SPAN><SPAN class="punctuation token">,</SPAN> df<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># Recheck all existing layers in the dataframe clicked</SPAN>
<SPAN class="keyword token">if</SPAN> lyr<SPAN class="punctuation token">.</SPAN>dataSource<SPAN class="punctuation token">.</SPAN>lower<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">==</SPAN> coordinate_fc<SPAN class="punctuation token">.</SPAN>lower<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># Find the created layer that has the coordinate fc as its datasource</SPAN>
coordinate_lyr <SPAN class="operator token">=</SPAN> lyr <SPAN class="comment token"># set the coordinate layer variable to the map layer that has the coordinate feature class</SPAN>
coordinate_lyr<SPAN class="punctuation token">.</SPAN>visible <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN> <SPAN class="comment token"># make sure the layer is visible</SPAN>
<SPAN class="comment token"># Capture date for the point geometries and coordinates of both spatial references, the user name and the date time when the data was captured</SPAN>
x_stateplane<SPAN class="punctuation token">,</SPAN> y_stateplane<SPAN class="punctuation token">,</SPAN> longitude<SPAN class="punctuation token">,</SPAN> latitude<SPAN class="punctuation token">,</SPAN> state_plane_PtGeom<SPAN class="punctuation token">,</SPAN> wgs_1984_PtGeom <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">,</SPAN> None<SPAN class="punctuation token">,</SPAN> None <SPAN class="comment token"># Initialize variables for state plain and wgs 1984 coordinates and points</SPAN>
point <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>Point<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Create a point object</SPAN>
point<SPAN class="punctuation token">.</SPAN>X<SPAN class="punctuation token">,</SPAN> point<SPAN class="punctuation token">.</SPAN>Y <SPAN class="operator token">=</SPAN> x<SPAN class="punctuation token">,</SPAN> y <SPAN class="comment token"># Set the x, y of the point object to the coordinates clicked</SPAN>
pointGeom <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>PointGeometry<SPAN class="punctuation token">(</SPAN>point<SPAN class="punctuation token">,</SPAN> sr<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># create a PointGeometry based on the point coordinates and spatial reference of the map click</SPAN>
<SPAN class="keyword token">if</SPAN> sr<SPAN class="punctuation token">.</SPAN>factoryCode <SPAN class="operator token">==</SPAN> <SPAN class="number token">2230</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># Spatial Reference is 2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet)</SPAN>
state_plane_PtGeom <SPAN class="operator token">=</SPAN> pointGeom <SPAN class="comment token"># set state_plane_PtGeom to pointGeom clicked</SPAN>
x_stateplane<SPAN class="punctuation token">,</SPAN> y_stateplane <SPAN class="operator token">=</SPAN> x<SPAN class="punctuation token">,</SPAN> y <SPAN class="comment token"># x_stateplane and y_stateplane to x and y clicked</SPAN>
srOut <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>SpatialReference<SPAN class="punctuation token">(</SPAN><SPAN class="number token">4326</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Create a 4326 (GCS_WGS_1984) Spatial Reference</SPAN>
wgs_1984_PtGeom <SPAN class="operator token">=</SPAN> pointGeom<SPAN class="punctuation token">.</SPAN>projectAs<SPAN class="punctuation token">(</SPAN>srOut<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Project the point clicked to 4326 (GCS_WGS_1984) spatial reference</SPAN>
longitude<SPAN class="punctuation token">,</SPAN> latitude <SPAN class="operator token">=</SPAN> wgs_1984_PtGeom<SPAN class="punctuation token">.</SPAN>centroid<SPAN class="punctuation token">.</SPAN>X<SPAN class="punctuation token">,</SPAN> wgs_1984_PtGeom<SPAN class="punctuation token">.</SPAN>centroid<SPAN class="punctuation token">.</SPAN>Y <SPAN class="comment token"># set longitude and latitude to projected X and Y</SPAN>
<SPAN class="keyword token">elif</SPAN> sr<SPAN class="punctuation token">.</SPAN>factoryCode <SPAN class="operator token">==</SPAN> <SPAN class="number token">4326</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># Spatial Reference is 4326 (GCS_WGS_1984) </SPAN>
wgs_1984_PtGeom <SPAN class="operator token">=</SPAN> pointGeom <SPAN class="comment token"># set wgs_1984_PtGeom to pointGeom clicked</SPAN>
longitude<SPAN class="punctuation token">,</SPAN> latitude <SPAN class="operator token">=</SPAN> x<SPAN class="punctuation token">,</SPAN> y <SPAN class="comment token"># set longitude to latitude to x and y clicked</SPAN>
srOut <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>SpatialReference<SPAN class="punctuation token">(</SPAN><SPAN class="number token">2230</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Create a 2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet) Spatial Reference</SPAN>
state_plane_PtGeom <SPAN class="operator token">=</SPAN> pointGeom<SPAN class="punctuation token">.</SPAN>projectAs<SPAN class="punctuation token">(</SPAN>srOut<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Project the point clicked to 2230 (NAD_1983_StatePlane_California_VI_FIPS_0406_Feet) spatial reference</SPAN>
x_stateplane<SPAN class="punctuation token">,</SPAN> y_stateplane <SPAN class="operator token">=</SPAN> state_plane_PtGeom<SPAN class="punctuation token">.</SPAN>centroid<SPAN class="punctuation token">.</SPAN>X<SPAN class="punctuation token">,</SPAN> state_plane_PtGeom<SPAN class="punctuation token">.</SPAN>centroid<SPAN class="punctuation token">.</SPAN>Y <SPAN class="comment token"># set x_stateplane and y_stateplane to projected X and Y</SPAN>
dtnow <SPAN class="operator token">=</SPAN> datetime<SPAN class="punctuation token">.</SPAN>now<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Capture the current datetime</SPAN>
<SPAN class="comment token"># Cutput the captured data</SPAN>
conn_str <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN>
r<SPAN class="string token">'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'</SPAN>
r<SPAN class="string token">'DBQ=\\agency\agencydfs\Annex\Files\TRAFFIC\TRAFFICDB\TRAFFICDB\TRAFFICDB.accdb;'</SPAN>
<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># set up a connection string for connecting to an Access accdb database</SPAN>
cnxn <SPAN class="operator token">=</SPAN> pyodbc<SPAN class="punctuation token">.</SPAN>connect<SPAN class="punctuation token">(</SPAN>conn_str<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># connect to the Access accdb database</SPAN>
crsr <SPAN class="operator token">=</SPAN> cnxn<SPAN class="punctuation token">.</SPAN>cursor<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># create a cursor from the connection</SPAN>
crsr<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"SELECT USER_NAME FROM tblGIS_COORDINATE WHERE USER_NAME = ?"</SPAN><SPAN class="punctuation token">,</SPAN> user_name<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Execute a query to find any records that match the user_name that clicked the point</SPAN>
row <SPAN class="operator token">=</SPAN> crsr<SPAN class="punctuation token">.</SPAN>fetchone<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># fetch the first record (there should only be none or one record)</SPAN>
<SPAN class="keyword token">if</SPAN> row<SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># If a row was found update the record with the coordinates and date time of the user click</SPAN>
crsr<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"UPDATE tblGIS_COORDINATE SET LONGITUDE = ?, LATITUDE = ?, X_COORDINATE = ?, Y_COORDINATE = ?, COORDINATE_DATE = ? WHERE USER_NAME = ?"</SPAN><SPAN class="punctuation token">,</SPAN> longitude<SPAN class="punctuation token">,</SPAN> latitude<SPAN class="punctuation token">,</SPAN> x_stateplane<SPAN class="punctuation token">,</SPAN> y_stateplane<SPAN class="punctuation token">,</SPAN> dtnow<SPAN class="punctuation token">,</SPAN> user_name<SPAN class="punctuation token">)</SPAN>
cnxn<SPAN class="punctuation token">.</SPAN>commit<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># If no row was found insert a record for the current user_name with the coordinates and date time of the user click</SPAN>
crsr<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"INSERT INTO tblGIS_COORDINATE(USER_NAME, LONGITUDE, LATITUDE, X_COORDINATE, Y_COORDINATE, COORDINATE_DATE) values (?, ?, ?, ?, ?, ?)"</SPAN><SPAN class="punctuation token">,</SPAN> user_name<SPAN class="punctuation token">,</SPAN> longitude<SPAN class="punctuation token">,</SPAN> latitude<SPAN class="punctuation token">,</SPAN> x_stateplane<SPAN class="punctuation token">,</SPAN> y_stateplane<SPAN class="punctuation token">,</SPAN> dtnow<SPAN class="punctuation token">)</SPAN>
cnxn<SPAN class="punctuation token">.</SPAN>commit<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
crsr<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># close the accdb cursor</SPAN>
cnxn<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># close the accdb connection</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>SelectLayerByAttribute_management<SPAN class="punctuation token">(</SPAN>coordinate_lyr<SPAN class="punctuation token">.</SPAN>name<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"CLEAR_SELECTION"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Make sure no records are selected in the layer displaying the clicked coordinate</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'SHAPE@'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'USER_NAME'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'LONGITUDE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'LATITUDE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'X_COORDINATE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Y_COORDINATE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'COORDINATE_DATE'</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># create a list of fields for the layer to be updated or inserted</SPAN>
count <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN> <SPAN class="comment token"># Create a counter to determine if a record exists already or needs to be inserted</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>coordinate_lyr<SPAN class="punctuation token">.</SPAN>name<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># process an update cursor on the layer</SPAN>
<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># iterate through all records</SPAN>
<SPAN class="keyword token">if</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">==</SPAN> user_name<SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># only update a record if it matches the user_name of the the user that clicked the map</SPAN>
row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> state_plane_PtGeom <SPAN class="comment token"># create the point shape using the point geomtery with preferred spatial reference</SPAN>
row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> longitude <SPAN class="comment token"># update the longitude</SPAN>
row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> latitude <SPAN class="comment token"># update the latitude</SPAN>
row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">4</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> x_stateplane <SPAN class="comment token"># update the x coordinate in the preferred spatial reference</SPAN>
row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">5</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> y_stateplane <SPAN class="comment token"># update the y coordinate in the preferred spatial reference</SPAN>
row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">6</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> dtnow <SPAN class="comment token"># update the coordinatte date to the datetime of the map click</SPAN>
cursor<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># post the update to the row</SPAN>
count <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="comment token"># increment the counter to so that another record will not be inserted</SPAN>
<SPAN class="keyword token">if</SPAN> count <SPAN class="operator token">==</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># determine if no feature with the user name exists</SPAN>
cursor <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>InsertCursor<SPAN class="punctuation token">(</SPAN>coordinate_lyr<SPAN class="punctuation token">.</SPAN>name<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># if none exists create an insert cursor</SPAN>
cursor<SPAN class="punctuation token">.</SPAN>insertRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">(</SPAN>state_plane_PtGeom<SPAN class="punctuation token">,</SPAN> user_name<SPAN class="punctuation token">,</SPAN> longitude<SPAN class="punctuation token">,</SPAN> latitude<SPAN class="punctuation token">,</SPAN> x_stateplane<SPAN class="punctuation token">,</SPAN> y_stateplane<SPAN class="punctuation token">,</SPAN> dtnow<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># insert a new feature for the user with the coordinate and date time of the click</SPAN>
<SPAN class="keyword token">del</SPAN> cursor <SPAN class="comment token"># delete the insert cursor</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>RefreshActiveView<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># refresh the active dataframe to show the point located at the position the user just clicked</SPAN>
<SPAN class="keyword token">except</SPAN> Exception <SPAN class="keyword token">as</SPAN> e<SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># catch any errors generated by the tool</SPAN>
pythonaddins<SPAN class="punctuation token">.</SPAN>MessageBox<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"An Error Occurred Attempting to Capture a Coordinate\n"</SPAN> <SPAN class="operator token">+</SPAN> str<SPAN class="punctuation token">(</SPAN>e<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Error Capturing Coordinate"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Display a message showing that an error occurred executing the tool</SPAN>
<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
