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
+22
- Contributor
- 1961 replies
-
5 July 2016
Could you aggregate the multiple records in each dataset prior to matching them?
Userlevel 2
+16
- Contributor
- 2180 replies
-
5 July 2016
Or could you use the DuplicateRemover or the Matcher before finding the spatial match to remove duplicates from the equation?
+6
- Contributor
- 13 replies
-
6 July 2016
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...
+6
- Contributor
- 13 replies
-
6 July 2016
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.
+3
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.
+6
- Contributor
- 13 replies
-
31 March 2017
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.
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.