Greetings, I'm new to GIS and new to Python. Trying to return a distinct array (or list or dictionary) of place_ids where objectid matches predefined list (will eventually be provided by user). There is a 1:m relationship between objectids and place_ids and script currently only returns objectids when what I need is distinct place_ids. I'm essentially trying to extract normalized key elements (and eventually attributes) from a table that is not normalized. Note, I could bring in all rows for objectid and place_id from my view and then compare or join on my objectIDFilter list, but that would be millions of rows so how can I apply the filter as I'm calling data from my tableRegistry to limit the data I am bringing in to just those tuples that match against my list? To demonstrate using SQL, the end state I am after and how I am thinking through the logic in my head, it would be written as (assuming my list was a table or view, which its not):
SELECT DISTINCT
v.place_id
FROM
mView v
INNER JOIN objectIDFilter F
ON v.objectid = f.objectid
This is wrong but it's all I've got...
objectIDFilter = (12345,54321,55555,44444,33333,22222,...)
# Set overwrite to true, for in memory table view
arcpy.env.overwriteOutput = True
tableRegistry = os.path.join(conn, "myView")
fields = ['objectid','place_id']
uniquePlaceIDs = {}
with arcpy.da.SearchCursor(tableRegistry, fields) as cursor:
occurances = [row[0] for row in cursor]
for i in set(objectIDFilter):
print(occurances)
#icount = occurances.count(i)
#print("{} has {} records".format(i, icount))
Thanks in advance for any assistance that can be provided.
Solved! Go to Solution.
Your current code is creating a string values for ObjectIDs, which won't work (and you are missing a right parenthesis on the end of line #12)
>>> whereExp = "objectid IN ({})".format(",".join(["'{}'".format(v) for v in objectIDList]))
>>> print(whereExp)
objectid IN ('55555','44444','33333','22222')
>>>
The following should address your WHERE clause:
>>> whereExp = "OBJECTID IN {}".format(objectIDList)
>>> print(whereExp)
OBJECTID IN (55555, 44444, 33333, 22222)
>>>
Your indentation needs to be corrected and line numbers can automatically be added if formatted
objectid matches predefined list (will eventually be provided by user).
OBJECTIDs and FIDs are system assigned and cannot be trusted to not be changed if a dataset is edited or modified in any way. Users should not be providing them.
Can you re-state your use case in terms of an application need?
This is actually more of an exercise on my part for the time being to get familiar with arcpy searchcursors for extracting data from a relational database. Just assume for the purpose of this exercise that objectid is an appropriate entity key for matching against relational attributes and I have a list of keys that I want to pass into the where clause when I call for data from the database. I think I will try to construct a SQL where clause as a parameter of the search cursor and format my list as a tuple; WHERE key in <comma separated string>. Something like example below but syntactically correct.
objectIDList = (55555,44444,33333,22222)
# Set overwrite to true, for in memory table view
arcpy.env.overwriteOutput = True
tableRegistry = os.path.join(conn, "myView")
fields = ['objectid', 'place_id']
##Use SearchCursor with list comprehension to return a
#### #unique set of placed_id values from place_id field
whereExp = "objectid IN ({})".format(",".join(["'{}'".format(v) for v in objectIDList])
values = [row[0] for row in arcpy.da.SearchCursor(tableRegistry, 'place_id', whereExp)]
uniqueValues = set(values)
print(uniqueValues)
Your current code is creating a string values for ObjectIDs, which won't work (and you are missing a right parenthesis on the end of line #12)
>>> whereExp = "objectid IN ({})".format(",".join(["'{}'".format(v) for v in objectIDList]))
>>> print(whereExp)
objectid IN ('55555','44444','33333','22222')
>>>
The following should address your WHERE clause:
>>> whereExp = "OBJECTID IN {}".format(objectIDList)
>>> print(whereExp)
OBJECTID IN (55555, 44444, 33333, 22222)
>>>
I was close. I had tried this:
whereExp = "OBJECTID IN ".format(objectIDList)
Thank you!
Ran this by the team and they stated that the objectid we will be using using is not registered with the geodatabase.