hi all, is there a way or a transformer that I can use to establish a match between two datasets at locations where there are multiple records? I am using the neighbor finder but when I have a location with say 2 records on both datasets being matched, I get a result of 4 matches instead of 2 thus duplicating the data. Your help is very much appreciated. Thank you
Question
Many to many spatial match
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+26- Contributor
Or could you use the DuplicateRemover or the Matcher before finding the spatial match to remove duplicates from the equation?
+12- Contributor
Or could you use the DuplicateRemover or the Matcher before finding the spatial match to remove duplicates from the equation?
hi, thanks for the initial answers but i thought i better make it more illustrated so what i am trying to do/correct is easier to understand.
Below is the example of on of the datasets. i have multiple lights on a pole. I need to spatially match other point geometries which are located very near.
Below is showing what the matching i get for these particular records. The highlighted record matches with the records form the other dataset and then the rest which sit on that location have no match. I have tried using the aggregator but this transformer totally removes the duplicates and that data is lost.
Is there a way of establishing a one to one match in a case where there are multiple records at the same location?
thanks again for any help or suggestions.
not sure why the images i inserted are not visible...
+12- Contributor
hi, thanks for the initial answers but i thought i better make it more illustrated so what i am trying to do/correct is easier to understand.
Below is the example of on of the datasets. i have multiple lights on a pole. I need to spatially match other point geometries which are located very near.
Below is showing what the matching i get for these particular records. The highlighted record matches with the records form the other dataset and then the rest which sit on that location have no match. I have tried using the aggregator but this transformer totally removes the duplicates and that data is lost.Is there a way of establishing a one to one match in a case where there are multiple records at the same location?
thanks again for any help or suggestions.
+15- Contributor
if the light points are around 2 or more poles and satisfy search condition of more than 1 pole, there is no way to distinguish to which they would need to be matched.
You could of course decide to pick the closest one, by testing the closest_candidate_distance attribute which is created by the neighborfinder.
To match multiple points to a pole, you need to set the points as base and pole as candidate and use keep candidate option.
+12- Contributor
hi, thanks for the initial answers but i thought i better make it more illustrated so what i am trying to do/correct is easier to understand.
Below is the example of on of the datasets. i have multiple lights on a pole. I need to spatially match other point geometries which are located very near.
Below is showing what the matching i get for these particular records. The highlighted record matches with the records form the other dataset and then the rest which sit on that location have no match. I have tried using the aggregator but this transformer totally removes the duplicates and that data is lost.Is there a way of establishing a one to one match in a case where there are multiple records at the same location?
thanks again for any help or suggestions.
to anyone interested the way to work this out is in the SQL. The workbench uses testers and neighbourhood finders.
what the SQL does is it creates two columns whcih are looked at by the testers.
Below is a saple of the SQL used.
with s_all as (
SELECT s.attribute_one, s.geom, t.x, t.y, COUNT (*) OVER (PARTITION BY T.Y, T.X) NUM_OBJECTS
FROM table1 s, TABLE (SDO_UTIL.getvertices (s.geom)) t
WHERE S.attribute_one = 'xoxoxoxo'
and s.attribute_two not like 'abc'
),
SL_RNK
AS ( SELECT OBJECT_IDENTIFIER,
ROW_NUMBER ()
OVER (PARTITION BY x
ORDER BY COUNT (*) DESC, OBJECT_IDENTIFIER DESC)
srnk, X COMMON_LON
FROM S_ALL
GROUP BY OBJECT_IDENTIFIER, X)
SELECT a.*, B.SRNK, B.COMMON_LON
FROM S_ALL a, SL_RNK b
WHERE a.OBJECT_IDENTIFIER = b.OBJECT_IDENTIFIER
and a.NUM_OBJECTS > 1
Hope this will help anyone attempting a similar task.