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

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

BenjaminBarton — Sat, 11 Dec 2021 12:40:21 GMT

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.

Re: Searchcursor to return distinct place ids where object id in predefined list

DanPatterson_Retired — Fri, 22 Feb 2019 19:11:21 GMT

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

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

Re: Searchcursor to Return Distinct Place IDs Where Object ID in Predefined List

curtvprice — Sat, 23 Feb 2019 04:16:30 GMT

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?

Re: Searchcursor to Return Distinct Place IDs Where Object ID in Predefined List

BenjaminBarton — Sat, 11 Dec 2021 12:40:23 GMT

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

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

JoshuaBixby — Sat, 11 Dec 2021 12:40:26 GMT

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

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

BenjaminBarton — Mon, 25 Feb 2019 17:24:50 GMT

I was close. I had tried this:

whereExp = "OBJECTID IN ".format(objectIDList)

Thank you!

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

BenjaminBarton — Thu, 28 Feb 2019 19:09:27 GMT

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