Solved

Aggregate attributes as comma delimited list from intersecting objects


Folks,

I have a series of points in one MI table and intersecting lines in another table in a telco network. I need to test each point and get all of the intersecting lines. The result being the ID of the point in one field and a comma delimited list of the IDs of all the intersecting polylines.

For other work in this task I've been successfully using SpatialFilters that work well for 1:1 but 1:N where the N's need to be aggregated into a single result seems to be evading me.

I don't need a spatial result for this process.

Your advice appreciated.

icon

Best answer by takashi 8 May 2017, 11:18

View original

12 replies

Userlevel 2
Badge +17
Hi @andyew, does every point match the end node(s) of one or more line belonging to the network? Or a point could be on a line but not match any node?

 

Hi @andyew, does every point match the end node(s) of one or more line belonging to the network? Or a point could be on a line but not match any node?

 

Thanks for asking @takashi the point to polyline relationship is N:N. The matches are endpoints only. So node only appear at a start or an end of a polyline. However I think I solved my own problem. I'll record an answer.

 

Userlevel 2
Badge +17
OK. In the condition, I think a possible way is: transform every line to its start/end points with two Snippers (or CoordinateRemovers), and then apply SpatialRelator and ListConcatenator.

 

I think I solved my own problem. To get the result I was after I used a SpatialFilter to determined the matches between point and polyline with the points a the Filter and the Polylines as the Candidiate. I then passed the result to an AttributeManager to return only the PointID and the PolylineID I then passed that result to an Aggregator and aggregated the result by Grouping on the PointID. The Aggregator has an Accumulator that produces a comma delimted (or any delimited) result from any other field. Since I need a record for every point with the polylineIDs aggregated this was the easiest result.

Userlevel 2
Badge +17

I think I solved my own problem. To get the result I was after I used a SpatialFilter to determined the matches between point and polyline with the points a the Filter and the Polylines as the Candidiate. I then passed the result to an AttributeManager to return only the PointID and the PolylineID I then passed that result to an Aggregator and aggregated the result by Grouping on the PointID. The Aggregator has an Accumulator that produces a comma delimted (or any delimited) result from any other field. Since I need a record for every point with the polylineIDs aggregated this was the easiest result.

It should work as expected if the relationship of point to line was 1:N, but just be aware that it may not be perfect if the relationship could be N:N.

 

It should work as expected if the relationship of point to line was 1:N, but just be aware that it may not be perfect if the relationship could be N:N.

 

Interesting @takashi Each of the points has a uniqueID. Would it not follow that if I have 5 points in the same location and two polylines starting\\terminating at this same group of points, wouldn't the Aggregator deal with each pointID one at a time and match with the two polylines? eg 5 records (one for each point), each with same two comma delimited PolylineIDs

 

Userlevel 2
Badge +17

If 5 points were on the same location, the SpatialFilter merges the PointID of any one (probably the one that arrived first) of the 5 points to every matched candidate. Then, you will get just a single record with a single PointID for the location, and the rest 4 PointID will be lost.

This workspace demonstrates the issue on N:N and a workaround using the SpatialRelator.

 

spatialfilter-vs-spatialrelator.fmw (FME 2017.0.1.0)
Userlevel 2
Badge +17

I think I solved my own problem. To get the result I was after I used a SpatialFilter to determined the matches between point and polyline with the points a the Filter and the Polylines as the Candidiate. I then passed the result to an AttributeManager to return only the PointID and the PolylineID I then passed that result to an Aggregator and aggregated the result by Grouping on the PointID. The Aggregator has an Accumulator that produces a comma delimted (or any delimited) result from any other field. Since I need a record for every point with the polylineIDs aggregated this was the easiest result.

This workspace demonstrates the issue on N:N and a workaround using the SpatialRelator.

 

spatialfilter-vs-spatialrelator.fmw (FME 2017.0.1.0)

 

If 5 points were on the same location, the SpatialFilter merges the PointID of any one (probably the one that arrived first) of the 5 points to every matched candidate. Then, you will get just a single record with a single PointID for the location, and the rest 4 PointID will be lost.

This workspace demonstrates the issue on N:N and a workaround using the SpatialRelator.

 

spatialfilter-vs-spatialrelator.fmw (FME 2017.0.1.0)
Point taken @takashi. I'll adapt my workspace to the Realtor example. Can you post this as an actual answer and I'll accept it. Thanks again for your invaluable help.

 

If 5 points were on the same location, the SpatialFilter merges the PointID of any one (probably the one that arrived first) of the 5 points to every matched candidate. Then, you will get just a single record with a single PointID for the location, and the rest 4 PointID will be lost.

This workspace demonstrates the issue on N:N and a workaround using the SpatialRelator.

 

spatialfilter-vs-spatialrelator.fmw (FME 2017.0.1.0)
@takashi I still agree with this approach but I'm not getting the complete answer. I'm sure it's some sort of filtering that need to be done I'm just to clear on where I would implement it.

 

I have 6500 points connected to the SpatialRealtor as Requestor. I have 200 polylines connected to the SpatialRealtor as Supplier. The problem is I'm getting 6500 results with 6000 points WITHOUT association with a polyline end. My expectation was this would act somewhat like LEFT JOIN in SQL returning only those records that intersect. This is acting like a FULL JOIN even when there is no actual match. I'm sure there's a way to limit the result to only those points that actually intersect the polyline ends.

 

@takashi I still agree with this approach but I'm not getting the complete answer. I'm sure it's some sort of filtering that need to be done I'm just to clear on where I would implement it.

 

I have 6500 points connected to the SpatialRealtor as Requestor. I have 200 polylines connected to the SpatialRealtor as Supplier. The problem is I'm getting 6500 results with 6000 points WITHOUT association with a polyline end. My expectation was this would act somewhat like LEFT JOIN in SQL returning only those records that intersect. This is acting like a FULL JOIN even when there is no actual match. I'm sure there's a way to limit the result to only those points that actually intersect the polyline ends.

 

 

@takashi I note from reading this performance of the SpatialRealtor in by design. All Requestors are passed through. If I can filter out all the records that have NO _relationships{}.id I should be good.
Userlevel 2
Badge +17
@takashi I still agree with this approach but I'm not getting the complete answer. I'm sure it's some sort of filtering that need to be done I'm just to clear on where I would implement it.

 

I have 6500 points connected to the SpatialRealtor as Requestor. I have 200 polylines connected to the SpatialRealtor as Supplier. The problem is I'm getting 6500 results with 6000 points WITHOUT association with a polyline end. My expectation was this would act somewhat like LEFT JOIN in SQL returning only those records that intersect. This is acting like a FULL JOIN even when there is no actual match. I'm sure there's a way to limit the result to only those points that actually intersect the polyline ends.

 

The SpatialRelator doesn't filter features as you have observed.

 

The transformer adds Related Suppliers Count Attribute (called "_related_candidates" by default) to every output feature, so you can test if the _releted_candidates is greater than 0 to filter out the points that aren't spatially related to any supplier, if necessary.

 

Alternatively, as you mentioned, testing if _relationships{0}.id has a value is also a possible way.

 

Reply