nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Splitting groups of points by common field values

Liam_

Hi,

I am trying to split groups of points that intersect polylines, but I cannot seem to find a tool that can perform the task. I have performed a spatial join with a one-to-many relationship so that all of the points now have the [Roads.ID] value stored in [Points.RoadsID] field where they intersect.

Here is a sample of my data: -

[Roads]
[Roads.ID]
[Roads.Name]
[Roads.Shape_Length]

[Points]
[Points.ID]
[Points.RoadsID]
[Points.Speed]

What I would like to do is split the [Points] table up into groups of points that all have the same [Points.RoadsID] value so I can perform statistical analysis on them on a road-by-road level.

Any help would be appreciated,

Thanks,

Liam.

Find more posts tagged with

Accepted answers

RichardFairhurst

Hi rfairhur24,

Thank you for your advice, I think I am a step closer to a solution.

I tried the first method you suggested using the summary statistics, however all that was returned was the frequency of points along each road by [Road.ID].

I also tried the 'Linear Referenced Routes' method and although I managed to create a join between the two tables, I could not then separate out the points by the roads in which they intersect.

I need to be able to calculate the average value for [Points.Speed] for example: -
Pseudo Code:  // Start the RoadNumber off at 0 Dim RoadNumber RoadNumber = 0   // Get the maximum amount of records in table Dim MaxRoads MaxRoads = GetMaximumValueOfField(Roads.ID)   // Create an array so store selection records Dim Selection[]  // Loop through all of the roads WHILE RoadNumber < (MaxRoads + 1) {    // Increment RoadNumber by 1  RoadNumber = RoadNumber + 1   // Select all the points which fall on the same road and save to array  Selection = SELECT * WHERE [Points.RoadsID] = RoadNumber    // Create a new table with the RoadNumber name and populate the table with the contents  // of the Selection array  CreateNewTable(RoadNumber, Selection)   // Clear the selection array  Selection = null  } 
I would end up with as many tables as there are roads (quite a few!). I would then be able to calculate the mean value of [.Speed] for each table and using joins copy that value to a [Roads.AverageSpeed] field where [Roads.ID] is equal to the name of each table.

I'm not sure if I can do this sort of calculation in the model builder or if it is even possible?

Thanks again,

Liam.

You need to do either the Spatial Join or the Locate Features Along Routes tools first before you can process the Summary Statistics tool. I did not mean to eliminate the Spatial Join step. After doing the Spatial Join or LR tool, specify that you want the Mean Speed in the Summary Statistics tool to get that summary. See my sample Summary Statistics tool set up and output. In this example I have already processed the Locate Features Along Route tool and am getting the minimum, maximum and average (mean) speed of all LR event points associated to each Road_ID in a table with multiple points tied to multiple Road_IDs. The data is made up so don't worry about whether the values are realistic. This is just to illustrate the principle of what I am telling you to do.

For the Linear Referencing option, I assumed you would create your LR Routes using the RoadID field as the RouteID, so that the Locate Features on Routes tool would automatically include the Road_ID in the output. The Locate Features by Route is a replacement for doing a Spatial Join, but you would still need to do the Summary Statistics on the Locate Features by Route tool output. It is just that LR events do much more than Spatial Join Outputs.

Your code is unnecessary and inefficient by comparison with what I am proposing. Also the Python code I suggested would be much more efficient if it was adapted to do an average speed. It can be adapted to do the same as the summary table I have created, but for just one summary value (average speed) the Summary Statistics method is just about as efficient. The Python script option also assumes you have previously run the Spatial Join or the Locate Features Along Routes tool prior to running the script.

Speed_Summaries.PNG

Speed_Summary.PNG

All comments

RichardFairhurst

Hi,

I am trying to split groups of points that intersect polylines, but I cannot seem to find a tool that can perform the task. I have performed a spatial join with a one-to-many relationship so that all of the points now have the [Roads.ID] value stored in [Points.RoadsID] field where they intersect.

Here is a sample of my data: -

[Roads]
[Roads.ID]
[Roads.Name]
[Roads.Shape_Length]

[Points]
[Points.ID]
[Points.RoadsID]
[Points.Speed]

What I would like to do is split the [Points] table up into groups of points that all have the same [Points.RoadsID] value so I can perform statistical analysis on them on a road-by-road level.

Any help would be appreciated,

Thanks,

Liam.

No need to split the output up. Just use Summary Statistics with the RoadsID as the case field. That will summarize the entire result into a single table of values that group on the RoadsID values. Then use the Make Feature Layer tool, the Join Tool and the Field Calculator Tool to transfer the result. You would join the original Roads and the summary output on the RoadsID field. If you have multiple summaries on the RoadsID groups (which can be created with one Summary Statistics tool run) you still have to run the field calculator to transfer the results for each summary field separately.

Alternatively use a Python da cursor, but that requires skill in understanding how to create a dictionary of summary values. The #14 post in this thread shows a script for filling in a running count field and count summary field grouped by a case field using a python cursor and a dictionary. More sophisticated summaries are possible using the principles of that script and it is possible to do many concurrent summary values (count, mean, min, max, std dev, etc) that output to multiple fields in just two passes (an aggregation pass followed by an output pass), similar to how the example does it with the unique numbering and count by group field. The script could be adapted to do the aggregation pass on the points and the output pass to the Roads rather than doing both passes on the same feature class.

Another suggestion is to create Linear Referenced Routes from your road lines (most likely by using Create Routes) and then use the Locate Features on Routes tool to create point events along the line rather than just a Spatial Join ID association. That tool preserves the Point ID values like a Spatial Join, but also associates the points to a RouteID and a measure (station) position on the line and it tells you if the points fell on the right or left side of the line with the optional distance field. I would calculate the original X and Y coordinates of the points into a pair of fields and then run the Locate Features Along Routes tools. With this technique you can see exactly where each point fell on the line with the Make Route Events tool or menu item. You can also calculate the new X and Y coordinates of the events on the line to see the offset in each plane. The relative distances along the line between the points can be determined from the event data as well as the sort order of the points along the line from one end of the line to the other.

LR events make the relationships between points and lines much more intelligent and permit you to do everything you would do with just the Spatial Join, plus much more. If the RoadsID is for a grouping of road segments already, the Route can create a workable single line representation of the RoadsID from those segments which also makes working with the segment groups more intelligent. Through geoprocessing it is possible to maintain an association between your original centerlines to the route representations so you only have to do updates to a single network. The 10.2 Roads and Highways extension may also be worth considering, since you seem to be working in the Transportation field.

Liam_

Hi rfairhur24,

Thank you for your advice, I think I am a step closer to a solution.

I tried the first method you suggested using the summary statistics, however all that was returned was the frequency of points along each road by [Road.ID].

I also tried the 'Linear Referenced Routes' method and although I managed to create a join between the two tables, I could not then separate out the points by the roads in which they intersect.

I need to be able to calculate the average value for [Points.Speed] for example: -

Pseudo Code:

// Start the RoadNumber off at 0
Dim RoadNumber
RoadNumber = 0


// Get the maximum amount of records in table
Dim MaxRoads
MaxRoads = GetMaximumValueOfField(Roads.ID)


// Create an array so store selection records
Dim Selection[]

// Loop through all of the roads
WHILE RoadNumber < (MaxRoads + 1) {


 // Increment RoadNumber by 1
 RoadNumber = RoadNumber + 1

 // Select all the points which fall on the same road and save to array
 Selection = SELECT * WHERE [Points.RoadsID] = RoadNumber 

 // Create a new table with the RoadNumber name and populate the table with the contents  // of the Selection array
 CreateNewTable(RoadNumber, Selection)

 // Clear the selection array
 Selection = null

}

I would end up with as many tables as there are roads (quite a few!). I would then be able to calculate the mean value of [.Speed] for each table and using joins copy that value to a [Roads.AverageSpeed] field where [Roads.ID] is equal to the name of each table.

I'm not sure if I can do this sort of calculation in the model builder or if it is even possible?

Thanks again,

Liam.

RichardFairhurst

Hi rfairhur24,

Thank you for your advice, I think I am a step closer to a solution.

I tried the first method you suggested using the summary statistics, however all that was returned was the frequency of points along each road by [Road.ID].

I also tried the 'Linear Referenced Routes' method and although I managed to create a join between the two tables, I could not then separate out the points by the roads in which they intersect.

I need to be able to calculate the average value for [Points.Speed] for example: -
Pseudo Code:  // Start the RoadNumber off at 0 Dim RoadNumber RoadNumber = 0   // Get the maximum amount of records in table Dim MaxRoads MaxRoads = GetMaximumValueOfField(Roads.ID)   // Create an array so store selection records Dim Selection[]  // Loop through all of the roads WHILE RoadNumber < (MaxRoads + 1) {    // Increment RoadNumber by 1  RoadNumber = RoadNumber + 1   // Select all the points which fall on the same road and save to array  Selection = SELECT * WHERE [Points.RoadsID] = RoadNumber    // Create a new table with the RoadNumber name and populate the table with the contents  // of the Selection array  CreateNewTable(RoadNumber, Selection)   // Clear the selection array  Selection = null  } 
I would end up with as many tables as there are roads (quite a few!). I would then be able to calculate the mean value of [.Speed] for each table and using joins copy that value to a [Roads.AverageSpeed] field where [Roads.ID] is equal to the name of each table.

I'm not sure if I can do this sort of calculation in the model builder or if it is even possible?

Thanks again,

Liam.

Speed_Summaries.PNG

Speed_Summary.PNG

Liam_

Thank you for all your help 🙂

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.