topic How can I select the minimum value for each group within a point file? in Python Questions

How can I select the minimum value for each group within a point file?

DPG — Thu, 26 May 2022 18:19:03 GMT

Hi there. I've been constructing a new python tool in ArcGIS Pro which generates a point file around an existing shapefile of polygons:

I now want to calculate which one of these points for each polygon is closest to the road (the polyline that runs down the centre). However, although I can calculate this value in my script, I am having difficulties selecting the closest point and writing it as a new shapefile.

I initially used Arcpy Near (Analysis) to return the distance of every single point to the road (please refer to my code at the bottom of the post).

I then tried an arcpy Select By Attribute command to select the point with the lowest distance for every group of points (points that sit on the edge of the same shape share a common original FID, field as seen in the attribute table):

The SQL expression looked like this, using the select by attribute tool.

NEAR_DIST = (SELECT MIN(NEAR_DIST) FROM asset_points GROUP BY ORIG_FID)

However, that didn't return the minimum value for each of the groups; only the first group in the point file attribute table.

Does anybody have any ideas about how I can achieve this, and then slot it into my python script? Here is what my desired output might look like:

Any help would be much appreciated. For reference, here is my current script:

# -*- coding: utf-8 -*- import arcpy import numpy class Toolbox(object): def __init__(self): self.label = "FindMinimumPoint" self.alias = "toolbox" self.tools = [Tool] class Tool(object): def __init__(self): self.label = "FindMinimumPoint" self.description = "FindMinimumPoint" self.canRunInBackground = False def getParameterInfo(self): # Param 0 = Road param0 = arcpy.Parameter( displayName="Road", name="Parameter0", datatype="GPFeatureLayer", parameterType="Required", direction="Input") # Param 1 = Shapes param1 = arcpy.Parameter( displayName="Shapes", name="Parameter1", datatype="GPFeatureLayer", parameterType="Required", direction="Input") params = [param0,param1] return params def isLicensed(self): return True def updateParameters(self, parameters): return def updateMessages(self, parameters): return def execute(self, parameters, messages): # Overwrite existing file of the same name arcpy.env.overwriteOutput = True # Reference Parameters road = parameters[0].valueAsText shapes = parameters[1].valueAsText # make feature layer arcpy.management.MakeFeatureLayer(shapes, "shapes_lyr") # Generate points along shape edges arcpy.management.GeneratePointsAlongLines("shapes_lyr", "shapes_points", 'PERCENTAGE', Percentage=7) # Find distance of each vertex to road arcpy.analysis.Near("shapes_points", road) return

Re: How to find and return the minimum value from groups within a point file?

DanPatterson — Thu, 26 May 2022 17:39:59 GMT

Why don't you incorporate

Near (Analysis)—ArcGIS Pro | Documentation

Generate Near Table (Analysis)—ArcGIS Pro | Documentation

into your workflow

Re: How to find and return the minimum value from groups within a point file?

DPG — Thu, 26 May 2022 17:58:29 GMT

Thanks for the suggestion. I used arcpy near initially to get the distance of every point to the line (I've updated my post to make it a little clearer). However, my problem comes from trying to select the lowest distanced point from each set of points on a polygon.

Re: How to find and return the minimum value from groups within a point file?

Anonymous User — Thu, 26 May 2022 19:11:22 GMT

It sounds like your analysis is working. Why don't you iterate over each polygon individually so that your "nearest" point returns one at a time. This might take a little longer but it seems straightforward?

Re: How can I select the minimum value for each group within a point file?

BlakeTerhune — Thu, 26 May 2022 22:29:47 GMT

@Anonymous User is correct. You need a way to group the points so you can run Near on each group separately. I would suggest a spatial join between the points and polygons so you can get the ObjectID (or some other unique identifier) of the polygon each point belongs to. Then run Near for each group or do Generate Near Table on everything and find the closest in each polygon group.

Re: How can I select the minimum value for each group within a point file?

JohannesLindner — Mon, 30 May 2022 09:45:27 GMT

You can also calculate a new field that tells you if a point is the closest point:

shapes_points = arcpy.management.GeneratePointsAlongLines("shapes_lyr", "memory/shapes_points", 'PERCENTAGE', Percentage=7) arcpy.analysis.Near(shapes_points, road) arcade_expression = """ var fs_points = FeatureSetByName($datastore, "shapes_points", ["ORIG_FID", "NEAR_DIST"], false) var fid = $feature.ORIG_FID fs_points = Filter(fs_points, "ORIG_FID = @fid") return $feature.NEAR_DIST == Min(fs_points, "NEAR_DIST") """ arcpy.management.CalculateField(shapes_points, "IsNearest", arcade_expression, "ARCADE", field_type="Short") with arcpy.da.UpdateCursor("shapes_points", ["IsNearest"], "IsNearest = 0") as cursor: for row in cursor: cursor.deleteRow() arcpy.management.DeleteField(shapes_points, "IsNearest")

Re: How can I select the minimum value for each group within a point file?

GarretDuffy1 — Mon, 20 Feb 2023 18:56:39 GMT

I have a similar issue but all I am trying to do is return the row with the minimum value for each group. I am getting the issue where only the value for the first group is returned. My query is:

Select * From Vehicle_subset WHERE:

time_Converted=(SELECT max( time_Converted) FROM Vehicle_subset GROUP BY step_id)

I am expecting a single row for every group (identified by step_id) but I only get the row from the first group.