Select to view content in your preferred language

how to create view from table relationship

303
4
06-13-2024 03:43 PM
MatthieuANDRE3
New Contributor II

hi everyone,
I've created a hosted feature layer with a layer and a table. There is a table relationship between the layer and the table.
I want to create a view of the table. How can I do this with the python api?
I know how to create a view from the layer (index 0), but not from the table (index 1).

here's my code

 

from arcgis.gis import GIS
gis = GIS("home")
from arcgis.features import FeatureLayerCollection
def search_layer(gis,layer_name):
    search_results = gis.content.search(layer_name, item_type="Feature Layer")
    proper_index = [i for i, s in enumerate(search_results) 
                    if '"' + layer_name + '"' in str(s)]
    found_item = search_results[proper_index[0]]
    flc = FeatureLayerCollection.fromitem(found_item)
    return flc

def create_view(gis, source_flc, view_name, layer_index, view_def):
    new_view = source_flc.manager.create_view(name=view_name)
    # Search for newly created View
    view_flc = search_layer(gis, view_name)
    # The viewDefinitionQuery property appears under layers
    view_layer = view_flc.layers[layer_index]
    # Update the definition to include the view definition query
    view_layer.manager.update_definition(view_def)
    search_results = gis.content.search(view_name, item_type="Feature Layer")
    item = search_results[0]
    item.move(folder='ports')
    print("View created")
    print(view_layer.manager.properties)

def main():
    # Index of the Layer to be filtered
    layer_index = 0
    source_flc = search_layer(gis, "udi_bfc_secheresse_v2")
    # Define a SQL query to filter out events past 1988
    view_def_synapse = {"fields": [{"name":"OBJECTID","visible":True},{"name":"UDI_CODE","visible":True,"alias":"Code de l'UDI"},{"name":"UDI_NOM","visible":True,"alias":"Nom de l'UDI"},{"name":"UGE_CODE","visible":False},{"name":"UGE_NOM","visible":False},{"name":"PRPDE_CODE","visible":False},{"name":"PRPDE_NOM","visible":True,"alias":"Nom de la PRPDE"},{"name":"DEP_CODE","visible":False},{"name":"DEP_NOM","visible":False},{"name":"DEP_GESTION_CODE","visible":False},{"name":"DEP_GESTION_NOM","visible":True,"alias":"Département gestionnaire"},{"name":"REG_CODE","visible":False},{"name":"REG_NOM","visible":False},{"name":"BASSIN_CODE","visible":False},{"name":"POP","visible":False},{"name":"ZONE_DEFENSE","visible":False},{"name":"ES_ESMS","visible":False},{"name":"SHAPE__Area","visible":False},{"name":"SHAPE__Length","visible":False},{"name":"GlobalID","visible":True}]}
    view_name_synapse = "test_synapse_ok"
    if(gis.content.is_service_name_available(service_name= view_name_synapse, service_type = 'featureService')):create_view(gis, source_flc, view_name_synapse, layer_index, view_def_synapse)
    
    view_def_prpde = {"fields": [{"name":"OBJECTID","visible":True},{"name":"UDI_CODE","visible":True,"alias":"Code de l'UDI"},{"name":"UDI_NOM","visible":True,"alias":"Nom de l'UDI"},{"name":"UGE_CODE","visible":False},{"name":"UGE_NOM","visible":False},{"name":"PRPDE_CODE","visible":False},{"name":"PRPDE_NOM","visible":False},{"name":"DEP_CODE","visible":False},{"name":"DEP_NOM","visible":False},{"name":"DEP_GESTION_CODE","visible":False},{"name":"DEP_GESTION_NOM","visible":False},{"name":"REG_CODE","visible":False},{"name":"REG_NOM","visible":False},{"name":"BASSIN_CODE","visible":False},{"name":"POP","visible":False},{"name":"ZONE_DEFENSE","visible":False},{"name":"ES_ESMS","visible":False},{"name":"SHAPE__Area","visible":False},{"name":"SHAPE__Length","visible":False},{"name":"GlobalID","visible":True}]}
    view_name_prpde = "test_prpde_ok"
    if(gis.content.is_service_name_available(service_name= view_name_prpde, service_type = 'featureService')):create_view(gis, source_flc, view_name_prpde, layer_index, view_def_prpde)
    
    layer_index = 1
    source_tbl_flc = search_layer(gis, "udi_bfc_secheresse_v2")    
    view_def_tbl_synapse = {"fields": [{"name":"objectid","visible":True},{"name":"globalid","visible":True},{"name":"dateSaisie","visible":True,"alias":"Date de saisie"},{"name":"selUDI","visible":True,"alias":"Code de l'UDI"},{"name":"nomDepGest","visible":False},{"name":"nomRegion","visible":False},{"name":"QDeclarationTension","visible":False},{"name":"QimpactQualQuant","visible":False},{"name":"QNonConformiteSecheresse","visible":False},{"name":"parametreRestriction","visible":False},{"name":"moyenGestionComplete","visible":False},{"name":"impactQuantDist","visible":False},{"name":"impactQuantPress","visible":False},{"name":"impactUDI","visible":False},{"name":"communesImpactees","visible":True,"alias":"Communes impactées"},{"name":"observation","visible":False},{"name":"editeur","visible":False},{"name":"pdfCount","visible":False},{"name":"indic_1_3","visible":True,"alias":"1.3 Impact qualitatif"},{"name":"indic_1_4","visible":True,"alias":"1.4 Impact quantitatif"},{"name":"indic_1_5","visible":True,"alias":"1.5 UDI en tension"},{"name":"indic_1_6","visible":True,"alias":"1.6 Mesure(s) non exceptionnelle(s)"},{"name":"indic_1_7","visible":True,"alias":"1.7 Interconnexion exceptionnelle"},{"name":"indic_1_8","visible":True,"alias":"1.8 Transfert d'eau brute"},{"name":"indic_1_9","visible":True,"alias":"1.9 Autorisation exceptionnelle d'un captage"},{"name":"indic_1_10","visible":True,"alias":"1.10 Alimentation par camion citerne"},{"name":"indic_1_11","visible":True,"alias":"1.11 Autre mesure exceptionnelle"},{"name":"indic_1_12","visible":True,"alias":"1.12 Recours à une unité de traitement mobile"},{"name":"indic_1_13","visible":True,"alias":"1.13 Distribution de bouteilles d'eau"},{"name":"indic_1_14","visible":True,"alias":"1.14 Organisation de tours d'eau"},{"name":"indic_1_15","visible":True,"alias":"1.15 Recours aux coupures d'eau durables"},{"name":"indic_1_16","visible":True,"alias":"1.16 Restriction d'usage dûe à la qualité"},{"name":"indic_1_17","visible":True,"alias":"1.17 Paramètre de qualité à l'origine de la restriction"},{"name":"indic_1_18","visible":True,"alias":"1.18 Présence d'ES/ESMS"},{"name":"indic_1_19","visible":True,"alias":"1.19 Commentaire"},{"name":"CreationDate","visible":False},{"name":"Creator","visible":False},{"name":"EditDate","visible":False},{"name":"Editor","visible":False},{"name":"rs_contact","visible":False},{"name":"name_contact","visible":False},{"name":"tel_contact","visible":False},{"name":"mail_contact","visible":False},{"name":"validation_ARS","visible":False}]}
    view_name_tbl_synape = "test_tbl_synapse_ok"
    if(gis.content.is_service_name_available(service_name= view_name_tbl_synape, service_type = 'featureService')):create_view(gis, source_tbl_flc, view_name_tbl_synape, layer_index, view_def_tbl_synapse)
    
    view_def_tbl_prpde = {"fields": [{"name":"objectid","visible":True},{"name":"globalid","visible":False},{"name":"dateSaisie","visible":True,"alias":"Date de saisie"},{"name":"selUDI","visible":True,"alias":"Code de l'UDI"},{"name":"nomDepGest","visible":False},{"name":"nomRegion","visible":False},{"name":"QDeclarationTension","visible":True,"alias":"UDI en tension"},{"name":"QimpactQualQuant","visible":True,"alias":"Type d'impact"},{"name":"QNonConformiteSecheresse","visible":False},{"name":"parametreRestriction","visible":False},{"name":"moyenGestionComplete","visible":False},{"name":"impactQuantDist","visible":False},{"name":"impactQuantPress","visible":False},{"name":"impactUDI","visible":False},{"name":"communesImpactees","visible":False},{"name":"observation","visible":False},{"name":"editeur","visible":False},{"name":"pdfCount","visible":False},{"name":"indic_1_3","visible":False},{"name":"indic_1_4","visible":False},{"name":"indic_1_5","visible":False},{"name":"indic_1_6","visible":False},{"name":"indic_1_7","visible":False},{"name":"indic_1_8","visible":False},{"name":"indic_1_9","visible":False},{"name":"indic_1_10","visible":False},{"name":"indic_1_11","visible":False},{"name":"indic_1_12","visible":False},{"name":"indic_1_13","visible":False},{"name":"indic_1_14","visible":False},{"name":"indic_1_15","visible":False},{"name":"indic_1_16","visible":False},{"name":"indic_1_17","visible":False},{"name":"indic_1_18","visible":False},{"name":"indic_1_19","visible":False},{"name":"CreationDate","visible":False},{"name":"Creator","visible":False},{"name":"EditDate","visible":False},{"name":"Editor","visible":False},{"name":"rs_contact","visible":False},{"name":"name_contact","visible":False},{"name":"tel_contact","visible":False},{"name":"mail_contact","visible":False},{"name":"validation_ARS","visible":False}]}
    view_name_tbl_prpde = "test_tbl_prpde_ok"
    if(gis.content.is_service_name_available(service_name= view_name_tbl_prpde, service_type = 'featureService')):create_view(gis, source_tbl_flc, view_name_tbl_prpde, layer_index, view_def_tbl_prpde)

 

 

can you tell me what I'm doing wrong?

thanks

0 Kudos
4 Replies
EarlMedina
Esri Regular Contributor

Hi, it's not clear to me if you have two problems here or one so my response is going to target both possibilities.

Is the problem that create_view is not creating a view with both the feature layer and table? Or are you wanting to create a view with just the table (not the feature layer)? It used to be in older versions of the ArcGIS API for Python that tables were not included in the create_view logic. Nowadays, unless you explicitly set the layers/tables you want using the view_layers/view_tables params, create_view should just create a view that contains all layers and tables.

 

If none of the above is what you're talking about and what you're really asking is how to set the view definition on a table, then the problem is that in your "create_view" function, your logic only looks at layers:

    # The viewDefinitionQuery property appears under layers
    view_layer = view_flc.layers[layer_index]

 

What you need to do for tables is:

    # The viewDefinitionQuery property appears under layers
    view_layer = view_flc.tables[idx]

 

So, you can just do a quick fix on the params. You can use an enum or just a boolean like this:

def create_view(gis, source_flc, view_name, idx, view_def, is_table=False):
    ...
    ...
    view_layer = view_flc.tables[idx] if table else view_flc.layers[idx]
    view_layer.manager.update_definition(view_def)

 

When you need to make an update on a table, simply set is_table to True.

 

0 Kudos
MatthieuANDRE3
New Contributor II

in fact what I want to do is create a view only with the layer and create a view only with the table

the problem I'm encountering is that each time the view contains the layer and the table

I've seen that the gis.content.create_service method also exists, but the problem is that I don't know how to format the definition file that will be used in the add_to_definition(definition_file) method for each of my needs (create a view with only the layer and create a view with only the table)

0 Kudos
EarlMedina
Esri Regular Contributor

Ah, thank you for clarifying. Unfortunately, as other people seem to have reported I observed the view_layers and view_tables parameters to be quite buggy. They also do not do what I would expect.

 

You can, however, do this with create_service and I have a very simplified sample that will add all fields from source table. I start with the source FeatureLayerCollection so that the source information can be tied back nicely without any extra work.

 

 

from arcgis.gis import GIS
from arcgis.features import FeatureLayerCollection
gis = GIS("https://www.arcgis.com", "user", "passwd")

url = "https://services3.arcgis.com/ON5SbcAnbs908sd90j/arcgis/rest/services/source/FeatureServer"
flc = FeatureLayerCollection(url, gis)

view_item = gis.content.create_service(name="tableOnly", is_view=True)
view_flc = FeatureLayerCollection.fromitem(view_item)

definition_to_add = {
  "layers": [],
  "tables": [
    {
      "name": flc.tables[0].properties.name,
      "type": "Table",
      "cacheMaxAge": 30,
      "description": "",
      "url": flc.tables[0].url,
      "adminLayerInfo": {
        "viewLayerDefinition": {
          "sourceServiceName": flc.service.properties.adminServiceInfo.name,
          "sourceLayerId": flc.tables[0].properties.id,
          "sourceLayerFields": "*"
        }
      }
    }
  ]
}

view_flc.manager.add_to_definition(definition_to_add)

 

  

After this step, you can apply your viewDefinitionQuery as usual using update_definition.

0 Kudos
MatthieuANDRE3
New Contributor II

thank you very much, I only have one table out.
I'm now trying to filter the visible and non-visible fields using the following code but it doesn't seem to work. I have all the output fields!

 

from arcgis.features import FeatureLayerCollection, Table
url = "https://services6.arcgis.com/**unspecified**/arcgis/rest/services/udi_bfc_secheresse_v2/FeatureServer"
flc = FeatureLayerCollection(url, gis)

view_item = gis.content.create_service(name="tableOnly", is_view=True)
view_flc = FeatureLayerCollection.fromitem(view_item)

tbl_url = "https://services6.arcgis.com/**unspecified**/arcgis/rest/services/udi_bfc_secheresse_v2/FeatureServer/1"
tbl = Table(tbl_url, gis)
view_flds = tbl.properties.fields

vis_flds = [
    {"name": f"{f.name}", "visible": True}
    if f.name not in ["nomDepGest", "nomRegion", "QDeclarationTension", "QimpactQualQuant","QNonConformiteSecheresse", "parametreRestriction", "moyenGestionComplete", "impactQuantDist","impactQuantPress", "impactUDI", "observation", "editeur","pdfCount", "rs_contact", "name_contact", "tel_contact", "mail_contact", "validation_ARS"]
    else {"name": f"{f.name}", "visible": False}
    for f in view_flds
]

definition_to_add = {
  "layers": [],
  "tables": [
    {
      "name": flc.tables[0].properties.name,
      "type": "Table",
      "cacheMaxAge": 30,
      "description": "",
      "url": flc.tables[0].url,
      "adminLayerInfo": {
        "viewLayerDefinition": {
          "sourceServiceName": flc.service.properties.adminServiceInfo.name,
          "sourceLayerId": flc.tables[0].properties.id,
          "sourceLayerFields": vis_flds
        }
      }
    }
  ]
}

view_flc.manager.add_to_definition(definition_to_add)

 

what's wrong with my code?

If I want to generate a view with only the layer and not the table, how do I set definition_to_add?

thanks

0 Kudos