Row by row comparison and update

Dear Knime experts and enthusiasts! Need some help.

Here is the task i’m trying to solve. I have tracking data for a bike for a particular period of time (recursive.knwf (41.5 KB)
). When the bike is idle info about some parameters, including latitude and longitude are stored, when bike in use - not.

In the end of the day a want to do a summary report about bike usage in the form of sequence of statuses and their start and end times with coordinates, like
status; start time; end time; param_1;latitude; longitude.

Idle; 2019-04-17T00:06:01; 2019-04-17T04:27:01; 81; 38.90767; 32.6516.
In use; 2019-04-17T04:30:01; 2019-04-17T04:30:01; ?; ?; ?.
Idle; 2019-04-17T04:33:01; 2019-04-17T04:57:01; 81; 38.90767; 32.65159.
In use; 2019-04-17T05:00:01; 2019-04-17T05:15:01; ?; ?; ?.
and so on.

The problem is that because of the weak GPS signal even if bike in staying on one place the coordinates are constantly changing, like:

|38.90768|32.65161|
|38.90768|32.65161|
|38.90768|32.6516|

|38.90767|32.6516|

|38.90767|32.65159|

It there any way to implement in Knime next logic: take the first coordinates and compare all the rest with them. If difference between latitudes and longitudes less then 0.001 (for example), substitute current coordinates with the first one.

Then go to the next coordinates and repeat. But in order not to update already changed coordinates, start comparison only for the ones that hasn’t been previously changed.

Updating the coordinates will give me an opportunity to do the grouping correctly.

Or maybe you can recommend some other logic how to deal with such task.

UPD: unfortunately, rounding of coordinates is not working for this scenario.

Thanks in advance.

Hi,

Would you please explain why rounding doesn’t work?
I used this expression in “Math Formula (Multi Column)” node just before the GroupBy node and it seems it’s working fine: (applied to “lat” and “lon” columns)
round($$CURRENT_COLUMN$$, 3)

Am I missing something?

Best,
Armin

Rounding will work but can introduce another problem, because you can have bike parked almost in the same stop several times per day. And problem happens when other parameters stay the same. When you round coordinates even to the 3 digit after the comma, during the grouping system thinks about such cases as a one sequence and max time is taken from the last occurrence. So you can have a sequence that will include inside itself some other from time perspective.

Hi there!

Welcome to KNIME community!

Sure there is a way in KNIME! You can use Recursive Loop node. In loop in Math Formula node compare value of first row with all other row and using Rule engine determine your new values. After that prepare data for another loop iteration. Be careful of conditions in Recursive loop and remove missing value or add logic for them :wink:

Here is an workflow example where any number with difference less than 3 is updated. If any question feel free to ask.

2019_04_23_RowByRowComparison.knwf (27.1 KB)

Br,
Ivan

3 Likes

Hello Ivan.

That is exactly i was thinking about. From my side i got confused on how to align recursive loop with Table row to variable loop? I was thinking that 2 end loop nodes are required and constantly got errors.

Your example shows me my mistakes. Thanks a lot.

As i see, community is very helpful. I’m glad to be a part of it!

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.