Create a replicate survey from a SQLite database in ArcGIS Pro

Summary

In ArcGIS Survey123, changes in our survey schema can sometimes result in data records being deleted from the hosted feature service. If we have access to the SQLite database that holds the results of our survey, we can rebuild the feature layer and survey, as described below.

Procedure

1. Recover the data:
   The first step is to recover the lost data from the devices the surveys were completed on. The steps can be found in the ArcGIS Survey123 documentation: Troubleshoot.
   • In the section entitled Recover data using desktop field app, stop at Step 3, as we don’t want to fix the database but just copy it.

Note:
It is important to inspect the data. The data from more than one survey can be in the database, we can remove this extra data later by using a tool such as DB Browser for SQLite.

2. Convert the data to a CSV:
   The next step is to convert the dataset into a CSV file. We will use a publicly available Python script that can be found in GitHub: Convert the survey data stored in the Survey123 Field App into a set of CSV files. Save the script in the same location as the SQLite database. Before running the script, we need to change the script status value to the value of our data, as shown in the example section of the script below:

 # Status indicates which 'box' the Survey is in
    # 0 - Drafts
    # 1 - Outbox
    # 2 - Sent
    # 3 - Submission Error
    # 4 - Inbox
    for row in cur.execute('SELECT name, data, status from Surveys where status = 2'):
        # print(row)

3. Collect and convert all datasets:
   If the survey data has been collected on more than one device, follow steps 1 and 2 for each device to collect all the data.
4. Edit and save the CSV as an xlsx file:
   Open the CSV file in Excel and collate all the data. We now need to clean up the data:
   • Delete empty rows
   • Convert the epoch value to a date-time value. We can use the following Excel expression:
     =TEXT((EPOCH/1000 + ("1/1/1970"-"1/1/1900"+1)*86400) / 86400,"DD/MM/YYYY HH:MM:SS")
   • If there are photos attached to each record, ensure that the file path to the image is listed under the photo. This allows us to easily attach the images to the new points.
   • Rename and save the workbook as an xlsx file.
5. Convert the data from Excel to a table:
   Use the Excel To Table (Conversion) conversion tool to add the data to an ArcGIS Pro project.
6. Add the data to a map:
   To add the data to the map, right-click > Display XY Data.

• • In the pop-up, ensure that the X Field and Y Field parameters are set to the correct data values and click OK.

Our points are then added to the map as a file geodatabase feature class with the survey data attached as attributes.

7. Enable Attachments and add photos:
   1. Enable attachments for the feature layer using the Enable Attachments (Data Management) geoprocessing tool. This creates a related attachment table.
   2. Use the Add Attachments (Data Management) geoprocessing tool to add the photos to the points. Because we have already specified the file path, we can set the point layer as both the Input Dataset and Match Table parameters. The Match Path Field parameter should be the attribute that holds the photo file path. The working folder can be left as the default. An example of these settings is shown in the next image.

The images are now available in the attribute information, as shown in the next screenshot.

8. Publish the layer to ArcGIS Online:
   To publish the layer to ArcGIS Online, right-click Sharing > Share as Web Layer. Rename the layer, and add metadata if required. Resolve any warnings or errors.
9. Prepare the feature layer:
   In ArcGIS Online, open the item details page for the re-created survey data. Using the Settings tab to enable editing and sync. It may be useful to enable layer delete protection and disable the delete option under the editing options.
10. Re-create the Survey:
    Open Survey123 Connect and create a new survey from a feature service. More information on this process can be found here: Use Survey123 with existing feature layers
    The name of the survey can be changed in the details tab.
11. Publish the survey:
    Use the Publish option on the left-hand ribbon to publish the survey.
12. Add the feature layer to the survey folder:
    In ArcGIS Online, move the hosted feature layer and service definition to the folder with the survey form and web map for simplicity.
13. Confirm that the data has been linked to the survey:
    Open the survey web map and click on a point to confirm that the attributes and images have been linked to the new survey.

We have now re-created our survey with the original data.