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 ++++ src/main/java/org/traccar/config/Keys.java | 16 ++++- .../java/org/traccar/database/DataManager.java | 36 ++++++++++ .../java/org/traccar/handler/FilterHandler.java | 79 ++++++++++++++++------ 4 files changed, 120 insertions(+), 24 deletions(-) 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; diff --git a/src/main/java/org/traccar/config/Keys.java b/src/main/java/org/traccar/config/Keys.java index 6d2296fb1..f7d61a993 100644 --- a/src/main/java/org/traccar/config/Keys.java +++ b/src/main/java/org/traccar/config/Keys.java @@ -887,8 +887,19 @@ public final class Keys { Collections.singletonList(KeyType.GLOBAL)); /** - * Time limit for the filtering in seconds. If the time difference between last position and a new one is more than - * this limit, the new position will not be filtered out. + * If FALSE, then filter compares duplicates, distance or period only with the last received location. + * Server expects all locations to come sequentially. + * + * 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. + */ + public static final ConfigKey FILTER_RELATIVE = new ConfigKey<>( + "filter.relative", + Collections.singletonList(KeyType.GLOBAL)); + + /** + * Time limit for the filtering in seconds. If the time difference between the last position was received by server + * and a new position is received by server is more than this limit, the new position will not be filtered out. */ public static final ConfigKey FILTER_SKIP_LIMIT = new ConfigKey<>( "filter.skipLimit", @@ -896,6 +907,7 @@ public final class Keys { /** * Enable attributes skipping. Attribute skipping can be enabled in the config or device attributes. + * If position contains any attribute mentioned in "filter.skipAttributes" config key, position is not filtered out. */ public static final ConfigKey FILTER_SKIP_ATTRIBUTES_ENABLE = new ConfigKey<>( "filter.skipAttributes.enable", diff --git a/src/main/java/org/traccar/database/DataManager.java b/src/main/java/org/traccar/database/DataManager.java index 15137ad91..3f702f78a 100644 --- a/src/main/java/org/traccar/database/DataManager.java +++ b/src/main/java/org/traccar/database/DataManager.java @@ -328,6 +328,42 @@ 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 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")) + .setLong("deviceId", deviceId) + .setDate("time", date) + .executeQuery(Position.class); + if (positions.isEmpty()) { + return null; + } else { + return positions.iterator().next(); + } + } + public void updateLatestPosition(Position position) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.updateLatestPosition")) .setDate("now", new Date()) diff --git a/src/main/java/org/traccar/handler/FilterHandler.java b/src/main/java/org/traccar/handler/FilterHandler.java index 7cd9153c1..817d6c8ba 100644 --- a/src/main/java/org/traccar/handler/FilterHandler.java +++ b/src/main/java/org/traccar/handler/FilterHandler.java @@ -25,6 +25,9 @@ import org.traccar.config.Keys; import org.traccar.helper.UnitsConverter; import org.traccar.model.Position; +import java.sql.SQLException; +import java.util.Date; + @ChannelHandler.Sharable public class FilterHandler extends BaseDataHandler { @@ -40,6 +43,7 @@ public class FilterHandler extends BaseDataHandler { private int filterDistance; private int filterMaxSpeed; private long filterMinPeriod; + private boolean filterRelative; private long skipLimit; private boolean skipAttributes; @@ -54,14 +58,15 @@ public class FilterHandler extends BaseDataHandler { filterDistance = config.getInteger(Keys.FILTER_DISTANCE); filterMaxSpeed = config.getInteger(Keys.FILTER_MAX_SPEED); filterMinPeriod = config.getInteger(Keys.FILTER_MIN_PERIOD) * 1000; + filterRelative = config.getBoolean(Keys.FILTER_RELATIVE); skipLimit = config.getLong(Keys.FILTER_SKIP_LIMIT) * 1000; skipAttributes = config.getBoolean(Keys.FILTER_SKIP_ATTRIBUTES_ENABLE); } private boolean filterInvalid(Position position) { return filterInvalid && (!position.getValid() - || position.getLatitude() > 90 || position.getLongitude() > 180 - || position.getLatitude() < -90 || position.getLongitude() < -180); + || position.getLatitude() > 90 || position.getLongitude() > 180 + || position.getLatitude() < -90 || position.getLongitude() < -180); } private boolean filterZero(Position position) { @@ -144,20 +149,13 @@ public class FilterHandler extends BaseDataHandler { StringBuilder filterType = new StringBuilder(); - Position last = null; - if (Context.getIdentityManager() != null) { - last = Context.getIdentityManager().getLastPosition(position.getDeviceId()); - } - + // general filtering if (filterInvalid(position)) { filterType.append("Invalid "); } if (filterZero(position)) { filterType.append("Zero "); } - if (filterDuplicate(position, last) && !skipLimit(position, last) && !skipAttributes(position)) { - filterType.append("Duplicate "); - } if (filterFuture(position)) { filterType.append("Future "); } @@ -167,19 +165,56 @@ public class FilterHandler extends BaseDataHandler { if (filterApproximate(position)) { filterType.append("Approximate "); } - if (filterStatic(position) && !skipLimit(position, last) && !skipAttributes(position)) { - filterType.append("Static "); - } - if (filterDistance(position, last) && !skipLimit(position, last) && !skipAttributes(position)) { - filterType.append("Distance "); - } - if (filterMaxSpeed(position, last)) { - filterType.append("MaxSpeed "); - } - if (filterMinPeriod(position, last)) { - filterType.append("MinPeriod "); - } + 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 "); + } + + 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 "); + } + if (filterMaxSpeed(position, previous) || filterMaxSpeed(next, position)) { + filterType.append("MaxSpeed "); + } + 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 (filterDuplicate(position, last) && !skipLimit(position, last) && !skipAttributes(position)) { + filterType.append("Duplicate "); + } + if (filterStatic(position) && !skipLimit(position, last) && !skipAttributes(position)) { + filterType.append("Static "); + } + if (filterDistance(position, last) && !skipLimit(position, last) && !skipAttributes(position)) { + filterType.append("Distance "); + } + if (filterMaxSpeed(position, last)) { + filterType.append("MaxSpeed "); + } + if (filterMinPeriod(position, last)) { + filterType.append("MinPeriod "); + } + } if (filterType.length() > 0) { StringBuilder message = new StringBuilder(); -- 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(-) 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(-) 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(-) 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(-) 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 From c345945c36b4c93f2f436cf61f89495577d072ea Mon Sep 17 00:00:00 2001 From: soshial Date: Sat, 18 Sep 2021 21:15:24 +0200 Subject: improve SQL query --- src/main/java/org/traccar/database/DataManager.java | 9 ++------- src/main/java/org/traccar/handler/FilterHandler.java | 2 +- 2 files changed, 3 insertions(+), 8 deletions(-) diff --git a/src/main/java/org/traccar/database/DataManager.java b/src/main/java/org/traccar/database/DataManager.java index 15c67cb74..75ec70ea7 100644 --- a/src/main/java/org/traccar/database/DataManager.java +++ b/src/main/java/org/traccar/database/DataManager.java @@ -329,15 +329,10 @@ public class DataManager { } public Position getPrecedingPosition(long deviceId, Date date) throws SQLException { - Collection positions = QueryBuilder.create(dataSource, getQuery("database.selectPrecedingPosition")) + return QueryBuilder.create(dataSource, getQuery("database.selectPrecedingPosition")) .setLong("deviceId", deviceId) .setDate("time", date) - .executeQuery(Position.class); - if (positions.isEmpty()) { - return null; - } else { - return positions.iterator().next(); - } + .executeQuerySingle(Position.class); } public void updateLatestPosition(Position position) throws SQLException { diff --git a/src/main/java/org/traccar/handler/FilterHandler.java b/src/main/java/org/traccar/handler/FilterHandler.java index 6331ff773..19d72d8a1 100644 --- a/src/main/java/org/traccar/handler/FilterHandler.java +++ b/src/main/java/org/traccar/handler/FilterHandler.java @@ -74,7 +74,7 @@ public class FilterHandler extends BaseDataHandler { } private boolean filterDuplicate(Position position, Position last) { - if (filterDuplicate && last != null && position.getFixTime().equals(last.getFixTime())) { + if (filterDuplicate && last != null && position.getFixTime().getTime() == last.getFixTime().getTime()) { for (String key : position.getAttributes().keySet()) { if (!last.getAttributes().containsKey(key)) { return false; -- cgit v1.2.3 From 83cff6dd3876fa81d3f3257f1fb3f72cbdf38e70 Mon Sep 17 00:00:00 2001 From: soshial Date: Tue, 28 Sep 2021 11:51:38 +0200 Subject: shorten IF clause --- src/main/java/org/traccar/handler/FilterHandler.java | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/main/java/org/traccar/handler/FilterHandler.java b/src/main/java/org/traccar/handler/FilterHandler.java index 19d72d8a1..6331ff773 100644 --- a/src/main/java/org/traccar/handler/FilterHandler.java +++ b/src/main/java/org/traccar/handler/FilterHandler.java @@ -74,7 +74,7 @@ public class FilterHandler extends BaseDataHandler { } private boolean filterDuplicate(Position position, Position last) { - if (filterDuplicate && last != null && position.getFixTime().getTime() == last.getFixTime().getTime()) { + if (filterDuplicate && last != null && position.getFixTime().equals(last.getFixTime())) { for (String key : position.getAttributes().keySet()) { if (!last.getAttributes().containsKey(key)) { return false; -- cgit v1.2.3 From 29c63d9bf48623ef11e89399e4c7c0e09bf1c446 Mon Sep 17 00:00:00 2001 From: soshial Date: Mon, 27 Dec 2021 15:38:46 +0400 Subject: revert to the original filtering logic --- src/main/java/org/traccar/handler/FilterHandler.java | 17 ++++++----------- 1 file changed, 6 insertions(+), 11 deletions(-) diff --git a/src/main/java/org/traccar/handler/FilterHandler.java b/src/main/java/org/traccar/handler/FilterHandler.java index 6331ff773..e8cafabc8 100644 --- a/src/main/java/org/traccar/handler/FilterHandler.java +++ b/src/main/java/org/traccar/handler/FilterHandler.java @@ -147,12 +147,9 @@ public class FilterHandler extends BaseDataHandler { private boolean filter(Position position) { - if (skipAttributes(position)) { - return false; - } - StringBuilder filterType = new StringBuilder(); + // filter out invalid data if (filterInvalid(position)) { filterType.append("Invalid "); } @@ -168,10 +165,8 @@ public class FilterHandler extends BaseDataHandler { if (filterApproximate(position)) { filterType.append("Approximate "); } - if (filterStatic(position)) { - filterType.append("Static "); - } + // filter out excessive data long deviceId = position.getDeviceId(); if (filterDuplicate || filterDistance > 0 || filterMaxSpeed > 0 || filterMinPeriod > 0) { Position preceding = null; @@ -186,13 +181,13 @@ public class FilterHandler extends BaseDataHandler { } else { preceding = getLastReceivedPosition(deviceId); } - if (skipLimit(position, preceding)) { - return false; - } if (filterDuplicate(position, preceding)) { filterType.append("Duplicate "); } - if (filterDistance(position, preceding)) { + if (filterStatic(position) && !skipLimit(position, preceding) && !skipAttributes(position)) { + filterType.append("Static "); + } + if (filterDistance(position, preceding) && !skipLimit(position, preceding) && !skipAttributes(position)) { filterType.append("Distance "); } if (filterMaxSpeed(position, preceding)) { -- cgit v1.2.3 From 41453ca2ee76372a031ea206c264c1c7cde58ee6 Mon Sep 17 00:00:00 2001 From: soshial Date: Mon, 27 Dec 2021 22:16:08 +0400 Subject: filtering logic: fix static --- src/main/java/org/traccar/handler/FilterHandler.java | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/main/java/org/traccar/handler/FilterHandler.java b/src/main/java/org/traccar/handler/FilterHandler.java index e8cafabc8..049512765 100644 --- a/src/main/java/org/traccar/handler/FilterHandler.java +++ b/src/main/java/org/traccar/handler/FilterHandler.java @@ -168,7 +168,7 @@ public class FilterHandler extends BaseDataHandler { // filter out excessive data long deviceId = position.getDeviceId(); - if (filterDuplicate || filterDistance > 0 || filterMaxSpeed > 0 || filterMinPeriod > 0) { + if (filterDuplicate || filterStatic || filterDistance > 0 || filterMaxSpeed > 0 || filterMinPeriod > 0) { Position preceding = null; if (filterRelative) { try { -- cgit v1.2.3