0

Im currently attempting to query a file geodatabase, where I want to find all the duplicate PID (sub for an OBJECTID), and then within each one of these matching groups of records, find where the LAND_SIZE_ACRES has increased over time (each row of the same PID represents land data of sequential years).

I have been using "Select By Attributes," where I've used this formula, except it doesnt seem to work on a file geodatabase:

[FIELD_NAME] In (SELECT [FIELD_NAME] FROM [TABLE_NAME] GROUP BY [FIELD_NAME] HAVING Count(*)>1 )

Therefore I need to be able to query each group of matching records, and then find the variation within said groups of records. I just dont know where to start.

Example data:

enter image description here

Very few of the records actually change in land_size_acres overtime, hence the picture with no variation.

2
  • Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Please check out our short tour to learn about our focussed Q&A format. I'd start by looking at the Summary Statistics tool.
    – PolyGeo
    Commented Apr 30, 2019 at 21:55
  • Your query syntax is for Access, not file geodatabase -- try removing the brackets
    – Vince
    Commented May 1, 2019 at 2:28

1 Answer 1

0

I could be wrong but I dont think your query is possible when data is in a file geodatabase, see SQL reference for query expressions used in ArcGIS the Subqueries part:

File geodatabases provide the limited support for subqueries explained in this section, while personal and ArcSDE geodatabases provide full support

You can use python though:

import arcpy
from collections import defaultdict

layer = 'new_layer' #Change to match your data. If outside ArcMap, create the layer using MakeFeatureLayer
fields = ['PID','SIZE'] #Change


d = defaultdict(list)
with arcpy.da.SearchCursor(layer,fields) as cursor:
    for pid,size in cursor:
        d[pid].append(size)

#d is now a dictionary with pid as key, and corresponding sizes as values:
#defaultdict(<class 'list'>, {1: [1.0, 1.0, 1.1, 1.1], 2: [3.0, 3.0, 3.0], 3: [4.0, 3.0, 5.0]})

increasing_pids = []
for key, values in d.items(): #d.iteritems() in python2/ArcMap
    if len(set(values))>1: #If all sizes are the same, length of the set will be 1
        if any(i<j for i,j in zip(values, values[1:])): #If size is ever increasing
            increasing_pids.append(key) #then store PID in list

if len(increasing_pids)==1: #If only one item in list, append one dummy value or tuple below will cause syntax error, for example look like: (123,)
    increasing_pids.append(-999)

sql = "{0} IN{1}".format(arcpy.AddFieldDelimiters(datasource=layer, field=fields[0]), tuple(increasing_pids))
arcpy.SelectLayerByAttribute_management(in_layer_or_view=layer,
                                        where_clause=sql)

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.