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

[DPG]

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

[DanPatterson]

Why don't you incorporate 

Near (Analysis)—ArcGIS Pro | Documentation

or 

Generate Near Table (Analysis)—ArcGIS Pro | Documentation

into your workflow

[DPG]

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. 

[Anonymous User]

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?

[BlakeTerhune]

@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.

[JohannesLindner]

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")

[GarretDuffy1]

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.