topic Re: Select records with most recent date for groups of points in Python Questions

Select records with most recent date for groups of points

JasonFultz — Fri, 12 May 2017 21:49:48 GMT

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

Re: Select records with most recent date for groups of points

BlakeTerhune — Fri, 12 May 2017 22:32:33 GMT

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.

Re: Select records with most recent date for groups of points

JoshuaBixby — Sat, 13 May 2017 02:19:37 GMT

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.

Re: Select records with most recent date for groups of points

JoshuaBixby — Sat, 11 Dec 2021 19:50:29 GMT

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

qry = "NOT EXISTS (SELECT 1 FROM fc f WHERE fc.point = f.point AND f.date > fc.date)"  
arcpy.SelectLayerByAttribute_management(layerName, "NEW_SELECTION", qry) ‍‍

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.

Re: Select records with most recent date for groups of points

JoshuaBixby — Sat, 13 May 2017 14:48:18 GMT

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

Re: Select records with most recent date for groups of points

JasonFultz — Thu, 18 May 2017 02:50:27 GMT

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.

Re: Select records with most recent date for groups of points

JoshuaBixby — Sat, 11 Dec 2021 19:50:31 GMT

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 = # A layer or tableview object, usually retrieved using listing methods
      # in arcpy.mapping

from collections import defaultdict, namedtuple

defaultdate = datetime.datetime(1000, 1, 1)
record = namedtuple("record", "oid date")
max_date = defaultdict(lambda: record(None, defaultdate))

with arcpy.da.SearchCursor(lyr, ["OID@", "Point", "Date"]) as cur:
    for oid, point, date in cur:
        if max_date[point].date < (date or defaultdate):
            max_date[point] = record(oid, date)
            
oids = [rec.oid for rec in max_date.values() if rec.oid]
lyr.setSelectionSet("NEW", oids)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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.

Re: Select records with most recent date for groups of points

AndresCastillo — Thu, 17 May 2018 20:08:58 GMT

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.

Re: Select records with most recent date for groups of points

JoshuaBixby — Thu, 17 May 2018 20:21:28 GMT

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.

Re: Select records with most recent date for groups of points

AndresCastillo — Fri, 18 May 2018 14:00:06 GMT

Thank you,

I started a new question here:

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

Re: Select records with most recent date for groups of points

NicholasReseburg1 — Wed, 31 Oct 2018 20:14:05 GMT

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)

Re: Select records with most recent date for groups of points

Jol1234 — Wed, 21 Sep 2022 16:37:43 GMT

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

Re: Select records with most recent date for groups of points

DaleSmith — Fri, 31 Mar 2023 04:26:26 GMT

Thanks! This helped immensely. Below is the code that worked for me.

feature class: WaterQuality

point field: StationID

Date field: SampleDate

OBJECTID IN (SELECT OBJECTID FROM WaterQuality a JOIN (SELECT StationID, MAX(SampleDate) DATE FROM WaterQuality GROUP BY StationID) b ON a.StationID = b.StationID AND a.SampleDate = b.DATE