Finding Start and End Point IDs for a Line [duplicate]

Question

I have a simple point feature class and line feature class. Each point has an ID. The Line feature class has a "Start Point ID" and "End Point ID" field, both of which are currently empty. The line start/end points are snapped to points.

I'd like to do some sort of spatial join to populate the Start Point ID and End Point ID fields of the line based on what points the line is touching.

Each line feature is a simple two-vertex line, no polylines.

Any ideas on how to accomplish this without just copying and pasting ids?

Answer 1

You can use Python and arcpy:

import arcpy

#Change to match your data and field names
arcpy.env.workspace = r'C:\database.gdb'

lines = 'Lines'
startfield = 'StartID'
endfield = 'EndID'

points = 'Points'
pointidfield = 'PointID'

arcpy.MakeFeatureLayer_management(points,'plyr')
with arcpy.da.UpdateCursor(lines,['SHAPE@',startfield,endfield]) as cursor:
    for row in cursor:
        for part in row[0]:
            startpoint = arcpy.PointGeometry(part[0])
            endpoint = arcpy.PointGeometry(part[-1])
            for i in [[startpoint,1],[endpoint,2]]:
                arcpy.SelectLayerByLocation_management(in_layer='plyr',select_features=i[0])
                row[i[1]] = [f[0] for f in arcpy.da.SearchCursor('plyr',pointidfield)][0]
        cursor.updateRow(row)

Answer 2

By postgres, tried:

create table test.point
as select (st_dumppoints(geom)).geom
from test.base a;

Then in Qgis insert $id on a new column. That must do put a serial number to each point in order that was create