10 Replies Latest reply on May 2, 2012 10:26 AM by curtvprice

    Definition Query

    smkruzik
      I know this probably the upteenth time a question like this has been asked, but my non-programming mind can't seem to wrap its head around it..

      I'm using data driven pages to plot towersites in an area.  Each page goes to a new site based on it's SiteID.  I only want the map to show the site that I'm currently on.  Presently, it shows all of the sites, some of which are very close to the current one.  I ran through a stand alone script and it went through fine without any errors, but it didn't return the definition query, so the exported png still had all the sites on each map.

      what am I doing wrong? 

      [HTML]# Workspace
      import os
      import arcpy
      import arcpy.mapping

      mxd = arcpy.mapping.MapDocument("C:\GIS Data\Schuylkill\Validations.mxd")
      mapLyr = arcpy.mapping.ListLayers(mxd, "Cell_Sites")[0]
      carryElem = arcpy.mapping.ListLayoutElements(mxd, "TEXT_ELEMENT","Carriers")[0]

      # activate data driven pages
      ddp = mxd.dataDrivenPages
      pageNum = ddp.currentPageID
      print pageNum
      contents = range(1,ddp.pageCount)
      print contents

      # set active view to first ddp, then iterate through each page
      while pageNum in contents:

          print "we got to the while statement"

          # single out the one you are working on
          pageName = mxd.dataDrivenPages.pageRow.CellSiteID
          Exporter = mxd.dataDrivenPages.pageRow.LDTProvider
          df = arcpy.mapping.ListDataFrames(mxd, "Sites")[0]
          CurrentSite = arcpy.mapping.ListLayers(mxd, "Cell_Sites", df)[0]
          CurrentField = "CellSiteID"
          arcpy.MakeFeatureLayer_management("F:\GIS Data\Schuylkill County\Schuylkill MasterData.gdb\Validations\Cell_Sites", "IndexSites")

          for lyr in arcpy.mapping.ListLayers(mxd):
              if lyr.name == CurrentSite:
                  lyr.definitionQuery = '"CellSiteID" = %s' % pageName[/HTML]

      There is more after this, but for space conservation, I only pasted up to the def. query

      As an aside, if you get me to where I need to be with this, and it works, I'll happily name a variable after you in my next script! :)
        • Re: Definition Query
          bruce.nielsen
          I think you've just about got it. Try this:
          lyr.definitionQuery = '"CellSiteID" = \'%s\'' % pageName

          The value in a definition query needs to be surrounded by single quotes. The backslashes tell Python to use the next character as part of the string.
          • Re: Definition Query
            jbarrette-esristaff
            Or you can also do it with double quotes.  Either way, you'll need to use an escape character.

            lyr.definitionQuery = "\"CellSiteID\" = '%s'" % pageName
            


            Jeff
            • Re: Definition Query
              smkruzik
              thanks all!

              it's working now.

              and for anyone interested, since I know I'm going to constantly forget how many ",',/ I need, and where, I saved a python module that has the syntax pre-set.  It's just a single function, so cut and paste as needed.  All you need is the field, operator, and value. Feel free to use if it helps

              [HTML]def DQ(name, operator, value):
                  sql = "\"" + name + "\"" + operator + "'" +  value + "'"
                  return sql[/HTML]

              just note that the value field is defined for a string, not an integer.  If you use this querying integers, you'll need to get rid of the 2 single quotes at the end.
              • Re: Definition Query
                curtvprice
                I saved a python module that has the syntax pre-set


                What a neat idea! Your function can be easily tweaked up to work with all data sources (mdb's use brackets) using the arcpy [URL=http://help.arcgis.com/en/arcgisdesktop/10.0/help/000v/000v0000004n000000.htm]AddFieldDelimiters[/URL] function. I also added a tweak to add quotes around strings and handle IS NULL constructions:


                def BuildQuery(table, field, operator, value=None):
                    """Generate a valid ArcGIS query expression
                    
                    arguments
                    
                      table - input feature class or table view
                      field - field name (string)
                      operator - SQL operators ("=","<>", etc)
                         "IS NULL" and "IS NOT NULL" are supported
                      value - query value (optional)
                      
                    examples
                    
                      >>> BuildQuery("x.dat","FIELD1","=", "spam")
                      u'"FIELD1" = \'spam\''
                      >>> BuildQuery("x.dat","FIELD1",">",42)
                      u'"FIELD1" > 42'
                      BuildQuery(r"E:\tables.mdb\table1","PwsID","IS NOT NULL")
                      u'[PwsID] IS NOT NULL'
                    """
                    # Use correct field delimeter (depends on table type)
                    # For 9.3, use "gp" instead of "arcpy" in the following line
                    qfield=arcpy.AddFieldDelimiters(table,field)
                
                    # tweak value delimeter for different data types
                    if type(value) == type("string"):
                        # add single quotes around string values
                        qvalue = "'" + value + "'"
                    elif value == None:
                        # drop value when not specified (used for IS NULL, etc)
                        qvalue = ""
                    else:
                        # numeric values are fine unmodified
                        qvalue = value
                    
                    sql = "%s %s %s" % (qfield,operator,qvalue)
                    return sql.strip()
                


                A few helpful references for the good of the order

                Arc 10.0 help:

                Building a query expression

                Specifying a query in Python

                ArcGIS Map Automation forum
                • Re: Definition Query
                  smkruzik
                  ok, so I thought I had this, but after running through it I discovered a problem and can not for the life of me figure out what's wrong.

                  The definition query itself is running through fine, but it doesn't produce anything.  There is no change to the featurelcass.  After lots and lots of digging, I figured out it's within the defquery section of Arcpy itself.

                  I turned on Python's debugger, and when it gets to the def. query, it throws me an "AttributeError: 'unicode' object has no attribute '_arc_object'"

                  I'm really unsure what it's looking for.  If I change it to string, it gives the same error, just with 'str' instead of unicode.  Is my featureclass not being referenced properly, does it not like variables within the query?

                  here's the snippet that I'm running through...

                  [HTML]
                  while pageNum in contents:
                      pageName = mxd.dataDrivenPages.pageRow.CellSiteID # data driven page name
                      Exporter = mxd.dataDrivenPages.pageRow.LDTProvider # which carrier you are on right now
                      df = arcpy.mapping.ListDataFrames(mxd, "Sites")[0] # there's 2 data frames, so call the 1st one
                      CurrentSite = arcpy.mapping.ListLayers(mxd,"*Cell_Sites*",df) # get the list of current layers
                      LayerSite = CurrentSite[0] # pull Current site out of the list as its own layer
                      CurrentField = "CellSiteID" # field used to isolate the F.class
                      Othercarriers = arcpy.MakeFeatureLayer_management("F:\GIS Data\Schuylkill County\Schuylkill MasterData.gdb\Validations\Cell_Sites", "IndexSites")
                      sql = dquery.DQ(CurrentField,"=",pageName)
                      check = LayerSite.supports("DEFINITIONQUERY")
                      print check
                      LayerSite.definitionQuery = str(sql)
                      arcpy.RefreshActiveView()[/HTML]

                  the variable for sql is using the function I defined in my previous post (though I definitely like the newer, more robust version).  Some other baseinfo: I have a featureclass called "Cell_Sites".  The CellsiteID is the field driving the dd pages.  For each map, I only want the current cell site displayed on the map, and no others.  Any help is greatly appreciated.  I'm at my wits end here.
                  • Re: Definition Query
                    curtvprice
                      If I change it to string, it gives the same error, just with 'str' instead of unicode.


                    Unless you're using international characters, string and unicode are really the same thing.

                    What's the contents of the sql variable? The best way to get an exact story is to use repr:

                    print repr(sql)
                    • Re: Definition Query
                      smkruzik
                      when using print repr(sql),

                      [HTML]>>> print repr(sql)
                      u'"CellSiteID"=\'1202\''
                      >>> [/HTML]

                      in the Python Shell window.

                      so the variables are definitely calling correctly, as that's what I expect to see.

                      that line is then getting passed on to the definition query itself.
                      • Re: Definition Query
                        curtvprice
                        I highly recommend putting spaces in there.
                        • Re: Definition Query
                          smkruzik
                          no dice.  It's still throwing the same error.  I have to believe it was simply syntax, then it would have still put it in, and just throw up a "failed to draw: invalid sql reference" error.

                          I actually tried up to this point with ArcMap open.  I changed the mxd to CURRENT, and when I got to the def query, again, it went through without giving any errors, but when I click on the featureclass, the def. query box is empty as though nothing happened.

                          This is actually why I went into the python debugger and stepped through the Arcpy module itself.  That is where I found the "AttributeError: object has no attribute '_arc_object'"  error being called.
                          • Re: Definition Query
                            curtvprice
                              That is where I found the "AttributeError: object has no attribute '_arc_object'"  error being called.


                            Looking at the error message, I'm wondering if it's recognizing your layer LayerSite - though you'd think it would fail on your check

                            check = LayerSite.supports("DEFINITIONQUERY")


                            Might want to run this one by support.