Joining points to points on the same road

04-20-2016 07:28 AM
New Contributor

I know there may be a really simple solution to this query but I have been looking for one for ages and have not been successful.

I have points that represent mammal sightings on roads, as well as points that represent traffic volume in the UK. I want to join the mammal points to the volume points so I know the traffic volume at each mammal point.

I have tried joining point to point, where the mammal points join to the closest volume point, which worked for some cases, but in most cases the mammal points joined to a volume point that was on a different road. I have also tried converting the volume points to a line but that doesn't seem to have worked either.

Does anyone know a way I can join 1 point to the 5 nearest points, or to join the mammal points to volume points that are on the same road as it? Thanks.

0 Kudos
8 Replies
MVP Legendary Contributor

have you ruled out 'relates' rather than a join? About joining and relating tables—Help | ArcGIS for Desktop

or perhaps linear referencing What is linear referencing?—Help | ArcGIS for Desktop

0 Kudos
Regular Contributor III

Do you have an advanced license? You could use Generate Near Table—Help | ArcGIS for Desktop  to get the points closest.

0 Kudos
MVP Frequent Contributor

Some possible tools that may help in resolving this:

Chris Donohue, GISP

MVP Honored Contributor


It might be good to point out that all three of those geoprocessing options all require an Advanced license to use.

0 Kudos
Occasional Contributor III

To give a data concentric approach to your problem ...

Traffic volume is generally analysed as a line segment along a road.  The volume is calculated generally from traffic counters/meters (Point) and applied to a specific line segment along the road.  First I would look for volumes as a line segment (not the traffic counter point) from you transportation department; here in the US this called AADT or Average Annual Daily Traffic.  I am sure the Brits have a comparable analogous line layer of data.  An attribute within the traffic volume data should be the road.

Now to it is a simple matter to intersect your mammal sighting (point) to the traffic volume line segment.  Once done the volume attribute will be added to your mammal point layer.

Hopefully this gives you an alternative approach to your question.

-----  Post edit ----

I wrote the above assuming that your point were located on or very near the associated roads.  However, if you points are randomly located offset from the referenced roads then a more complicated process will be involved.  Before intersecting the mammal points with the volume line segments you would first create a new mammal layer that is "snapped" to the referenced road.  Tools mention by the experts in this post will get you partly there but you will still have points joining to the wrong road if the point is geometrically located closer to another road other than the referenced one...  To accomplish this directly would involve some scripting or a series of manual filtered near analysis.

My inhouse approach to this problem is to write a script that will loop through your mammal list, filter your road layer by the mammal point road reference (leaves you with only one road), snap the point to that "one" road.  Continue the loop process throughout the complete mammal point list.  When finished you will have a copy of your mammal points that rests on the correct road.  Then you can perform your intersection with 100% assurance the correct volumes will be picked up.  -- The python experts here can probably guide you through this in their sleep.

MVP Honored Contributor

I would use Linear Referencing to solve this problem.  After making each road into a route with measures, I would use the Locate Features Along Route tool for both the traffic volume and the animal sightings to create two separate point event tables.  Make sure the animal and volumes each have a unique ID field other than the ObjectID.  Also make sure the two tables use different field names for the measure parameter of the tool so that when they combine both with be preserved.  This will establish both sets of data relative to an identical road based measuring system and a common route reference that will be respected by the Linear Referencing tools (so an event does not jump from one road to another).  Since you have to use a radius to tie the offset points to the routes, use the match all routes option and not the closest route option.  Just throw out any routes that do not match the route associated with each sighting and volume (hopefully you can use a road name field associated with each data source to find the correct road to tie each event to).

Next I would get each sighting tied to all of the volumes on the road.  This can be done by calculating the beginning and ending measure on each route (Python field calculation using either !Shape.FirstPoint.M! or !Shape.LastPoint.M!), joining that to the animal sightings and copying the table to a new table.  Now that each animal is associated to the measures of the full limit of its route and will ignore all other routes, you can use the Overlay Route Events tool to combine this animal table with the volume table matching the full route measures to the volume measure and outputting a point event while preserving all fields.  Because the AnimalSighting measure will be combined with the Volume measure now, a Separation field that holds the distance as measured along the road between the animal sighting and each volume point can be calculated using the VB Script formula Abs(Animal_Measure - Volume_Measure).

Then run the Sort tool on the combined event table using the RouteID, AnimalSightingID and the Separation fields as the sort criteria.  Run the Summary Statistics tool in the Analysis toolbox, Statistics toolset to get the Min of the Object ID associate with each Route and AnimalSightingID value as the 2 case fields.  Run my tool that creates a single key from a multi field key.  Join on the new single field key and select SortedTable.ObjectID = SummaryTable.Min_ObjectID if you want the closest volume to the animal sighting or use SortedTable.ObjectID > SummaryTable.Min_ObjectID + 5 and SortedTable.AnimalSightingID = SummaryTable.AnimalSightingID if you want the top 5 closest volumes on that route.  Export the selection and you have an event table of all animal sightings combined with the closest volumes you chose, plus you will know the distance separating them along the route.

You will be able to create several unique event layers by using different measures gathered at each step.  For example you can create line segments between the animal sighting and each volume point or a point for either the animal or the volume all using this one table.  If you preserved a side offset distance from the Locate Features Along Route tool used for the animal at the beginning you can offset the points or the line to the side of the line the animal was seen on using that field in the Make Route Event layer tool.

All of the tools above work under a Basic license.

MVP Legendary Contributor

moved to Geoprocessing​ to see if there are any other ideas not covered by the above

0 Kudos
Occasional Contributor III

You want to join point data between different layers by road ( closest in same road),Am I right?

You want to join one point to one point in other layer, Isn't that?

   I can solve this, but I want to know if all points snap to roads or not? and if there are points in the intersect of two roads or not?

0 Kudos