Create a joined hosted feature layer view using ArcGIS API for Python

Summary

A joined hosted feature layer view uses combined data from two different sublayers. These sublayers can be from the same or different hosted feature layers or table layers based on a relationship between nonspatial attributes in each layer. Joined views are useful for dynamically updating data from two layers alongside the source layer. It is possible to use ArcGIS API for Python to programmatically create a joined hosted feature layer view without accessing the layer on Map Viewer. This applies to both ArcGIS Online and Portal for ArcGIS. This article describes how to do so in a standalone script or ArcGIS Notebook.

Procedure

1. Import the necessary modules and specify the credentials.

from arcgis.gis import GIS
from arcgis.features import FeatureLayer, Table, FeatureLayerCollection

#For ArcGIS Online
gis = GIS("ORGANIZATION_URL", "username", "password")

#For ArcGIS Notebook
gis = GIS("Home")

#For Portal for ArcGIS
gis = GIS("https://<machine>.<domain>.com/<web_adaptor_name>", "username", "password", verify_cert=False)

2. Specify the layers to be joined using the services URL.

featurel_url = "https://services.domain.com/<xxxxxxxxxxx>/arcgis/rest/services/<feature_layer>/FeatureServer/<feature_id>"
joinl_url = "https://services9.arcgis.com/<xxxxxxxxxxx>/arcgis/rest/services/<join_feature_layer>/FeatureServer/<feature_id>"

fl = FeatureLayer(fl_url, gis)
tbl = Table(tbl_url, gis)

3. Add an index on the join field to both services.

index_to_add = {"indexes":[
    {
        "name": "<field>_Index", 
        "fields": "<field>", 
        "isUnique": False, 
        "isAscending": True, 
        "description": "<field>_Index"
    }
]}

fl.manager.add_to_definition(index_to_add)
tbl.manager.add_to_definition(index_to_add)

4. Create a blank view service and initialize run the FeatureLayerCollection() function using the blank view as the input.

view_service = gis.content.create_service(name="joined_view", is_view=True)
view_flc = FeatureLayerCollection.fromitem(view_service )

5. Select the fields required to be included in the layer view.

sourceFeatureLayerFields = [
    {
        "name": "<field1>",
        "alias": "<field1_alias>",
        "source": "<field1>"
    },
    {
        "name": "<field2>",
        "alias": "<field2_alias>",
        "source": "<field1>"
    },
    {
        "name": "<field3>e",
        "alias": "<field3_alias>",
        "source": "<field3>"
    },
    {
        "name": "<field4>",
        "alias": "<field4_alias>",
        "source": "<field4>"
    }
]

sourceTableFields = [
    {
        "name": "<join_field1>",
        "alias": "<join_field1_alias>",
        "source": "<join_field1>"
    }
]

6. Create a definition from the specified information in Step 5 and generate the joined hosted feature layer view.

field_to_join_on = "<Field>"
view_lyr_name = "<view_layer_name>"
definition_to_add = {
  "layers": [
    {
      "name": view_lyr_name,
      "displayField": "",
      "description": "AttributeJoin",
      "adminLayerInfo": {
        "viewLayerDefinition": {
          "table": {
            "name": "sampleJoinedView",
            "sourceServiceName": fl.properties.name,
            "sourceLayerId": 0,
            "sourceLayerFields": sourceFeatureLayerFields,
            "relatedTables": [
              {
                "name": "testjoin",
                "sourceServiceName": tbl.properties.name,
                "sourceLayerId": 0,
                "sourceLayerFields": sourceTableFields,
                "type": "LEFT",
                "parentKeyFields": [
                  field_to_join_on
                ],
                "keyFields": [
                  field_to_join_on
                ],
                "topFilter": {
                  "groupByFields": field_to_join_on,
                  "orderByFields": "OBJECTID ASC",
                  "topCount": 1
                }
              }
            ],
            "materialized": False
          }
        },
        "geometryField": {
          "name": f"{view_lyr_name}.Shape"
        }
      }
    }
  ]
}

view_flc.manager.add_to_definition(definition_to_add)

The following is a sample script run on ArcGIS Online.

from arcgis.gis import GIS
from arcgis.features import FeatureLayer, Table, FeatureLayerCollection

gis = GIS("https://www.arcgis.com", "username123", "password123")
fl_url = "https://services9.arcgis.com/xxxxxxxxxx/arcgis/rest/services/TestCopySymbology2/FeatureServer/0"
tbl_url = "https://services9.arcgis.com/xxxxxxxxxxx/arcgis/rest/services/TestPointSymbologyRestTransfer/FeatureServer/0"

fl = FeatureLayer(fl_url, gis)
tbl = Table(tbl_url, gis)

index_to_add = {"indexes":[
    {
        "name": "TestJoin_Index", 
        "fields": "TestJoin", 
        "isUnique": False, 
        "isAscending": True, 
        "description": "TestJoin_Index"
    }
]}

fl.manager.add_to_definition(index_to_add)
tbl.manager.add_to_definition(index_to_add)

view_service = gis.content.create_service(name="joined_view", is_view=True)
view_flc = FeatureLayerCollection.fromitem(view_service )

sourceFeatureLayerFields = [
    {
        "name": "TestJoin",
        "alias": "TestJoin",
        "source": "TestJoin"
    }
]

sourceTableFields = [
    {
        "name": "TestJoin",
        "alias": "TestJoin",
        "source": "TestJoin"
    }
]

field_to_join_on = "TestJoin"
view_lyr_name = "sampleJoinedView"
definition_to_add = {
  "layers": [
    {
      "name": view_lyr_name,
      "displayField": "",
      "description": "AttributeJoin",
      "adminLayerInfo": {
        "viewLayerDefinition": {
          "table": {
            "name": "sampleJoinedView",
            "sourceServiceName": fl.properties.name,
            "sourceLayerId": 0,
            "sourceLayerFields": sourceFeatureLayerFields,
            "relatedTables": [
              {
                "name": "testjoin",
                "sourceServiceName": tbl.properties.name,
                "sourceLayerId": 0,
                "sourceLayerFields": sourceTableFields,
                "type": "LEFT",
                "parentKeyFields": [
                  field_to_join_on
                ],
                "keyFields": [
                  field_to_join_on
                ],
                "topFilter": {
                  "groupByFields": field_to_join_on,
                  "orderByFields": "OBJECTID ASC",
                  "topCount": 1
                }
              }
            ],
            "materialized": False
          }
        },
        "geometryField": {
          "name": f"{view_lyr_name}.Shape"
        }
      }
    }
  ]
}

view_flc.manager.add_to_definition(definition_to_add)