+ Reply to Thread
Results 1 to 11 of 11

Thread: Definition Query

  1. #1
    Stephen Kruzik
    Join Date
    Dec 2010
    Posts
    30
    Points
    12
    Answers Provided
    2


    0

    Default Definition Query

    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 Code:
    # 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
    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!

  2. #2
    Bruce Nielsen
    Join Date
    Jan 2011
    Posts
    299
    Points
    130
    Answers Provided
    21


    2

    Default Re: Definition Query

    I think you've just about got it. Try this:
    Code:
    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.
    GIS Specialist
    USDA-Natural Resources Conservation Service

  3. #3
    Jeffrey Barrette

    Join Date
    Oct 2009
    Posts
    714
    Points
    201
    Answers Provided
    64


    1

    Default Re: Definition Query

    Or you can also do it with double quotes. Either way, you'll need to use an escape character.

    Code:
    lyr.definitionQuery = "\"CellSiteID\" = '%s'" % pageName
    Jeff

  4. #4
    Stephen Kruzik
    Join Date
    Dec 2010
    Posts
    30
    Points
    12
    Answers Provided
    2


    0

    Default Re: Definition Query

    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 Code:
    def DQ(name, operator, value):
        sql = "\"" + name + "\"" + operator + "'" +  value + "'"
        return sql
    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.
    Last edited by smkruzik; 04-23-2012 at 10:29 AM. Reason: 1 more thing...

  5. #5
    Curtis Price

    Join Date
    Oct 2009
    Posts
    1,798
    Points
    875
    Answers Provided
    127


    1

    Default Re: Definition Query

    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 AddFieldDelimiters function. I also added a tweak to add quotes around strings and handle IS NULL constructions:


    Code:
    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
    Last edited by curtvprice; 04-23-2012 at 12:28 PM.

  6. #6
    Stephen Kruzik
    Join Date
    Dec 2010
    Posts
    30
    Points
    12
    Answers Provided
    2


    0

    Default Re: Definition Query

    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 Code:
    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()
    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.

  7. #7
    Curtis Price

    Join Date
    Oct 2009
    Posts
    1,798
    Points
    875
    Answers Provided
    127


    0

    Default Re: Definition Query

    Quote Originally Posted by smkruzik View Post
    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)
    Last edited by curtvprice; 04-30-2012 at 12:36 PM.

  8. #8
    Stephen Kruzik
    Join Date
    Dec 2010
    Posts
    30
    Points
    12
    Answers Provided
    2


    0

    Default Re: Definition Query

    when using print repr(sql),

    HTML Code:
    >>> print repr(sql)
    u'"CellSiteID"=\'1202\''
    >>>
    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.

  9. #9
    Curtis Price

    Join Date
    Oct 2009
    Posts
    1,798
    Points
    875
    Answers Provided
    127


    0

    Default Re: Definition Query

    I highly recommend putting spaces in there.
    Last edited by curtvprice; 05-02-2012 at 10:22 AM.

  10. #10
    Stephen Kruzik
    Join Date
    Dec 2010
    Posts
    30
    Points
    12
    Answers Provided
    2


    0

    Default Re: Definition Query

    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.

  11. #11
    Curtis Price

    Join Date
    Oct 2009
    Posts
    1,798
    Points
    875
    Answers Provided
    127


    0

    Default Re: Definition Query

    Quote Originally Posted by smkruzik View Post
    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

    Code:
    check = LayerSite.supports("DEFINITIONQUERY")
    Might want to run this one by support.

+ Reply to Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts