Searchcursor to Return Distinct Place IDs Where FK having 1:M relationship with Place ID in Predefined List

641
6
Jump to solution
02-22-2019 11:02 AM
BenjaminBarton
New Contributor III

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.

0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

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)
>>> 

View solution in original post

6 Replies
DanPatterson_Retired
MVP Esteemed Contributor

Your indentation needs to be corrected and line numbers can automatically be added if formatted

/blogs/dan_patterson/2016/08/14/script-formatting 

curtvprice
MVP Esteemed Contributor

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?

BenjaminBarton
New Contributor III

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)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
JoshuaBixby
MVP Esteemed Contributor

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)
>>> 
BenjaminBarton
New Contributor III

I was close. I had tried this:

whereExp = "OBJECTID IN ".format(objectIDList)

Thank you! 

BenjaminBarton
New Contributor III

Ran this by the team and they stated that the objectid we will be using using is not registered with the geodatabase. 

0 Kudos