Arcpy / Modelbuilder

[StevenHougaard]

Greetings,

Let me start by saying, i know very little about GIS in general, so please bear with me. I am just the IT support guy that somehow got involved in some assignments for our GIS department, sorry for the long explanation in advance.

The GIS departments are looking for some tools to help ease the daily jobs, for instance simplifying the search function, so they can search for an address and zoom to said address, i have managed to fulfill this assignment with ArcToolbox and ArcPy and are now working on another one.

I have a ~50.000 rows database with ~25 columns / fields. There is 2 things i need from this, which is "ELAVSNAVN" (Which is similar to a company) and "MATRNR" (which is a lot number, not entirely sure that it is the correct english technical term). The MATRNR is unique to ELAVNSNAVN but not in general, IE. a MATRNR could be the same for 2 companies.

The assignment is similar to the first one i solved, however this needs to be done with dropdown boxes.

Top box will be populated with the unique values of ELAVSNAVN and then the bottom box will be populated with the unique values of MATRNR, like so:

So let's say we have the following database

    ELAVSNAVN     MATRNR

1. Option 1         A
2. Option 1         B
3. Option 2         A
4. Option 3         A
5. Option 2         B
6. Option 3         B
7. Option 1         C

The top box would show Option 1, Option 2, Option 3 and the bottom box will be empty.

If i then choose Option 2, the bottom box will be populated with A and B, and i can then choose for instance B and it would then know it is row 5.

I have managed to make a python script and arctoolbox, that almost does this (ran into some unicode problems, but that's not the issue right now, can use a different column which is like the ID of the ELAVSNAVN for testing purposes), but it is done by making 2 lists and then combining into a dictionary, and while it works it kinda eats all resources, it feels like it keeps going through the entire database, multiple times. I think it's a horrible inefficient way of doing it. I think it can be done via SQL expressions somehow.

I have been looking into ModelBuilder, but i have a hard time finding info pertaining this exact issue.

Just looking for some pointers on what tools / direction you guys would go with this, as i am a tad stuck.

Using ArcMap / Catalog 10.3.1 if that is relevant.

[DanPatterson_Retired]

If the data do not change often with respect to the unique values in each column, then perhaps you can construct the dictionary once (or occasionally), save it to a file and read it when necessary.

Since you are relatively new to the gis thing, I would normally recommend that you incorporate a search cursor to read the values from a column/field (not the whole file), make a 'set' (python set) from the values, then sort it.  This will give you the unique values.  Don't know how fast it would be

Numpy offers that capability as well and simply involves reading a table's column then getting the unique values in a vectorized fashion (the numpy part is blazingly fast, and the arcpy interface is relatively speedy)

Perhaps some code snippet as to how you are reading and populating tool's parameters comboboxes might twig some other suggestions or assistance with your existing code.

[XanderBakker · Accepted solution]

Here is an example blog post that shows how to implement cascading drop down boxed using the validation of a toolbox script: https://community.esri.com/people/xander_bakker/blog/2016/07/19/implementing-cascading-drop-down-lis... 

[StevenHougaard]

Thanks for the very fast replies.

I am heading off for today but will have a look at the link from Xander Bakker, looks very promising.

Dan Patterson‌ This was the raw script, i don't have access to the tool validation part right now, but it was adapted from this. It is a good idea with the static file instead. I will pursue this if Xander's example doesn't pan out.

EDIT: New code in next post

[StevenHougaard]

The script itself:

# -*- coding: utf-8 -*-
# ---------------------------------------------------------------------------
# ModelBuilder.py
# Created on: 2018-01-22 10:32:53.00000
#   (generated by ArcGIS/ModelBuilder)
# Description: 
# ---------------------------------------------------------------------------

# Import arcpy module
import arcpy



# Local variables:
lyr = "ArcGIS_Drift_Grundkort_DB.GISGRUNDKORT.Jordstykke"
mxd = arcpy.mapping.MapDocument('CURRENT')
df = arcpy.mapping.ListDataFrames(mxd, "Layers") [0]
elav = "'" + arcpy.GetParameterAsText(0) + "'"
matrnr = "'" + arcpy.GetParameterAsText(1) + "'"
whereClause = "ELAVSNAVN = %s AND MATRNR = "'%s'""  % (elav, matrnr)
 

# Process: Select Layer By Attribute
arcpy.SelectLayerByAttribute_management(lyr, "NEW_SELECTION", whereClause)

#Zoom to selected feature
df.zoomToSelectedFeatures()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Which works just fine, however i'd like the autofill functionality via the validator from Xander Bakker's blog post, however my brain cannot fathom it properly.

Using a searchcursor i think it would be something like this for the top dropdownbox: 

fc = "ArcGIS_Drift_Grundkort_DB.GISGRUNDKORT.Jordstykke"
cursorElav = arcpy.da.SearchCursor(fc, "ELAVSNAVN", sql_clause=('DISTINCT', None)):
  
#Elavsnavn
self.params[0].enabled = True  
elavFilter = self.params[0].filter  
elavFilter.list = sorted(list(set([cursorElav])))  
elavListe = elavFilter.list[0]  
self.params[0].value = elavListe  ‍‍‍‍‍‍‍‍‍

This is in the toolvalidator under the def initializeParameters(self):

Can't even get this part to work, it's the line 7 where i don't get the code "muni for comu, muni in dct_com_muni.items", i get the last part with the imported dict, but i am lost on the "muni for comu, muni in" part. I have tried some variations of that code, like "x for row".

Then i'd make the 2nd dropdownbox, which would have a searchcursor with SELECT * FROM MATRNR WHERE ELAVSNAVN = '%s" % arcpy.GetParameterAsText(0) 

or a reference to the topbox like elavListe instead of the arcpy.GetParameterAsText(0).

It might be horrible wrong, but so far it feels like the right path compared to the old code which was horribly inefficient.

Best regards

Steven

[XanderBakker]

Concerning the first part of you code, the tool itself, I'm a bit confused if "elav" is a value or a string. I would assume it is a value and if it is, this would change the where clause. Something like this is possible:

import arcpy

# Local variables:
lyr = "ArcGIS_Drift_Grundkort_DB.GISGRUNDKORT.Jordstykke"  # must be in the active dataframe
mxd = arcpy.mapping.MapDocument('CURRENT')
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
elav = arcpy.GetParameterAsText(0)
matrnr = arcpy.GetParameterAsText(1)
whereClause = "ELAVSNAVN = {0} AND MATRNR = '{1}'".format(elav, matrnr)
arcpy.AddMessage("whereClause:{0}".format(whereClause))

# Process: Select Layer By Attribute
arcpy.SelectLayerByAttribute_management(lyr, "NEW_SELECTION", whereClause)

#Zoom to selected feature
df.zoomToSelectedFeatures()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I changed a few things is the code (for instance there was a  space before [0] at the end of line 6).

For the validation, in order to make the tool more responsive and in case a user could be changing his mind various times by selecting different values from the drop down boxes, it might be better to read the values only one time from the featureclass and populate a list or dictionary, depending the specific functionality required. To create a sorted list of all the unique elements in a field you can use this:

lst_elav = sorted(list(set([r[0] for r in arcpy.da.SearchCursor(fc, ("ELAVSNAVN"))])))‍

Can you explain a bit more (show part of the data perhaps) in order to understand what type of dependencies you have between the drop down boxes and how this is represented in the data? 

In my case in the part that you mentioned "for comu, muni in dct_com_muni.items()", I guess you are referring to this line:

muni_filter.list = sorted(list(set([muni for comu, muni in dct_com_muni.items()])))  ‍

It is the part where it reads out a dictionary "dct_com_muni" that has the neighborhood (com) as key and the municipality (muni) as value. It reads out all the values for municipality (which could have been done by using dct_com_muni.values()) and creates a unique list of the municipality list. This is the first selection list and the highest level.

The second list should be populated depending on the selection from the first list. This happens on line 33:

 comu_filter.list = sorted(list(set([comu for comu, muni in dct_com_muni.items() if muni == municipio]))‍

Basically the same thing as the line explained before, but this time it only takes hose neighborhoods that are part of the selected municipality (hence the if statement at the end of the line)

[StevenHougaard]

Thank you very much for helping, sorry for the long answertime, been out with the flu. I will try with your code.

Cutout of data, the database is around 52000 rows:

ELAVSNAVN is a string that can contain danish letters like æøå. This is the name of property owners, there might be around 150-200 unique values. This is what needs to go in the top box.

MATRNR is a sequence of numbers and letters and can also contain danish letters like æøå (for example: "80aæ"). This is the code for the land lot, as you can see there is duplicates between the property owners (ELAVSNAVN) but they are unique within it's own property owner, in the above example there is Lemvig Bygrunde, and there will only be a single entry with the value "1" in MATRNR.

Bottom box should get populated with the values from MATRNR that corresponds to the ELAVSNAVN chosen in the top box.

EDIT: bigger list 

[XanderBakker]

I think it would be best to start with setting up the dictionary the will be used in the process. So you would want to create it initially (and only one time like this):

fc = r'your connection to ArcGIS_Drift_Grundkort_DB.GISGRUNDKORT.Jordstykke'
fld_key = 'ELAVSNAVN'
fld_val = 'MATRNR'

dct = {}
with arcpy.da.SearchCursor(fc, (fld_key, fld_val)) as curs:
    for row in curs:
        key = row[0]
        val = row[1]
        if key in dct:
            lst = dct[key]
            lst.append(val)
            lst = list(set(lst))
            dct[key] = sorted(lst)
        else:
            dct[key] = [val]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This will create a dictionary where your keys will be the unique values of the field ELAVNAVN and each key will have a list of values from the field MATRNR assign to it.

The list for the first combo box should be the keys: dct.keys()

When a selection is made in the first combobox this should trigger filling the second list using something like:

dct[selected ELEVNAVN]

... where "selected ELEVNAVN" should be replaced by the actual selected values from the list of ELEVNANV values.

[StevenHougaard]

Oh my lord yes! Finally got it working.

Thank you again Xander Bakker both for your help here and for the blog post you did, would never have been able to solve it without it.

I ended up making a dictionary file using JSON and made a script the GIS department can run when there is changes to the database (every 3 months).

Still need to finish it properly, needs to go on the network instead of a local dictionary, but that should be easy.

But for now it works as i wanted, select value in topbox, which has been populated by the dict keys, it empties bottombox and populates with dict key values.

Pasting code, should anyone else need something similar:

import arcpy
import json

#Import of elavDict.json
dictPath = "C:\elavDict.json"
dct = json.loads(open(dictPath).read())

class ToolValidator(object):
  """Class for validating a tool's parameter values and controlling
  the behavior of the tool's dialog."""

  def __init__(self):
    """Setup arcpy and the list of tool parameters."""
    self.params = arcpy.GetParameterInfo()

  def initializeParameters(self):
    """Refine the properties of a tool's parameters.  This method is
    called when the tool is opened."""

    #Elavsnavn
    self.params[0].enabled = True
    elavFilter = self.params[0].filter
    elavFilter.list = sorted(dct.keys())
    elavListe = elavFilter.list[0]
    self.params[0].value = elavListe

    self.params[1].enabled = True
    matrFilter = self.params[1].filter
    matrFilter.list = sorted(dct[self.params[0].value])
    matrListe = matrFilter.list[0]
    self.params[1].value = ""


    return

  def updateParameters(self):
    """Modify the values and properties of parameters before internal
    validation is performed.  This method is called whenever a parameter
    has been changed."""

    elavListe = self.params[0].value
    matrListe = self.params[1].value

    if all([elavListe != None, elavListe != '']):
        # elavListe is set
        lst_matrnr = sorted(dct[self.params[0].value])
        if matrListe not in lst_matrnr:
        # matrListe is not valid, default
            self.params[1].filter.list = lst_matrnr
            self.params[1].value = ''

        else:
            # matrListe not set, reset list matrListe
            self.params[1].filter.list = lst_matrnr

    else:
        # elavListe not set, blank matrListe
        self.params[1].value = ''

    return

  def updateMessages(self):
    """Modify the messages created by internal validation for each tool
    parameter.  This method is called after internal validation."""
    return
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[XanderBakker]

I am glad you could make it work and thank for posting back your code. Can you mark the post that answered the question as the correct answer? This way other users can find the solution more quickly in case they face a similar challenge. .