Joined View Layer

gargarcia

Is it possible to use the python Api to create a joined view layer? I have checked the reference guide and saw the join analysis tool, but it didn't appear to have a parameter for output type like using the tool in the classic map viewer has. I also see the function for creating a view, but nothing associated about joins. If it is not possible with the python Api are there alternative methods to programmatically create joined view layers?

 

EarlMedina

You can accomplish this, but it requires a bit of setup and understanding of how things work. Let me give you an example.

Suppose I want to join Feature Layer A to Table B. I know the field I want to join on is named "Activity," and I want to perform a Left Join.

 

Begin with necessary imports, initial setup:

 

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

gis = GIS("https://www.arcgis.com", username="hari", password="seldon")
fl_url = "https://services3.arcgis.com/xyz/arcgis/rest/services/pntLayer/FeatureServer/0"
tbl_url = "https://services3.arcgis.com/xyz/arcgis/rest/services/TableToJoin/FeatureServer/0"

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

 

 

Next, you'll want to add an index on the join field to both services:

 

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

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

 

 

Create a blank view service, initialize a FeatureLayerCollection from the result for later use:

 

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

 

 

Now, you need to think about which fields you want from the source feature layer and source table:

 

sourceFeatureLayerFields = [
    {
        "name": "Activity",
        "alias": "Activity",
        "source": "Activity"
    },
    {
        "name": "Description",
        "alias": "Description",
        "source": "Description"
    },
    {
        "name": "StartDate",
        "alias": "StartDate",
        "source": "StartDate"
    },
    {
        "name": "EndDate",
        "alias": "EndDate",
        "source": "EndDate"
    }
]

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

 

 

This is where the magic happens. We use some of the info above to create a definition we can use to end up with our desired joined view:

 

field_to_join_on = "Activity"
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)

 

 

Put it all together and we have:

 

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

gis = GIS("https://www.arcgis.com", username="hari", password="seldon")
fl_url = "https://services3.arcgis.com/xyz/arcgis/rest/services/pntLayer/FeatureServer/0"
tbl_url = "https://services3.arcgis.com/xyz/arcgis/rest/services/TableToJoin/FeatureServer/0"

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

index_to_add = {"indexes":[
    {
        "name": "Activity_Index", 
        "fields": "Activity", 
        "isUnique": False, 
        "isAscending": True, 
        "description": "Activity_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": "Activity",
        "alias": "Activity",
        "source": "Activity"
    },
    {
        "name": "Description",
        "alias": "Description",
        "source": "Description"
    },
    {
        "name": "StartDate",
        "alias": "StartDate",
        "source": "StartDate"
    },
    {
        "name": "EndDate",
        "alias": "EndDate",
        "source": "EndDate"
    }
]

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

field_to_join_on = "Activity"
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)

 

 

There are other options you can play around with in the view layer definition, but this should give you a good starting point. To get a better idea of what your definition needs to look like, you could use the UI to create the exact view you want and capture the network traffic right after you click the button to create the joined view. The call you need to pay most attention to is "addToDefinition".

 

Hope this helps you and a lot of other people.