From 2a5bf04563ecc8b09f07a0007335bbd111f9c0a2 Mon Sep 17 00:00:00 2001 From: soshial Date: Fri, 10 Sep 2021 09:22:55 +0200 Subject: add support for relative filtering --- setup/default.xml | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'setup') diff --git a/setup/default.xml b/setup/default.xml index 0564182c7..e798fcf88 100644 --- a/setup/default.xml +++ b/setup/default.xml @@ -22,6 +22,7 @@ true 86400 + false true true @@ -48,6 +49,18 @@ SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to ORDER BY fixTime + + SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime = :time LIMIT 1 + + + + SELECT * FROM tc_positions WHERE deviceId = :deviceId AND NOT fixTime lt :time LIMIT 1 ORDER BY fixTime DESC + + + + SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime gt :time LIMIT 1 ORDER BY fixTime ASC + + SELECT tc_positions.* FROM tc_positions INNER JOIN tc_devices ON tc_positions.id = tc_devices.positionid; -- cgit v1.2.3 From d9ad81991da306dcae35bb6b7ba7f90c6d6b8725 Mon Sep 17 00:00:00 2001 From: soshial Date: Sun, 12 Sep 2021 05:23:12 +0200 Subject: filtering optimizations --- setup/default.xml | 4 +- src/main/java/org/traccar/config/Keys.java | 7 ++- .../java/org/traccar/handler/FilterHandler.java | 62 +++++++++------------- 3 files changed, 32 insertions(+), 41 deletions(-) (limited to 'setup') diff --git a/setup/default.xml b/setup/default.xml index e798fcf88..54677d845 100644 --- a/setup/default.xml +++ b/setup/default.xml @@ -54,11 +54,11 @@ - SELECT * FROM tc_positions WHERE deviceId = :deviceId AND NOT fixTime lt :time LIMIT 1 ORDER BY fixTime DESC + SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime lte :time ORDER BY fixTime DESC LIMIT 1 - SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime gt :time LIMIT 1 ORDER BY fixTime ASC + SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime gt :time ORDER BY fixTime ASC LIMIT 1 diff --git a/src/main/java/org/traccar/config/Keys.java b/src/main/java/org/traccar/config/Keys.java index f7d61a993..c6a202d2f 100644 --- a/src/main/java/org/traccar/config/Keys.java +++ b/src/main/java/org/traccar/config/Keys.java @@ -872,8 +872,11 @@ public final class Keys { Collections.singletonList(KeyType.GLOBAL)); /** - * Filter records by Maximum Speed value in knots. Can be used to filter jumps to far locations even if they're - * marked as valid. Shouldn't be too low. Start testing with values at about 25000. + * Filter records by Maximum Speed value in knots. Can be used to filter jumps to far locations even if Position + * appears valid or if Position `speed` field reported by the device is also within limits. Calculates speed from + * the distance to the previous position and the elapsed time. + * + * Tip: Shouldn't be too low. Start testing with values at about 25000. */ public static final ConfigKey FILTER_MAX_SPEED = new ConfigKey<>( "filter.maxSpeed", diff --git a/src/main/java/org/traccar/handler/FilterHandler.java b/src/main/java/org/traccar/handler/FilterHandler.java index 817d6c8ba..16e5ae2da 100644 --- a/src/main/java/org/traccar/handler/FilterHandler.java +++ b/src/main/java/org/traccar/handler/FilterHandler.java @@ -146,6 +146,9 @@ public class FilterHandler extends BaseDataHandler { } private boolean filter(Position position) { + if (skipAttributes(position)) { + return false; + } StringBuilder filterType = new StringBuilder(); @@ -165,58 +168,43 @@ public class FilterHandler extends BaseDataHandler { if (filterApproximate(position)) { filterType.append("Approximate "); } - if (filterRelative) { - try { - if (filterStatic(position) && !skipAttributes(position)) { - filterType.append("Static "); - } - - Date newfixTime = position.getFixTime(); - Position duplicate = Context.getDataManager().getPositionByTime(position.getDeviceId(), newfixTime); - if (filterDuplicate(position, duplicate) && !skipLimit(position, duplicate) - && !skipAttributes(position)) { - filterType.append("Duplicate "); - } + if (filterStatic(position)) { + filterType.append("Static "); + } - Position previous = Context.getDataManager().getPrevPosition(position.getDeviceId(), newfixTime); - Position next = Context.getDataManager().getNextPosition(position.getDeviceId(), newfixTime); - if ((filterDistance(position, previous) || filterDistance(next, position)) - && !skipAttributes(position)) { - filterType.append("Distance "); + // relative filtering + if (filterDuplicate || filterDistance != 0 || filterMaxSpeed != 0 || filterMinPeriod != 0) { + Position previous = null; + if (filterRelative) { + try { + Date newFixTime = position.getFixTime(); + previous = Context.getDataManager().getPrevPosition(position.getDeviceId(), newFixTime); + } catch (SQLException e) { + LOGGER.warn("Error filtering position", e); } - if (filterMaxSpeed(position, previous) || filterMaxSpeed(next, position)) { - filterType.append("MaxSpeed "); + } else { + if (Context.getIdentityManager() != null) { + previous = Context.getIdentityManager().getLastPosition(position.getDeviceId()); } - if (filterMinPeriod(position, previous) || filterMinPeriod(next, position)) { - filterType.append("MinPeriod "); - } - } catch (SQLException e) { - LOGGER.warn("Error filtering position", e); } - } else { - Position last = null; - if (Context.getIdentityManager() != null) { - last = Context.getIdentityManager().getLastPosition(position.getDeviceId()); + if (skipLimit(position, previous)) { + return false; } - - if (filterDuplicate(position, last) && !skipLimit(position, last) && !skipAttributes(position)) { + if (filterDuplicate(position, previous)) { filterType.append("Duplicate "); } - if (filterStatic(position) && !skipLimit(position, last) && !skipAttributes(position)) { - filterType.append("Static "); - } - if (filterDistance(position, last) && !skipLimit(position, last) && !skipAttributes(position)) { + if (filterDistance(position, previous)) { filterType.append("Distance "); } - if (filterMaxSpeed(position, last)) { + if (filterMaxSpeed(position, previous)) { filterType.append("MaxSpeed "); } - if (filterMinPeriod(position, last)) { + if (filterMinPeriod(position, previous)) { filterType.append("MinPeriod "); } } - if (filterType.length() > 0) { + if (filterType.length() > 0) { StringBuilder message = new StringBuilder(); message.append("Position filtered by "); message.append(filterType.toString()); -- cgit v1.2.3 From 7a3b106dfc28a6254825b8d664db8181f4bd19f1 Mon Sep 17 00:00:00 2001 From: soshial Date: Sun, 12 Sep 2021 09:31:38 +0200 Subject: improve naming and comments --- setup/default.xml | 7 +---- src/main/java/org/traccar/config/Keys.java | 9 +++--- .../java/org/traccar/database/DataManager.java | 16 ++-------- .../java/org/traccar/handler/FilterHandler.java | 35 ++++++++++++++-------- 4 files changed, 30 insertions(+), 37 deletions(-) (limited to 'setup') diff --git a/setup/default.xml b/setup/default.xml index 54677d845..7ed3e35b2 100644 --- a/setup/default.xml +++ b/setup/default.xml @@ -22,7 +22,6 @@ true 86400 - false true true @@ -53,14 +52,10 @@ SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime = :time LIMIT 1 - + SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime lte :time ORDER BY fixTime DESC LIMIT 1 - - SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime gt :time ORDER BY fixTime ASC LIMIT 1 - - SELECT tc_positions.* FROM tc_positions INNER JOIN tc_devices ON tc_positions.id = tc_devices.positionid; diff --git a/src/main/java/org/traccar/config/Keys.java b/src/main/java/org/traccar/config/Keys.java index c6a202d2f..a77204175 100644 --- a/src/main/java/org/traccar/config/Keys.java +++ b/src/main/java/org/traccar/config/Keys.java @@ -890,11 +890,12 @@ public final class Keys { Collections.singletonList(KeyType.GLOBAL)); /** - * If FALSE, then filter compares duplicates, distance or period only with the last received location. - * Server expects all locations to come sequentially. + * If false, the server expects all locations to come sequentially (for each device). Filter checks for duplicates, + * distance, speed, or time period only against the location that was last received by server. * - * If TRUE, then filter compares duplicates, distance or period with Position's fixTime strictly before and after. - * Server expects locations to come at random order, since tracking device might go offline. + * If true, the server expects locations to come at random order (since tracking device might go offline). + * Filter checks for duplicates, distance, speed, or time period against the preceding Position's. + * Important: setting to true can cause potential performance issues. */ public static final ConfigKey FILTER_RELATIVE = new ConfigKey<>( "filter.relative", diff --git a/src/main/java/org/traccar/database/DataManager.java b/src/main/java/org/traccar/database/DataManager.java index 3f702f78a..d8401774e 100644 --- a/src/main/java/org/traccar/database/DataManager.java +++ b/src/main/java/org/traccar/database/DataManager.java @@ -340,20 +340,8 @@ public class DataManager { } } - public Position getPrevPosition(long deviceId, Date date) throws SQLException { - Collection positions = QueryBuilder.create(dataSource, getQuery("database.selectPrevPosition")) - .setLong("deviceId", deviceId) - .setDate("time", date) - .executeQuery(Position.class); - if (positions.isEmpty()) { - return null; - } else { - return positions.iterator().next(); - } - } - - public Position getNextPosition(long deviceId, Date date) throws SQLException { - Collection positions = QueryBuilder.create(dataSource, getQuery("database.selectNextPosition")) + public Position getPrecedingPosition(long deviceId, Date date) throws SQLException { + Collection positions = QueryBuilder.create(dataSource, getQuery("database.selectPrecedingPosition")) .setLong("deviceId", deviceId) .setDate("time", date) .executeQuery(Position.class); diff --git a/src/main/java/org/traccar/handler/FilterHandler.java b/src/main/java/org/traccar/handler/FilterHandler.java index 16e5ae2da..5287a7639 100644 --- a/src/main/java/org/traccar/handler/FilterHandler.java +++ b/src/main/java/org/traccar/handler/FilterHandler.java @@ -146,6 +146,7 @@ public class FilterHandler extends BaseDataHandler { } private boolean filter(Position position) { + if (skipAttributes(position)) { return false; } @@ -173,43 +174,44 @@ public class FilterHandler extends BaseDataHandler { } // relative filtering - if (filterDuplicate || filterDistance != 0 || filterMaxSpeed != 0 || filterMinPeriod != 0) { - Position previous = null; + long deviceId = position.getDeviceId(); + if (filterDuplicate || filterDistance > 0 || filterMaxSpeed > 0 || filterMinPeriod > 0) { + Position preceding = null; if (filterRelative) { try { Date newFixTime = position.getFixTime(); - previous = Context.getDataManager().getPrevPosition(position.getDeviceId(), newFixTime); + preceding = Context.getDataManager().getPrecedingPosition(deviceId, newFixTime); } catch (SQLException e) { - LOGGER.warn("Error filtering position", e); + LOGGER.warn("Error retrieving preceding position; fallbacking to last received position.", e); + preceding = getLastReceivedPosition(deviceId); } } else { - if (Context.getIdentityManager() != null) { - previous = Context.getIdentityManager().getLastPosition(position.getDeviceId()); - } + preceding = getLastReceivedPosition(deviceId); } - if (skipLimit(position, previous)) { + if (skipLimit(position, preceding)) { return false; } - if (filterDuplicate(position, previous)) { + if (filterDuplicate(position, preceding)) { filterType.append("Duplicate "); } - if (filterDistance(position, previous)) { + if (filterDistance(position, preceding)) { filterType.append("Distance "); } - if (filterMaxSpeed(position, previous)) { + if (filterMaxSpeed(position, preceding)) { filterType.append("MaxSpeed "); } - if (filterMinPeriod(position, previous)) { + if (filterMinPeriod(position, preceding)) { filterType.append("MinPeriod "); } } if (filterType.length() > 0) { + StringBuilder message = new StringBuilder(); message.append("Position filtered by "); message.append(filterType.toString()); message.append("filters from device: "); - message.append(Context.getIdentityManager().getById(position.getDeviceId()).getUniqueId()); + message.append(Context.getIdentityManager().getById(deviceId).getUniqueId()); LOGGER.info(message.toString()); return true; @@ -218,6 +220,13 @@ public class FilterHandler extends BaseDataHandler { return false; } + private Position getLastReceivedPosition(long deviceId) { + if (Context.getIdentityManager() != null) { + return Context.getIdentityManager().getLastPosition(deviceId); + } + return null; + } + @Override protected Position handlePosition(Position position) { if (filter(position)) { -- cgit v1.2.3 From fd91d6bbb189b091799708a4d3f4e9d9ca114763 Mon Sep 17 00:00:00 2001 From: soshial Date: Mon, 13 Sep 2021 08:21:35 +0200 Subject: remove comments --- setup/default.xml | 4 ---- src/main/java/org/traccar/database/DataManager.java | 12 ------------ src/main/java/org/traccar/handler/FilterHandler.java | 2 -- 3 files changed, 18 deletions(-) (limited to 'setup') diff --git a/setup/default.xml b/setup/default.xml index 7ed3e35b2..419eaef45 100644 --- a/setup/default.xml +++ b/setup/default.xml @@ -48,10 +48,6 @@ SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to ORDER BY fixTime - - SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime = :time LIMIT 1 - - SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime lte :time ORDER BY fixTime DESC LIMIT 1 diff --git a/src/main/java/org/traccar/database/DataManager.java b/src/main/java/org/traccar/database/DataManager.java index d8401774e..15c67cb74 100644 --- a/src/main/java/org/traccar/database/DataManager.java +++ b/src/main/java/org/traccar/database/DataManager.java @@ -328,18 +328,6 @@ public class DataManager { .executeQuery(Position.class); } - public Position getPositionByTime(long deviceId, Date date) throws SQLException { - Collection positions = QueryBuilder.create(dataSource, getQuery("database.selectPositionByTime")) - .setLong("deviceId", deviceId) - .setDate("time", date) - .executeQuery(Position.class); - if (positions.isEmpty()) { - return null; - } else { - return positions.iterator().next(); - } - } - public Position getPrecedingPosition(long deviceId, Date date) throws SQLException { Collection positions = QueryBuilder.create(dataSource, getQuery("database.selectPrecedingPosition")) .setLong("deviceId", deviceId) diff --git a/src/main/java/org/traccar/handler/FilterHandler.java b/src/main/java/org/traccar/handler/FilterHandler.java index 5287a7639..6331ff773 100644 --- a/src/main/java/org/traccar/handler/FilterHandler.java +++ b/src/main/java/org/traccar/handler/FilterHandler.java @@ -153,7 +153,6 @@ public class FilterHandler extends BaseDataHandler { StringBuilder filterType = new StringBuilder(); - // general filtering if (filterInvalid(position)) { filterType.append("Invalid "); } @@ -173,7 +172,6 @@ public class FilterHandler extends BaseDataHandler { filterType.append("Static "); } - // relative filtering long deviceId = position.getDeviceId(); if (filterDuplicate || filterDistance > 0 || filterMaxSpeed > 0 || filterMinPeriod > 0) { Position preceding = null; -- cgit v1.2.3 From a6ccfd679686cfb62f8390334229ba7fdf92eb42 Mon Sep 17 00:00:00 2001 From: soshial Date: Mon, 13 Sep 2021 21:42:56 +0200 Subject: fix SQL syntax in XML --- setup/default.xml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'setup') diff --git a/setup/default.xml b/setup/default.xml index 419eaef45..e427d05e5 100644 --- a/setup/default.xml +++ b/setup/default.xml @@ -49,7 +49,7 @@ - SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime lte :time ORDER BY fixTime DESC LIMIT 1 + SELECT * FROM tc_positions WHERE deviceId = :deviceId AND fixTime <= :time ORDER BY fixTime DESC LIMIT 1 -- cgit v1.2.3