Using Defintion Query in a Python Script

[ChadFoster]

I have a script that is using a parcel feature class from SDE.  This parcel feature has Right-of-Way (ROW) information.  I would like to use a definition query so that when I do a select by location, the ROW features are not in the selection.

for layer in arcpy.mapping.ListLayers(mxd, "*"):
     if layer.name == "DOUGLAS_GIS.GISDATA.Parcel_Boundary":
          layer.definitionQuery = "'TAG' IS NOT \'ROW %\'"

Could someone please look at this syntax and let me know if there is something wrong?

This is the definition query that I use in an MXD.

TAG NOT LIKE 'ROW %'



Thanks,

Chad

[DouglasSands]

Chad,

Looking at your query, the syntax seems incorrect. Have you checked out this article http://resources.arcgis.com/en/help/main/10.1/index.html#//00s50000002t000000 or this one http://resources.arcgis.com/en/help/main/10.1/index.html#//002z0000001r000000?

I haven't applied queries in this way, but just looking at the syntax, you are wrapping both TAG and ROW % in single quotes, so the query that is being sent to the layer looks like this:

'TAG' IS NOT 'ROW %'

Based on this (from the second link):

"The field delimiters used in a SQL expression differ depending on the format of the queried data. For instance, file geodatabases and shapefiles use double quotes (" "), personal geodatabases use square brackets ([ ]), and ArcSDE geodatabases don't use field delimiters. The AddFieldDelimiters function can take away some of the guess work in ensuring that the field delimiters used with your SQL expression are the correct ones. The following example expands on the above example to add the proper field delimiters for the SQL expression."

In which case the string TAG will never be equal to the string ROW %

You want the query python generates to look exactly like what you are using in the MXD, so I think you might want

layer.definitionQuery = "TAG IS NOT \'ROW %\'"

Without the data though, can't test this. Hope this helps.
-Doug

[DouglasSands]

If you only need the distance, this may be fairly straight forward. Having looked into this before (trying to move points to the closest edge segment of a polygon) it is however a bit more complicated than at face value.

But this article here http://math.ucsd.edu/~wgarner/math4c/derivations/distance/distptline.htm is a great place to start. The basic equation is given towards the bottom:

Shortest distance from y=m*x + b to (x1, y1):

|y1 - m*x1 - b| / root(m^2 + 1)

Good luck!

[T__WayneWhitley]

Good info, and I'd just like to add escaping quotes can be excruciating, so maybe do it this way:

layer.definitionQuery = "{0} NOT LIKE '{1} %'".format(arcpy.AddFieldDelimiters(layer, 'TAG'), 'ROW')

To further explain, the query expression is contained in:

"{0} NOT LIKE '{1} %'"

{0} and {1} are variables for substitution using the '.format' function, so...

{0} is what is evaluated by arcpy.AddFieldDelimiters(layer, 'TAG')
{1} is the string 'ROW'

...which yields a query string (for an SDE source):
"TAG" NOT LIKE 'ROW %'


You have to be careful with the multiple character wildcard, and if that query yields unexpected results, then you could simply 'lop off' that nonsense with a minor adjustment and include the 'not equal to' operator:

layer.definitionQuery = "{0} <> '{1}'".format(arcpy.AddFieldDelimiters(layer, 'TAG'), 'ROW')

...which of course the query string would be (for an SDE source):
"TAG" <> 'ROW'


Hope that clears things up some...this is a fairly powerful technique when it's right - always a pain to trip on just the syntax of it.

Enjoy,
Wayne


EDIT - A final comment about SQL syntax, note that you have a space in your value, 'ROW %' --- for me this does not filter any values out if I have no characters after the 'W'; however, using this value, 'ROW%' performs as expected.  So a rule of thumb to follow (and it does look like you have your bases covered) is to make certain your SQL is sound i.e. returning what you intended (and only what you intended).  Otherwise code your field values a little 'tighter', but that's really a separate topic of data integrity.

[ChadFoster]

Wayne,

Thanks for the info, I will give it a try.

In the meantime, instead of using the definiton query I just added another select statement after the initial select, "REMOVED_FROM_SELECTED" the ROW's.  Should have thought of that first.

Thanks,

Chad

[T__WayneWhitley]

That'll be fine, although really not as efficient...you get a point regardless for ingenuity.  Still, if you run across this type thing again, a def query could be your ticket and is worth the practice.  Also, if you're just interested in working with in-memory layers rather than messing with your ArcMap TOC layer objects, you could use Make Feature Layer with a query (or copy to in_memory workspace), etc...and you're back to the need for query syntax again.  Nice to know that there are various legitimate options!

Glad you got it!

Enjoy,
Wayne