Skip to main content
Solved

Aggregate attributes as comma delimited list from intersecting objects

A

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.

Best answer by takashi

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)
View original
Did this help you find an answer to your question?

12 replies

takashi
Contributor
Forum|alt.badge.img+21
  • takashiContributor
  • Contributor
  • May 8, 2017
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?

 

A
  • andyew
  • Author
  • May 8, 2017
takashi wrote:
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.

 

takashi
Contributor
Forum|alt.badge.img+21
  • takashiContributor
  • Contributor
  • May 8, 2017
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.

 

A
  • andyew
  • Author
  • May 8, 2017

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.

takashi
Contributor
Forum|alt.badge.img+21
  • takashiContributor
  • Contributor
  • May 8, 2017
andyew wrote:

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.

 

A
  • andyew
  • Author
  • May 8, 2017
takashi wrote:
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

 

takashi
Contributor
Forum|alt.badge.img+21
  • takashiContributor
  • Contributor
  • Best Answer
  • May 8, 2017

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
Contributor
Forum|alt.badge.img+21
  • takashiContributor
  • Contributor
  • May 8, 2017
andyew wrote:

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)

 

A
  • andyew
  • Author
  • May 9, 2017
takashi wrote:

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.

 

A
  • andyew
  • Author
  • May 10, 2017
takashi wrote:

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.

 

A
  • andyew
  • Author
  • May 11, 2017
andyew wrote:
@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.
takashi
Contributor
Forum|alt.badge.img+21
  • takashiContributor
  • Contributor
  • May 11, 2017
andyew wrote:
@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


Helpful Members This Week

Community Stats

30,985
Posts
117,433
Replies
38,764
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.