Add Table from Hosted Service to Another and Create Relationship
Data can often be provided as a Shapefile with an accompanying file containing the attribute information, or surplus attributes to compliment those in the Shapefile, such as a CSV for example. This is the case for Census Data in Ireland, provided by the Central Statistics Office (CSO), results are available in CSV format and can be related to various boundary types in a polygon Shapefile. This blog will use a subset of the data for illustration purposes, we will focus on Electoral Divisions in County Kildare. The fields have been clipped from 799 available to the first 10, I'm not even sure AGOL could handle that amount of fields? Anyone know if there is a limit, or recommended limit for performance? The data used for the workflow outlined in the blog is available for download at the end of the post. The original data is available on the CSO website.
The ArcGIS API for Python provides no method for publishing a Shapefile and CSV together into one Feature Service from a zipped folder with the data represented as a single Hosted Service containing a Feature Layer and a Table. These have to be added to AGOL separately and published separately, unless I've completely missed something and you can correct me in the comments. This blog post will take a published Table and add as a Table to a Feature Service containing a single Feature Layer. A relationship will be created between the Feature Layer and the Table. While this can be achieved by publishing via ArcGIS Pro with a little preparation beforehand, here, we will use the ArcGIS API for Python only, not everyone has access to ArcGIS Pro. You might also have a situation where you do not want to overwrite a Feature Service just to add a Table.
So let's get the show on the road! We begin with the usual import of the GIS module, we require the FeatureLayerCollection module, and we connect to AGOL.
## import the GIS module
from arcgis import GIS
from arcgis.features import FeatureLayerCollection
## the GIS module acts as an entry point to your AGOL
agol = GIS("home")
Add the Shapefile zip to AGOL as an Item. This contains the County Kildare Electoral Division boundaries. The zipped folder is available for download at the end of the blog post (CSO_ED2016_Kildare.zip)
## path to zipped folder that contains the Shapefile
shp_zip = r"path\to\CSO_ED2016_Kildare.zip"
## add the Shapefile as an item to AGOL
shp_agol = agol.content.add(item_properties={}, data=shp_zip)
Add the CSV file to as an Item. The CSV contains census information (10 of the 799 ). The CSV file is available for download at the end of the blog post (SAPS2016_ED3409_Kildare.csv).
## path to csv file
csv_file = r"path\to\SAPS2016_ED3409_Kildare.csv"
## add as csv item in AGOL
csv_agol = agol.content.add(item_properties={}, data=csv_file)
We used the ContentManager add() method to add these items, you can find more information about this method here.
Publish the Shapefile Item as a Feature Service.
## publish hosted feature service
shp_fs = shp_agol.publish()
Publish the CSV Item as a Hosted Table.
## publishing properties so csv is added as a table and not a spatial layer.
csv_properties = {
"type:" : "csv",
"locationType" : "none"
}
## publish hosted table
csv_ht = csv_agol.publish(publish_parameters=csv_properties)
We used the Item publish() method to publish our Hosted Services, more information on the publish() method can be found here.
Our next task is to add the Table to the Feature Service.
## Access the Table in the Hosted Service
tbl = csv_ht.tables[0]
## convert the Table properties to a Dictionary
tbl_def = dict(tbl.properties)
## get the item as a FeatureLayerCollection object
flc = FeatureLayerCollection.fromitem(shp_fs)
## add the Table definition to the Feature Service
flc.manager.add_to_definition({"tables" : [tbl_def]})
Check the Feature Service and see if the Table was added.
And check the Data tab to see that the fields have been added.
Notice that the data is missing. Essentially we have added the Table schema to our Feature Service. Next, we add/append the data from the original CSV.
## use analyze as per append docs when using a csv
csv_data = agol.content.analyze(
item = csv_agol.id,
file_type = "csv",
geocoding_service = None,
location_type = None
)
## reload the Feature Service with the Table added
shp_fs = agol.content.get(shp_fs.id)
## access the Table
tbl = shp_fs.tables[0]
## append in data
tbl.append(
item_id = csv_agol.id,
upload_format = "csv",
source_info = csv_data,
upsert = False
)
We used the ContentManager analyze() method to prepare the data for the append() method. More on the analyze() method can be found here, and the append() method for a Table object can be found here. Refresh or navigate back to the Data tab for the Table in the Feature Service and check if the data has been successfully added?
Looking good so far. Now we want to create a relationship between the Feature Layer and the Table within the Feature Service. In order to do this we need to define the relationship properties for both. There needs to be a field that contains common attributes in both layer and table. In the Feature Layer this needs to be a field that contains unique attributes (primary key) and a field (foreign key) in the Table that uses one or multiple of each of the unique values from the Feature Layer field. This will determine if the relationship should be one-to-one or one-to-many. We have a one-to-one relationship here with the CSOED_34_1 Feature Layer field containing unique attributes and having exactly one match in the GEOGDESC field of the Table.
## get the Feature Layer and its ID
lyr = shp_fs.layers[0]
lyr_id = lyr.properties.id
## get the Table ID
tbl_id = tbl.properties.id
## a dictionary containing the relationship properties for the Feature Layer
lyr_rel_dict = {
"name": "Layer_to_Table",
"relatedTableId": int(tbl_id),
"cardinality": "esriRelCardinalityOneToOne",
"role": "esriRelRoleOrigin",
"keyField": "CSOED_34_1",
"composite": True
}
## a dictionary containing the relationship properties for the Table
tbl_rel_dict = {
"name": "Table_to_Layer",
"relatedTableId": int(lyr_id),
"cardinality": "esriRelCardinalityOneToOne",
"role": "esriRelRoleDestination",
"keyField": "GEOGDESC",
"composite": True
}
## update the relationship properties in the Feature Layer and Table
lyr.manager.add_to_definition({"relationships" : [lyr_rel_dict]})
tbl.manager.add_to_definition({"relationships" : [tbl_rel_dict]})
The cardinality is set as one-to-one, this means that each record in the spatial Feature Layer can have only one related record in the related Table. Setting the composite to True means each feature in the destination table is expected to be related to an origin feature.
Let's check that the relationship was created successfully. Open the data in a Map and click on a Polygon. The Popup Window should contain a link Show Related Data, click it.
Look at that!
We have shown that the ArcGIS API for Python can be used to add a Table to a Feature Service that already exists. This can be useful if you do not have access to ArcGIS Pro or if you have a mature Feature Service that you don't want to overwrite but want to add a Table to the service. We also explored adding a relationship data between a Feature Layer and a Table within the same Feature Service.
Here' all the code...
## import the GIS module
from arcgis import GIS
from arcgis.features import FeatureLayerCollection
## the GIS module acts as an entry point to your AGOL
agol = GIS("home")
## path to zipped folder that contains the Shapefile
shp_zip = r"path\to\CSO_ED2016_Kildare.zip"
## add the Shapefile as an item to AGOL
shp_agol = agol.content.add(item_properties={}, data=shp_zip)
## path to csv file
csv_file = r"path\to\SAPS2016_ED3409_Kildare.csv"
## add as csv item in AGOL
csv_agol = agol.content.add(item_properties={}, data=csv_file)
## publish hosted feature service
shp_fs = shp_agol.publish()
## publishing properties so csv is added as a table and not a spatial layer.
csv_properties = {
"type:" : "csv",
"locationType" : "none"
}
## publish hosted table
csv_ht = csv_agol.publish(publish_parameters=csv_properties)
## Access the Table in the Hosted Service
tbl = csv_ht.tables[0]
## convert the Table properties to a Dictionary
tbl_def = dict(tbl.properties)
## get the item as a FeatureLayerCollection object
flc = FeatureLayerCollection.fromitem(shp_fs)
## add the Table definition to the Feature Service
flc.manager.add_to_definition({"tables" : [tbl_def]})
## use analyze as per append docs when using a csv
csv_data = agol.content.analyze(
item = csv_agol.id,
file_type = "csv",
geocoding_service = None,
location_type = None
)
## reload the Feature Service with the Table added
shp_fs = agol.content.get(shp_fs.id)
## access the Table
tbl = shp_fs.tables[0]
## append in data
tbl.append(
item_id = csv_agol.id,
upload_format = "csv",
source_info = csv_data,
upsert = False
)
## get the Feature Layer and its ID
lyr = shp_fs.layers[0]
lyr_id = lyr.properties.id
## get the Table ID
tbl_id = tbl.properties.id
## a dictionary containing the relationship properties for the Feature Layer
lyr_rel_dict = {
"name": "Layer_to_Table",
"relatedTableId": int(tbl_id),
"cardinality": "esriRelCardinalityOneToOne",
"role": "esriRelRoleOrigin",
"keyField": "CSOED_34_1",
"composite": True
}
## a dictionary containing the relationship properties for the Table
tbl_rel_dict = {
"name": "Table_to_Layer",
"relatedTableId": int(lyr_id),
"cardinality": "esriRelCardinalityOneToOne",
"role": "esriRelRoleDestination",
"keyField": "GEOGDESC",
"composite": True
}
## update the relationship properties in the Feature Layer and Table
lyr.manager.add_to_definition({"relationships" : [lyr_rel_dict]})
tbl.manager.add_to_definition({"relationships" : [tbl_rel_dict]})