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):
<SPAN class="keyword token">SELECT</SPAN> <SPAN class="keyword token">DISTINCT</SPAN>
v<SPAN class="punctuation token">.</SPAN>place_id
<SPAN class="keyword token">FROM</SPAN>
mView v
<SPAN class="keyword token">INNER</SPAN> <SPAN class="keyword token">JOIN</SPAN> objectIDFilter F
<SPAN class="keyword token">ON</SPAN> v<SPAN class="punctuation token">.</SPAN>objectid <SPAN class="operator token">=</SPAN> <SPAN class="number token">f</SPAN><SPAN class="punctuation token">.</SPAN>objectid<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
This is wrong but it's all I've got...
objectIDFilter <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="number token">12345</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="number token">54321</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="number token">55555</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="number token">44444</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="number token">33333</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="number token">22222</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># Set overwrite to true, for in memory table view</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>overwriteOutput <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN>
tableRegistry <SPAN class="operator token">=</SPAN> os<SPAN class="punctuation token">.</SPAN>path<SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>conn<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"myView"</SPAN><SPAN class="punctuation token">)</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'objectid'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'place_id'</SPAN><SPAN class="punctuation token">]</SPAN>
uniquePlaceIDs <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>tableRegistry<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
occurances <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> set<SPAN class="punctuation token">(</SPAN>objectIDFilter<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>occurances<SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token">#icount = occurances.count(i)</SPAN>
<SPAN class="comment token">#print("{} has {} records".format(i, icount))</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>Thanks in advance for any assistance that can be provided.
