Update Features With Incident Records (Crime Analysis and Safety)

Summary

Updates an existing table or converts a nonspatial table to point features based on x,y-coordinates or street addresses and updates an existing dataset with the new or updated record information from the table.

Usage

• A point feature class, point feature layer, or table must exist where records from the input table will be appended or updated.

• You can use tabular data exported from a computer-aided dispatch system or records management system in this tool.

• This tool supports the following table formats as input:

  • Geodatabase
  • dBASE (.dbf)
  • Microsoft Excel worksheets (.xls and .xlsx)
  • Memory-based tables
  • Delimited files
    • Comma-delimited files (.csv, .txt, and .asc)
    • Tab-delimited files (.tsv and .tab)
    • Pipe-delimited files (.psv)

  For delimited files, the first row of the input file is used as the field names on the output table. Field names cannot contain spaces or special characters (such as $ or *), and an error will occur if the first row of the input file contains spaces or special characters.

• Performing geocoding operations using the ArcGIS World Geocoding Service requires an ArcGIS Online organizational account and consumes credits. The organizational account must have enough credits to complete the entire geocoding request.

• When the optional Invalid Records Table parameter value is created, the table contains records that were not processed as new or updated features. The complete input table record will be added to the Invalid Records Table parameter value as well as an INV_REASON field. The INV_REASON field will include a code identifying the reason the record could not be processed as new or updated features.

  The INV_REASON field is added to the Invalid Records Table value with the following codes:

  • NULL_ADDR—All address fields in the input table record contain null values.
  • NULL_ID—The input ID fields of the input table record contain null values.
  • NULL_XY—The input table's x- and y-coordinate fields contain a null value.
  • BAD_DATE—The input date of the input table record is null or could not be converted to a valid date.
  • BAD_ADDR—The address candidate from the input table record is unmatched.
  • BAD_FM—The field mapping of the input table record was unsuccessful because a value could not be written to the output field. For example, text values cannot be added to numeric fields, and text values cannot be added to text fields when the values are longer than the field length. When this code occurs, the INV_FLD field is added to the Invalid Records Table value and lists the fields from the Input Table value that contain values that could not be written to the Target Features value.
  • DUP_ID—The input table record has a duplicate ID and has not been updated to the target features because it is older than the existing record or a newer record exists. This is determined by the last modified date. If the last modified date is not used, it is determined by the highest OBJECTID value.

  When the Invalid Records Table parameter value is not provided, an invalid_records_<timestamp>.fid file is created in your scratch workspace environment. This file will contain the OBJECTID value of each Input Table record that could not be added or updated in the Target Features parameter.

• When both the Update Existing Target Features and Update Geometry for Existing Features parameters are checked, location fields in the Input Table parameter value are compared to the location fields in the Target Features parameter value to determine whether the geometry will be updated in the Target Feature parameter value. When Location Type is set to Addresses, the Address Fields values are compared. If a difference is detected between the Input Table record and the Target Features parameter values, the Target Features geometry is updated with the geocoded position based on the address fields in the Input Table record. The Input Table record will not be geocoded if no difference is detected.

• When the layer specified in the Target Features parameter value is part of a feature service, all values in date fields in the Input Table parameter value will be converted to coordinated universal time prior to being appended or updated. Date values in the Input Table parameter value are assumed to be in the same time zone as the machine that is running the tool.

Parameters

Dialog

Label	Explanation	Data Type
Input Table	The nonspatial table or table containing the x- and y-coordinates or addresses that define the locations of the records.	Table View
Target Features	The point feature class, point feature layer, or table that will be updated.	Feature Layer; Table View
Location Type (Optional)	Specifies whether features will be created using x,y-coordinates or addresses. This parameter is only active when the Target Features parameter value is a feature class or layer. Coordinates—Features will be created using the x,y-coordinates of the input record. Addresses—Features will be created using the address of the input record using a locator.	String
X Field (Optional)	The field in the input table that contains the x-coordinates (or longitude). This parameter is only active when the Location Type parameter is set to Coordinates and the Target Features parameter value is a feature class or layer.	Field
Y Field (Optional)	The field in the input table that contains the y-coordinates (or latitude). This parameter is only active when the Location Type parameter is set to Coordinates and the Target Features parameter value is a feature class or layer.	Field
Coordinate System (Optional)	The coordinate system of the x- and y-coordinates. This parameter is only active when the Location Type parameter is set to Coordinates and the Target Features parameter value is a feature class or layer.	Coordinate System
Address Locator (Optional)	The address locator that will be used to geocode the table of addresses. When this parameter is set to use ArcGIS World Geocoding Service, this operation may consume credits. Note: When using a local address locator, adding the .loc extension after the locator name at the end of the locator path is optional. This parameter is only active when the Location Type parameter is set to Addresses and the Target Features parameter value is a feature class or layer.	Address Locator
Address Type (Optional)	Specifies how address fields used by the address locator will be mapped to fields in the input table of addresses. Select Single Field if the complete address is stored in one field in the input table, for example, 303 Peachtree St NE, Atlanta, GA 30308. Select Multiple Fields if the input addresses are divided into multiple fields such as Address, City, State, and ZIP for a general United States address. This parameter is only active when the Location Type parameter is set to Addresses and the Target Features parameter value is a feature class or layer. Multiple Fields—Addresses will be divided into multiple fields. Single Field—Addresses will be contained in one field.	String
Address Fields (Optional)	The input table fields that correspond to the locator address fields of the address locator. Some locators support multiple input address fields, such as Address, Address2, and Address3. In this case, the address component can be separated into multiple fields, and the address fields will be concatenated at the time of geocoding. For example, 100, Main st, and Apt 140 across three fields or 100 Main st and Apt 140 across two fields, both become 100 Main st Apt 140 when geocoding. If you do not map an optional input address field used by the address locator to a field in the input table of addresses, specify that there is no mapping by leaving the field name blank. This parameter is only active when the Location Type parameter is set to Addresses.	Value Table
Invalid Records Table (Optional)	The output table containing a list of invalid records and associated invalidation codes.	Table
Expression (Optional)	The SQL expression that will be used to select a subset of the input datasets' records. If multiple input datasets are specified, they will all be evaluated using the expression. If no records match the expression for an input dataset, no records from that dataset will be appended to the target. For more information about SQL syntax, see SQL reference for query expressions used in ArcGIS.	SQL Expression
Update Existing Target Features (Optional)	Specifies whether existing records will be updated in the Target Features parameter value. Checked—Records from the Input Table parameter value will be updated in the Target Features parameter if they exist there. Unchecked—Records from the Input Table parameter value will be appended to the Target Features parameter. This is the default.	Boolean
Match Fields (Optional)	The ID field or fields that will be used to determine matches between the Input Table values and the Target Features values. This parameter is only active when the Update Existing Target Features parameter is checked.	Value Table
Input Table Last Modified Date Field (Optional)	The field containing the last modified date of the Input Features records. Date and string field types are supported. This parameter is only active when the Update Existing Target Features parameter is checked.	Field
Target Features Last Modified Date Field (Optional)	The field containing the last modified date of the Target Features records. This field must be a date field type. This parameter is only active when the Update Existing Target Features parameter is checked.	Field
Update Only Matching Features (Optional)	Specifies whether only existing records will be updated or existing records will be updated and new records will be added. Checked—Only existing records will be updated. Unchecked—Existing records will be updated and new records will be added. This is default. This parameter is only active when the Update Existing Target Features parameter is checked.	Boolean
Update Geometry for Existing Features (Optional)	Specifies whether the geometry of existing features will be updated. Checked—The geometry of existing records will be updated when the geometry information from the Input Table parameter value is different than the geometry of the Target Features parameter value. This is the default. Unchecked—The geometry of existing records will not be updated. This parameter is only active when the Update Existing Target Features parameter is checked and the Target Features parameter value is a feature class or layer.	Boolean
Field Matching Type (Optional)	Specifies whether the fields of the input table must match the fields of the target features for data to be appended. Input fields must match target fields—Fields from the input dataset match the fields of the target dataset. Fields that do not match will be ignored. This is the default Use the field map to reconcile field differences—Fields from the input dataset do not need to match the fields of the target dataset. Fields from the input dataset that do not match the fields of the target dataset will not be mapped to the target dataset unless the mapping is explicitly set in the Field Map parameter.	String
Field Map (Optional)	Controls how the attribute fields from the input table will be transferred or mapped to the target features. This parameter is only active if the Field Matching Type parameter is set to Use the field map to reconcile field differences. Because the input table values are appended to an existing target feature that has predefined fields, you cannot add, remove, or change the type of the fields in the field map. You can, however, set merge rules for each output field. Merge rules allow you to specify how values from two or more input fields will be merged or combined into a single output value. The following merge rules can be used to determine how the output field will be populated with values: First—Use the input fields' first value. Last—Use the input fields' last value. Join—Concatenate (join) the input field values. Sum—Calculate the total of the input field values. Mean—Calculate the mean (average) of the input field values. Median—Calculate the median (middle) of the input field values. Mode—Use the value with the highest frequency. Min—Use the minimum value of all the input field values. Max—Use the maximum value of all the input field values. Standard deviation—Use the standard deviation classification method on all the input field values. Count—Find the number of records included in the calculation.	Field Mappings
Time Format	The format of the input field containing the time values. The type can be short, long, float, double, text, or date. You can either choose a standard time format from the drop-down list or provide a custom format. Note: The format strings are case sensitive. If the data type of the time field is date, no time format is required. If the data type of the time field is numeric (short, long, float, or double), a list of standard numeric time formats is provided in the drop-down list. If the data type of the time field is string, a list of standard string time formats is provided in the drop-down list. For string fields, you can also specify a custom time format. For example, the time values may have been stored in a string field in one of the standard formats such as yyyy/MM/dd HH:mm:ss or in a custom format such as dd/MM/yyyy HH:mm:ss. For the custom format, you can also specify the a.m. or p.m. designator. Some commonly used formats are listed below: yyyy—Year represented by four digits MM—Month as digits with leading zero for single-digit months MMM—Month as a three-letter abbreviation dd—Day of month as digits with leading zero for single-digit days ddd—Day of week as a three-letter abbreviation hh—Hours with leading zero for single-digit hours; 12-hour clock HH—Hours with leading zero for single-digit hours; 24-hour clock mm—Minutes with leading zero for single-digit minutes ss—Seconds with leading zero for single-digit seconds t—One character time marker string, such as A or P tt—Multicharacter time marker string, such as AM or PM unix_us—Unix time in microseconds unix_ms—Unix time in milliseconds unix_s—Unix time in seconds unix_hex—Unix time in hexadecimal Learn more about custom dates and time formats This parameter is only active when the Input Table Last Modified Date parameter value is a text field and the Target Features Last Modified Date Field parameter value is a date field, or the Field Map parameter input value is a text field and the output value is a date field.	String

Derived Output

Label	Explanation	Data Type
Updated Target Features	The updated target dataset.	Feature Layer; Table View

Python

arcpy.ca.UpdateFeaturesWithIncidentRecords(in_table, target_features, {location_type}, {x_field}, {y_field}, {coordinate_system}, {address_locator}, {address_type}, {address_fields}, {invalid_records_table}, {where_clause}, {update_target}, {match_fields}, {in_date_field}, {target_date_field}, {update_matching}, {update_geometry}, {field_matching_type}, {field_mapping}, time_format)

Name	Explanation	Data Type
in_table	The nonspatial table or table containing the x- and y-coordinates or addresses that define the locations of the records.	Table View
target_features	The point feature class, point feature layer, or table that will be updated.	Feature Layer; Table View
location_type (Optional)	Specifies whether features will be created using x,y-coordinates or addresses. COORDINATES—Features will be created using the x,y-coordinates of the input record. ADDRESSES—Features will be created using the address of the input record using a locator. This parameter is only enabled when the target_features parameter value is a feature class or layer.	String
x_field (Optional)	The field in the input table that contains the x-coordinates (or longitude). This parameter is only enabled when the location_type parameter is set to COORDINATES and the target_features parameter value is a feature class or layer.	Field
y_field (Optional)	The field in the input table that contains the y-coordinates (or latitude). This parameter is only enabled when the location_type parameter is set to COORDINATES and the target_features parameter value is a feature class or layer.	Field
coordinate_system (Optional)	The coordinate system of the x- and y-coordinates. This parameter is only enabled when the location_type parameter is set to COORDINATES and the target_features parameter value is a feature class or layer.	Coordinate System
address_locator (Optional)	The address locator that will be used to geocode the table of addresses. When this parameter is set to use ArcGIS World Geocoding Service, this operation may consume credits. Note: When using a local address locator, adding the .loc extension after the locator name at the end of the locator path is optional. This parameter is only enabled when the location_type parameter is set to ADDRESSES and the target_features parameter value is a feature class or layer.	Address Locator
address_type (Optional)	Specifies how address fields used by the address locator will be mapped to fields in the input table of addresses. MULTI_FIELD_ADDRESS—Addresses will be divided into multiple fields. SINGLE_FIELD_ADDRESS—Addresses will be contained in one field. Select SINGLE_FIELD_ADDRESS if the complete address is stored in one field in the input table, for example, 303 Peachtree St NE, Atlanta, GA 30308. Select MULTI_FIELD_ADDRESS if the input addresses are divided into multiple fields such as Address, City, State, and ZIP for a general United States address. This parameter is only enabled when the location_type parameter is set to ADDRESSES and the target_features parameter value is a feature class or layer.	String
address_fields [address_fields,...] (Optional)	The input table fields that correspond to the locator address fields of the address locator. Some locators support multiple input address fields, such as Address, Address2, and Address3. In this case, the address component can be separated into multiple fields, and the address fields will be concatenated at the time of geocoding. For example, 100, Main st, and Apt 140 across three fields or 100 Main st and Apt 140 across two fields, both become 100 Main st Apt 140 when geocoding. If you do not map an optional input address field used by the address locator to a field in the input table of addresses, specify that there is no mapping by leaving the field name blank. This parameter is only enabled when the location_type parameter is set to ADDRESSES.	Value Table
invalid_records_table (Optional)	The output table containing a list of invalid records and associated invalidation codes.	Table
where_clause (Optional)	The SQL expression that will be used to select a subset of the input datasets' records. If multiple input datasets are specified, they will all be evaluated using the expression. If no records match the expression for an input dataset, no records from that dataset will be appended to the target. For more information about SQL syntax, see SQL reference for query expressions used in ArcGIS.	SQL Expression
update_target (Optional)	Specifies whether existing records will be updated in the target_features parameter value. UPDATE—Records from the in_table parameter value will be updated in the target_features parameter value if they exist there. APPEND—Records from the in_table parameter value will be appended to the target_features parameter value. This is the default.	Boolean
match_fields [match_fields,...] (Optional)	The ID field or fields that will be used to determine matches between the in_table values and the target_features values. This parameter is only enabled when the update_target parameter is set to UPDATE.	Value Table
in_date_field (Optional)	The field containing the last modified date of the in_table records. Date and string field types are supported. This parameter is only enabled when the update_target parameter is set to UPDATE.	Field
target_date_field (Optional)	The field containing the last modified date of the target_features records. This field must be a date field type. This parameter is only enabled when the update_target parameter is set to UPDATE.	Field
update_matching (Optional)	Specifies whether only existing records will be updated or existing records will be updated and new records will be added. UPDATE_MATCHING_ONLY—Only existing records will be updated. UPSERT—Existing records will be updated and new records will be added. This is the default. This parameter is only enabled when the update_target parameter is set to UPDATE.	Boolean
update_geometry (Optional)	Specifies whether the geometry of existing features will be updated. UPDATE_GEOMETRY—The geometry of existing records will be updated when the geometry information from the in_table parameter value is different than the geometry of the target_features parameter value. This is the default. KEEP_GEOMETRY—The geometry of existing records will not be updated. This parameter is only enabled when the update_target parameter is set to UPDATE and the target_features parameter value is a feature class or layer.	Boolean
field_matching_type (Optional)	Specifies whether the fields of the input table must match the fields of the target features for data to be appended. AUTOMATIC—Fields from the input dataset match the fields of the target dataset. Fields that do not match will be ignored. This is the default FIELD_MAP—Fields from the input dataset do not need to match the fields of the target dataset. Fields from the input dataset that do not match the fields of the target dataset will not be mapped to the target dataset unless the mapping is explicitly set in the Field Map parameter.	String
field_mapping (Optional)	Controls how the attribute fields from the input table will be transferred or mapped to the target features. This parameter is only enabled if the field_matching_type parameter is set to FIELD_MAP. Because the input table values are appended to an existing target feature that has predefined fields, you cannot add, remove, or change the type of the fields in the field map. You can, however, set merge rules for each output field. Merge rules allow you to specify how values from two or more input fields will be merged or combined into a single output value. The following merge rules can be used to determine how the output field will be populated with values: First—Use the input fields' first value. Last—Use the input fields' last value. Join—Concatenate (join) the input field values. Sum—Calculate the total of the input field values. Mean—Calculate the mean (average) of the input field values. Median—Calculate the median (middle) of the input field values. Mode—Use the value with the highest frequency. Min—Use the minimum value of all the input field values. Max—Use the maximum value of all the input field values. Standard deviation—Use the standard deviation classification method on all the input field values. Count—Find the number of records included in the calculation. In Python, you can use the FieldMappings class to define this parameter.	Field Mappings
time_format	The format of the input field containing the time values. The type can be short, long, float, double, text, or date. You can either choose a standard time format from the drop-down list or provide a custom format. Note: The format strings are case sensitive. If the data type of the time field is date, no time format is required. If the data type of the time field is numeric (short, long, float, or double), a list of standard numeric time formats is provided in the drop-down list. If the data type of the time field is string, a list of standard string time formats is provided in the drop-down list. For string fields, you can also specify a custom time format. For example, the time values may have been stored in a string field in one of the standard formats such as yyyy/MM/dd HH:mm:ss or in a custom format such as dd/MM/yyyy HH:mm:ss. For the custom format, you can also specify the a.m. or p.m. designator. Some commonly used formats are listed below: yyyy—Year represented by four digits MM—Month as digits with leading zero for single-digit months MMM—Month as a three-letter abbreviation dd—Day of month as digits with leading zero for single-digit days ddd—Day of week as a three-letter abbreviation hh—Hours with leading zero for single-digit hours; 12-hour clock HH—Hours with leading zero for single-digit hours; 24-hour clock mm—Minutes with leading zero for single-digit minutes ss—Seconds with leading zero for single-digit seconds t—One character time marker string, such as A or P tt—Multicharacter time marker string, such as AM or PM unix_us—Unix time in microseconds unix_ms—Unix time in milliseconds unix_s—Unix time in seconds unix_hex—Unix time in hexadecimal Learn more about custom dates and time formats This parameter is only enabled when the in_date_field parameter value is a text field and the target_date_field parameter value is a date field, or the field_mapping parameter input value is a text field and the output value is a date field This parameter is only enabled when the update_target parameter is set to UPDATE.	String

Derived Output

Name	Explanation	Data Type
updated_target_features	The updated target dataset.	Feature Layer; Table View

Code sample

UpdateFeaturesWithIncidentRecords example 1 (Python window)

The following script demonstrates how to use the UpdateFeaturesWithIncidentRecords function in immediate mode.

import arcpy
arcpy.env.workspace = r"C:\data\city_pd.gdb"

arcpy.ca.UpdateFeaturesWithIncidentRecords("C:/data/calls_for_service_records.csv",
                                           "calls_for_service_features",
                                           "COORDINATES",
                                           "Longitude",
                                           "Latitude",
                                           4326)

UpdateFeaturesWithIncidentRecords example 2 (stand-alone script)

The stand-alone script below is an example of how to use the UpdateFeaturesWithIncidentRecords function in a script:

# Name: UpdateFeaturesWithIncidentRecords.py
# Description: Update the calls for service feature class with new updates from the records management system (RMS).

# Import script modules 
import arcpy

# Set the workspace
arcpy.env.workspace = r"C:/data/city_pd.gdb"

# Address Field Pairs formatted as ["Locator Address Field Name", "Input Field Name"]
address_field_pairs = [
        ["Address", "locdesc"],
        ["Address2", ""],
        ["Address3", ""],
        ["Neighborhood", ""],
        ["City", "municipality"],
        ["Subregion", ""],
        ["Region", "state"],
        ["Postal", "zip5"],
        ["PostalExt", ""],
        ["CountryCode", ""]
    ]

arcpy.ca.UpdateFeaturesWithIncidentRecords("C:/data/calls_for_service_records.csv",
                                           "calls_for_service_features",
                                           location_type="ADDRESSES",
                                           address_locator="https://geocode.arcgis.com/arcgis/rest/services/World/GeocodeServer/ArcGIS World Geocoding Service",
                                           address_type="MULTI_FIELD_ADDRESS",
                                           address_fields=address_field_pairs,
                                           update_target="UPDATE",
                                           match_fields=[["callid", "callid"]],
                                           update_matching="UPSERT",
                                           update_geometry="UPDATE_GEOMETRY",
                                           field_matching_type="AUTOMATIC")

Environments

Current Workspace

Licensing information

• Basic: Yes
• Standard: Yes
• Advanced: Yes