AnsweredAssumed Answered

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

Question asked by bbarton-esristaff on Feb 22, 2019
Latest reply on Feb 28, 2019 by bbarton-esristaff

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.

Outcomes