How to add a new date to existing indicators in a data catalog

Related posts:

This article is particularly aimed at clients that use the InstantAtlas National Data Service and describes how to add a new date to existing indicators in a data catalog.

Before carrying out any of the steps described below, we recommend that you read the full article to make sure that you have a clear understanding of the entire process. You should make a plan of what you need to do and note down the names that you want your geodatabases, hosted layers and fields to have. Think carefully about these and be consistent, as it may be difficult to change them later once you have created outputs (reports or dashboards) based on the new core layers and data. Contact support@instantatlas.com if you are unsure about anything before you start.

Please note that if you use one of the InstantAtlas National Data Services , you will not be able to add dates to indicators that are part of the service. You can only add new dates to indicators you have loaded yourself.

For this example, let’s assume that you wish to load fuel poverty data for the year 2016 for LSOA, UTLA, Region and Country. The indicators already exist in your data catalog with the dates 2012 – 2015 for all four of these core layers.

To add new dates to existing indicators, you must first locate the feature layer that the existing instances are stored in. All dates for the same indicator must be saved in the same feature layer. The easiest way to find the feature layer is to open your data catalog in the Data Catalog Manager, find the indicator and click on the icon. At the top of the dialog you will see the link to the feature service.

Click this link and you will see the layer description of the feature service. In the bread crumb at the top click on the item one level above. It will be called ‘something (FeatureServer)’.

On the feature server page, find the Service ItemId. Copy it to your clipboard.

Now sign in to your ArcGIS Online account and click on the magnifying glass in the main menu at the top to search for items. Paste the ID from your clipboard into the search field. As a result, you will see the feature layer that contains the fuel poverty indicators.

You can now either export the feature layer to a file geodatabase (open the item’s details page and click Export Data – Export to FGDB) or you can look for the file geodatabase that was used to originally create the feature layer. The latter would save the step of exporting but should only be done, if you are confident that the feature layer has not been changed since it was uploaded.

To find the file geodatabase from which the feature layer was originally created, open the details page for the feature layer and have a look at the Details section on the right underneath the buttons. Follow the link to the file geodatabase listed next to Created from.

From the details page ofthe File Geodatabase, you can download the database using the Download button on the top right-hand side. Browse to the location you downloaded the database to and extract the zip archive.

Now open ArcGIS Pro and connect this file geodatabase to your project. To do this, right-click on Databases in the Catalog Project pane and select Add Database. Then browse to the file geodatabase.

Expand the contents of the database and you will see the four data layers with the fuel poverty data as well as the relationship classes between them. If you right-click on one of the data layers and click on Design – Fields you can see the fields that are currently part of this layer. You can see the required field format for indicators with multiple dates in the Alias column: the indicator name needs to be followed by a pipe character ‘|’ and the date.

Before you can add the data for 2016, you need to ensure that it is correctly formatted. You will need one CSV file per data layer. The first row needs to contain the column headers and each file needs to contain a column with the  feature codes. These codes need to match the code column in the respective  data layer in the file geodatabase so that you can connect the data to the data layers. The column headers of the indicator columns need to match those of the existing fields in the layer in ArcGIS Pro; only the date following the pipe character should be different.

If you wish to add multiple dates at the same time, you can do this in additional columns in the same file, applying the same rules.

Now you can load the CSV files into your File Geodatabase as a table. To do this, right-click on the database and select Import – Table.

The Geoprocessing Tool Table To Table opens. Select one of the CSV files as Input Rows and give it a suitable Output Name (this table is just a temporary item in the database so the name does not matter).

It is now important to check that all of the output fields are loaded in the Field Map with their correct data type. Click on the first field and select Properties. The Type property is derived from the first value of the field so you should check that it is correct for each field and adjust if necessary. For example, if you add a rate indicator and the first value of the column happens to be an integer value, the whole field will be imported as Long Integer, stripping the decimal places from all other values of the column.

Click Run to import the data. When complete, the table will appear within your database in the Catalog Project pane. To check whether the import was successful you can add the table to a map. You can open the table and see the values if you right-click on the layer in the Content pane and select Open.

Repeat the data loading steps for each of the CSV files.

The next step is to join the data from the imported tables to the data layers. In the Geoprocessing pane, click the Back icon and find the tool called Join Field. Choose your data layer as the Input Table (you can drag and drop it from the Catalog Project pane) and the imported table as the Join Table. Select the matching code fields as Input and Output Join Field.

Open the Join Fields drop down and toggle all checkboxes to select all fields. There is a button at the bottom of the drop-down list that does this for you. You may wish to uncheck the columns containing the feature code and names as they will already exist in the data layer.

Then click Add and Run to join the selected fields to the data layer. The layer will automatically be added to your map if you have one open. You should check that the join was successful by opening the attribute table of the layer (right-click on the layer in the Content pane and select Attribute Table).

Once the joins are complete, you can delete the temporary tables you imported from the CSV files from the database (right-click on the item in the Catalog Project pane and select Delete).

Now close ArcGIS Pro and browse to your file geodatabase in Windows explorer. Zip it and ensure that the file name is the same as the zip archive you downloaded earlier from ArcGIS Online. If the original zip file is saved in the same folder and you want to keep it as a backup, you may wish to rename it first.

Back in ArcGIS Online, open the details page of the Fuel Poverty feature layer (not the related file geodatabase item) and click on the Update Data button on the right. Then select the option Overwrite Entire Layer.

Browse to your zipped file geodatabase (the one you just added the 2016 data to) and click Overwrite. This will update the feature layer as well as the related file geodatabase.

Now you should sign in to https://hub.instantatlas.com/ and click on the Manage Catalog button. You should see your Data Catalog with Core Layers and Data Model for each layer.

Select one of the core layers that has data for Fuel Poverty, e.g. LSOA and find the indicators in the tree.

Click on the icon for one of the indicators and then click the Check for Updates button in the bottom left corner of the Dates dialog. The Data Catalog Manager will find 2016 as a new date for the indicator.

Click Save Changes to confirm that you wish to add the new date.

You should now repeat the Check for Update step for each indicator of each core layer for  this dataset.

You may wish to update the ‘Temporal’ metadata key (and maybe others) for the indicators to add the new date to the range. You can do this by clicking the icon and then clicking on the Edit button.

If you have a large number of indicators, adding metadata through the Data Catalog Manager interface may not be the most efficient method. As an alternative, you can create a CSV file containing the metadata for your new indicators and append it to your metadata table. Please contact support@instantatlas.com for information on how to do this.