nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Select records with most recent date for groups of points

JasonFultz

Hi all,

I am working with a feature class (sample_Locations) in an ArcSDE Geodatabase and my table has a "Point" field and a "Date" field, the field types are string and date, respectively. There are over 2000 features in the table and the point ID's are not unique, there are only ~100 point ID's, so there may be many points with the same Point ID. I am trying to select records from the table with the most recent date for sets of values, where the field "Point" is the field that contains the values that define groups.

I have tried using the following subquery, but it did not select the correct records. It selected a very random assortment of features.

[Date] in (SELECT max( [Date] ) FROM Sample_Locations GROUP BY [Point])

Can anyone provide help for performing this selection using a Python script? Ideally, I would like to select the features and export these selected features to a standalone excel table.

Any help would be greatly appreciated!!

Jason

Find more posts tagged with

Accepted answers

All comments

blake.terhune

You could use the dissolve geoprocessing tool with Point as the dissolve field and and specify Date as the statistics field with the MAX statistic type.

EDIT:

Since you just want a flat file, you can save the dissolve output to memory, make a table view, export it to Excel, then clean up the mess.

JoshuaBixby

Since you are using an enterprise geodatabase, it is fairly straightforward using SQL NOT EXISTS. See https://community.esri.com/thread/169714 for example code.

JoshuaBixby

Replace fc in the following with the actual name of the table:

qry <SPAN class="operator token">=</SPAN> <SPAN class="string token">"NOT EXISTS (SELECT 1 FROM fc f WHERE fc.point = f.point AND f.date > fc.date)"</SPAN>&nbsp; 
arcpy<SPAN class="punctuation token">.</SPAN>SelectLayerByAttribute_management<SPAN class="punctuation token">(</SPAN>layerName<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"NEW_SELECTION"</SPAN><SPAN class="punctuation token">,</SPAN> qry<SPAN class="punctuation token">)</SPAN> <SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Given that "Date" can be both a data type and function depending on the languages involved, I strongly discourage the use of it as a field name. If you haven't run into issues already, I expect you eventually will run into some. Also, "Point" is typically thought of in a spatial sense in GIS systems. Having a text field named Point and storing non-spatial values in it, might create some confusion or usability issues down the road.

JoshuaBixby

Is using Python an option or does it have to be SQL-based? There are Python-based solutions to this question as well.

JasonFultz

Hi Joshua, Thank you for the reply. I would actually prefer to use a Python script as opposed to an SQL query, if possible.

I will give the SQL query a try and let you know the result, as I am away from the data at the moment.

JoshuaBixby

There are several ways to go about this using Python, some are straight Python and others mix Python with some basic SQL for sorting the data set first.

One Python approach that avoids sorting the data first relies on using dictionaries to store and compare values:

lyr <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># A layer or tableview object, usually retrieved using listing methods</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># in arcpy.mapping</SPAN>

<SPAN class="keyword token">from</SPAN> collections <SPAN class="keyword token">import</SPAN> defaultdict<SPAN class="punctuation token">,</SPAN> namedtuple

defaultdate <SPAN class="operator token">=</SPAN> datetime<SPAN class="punctuation token">.</SPAN>datetime<SPAN class="punctuation token">(</SPAN><SPAN class="number token">1000</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN>
record <SPAN class="operator token">=</SPAN> namedtuple<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"record"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"oid date"</SPAN><SPAN class="punctuation token">)</SPAN>
max_date <SPAN class="operator token">=</SPAN> defaultdict<SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">lambda</SPAN><SPAN class="punctuation token">:</SPAN> record<SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN> defaultdate<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>lyr<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"OID@"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Point"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Date"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cur<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> oid<SPAN class="punctuation token">,</SPAN> point<SPAN class="punctuation token">,</SPAN> date <SPAN class="keyword token">in</SPAN> cur<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> max_date<SPAN class="punctuation token">[</SPAN>point<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>date <SPAN class="operator token"><</SPAN> <SPAN class="punctuation token">(</SPAN>date <SPAN class="operator token">or</SPAN> defaultdate<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; max_date<SPAN class="punctuation token">[</SPAN>point<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> record<SPAN class="punctuation token">(</SPAN>oid<SPAN class="punctuation token">,</SPAN> date<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
oids <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>rec<SPAN class="punctuation token">.</SPAN>oid <SPAN class="keyword token">for</SPAN> rec <SPAN class="keyword token">in</SPAN> max_date<SPAN class="punctuation token">.</SPAN>values<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">if</SPAN> rec<SPAN class="punctuation token">.</SPAN>oid<SPAN class="punctuation token">]</SPAN>
lyr<SPAN class="punctuation token">.</SPAN>setSelectionSet<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"NEW"</SPAN><SPAN class="punctuation token">,</SPAN> oids<SPAN class="punctuation token">)</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>‍</SPAN></SPAN>

defaultdicts are handy and make writing and reading code much easier in certain situations, like this one. I find namedtuples are handy as well, and they make working with the code much easier. For example, I find max_date[point].date much clearer than max_date[point][1].

Although your data appears to have date values for every record, the code above was written to handle records not having date values. Unless you know the date field in the data set doesn't allow NULL, it is good to have your code accommodate the possibility in case one does show up.

If I find some time, I will show an example using straight Python that involves sorting the data set first.

AndresCastillo

Mr. Bixby,

Maybe you can contribute to a thread I am involved in with Jerry Corum on May 17, 2018 12:11 PM:

https://community.esri.com/message/772784-re-collector-use-related-layer-to-symbolise-feature?commentID=772784#comment-772784

I think we are trying to use a SQL query with no python.

JoshuaBixby

Andres, I took a look at the thread you referenced, and I think it might be best to start a new, clean thread. The thread is from 2015 and has comments spanning all years since. Unless I am misreading comments, the thread seem to wonder in terms of topic, which is why I wonder if starting a new thread and asking your question might be easier for people to response to.

AndresCastillo

Thank you,

I started a new question here:

https://community.esri.com/message/773010-symbolizing-feature-class-using-related-table-values

NicholasReseburg1

I just wanted to follow up on this because I needed to do this and the above sql failed for me. So in case anyone out there is looking for a SQL solution contained in a def query, I re-wrote the query to something that worked (for me), remembering this will only work forfeature classes stored in a SDE, not a file geodatabase

replace:

featureclass the name of your feature class

Point The name field for your feature class, in the original question called "Point"

DATE The date field of your feature class you are trying to get the latest date of

OBJECTID IN (SELECT OBJECTID FROM featureclass a JOIN (SELECT Point, MAX (date) DATE FROM featureclass GROUP BY Point) b ON a.Point = b.Point AND a.DATE = b.DATE)

Jol1234

I had issues making this work as displayed above and would suggest the following changes just to make a bit more clear regarding what is a field in feature class vs what is a text from the query. Great solution though and thank you.

OBJECTID IN (SELECT OBJECTID FROM featureclass a JOIN (SELECT point, MAX(datefield) as 'DateField' FROM featureclass GROUP BY point)b on a.point = b.point AND a.datefield = b.DateField)

and since MAX ignores Null values I also tagged on a "AND datefield IS NOT NULL" to the end

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.