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

Question

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

GarretDuffy1 · Answer

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.

JohannesLindner · Answer

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

blake.terhune · Answer

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