Question

Merging features on less than datetime AND attribute value

  • 31 December 2018
  • 2 replies
  • 0 views

I work in local government and I am constructing a BI report using telematics data from refuse collection vehicles combined with weighbridge data from our waste disposal partners to analyse waste composition from residential waste in specific geographic locations.

Our telematics system on our refuse collection fleet can be used to generate reports on the location of every lifted residential bin including datetime, vehicle registration and the WGS84 latitude and longitude.

Vehicles are unloaded at the waste disposal plant several times a day, providing a ticket number along with the weight, type and quality of the individual load which we receive as via email on a regular basis. This includes the vehicle registration and the datetime.

Complicating matters; vehicles are often swapped between teams depending on queues and breakdowns, can assist other times and vice versa, and spare vehicles are sometimes used. This means it is not adequate to simply match the daily summary total of bin lifts to a date and vehicle.

My solution to this is to join each telematics record of a bin being lifted to the specific ticket from the weighbridge for when the vehicle was next emptied, which over time will allow for spatial analysis indicating specific areas where non-recycling waste is being put into the recycling collection.

In short, I want the point data for each specific bin lift to be joined with the ticket number from the load its contents were tipped with.

My main issues are joins based on multiple criteria, joins based on less-than datetimes, and how to combine the two.

Any help with the join would be greatly appreciated.

 

Telematics Data:

Datetime, Vehicle Registration, Latitude, Longitude

 

Weighbridge Data:

Datetime, Vehicle Registration, Weight, Quality


2 replies

Badge +7

Hi @williamjmartin8,

 

Thanks for posting your interesting data problem!

If you're familiar with SQL you should be able to make use of the InlineQuerier to create complex joins (eg. using JOIN statements or multiple WHERE clauses). Alternatively sometimes stringing together multiple FeatureJoiners or FeatureMergers can achieve a similar join. Also, don't forget that we also have powerful filtering transformers such as the Tester, TestFilter, and AttributeFilter. It's possible to set multiple test clauses in these transformers, so you might be able to combine filters and joins to achieve your end result.

 

 

For an overview of all our spatial and non-spatial joining transformers, head over to the following to Knowledge Centre articles:

 

 

- https://knowledge.safe.com/articles/83808/performing-spatial-joins-and-merges.html

- https://knowledge.safe.com/articles/34619/working-with-database-transformers-1.html

 

Hope this helps!

 

Nathan
Badge +10

I'd probably go with an inline querier for this, although unsure about the performance over a larger dataset and the sql is a bit tricky to get right and it's easier to convert to FME datetime first than get the correct syntax in the inlinequerier imo

 

This should take the telemetrics data and merges the correct ticket number/weighbridge info onto this data

SELECT t1.*, t2."Ticket Number", t2.Quality,
 (select "Weighbridge Time"
  from "Weighbridge_info" t2 where t2."Truck"=t1."Vehicle"
  and "Weighbridge Time"-"DateTime" > 0
  ORDER BY "Weighbridge Time"-"DateTime" asc
  limit 1) as t2_nearest_date
from "Telematics" t1,
"Weighbridge_info" t2
where t2_nearest_date = t2."Weighbridge Time"
and t1.Vehicle = t2.Truck


 

Merging_less_than_inline.fmwt

 

Reply